About myself and everyone else.
If you are a DBA I’m sure you might have looked into the dmv sys.dm_os_wait_stats.
Similar to any good worker, when SQL isn’t working it’s waiting. This view is one of my favorites to look at what SQL is waiting on. The assumption is that if SQL is waiting for something it is spending CPU ticks doing nothing i.e. waiting instead of doing the work that you gave to it. The story goes then that if you remove whatever SQL is waiting for you will see an improvement in performance, remember this can be many things. Improvement can be RAM used more optimally than before. I could be lower disk queues to it could be faster response times for the user.
There are several quick fix wait types that you can spot in this dmv. The ones I’m going to look at now is the latches and IO related ones. So lets say that in the top5 you can see several IO latch waits as common. Where to now. You can just go to your disk and see if the best practise steps have been taken but for this lesson let’s assume it’s already done. If you have terabytes of data or a couple of hundred systems this might lead to some guessing, which is usually not good to go by.
The next dmv sys.dm_db_index_operational_stats helps you with providing more detail.
We know our SQL server is waiting on latches, but why and which table.
select db_name(database_id),object_name(object_id,database_id),rank()over (partitionby@@servernameorderby a.page_io_latch_wait_in_ms desc)As page_io_latch_wait_in_ms_rank,rank()over (partitionby@@servernameorderby a.page_latch_wait_in_ms desc)As page_latch_wait_in_ms ,rank()over (partitionby@@servernameorderby a.row_lock_wait_in_ms desc)As row_lock_wait_in_ms,rank()over (partitionby@@servernameorderby a.page_lock_wait_in_ms desc)As page_lock_wait_in_ms, a.*fromsys.dm_db_index_operational_stats(null,null,null,null) aorderby a.page_io_latch_wait_in_ms desc
You can find all kinds of interesting things from this query.
I’ve used rank to show my the values of each table for each database relative to the other tables on the server. You can easily modify this in order to show the tables for only on database but as I’m concerned with all the databases as I’m experiencing a server wide issue , thus the scope for the rank is servername.
In my particular problem I found that I’ve got a single table that is causing 90% of the waits of my SQL server and since according to my view sys.dm_os_wait_stats mentions the IOLATCH waits in top 5, I’m almost guaranteed that by rectifying whatever is causing this table to use 90% of the wait time. I’d see better performance.
The next question, why is this table which is not even the biggest table on the system using most of the waiting time.