SQL Server Scripts

Troubleshooting

Whats Running?

Shows what queires are currently running on the server

                
BEGIN
    -- Do not lock anything, and do not get held up by any locks.
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    -- What SQL Statements Are Currently Running?
    SELECT [Spid] = session_Id
	, ecid
	, [Database] = DB_NAME(sp.dbid)
	, [User] = nt_username
	, [Status] = er.status
	, [Wait] = wait_type
	, [Individual Query] = SUBSTRING (qt.text, 
             er.statement_start_offset/2,
	(CASE WHEN er.statement_end_offset = -1
	       THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
		ELSE er.statement_end_offset END - 
                                er.statement_start_offset)/2)
	,[Parent Query] = qt.text
	, Program = program_name
	, Hostname
	, nt_domain
	, start_time
    FROM sys.dm_exec_requests er
    INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
    CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
    WHERE session_Id > 50              -- Ignore system spids.
    AND session_Id NOT IN (@SPID)     -- Ignore this current statement.
    ORDER BY 1, 2
END
        
        

Whats Blocking?

Shows what queires currently blocking/being blocked

  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- If there are blocked processes...
IF EXISTS(SELECT 1 FROM sys.sysprocesses WHERE 
  blocked != 0 AND spid != blocked) 
BEGIN
  -- Identify the root-blocking spid(s)
  SELECT  distinct t1.spid  AS [Root blocking spids]
     , t1.[loginame] AS [Owner]
     --, master.dbo.dba_GetSQLForSpid(t1.spid) AS 'SQL Text' 
     , t1.[cpu]
     , t1.[physical_io]
     , DatabaseName = DB_NAME(t1.[dbid])
     , t1.[program_name]
     , t1.[hostname]
     , t1.[status]
     , t1.[cmd]
     , t1.[blocked]
     , t1.[ecid] 
  FROM  sys.sysprocesses t1, sys.sysprocesses t2
  WHERE t1.spid = t2.blocked
    AND t1.ecid = t2.ecid
    AND t1.blocked = 0 
  ORDER BY t1.spid, t1.ecid
  -- Identify the spids being blocked.
  SELECT t2.spid AS 'Blocked spid'
     , t2.blocked AS 'Blocked By'
     , t2.[loginame] AS [Owner]
     --, master.dbo.dba_GetSQLForSpid(t2.spid) AS 'SQL Text' 
     , t2.[cpu]
     , t2.[physical_io]
     , DatabaseName = DB_NAME(t2.[dbid])
     , t2.[program_name]
     , t2.[hostname]
     , t2.[status]
     , t2.[cmd]
     , t2.ecid
  FROM sys.sysprocesses t1, sys.sysprocesses t2 
  WHERE t1.spid = t2.blocked
    AND t1.ecid = t2.ecid
  ORDER BY t2.blocked, t2.spid, t2.ecid
END
ELSE -- No blocked processes.
  -- SELECT 'No processes blocked.' AS 'BlockStatus'
  PRINT 'No processes blocked.'
SELECT   r.session_id,
         --r.request_id,
         DB_NAME(r.database_id) AS [database_name],
         s.login_name,
         r.[status],
         SCHEMA_NAME(o.[schema_id]) + '.' + o.name AS [object_name],
         CAST('' + 
        REPLACE(REPLACE(REPLACE( CASE WHEN r.statement_start_offset/2 > LEN(st.[text])
        THEN st.[text]
        ELSE SUBSTRING ( st.[text],
        r.statement_start_offset/2, 
        (  CASE WHEN r.statement_end_offset = -1 
        THEN LEN(CONVERT(NVARCHAR(MAX), st.[text])) * 2 
        ELSE r.statement_end_offset
        END - r.statement_start_offset
        )/2
       )
       END,
              '<','[lt]'),'>','[gt]'),'&','[amp]')+ '' AS XML) AS [QueryAsXML],
         qp.query_plan,
         r.start_time,
         r.blocking_session_id,
         r.wait_type,
         r.wait_resource,
         r.wait_time,
         r.last_wait_type,
         CASE s.transaction_isolation_level
              WHEN 0 THEN 'Unspecified'
              WHEN 1 THEN 'Read Uncomitted'
              WHEN 2 THEN 'Read Committed'
              WHEN 3 THEN 'Repeatable'
              WHEN 4 THEN 'Serializable'
              WHEN 5 THEN 'Snapshot'
              ELSE '(Unknown)'
         END AS [isolation_level],
         r.cpu_time,
         r.total_elapsed_time,
         r.reads,
         r.writes,
         r.total_elapsed_time,
         s.[host_name],
         s.[program_name],
         c.connect_time,
         c.client_net_address
/*
         st.[text] AS [ParentQuery],
         DATALENGTH(st.[text]) AS [ModuleSize],
         s.original_login_name,
         s.nt_domain,
         s.nt_user_name,
         s.[status],
         r.command,
*/
FROM     sys.dm_exec_requests r
INNER JOIN
         sys.dm_exec_sessions s
         ON  s.session_id = r.session_id
CROSS APPLY
         sys.dm_exec_sql_text(r.[sql_handle]) st
CROSS APPLY
         sys.dm_exec_query_plan(r.plan_handle) qp
LEFT OUTER JOIN
         sys.objects o
         ON  o.[object_id] = st.objectid
LEFT OUTER JOIN
         sys.dm_exec_connections c
         ON  c.connection_id = r.connection_id       
WHERE    st.[text] IS NOT NULL
AND      s.session_id <> @SPID
ORDER BY DB_NAME(r.database_id),
         CASE WHEN s.login_name = 'sa' THEN 1 ELSE 2 END,
         s.login_name,
         r.session_id
GO
sp_who2 active