Guiding principles of wait time analysis

12 February,2017 by Jack Vamvas

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

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Guiding principles of wait time analysis


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer