The Author Online Book Forums are Moving

The Author Online Book Forums will soon redirect to Manning's liveBook and liveVideo. All book forum content will migrate to liveBook's discussion forum and all video forum content will migrate to liveVideo. Log in to liveBook or liveVideo with your Manning credentials to join the discussion!

Thank you for your engagement in the AoF over the years! We look forward to offering you a more enhanced forum experience.

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