What is SQL Server Query Tuning?

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. 

What is Query tuning?

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

DBA Strategy: The 80/20 rule

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.

Diagnosing a SQL Server Problem Method

1) Ask the right question

2) Drilling into the problem

3)  A set based methodology . Repeatable and provable. Document everything as you go along

An example:

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
6.Tune indexes/queries

Other considerations

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

Read More

SSMS query tuning – simulate the application - SQL Server DBA

The future of the SQL Server Performance Tuning ... - SQL Server DBA

SQL Server Rapid Tuning - SQL Server DBA

SQL Server – How to troubleshoot a slow running query