SQL Performance tuned!
Done! But wait… Why can’t I feel a (damn) difference?!
I ran into an issue recently.
After doing (an incredible!) job at tuning SQL Server performance, I received this note from the client:
“The changes are made, but we don’t feel the difference”
*shortened to distill down to the point
Which… umhh… really bothers me…
If I die this week, this probably had something to do with it… Nah, just kidding…
The real problem
If I can’t explain the benefit of our SQL performance tuning services, that loses a ton of value. So, the problem is on my end.
He then proceeded to say:
“The stats show a very large percentage improvement but what is the tangible results that you can point us to?
Want to learn to better understand the value provided.”
I sent him this below (I plan to point clients here in case this happens again).
The only way to accurately measure T-SQL query performance is with BEFORE and AFTER numbers.
And it’s not by looking using SQL Server Management Studio right bottom corner to show how long the query ran for. DON’T use that method.
Since I touched on that, let’s see why.
If I leave work and start driving home, how long will it take? 15min? Or 60 min? If you live anywhere where there is traffic – you know that’s a big unknown.
Same with the SQL.
You can query now. It may take 60 seconds. Run again and it completes in 15 sec. The only difference – the load on the server was different. Different things were running while you ran yours.
Add the other components your query travels through – from the disk on the DB server you’re querying down to your screen – and you will see that all of these components may be doing different things at any given second.
How to measure query performance properly?
Here are a few ways:
- Method 1 – SET STATISTICS IO, TIME ON
- Method 2 – SQL Server Profiler
- Method 3 – Extended Events
Let’s start with the simplest – Method 1.
Simply add SET STATISTICS IO, TIME ON above your query and then don’t look at the SSMS’s bottom right corner for timing.
Look at the output this command shows. I am using AdventuresWorks DB.
Let’s run this query:
SELECT oh.CustomerID
,SUM(LineTotal)
FROM Sales.SalesOrderDetail od
JOIN Sales.SalesOrderHeader oh ON od.SalesOrderID = oh.SalesOrderID
GROUP BY oh.CustomerID
Things to notice:
1. Query time in SSMS shown as zero. Wait… it is free then? So if I ran 100K of these per minute, it would still be free? Of course not.
2. Logical reads – this is the metric we want! This shows how many read operations SQL had to “consume” from the disk drive (ignore SSDs, RAIDs, Azure, or AWS for simplicity, the disk acts differently there, but the idea is still the same). Why? 95% of SQL Servers out there bottleneck on storage or disk drive throughput. So if you tune the slowest component, things will get better!
3. CPU time – how many CPU cycles did the query consume? Here, we want CPU time – which, in this case, shows 140 ms. SQL is indicating, “It took 125 ms to run this query”. The elapsed time is shown as 345 milliseconds.
This is where SQL says, “Since you’re not the only process running on the CPU and you compete with other processes like the OS or other programs, in reality, you had to wait 339 milliseconds to get your query back.”
Now that we have some gas, some utensils, and a grill – we cooking with gasoline! Let’s prove a point now.
Now I have my TRUE cost of the query:
CPU cost = 140ms
Storage cost = 1247+689 = 1,936
This is our BEFORE number. Remember that.
Now you spent countless hours on how to tune the same query and you came up with this cool method of indexed views.
Let’s see how that performs:
CPU cost = 0
Storage cost = 92
This is our AFTER number.
Conclusion of this tuning:
- CPU performance increased 140x
- Storage performance increased 92x (or 2,104% faster)
Can I hear a “WHOOOP….WHOOOP”?!
So for this client numbers looked like this.
Pretty darn good, wouldn’t you say?
Fix One Bottleneck, Find Another!
Tuning is a bit like a car driving through traffic.
If seven highway lanes become one, traffic throughput will never be greater than one lane. Never.
Once that single-lane problem is fixed, things will pile up at the next bottleneck! Which may be a spot with 2 lanes.
Once you remove one bottleneck, you find the next one. And the next one.
“Damn it… I thought, once I tune in, everything starts to fly” – you say… Nope. Not if you have a ton of things happening.
Often SQL tuning gets the job done and is enough. But in some rare cases, you discover another bottleneck. And all of a sudden it’s your application or web servers that are the bottleneck.
So until all roads are at 7 lanes, from point A to B, traffic simply won’t go at 7 lane speeds. Simple, right?
Back to the technical world…
There could be lots of things happening:
- It could be that the app does multiple queries to return a single screen and queries that were improved are just a small drop in the bucket of what the application actually needs to show that single screen.
- It could be multiple calls to DB. So we tune a few, but there are others left.
- It could be a slow network.
- It could be that an app is waiting for a slow client to consume the rows sent.
- It could bunch of other things.
The way to know is to measure from end to end.
In those cases database guy is the wrong person for this job.
There are tools for this or app instrumentation (look into how StackOverflow does it)… And database latency is just a small component.
All database guys can do is show you the results of tuning using BEFORE vs. AFTER numbers. (and not by showing duration in SSMS right bottom).
And in this case, client tuning numbers were very solid. And we got it done using the cheapest method – indexes.
Didnt even have to get into tuning T-SQL code or stored procs.
And don’t get used to 35,000% improvements. Those won’t last. It’s great that we got them. But it just means you got a ton of low-hanging fruit there. But often tuning of 200% is great.
It would be challenging to do DB tuning in any better way – I am not THE BEST, believe me, I know. But I also know I am good. Numbers don’t lie, so not gonna feel bad about saying it 🙂
So let’s come back to the original question “What is the benefit of tuning like this if you can’t “feel the benefit” then?”
- When queries consume fewer resources – server capacity increases.
- You can now handle more queries on the same hardware, at the same cost. The best companies out there tune on an ongoing basis, so worst-performing queries are handled periodically, capacity is increased, server hardware gets more life, and things are faster in general.
- Not to mention, your queries are now faster.
It pains me (feels like more than that) that the value is not seen. However, at the same time, I want you, the client, to be happy.
Speed is a feature. It took me a while to understand what that means. But reading how Stack Overflow treats their code, really helped. Lots of respect to Nick Craver there! I wish to be half as good as that guy!
Conclusion
So moral of the story… I feel like I provided an excellent service tuning.
And next time this issue is raised, I will point the person to this blog post and see if that helps.
I would love to hear how you handle issues like this. What could have been done differently or better?
Basically, tell me where I suck!
