Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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:

SELECT
    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)
WHERE 
 s.spid > 50
ORDER BY
    DatabaseName
,   loginName
share|improve this question

migrated from stackoverflow.com 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
2  
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 Serverfault.com 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

1 Answer 1

up vote 3 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
1  
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

 
discard

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.