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