This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Report - Find SQL by SPID

DESCRIPTION

This procedure finds all SQL statements for the given date range and SPID ordered by wait times. See comments inside the procedure for more details on the parameters and methods for calling. Install the stored procedure into the Ignite repository database.

STORED PROCEDURE

IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'FindSessionSQL') = 1

DROP PROC FindSessionSQL

GO

-- FindSessionSQL PROC

--

-- PARMS '@FROM_DATE', '@TO_DATE', '@DBNAME' , '@SPID'

-- EXAMPLE:

--

-- EXEC FindSessionSQL '2010-01-23 00:00','2010-01-30 00:00','JGRIFFIN\SQLEXPRESS','67'

CREATE PROC FindSessionSQL

(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @DBNAME VARCHAR(50), @SPID VARCHAR(50))

AS

DECLARE

@SQL VARCHAR(4000),

@DBID VARCHAR(3)

BEGIN

SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME

SELECT @SQL = 'SELECT substring(cast(sw.d as varchar), 1,19) START_DATE, ' +

   'sw.vdsi SPID, sw.izho HASH, '+

   'st.st SQL_TEXT, SUM(qp/100) timesecs, U.NAME ' +

   'FROM CONSW_' + @DBID + ' SW JOIN CONST_' + @DBID + ' ST ON SW.IZHO = ST.H ' +

   'INNER JOIN CONU_' + @DBID + ' U ON SW.XCUW = U.ID ' +

   'WHERE SW.D BETWEEN ' +

   'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +

   'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +

   'AND st.p = 0 '+

   'AND sw.vdsi like ' + '''' + @SPID + '%' + '''' +

   ' GROUP BY substring(cast(sw.d as varchar),1,19),sw.vdsi,st.st,sw.izho,u.name ' +

   'ORDER BY 1 '

EXEC (@SQL)

END

GO