OK, a member of the application team rushes towards me and says “the database server is slow due to blocking” or “everything has gone really slow” etc. As a DBA ,I’m quite often beset upon by application owners – and answers are needed quickly.
One tool at my disposal is the dynamic management view - sys.dm_os_waiting_tasks. This will return information about the wait queue of tasks awaiting resource usage. Most importantly, this will display the list for this current time.
The thing I like about this dmv is it brings a performance problem down to its bare essentials. i.e server – application – users relationship.
If to many tasks are waiting then consideration is given to questions, such as:
1)Is the application user base greater than expected?
2)Is the SQL Server performance substandard?
3)Is the database size greater than originally specified?
4)Should more resources be allocated?
In a future blog, I’ll expand on how you can use this information to better analyse the processes.
Republished from http://www.sqlserver-dba.com.
Republished from SQL Server DBA [65 clicks].
Read the original version here [32134 clicks].