question

leonardustono avatar image
leonardustono asked

monitoring sql server queue process

Hi there, i have a case where my sql server suddenly run slowly when my cpu performance still 60%. i monitored sql server with DMV and sp_who2 but i think, all of this tools is not enough. i tried kill one spid that consumed the highest total cpu_time, but my system was not be better. i curious, may there is any queueing process in sql server? if yes, can you help me to give a suggestion for monitoring that queueing process? Thank you very much for your help. best regards, Leo
sql-server-2008-r2queue
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
It's possible for the queries to run slow and never see CPU go up. You can also be blocked on memory and disk I/O. To see how processes are behaving in a SQL Server 2005 or better environment, rather than run sp_who2, you should be using the Dynamic Management Objects (DMO). To see activity as it's occurring you look at sys.dm_exec_requests. In that you'll see not only if a process is blocked and if so, which process is blocking it, but you'll see precisely what any active process is waiting on and what it last waited on. These are in the wait_type and Last_wait_type columns. You can also see how long a process has been waiting, wait_time. Then you can combine the information in this DMO with others such as sys.dm_exec_sql_text and sys.dm_exec_query plan to see the actual statement and execution plan for the queries in question. This is how to determine why a system is running slow at a given point in time. For details on how exactly to use the DMOs, I suggest getting a copy of Louis Davidson and Tim Ford's book, Performance Tuning with SQL Server Dynamic Management Views. You can [download a copy of it for free][1] (or [purchase a print copy][2]). [1]: http://www.red-gate.com/community/books/dynamic-management-views [2]: http://www.amazon.com/Performance-Tuning-Server-Dynamic-Management/dp/1906434476
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.