What's the best way to find the SQL statements you need to tune? Here are some of the things we do (and, as we get more knowledgeable, we grow and continue to improve our processes). This is not an endorsement for a particular set of tools, but what appears to work for us currently.
1. Third Party tools:
• We use SolarWinds DPA, as it appears more geared towards query analysis and performance than some of the other DB monitoring tools we evaluated. Other monitoring tools may do a better job of overall server monitoring, but all the developers voted for this tool.
• SQL Sentry Plan Explorer is both a free and paid-license tool for more in-depth execution plans than what is provided in SSMS.
2. Books... and lots of them! (SQL Server Execution Plans (first and second editions) by Grant Fritchey, for instance).
3. Overall system monitoring:
• Brent Ozar and his FindMissingIndexes queries: We check when the last time the server was rebooted... if it has been more than 3 days, we run the query weekly against all databases and put the results into a table, then periodically run analysis on the cumulative results to see if there are any potential indexes we might add to improve performance. We then use RANK() OVER (ORDER BY Impact/CntOccur DESC) to try and find missing indexes, start doing an analysis.
We are using the older code found here:
but I see there have been improvements that we will need to check out:
• Plus the corollary, finding "un-used" indexes based on reads and writes.
Then, of course, there are the customer inputs... "Hey... my query is running slow" or "My system is not responding" calls we get.
Argue for your limitations, and sure enough they're yours (Richard Bach, Illusions)