paweltrader (1) [Avatar] Offline
#1
Can you please provide the code for dba_WhatSQLIsRunning Stored Procedure? It seems to be missing from Ch. 1
ian.stirk (27) [Avatar] Offline
#2
Re: Missing SP code for dba_WhatSQLIsRunning
Hi,

As described in Chapter 1, the code (and article describing usage) is available as follows:

dba_BlockTracer is here: http://visualstudiomagazine.com/features/article.aspx?editorialsid=2490

dba_WhatSQLIsRunning is here: http://www.sqlservercentral.com/articles/DMV/64425/

I've included the source code for both at the end of this message.

Thanks
Ian


CREATE PROC [dbo].[dba_BlockTracer]
AS
/*--------------------------------------------------

Purpose: Shows details of the root blocking process, together with details of any blocked processed

----------------------------------------------------

Parameters: None.

Revision History:
19/07/2007 Ian_Stirk@yahoo.com Initial version

Example Usage:
1. exec YourServerName.master.dbo.dba_BlockTracer

--------------------------------------------------*/

BEGIN

-- Do not lock anything, and do not get held up by any locks.
SET TRANSACTION ISOLATION LEVEL READ
UNCOMMITTED

-- If there are blocked processes...
IF EXISTS(SELECT 1 FROM sys.sysprocesses WHERE
blocked != 0)
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.
PRINT 'No processes blocked.'

END


CREATE PROC [dbo].[dba_WhatSQLIsExecuting]
AS
/*--------------------------------------------------------------------
Purpose: Shows what individual SQL statements are currently executing.
----------------------------------------------------------------------
Parameters: None.
Revision History:
24/07/2008 Ian_Stirk@yahoo.com Initial version
Example Usage:
1. exec YourServerName.master.dbo.dba_WhatSQLIsExecuting
---------------------------------------------------------------------*/
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
Aaron Nelson - SQLvariant (2) [Avatar] Offline
#3
Re: Missing SP code for dba_WhatSQLIsRunning
Ian,

The links in the book and in your post go to code that creates "dba_WhatSQLIsExecuting" (pg.9) but the code on the top of page 10 and the final sentence of section 1.2.3 both refer to "WhatSQLIsRunning".

Is this a different proc altogether or just a different name for the same proc?
ian.stirk (27) [Avatar] Offline
#4
Re: Missing SP code for dba_WhatSQLIsRunning
Hi Aaron,

Thanks for identifying this, and yes you are correct, it is a different name for the same proc. I will ensure the name is corrected.

Thanks again
Ian