The dashed line plots the response time when using the SCALE_SLOW
index. It grows by up to 32 seconds if there are 25 queries running at the same time. In comparison to the response time without background load—as it might be the case in your development environment—it takes 30 times as long. Even if you have a full copy of the production database in your development environment, the background load can still cause a query to run much slower in production.
The solid line shows the response time using the SCALE_FAST
index—it does not have any filter predicates. The response time stays well below two seconds even if there are 25 queries running concurrently.
Careful execution plan inspection yields more confidence than superficial benchmarks.
A full stress test is still worthwhile—but the costs are high.
Suspicious response times are often taken lightly during development. This is largely because we expect the “more powerful production hardware” to deliver better performance. More often than not it is the other way around because the production infrastructure is more complex and accumulates latencies that do not occur in the development environment. Even when testing on a production equivalent infrastructure, the background load can still cause different response times. In the next section we will see that it is in general not reasonable to expect faster responses from “bigger hardware”.
Article “Latency: Security vs. Performance” about latencies in complex infrastructures.
Article: “We are experiencing too much load. Let’s add a new server” by Jams Golick.
You can’t learn everything in one day. Subscribe the newsletter via E-Mail, Bluesky or RSS to gradually catch up. Have a look at modern-sql.com as well.
The essence of SQL tuning in 200 pages
Buy now!
(paperback and/or PDF)
Paperback also available at Amazon.com.
Markus offers SQL training and consulting for developers working at companies of all sizes.
Learn more »