2

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
Aplato
  • 23
  • 1
  • 4
  • possible duplicate of [What is your favorite SQL Administrative or META Queries?](http://stackoverflow.com/questions/5095123/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](http://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 Answers1

4

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
mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • 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