19 June,2011 by Jack Vamvas
I suggested to a client last week ,Query Tuning is required to troubleshoot some SQL performance issues. He wanted a definition of Query Tuning. I sent him an email , defining a Query Tuning approach .In summary.
In practise, there are predeployment queries and post deployment queries. Both require optimisation. The first type need to be optimised, the second type need to be optimised ONCE they are identified as causing the problem. Focus on problem queries. Lesson: SQL Performance tuning is Asking the right question
The 80/20 rule ,is it worth trying to spend 80% of your time to solve 20% of waits. A methodology is required to lead to systematically lead us to the main bottlenecks.
Ultimately the end user doesn't care about performance measures such as CPU counts, they are interested in getting a result. Typically , a wait for longer than 3 seconds is considered a performance issue.
3) A set based methodology . Repeatable and provable. Document everything as you go along
1.Analyse Waits at instance level
2.Match waits with queues
3.Conclusion & plan
4.Drill down to the db level
5.Drill down to the process level
1. Analyse waits at the instance level.
SQL 2005 use sys.dm_os_wait_stats ; SQL 2000 use DBCC SQLPERF(WAITSTATS) Focus on I/O, although there are other issues
2. Match waits with queries
use System monitor - e.g if most waits were I/O based use the different I/O queues
3. Determine course of action
What is the cause of the problem?
SQL Server Optimiser
SQL Server Instance/Database Configuration & Optimization
Design your db for performance
Set up an appropriate maintenance plan for data
Perfmon,Waits and Queues is a good approach to analysing SQL Performance problems and SQL Query Tuning. Use Waits for a quick and broad approach to the main delays - correlate with Perfmon - and then decide on a course of action.
Poor indexes and queries are not the only cause of performance problems. For example ,SQL Server – SOS_SCHEDULER_YIELD and how to reduce it . Using the approach above - will assist in diagnosing other causes