Guiding principles of wait time analysis

Question: Could you send me some information about the guiding principles of wait time analysis?

Answer: Wait time analysis for SQL Server performance tuning is a methodology covering a wide range of problem sets.

But at its core are some guiding principles.

1) Identify what the impact is on the end user

2) The main focus is on aiming to to make a positive impact on the application end user. This is in contrast to aiming to deliver a better cache hit ratio or cpu usage etc. This doesn’t mean that cache hit ratio and cpu usage are not important – but they are goals to make a positive impact on the end user

3) Identify factors causing the largest impact . Of course there are thousands\endless fascinating technical issues , but aim to aggregate the delays.

4) Wait time accumulation is main indicator for performance tuning. As opposed to overall server performance and queue counts, these can easily lead to false assumptions

5) Wait time analysis gets atomic by reporting on every step of a sql execution . The focus is on the steps taking the longest – and trying to understand why it’s taking the longest

Read more on the nuts and bolts of wait time analysis

 SQL Server - Perfmon ,Waits and Queues

SQL Server – Pageiolatch_sh and how to reduce it

SQL WAIT TYPE – RESOURCE SEMAPHORE and how to reduce it ...

SQL Server RUNNABLE – SUSPENDED – RUNNING status model ...

SQL Server - Query Response Time measure