Optimizing CPU usage for this query

AliRazeghi 2016-07-08 21:05:28

I have seen a query that is called tens of millions of times a day cause CPU pressure. It's a relatively benign query calling in data from a view that joins several tables together. 1 table has maybe 80k rows and the other ones are small. I was able to improve CPU usage a bit with adding indexes but I'm curious what else I can do to help the devs and have this code not use such a expensive hash match, or any other ideas you might have. I'm an ops guy but this query could cause issues so I wanted to optimize the CPU usage as much as possible, even by modifying/adding indexes. Thanks.

Aaron Bertrand 2016-07-08 21:07:12
Hi Ali, heading out but just wanted to point you to this resource, which is a good starting point for general CPU performance troubleshooting: http://sqlperformance.com/2013/05/io-subsystem/cpu-troubleshooting
AliRazeghi 2016-07-08 21:19:55
Hi Aaron, thank you for the link! Since this query is called millions of times a day and I see it as taking up the most CPU resources out of all of my queries by manifolds I was focusing on this particular query for the time being.

This is a great article though on how to properly diagnose CPU tuning and finding the root cause. I'm sure I'll be re-reading it again very soon. Just finished reviewing it.

Aaron Bertrand 2016-07-13 02:49:19
Ali, it is hard to comment because other than the join diagram there is no way to see what this query is actually doing. It seems you are executing a stored procedure that selects a single column from a view, but there is no visibility into the view definition (anonymization doesn't help here). I see a couple of hash matches with mismatched estimates which suggest off the cuff that there is still some room for index improvement.