After announcing the SQL Seminar series and SQL Health Check series, there has been a great response from them. I already have signed up assignments until December 2010 Mid Week for doing various health checks for different organizations. One thing that I noticed is that there’s something common and popular in many  health check services– the Wait Stats.
SQL Server Resource Wait Stats Analysis
Wait Stat Analysis is very crucial for optimizing databases, but it is often overlooked due to lack of understanding. We perform advanced resource Wait Statistics Analysis and provide you with suggestions to optimize your database server. We train your DBA to make them skillful enough so they can perform this analysis by themselves in the future.
It is quite a surprise that there is a lot of awareness on this subject across diverse organizations. Wait Stats and Wait Types can be very vital since they can help detect your server’s issue very quickly. I have previously written about Wait Types and Wait Stats here: SQL SERVER – What are Wait Types, Wait Stats and its Importance. I plan to start a series on the subject soon. If you want to learn more about them, you can follow the links mentioned in that article.
This question is for anybody who is responsible of performance tuning, that is, if they pay attention to wait stats:
“What Wait Stats can you see at the top most Wait Stats on your server?
You can run the following query to find out about your server’s Wait Stats:
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
GO
Send me a email or leave a comment here. Let us see if I can guide you with to optimize it. Leave your correct email address as I will only send email and not leave comment in open.
Reference: Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
Interesting need to read up on this….thank you
Hi Pinal,
Your blog is very use full,and i am using it for my day to day work.
One thing i have noticed is that your “Search” in your blog is not working ?
Thanks,
Gangadhar
Heres a snapshot of ours:
See anything strange?
Hi Pinal,
I have run the query
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
GO
and I got at top 1
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
LCK_M_U 38421007 6485427256 22393179 19923667
I have also checked in sys.dm_tran_locks
then it will show all the sessions with “s” requeset mode with ‘Grant ‘ request state.
What’s meaning of that, what should i need to check?
Hi Pinal,
I ran the query
SELECT *
FROM sys.dm_os_wait_stats
order by wait_time_ms desc
The results are as follows:
The top wait type was CLR_AUTO_EVENT
waiting_task_count = 6781
Wait_time_ms = 14257201750
Max_wait_time_ms = 83705968
Signal_wait_time_ms = 59234
Thank you
Steven
Hey Pinal, are you no perhaps longer offering the “SQL Health Check” service? The link above (to fails.