Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Recently our SQL Server is getting bogged down by something. I was wondering what is the best way to check what could be causing the problem by querying the database. This is the best I've found so far:

    SPID            = s.spid
,   BlockingSPID    = s.blocked
,   DatabaseName    = DB_NAME(s.dbid)
,   ProgramName     = s.program_name
,   [Status]        = s.[status]
,   LoginName       = s.loginame
,   ObjectName      = OBJECT_NAME(objectid, s.dbid)
,   [Definition]    = CAST([text] AS VARCHAR(MAX))
FROM      sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
 s.spid > 50
,   loginName
share|improve this question

migrated from Feb 24 '11 at 16:32

This question came from our site for professional and enthusiast programmers.

possible duplicate of What is your favorite SQL Administrative or META Queries? – JNK Feb 24 '11 at 16:04
If you use the database-administration tag, you should realize you're in the wrong place. This site is about programming - you need to go to for admin stuff - that's where the sysadmins and DBA's hang out... – marc_s Feb 24 '11 at 16:05
not duplicate, prior question was in wrong format. Last time i logged in i don't think server fault existed. – Aplato Feb 24 '11 at 21:32
up vote 4 down vote accepted

That's a good place to start. You should also be looking at the waits by using the sys.dm_os_waiting_tasks DMV which will tell you which resource is causing each process to wait.

select wait_type, sum(wait_duration_ms) sum_wait_duration_ms, avg(wait_duration_ms) avg_wait_duration_ms, count(*) waits
from sys.dm_os_waiting_tasks
group by wait_type
share|improve this answer
Could you please include (in your answer) a select statement that does this? If not, then how would you do this? – jftuga Feb 24 '11 at 18:50
I've added some code which you can use to find the wait types which are causing problems. – mrdenny Feb 24 '11 at 22:46

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.