Description
This procedure, when executed, will return all SQL statements for the given date range and timeslice, e.g. SQLs the executed between 8am and 5pm, ordered by wait time. This report is also included in the Ignite GUI under Advanced Options for a report. 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 'top_sqls_timeslice') = 1
DROP PROC top_sqls_timeslice
GO
-- top_sqls_timeslice PROC
--
-- PARMS '@BEGIN_DATE';,'@BEGIN_HOUR';, '@END_DATE';, '@END_HOUR';, '@DBNAME';
-- EXAMPLE:
--
-- exec top_sqls_timeslice '2008-12-29 00:00','11:30:00','2008-12-30 13:00','12:00:00','WT10A_DEMO'
CREATE PROC top_sqls_timeslice
(@BEGIN_DATE VARCHAR(16), @BEGIN_HOUR VARCHAR(8),
@END_DATE VARCHAR(16),@END_HOUR VARCHAR(8),
@DBNAME VARCHAR(50))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @SQL = 'SELECT HASH_OR_NAME, WAIT_TIME_SECS, ' +
'AVG_EXEC_TIME = ' +
'CASE WHEN EXECUTIONS = 0 THEN -1 ' +
' ELSE ROUND(WAIT_TIME_SECS/EXECUTIONS, 3) ' +
'END ' +
'FROM ( ' +
'SELECT ISNULL(N.NAME, SS.SQLHASH) HASH_OR_NAME, ' +
'SUM(TIMESECS) WAIT_TIME_SECS, SUM(EXECS) EXECUTIONS ' +
'FROM CON_STATS_SUM_' + @DBID + ' SS LEFT OUTER JOIN CON_SQL_NAME N ' +
'ON SS.SQLHASH = N.HASH ' +
'WHERE SS.DATEHOUR BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @BEGIN_DATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @END_DATE + '''' + ', 101) ' +
'AND CONVERT(CHAR,SS.DATEHOUR,108) BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @BEGIN_HOUR + '''' + ', 108) AND ' +
'CONVERT(DATETIME,' + '''' + @END_HOUR + '''' + ', 108) ' +
'GROUP BY ISNULL(N.NAME, SS.SQLHASH)) MAIN ' +
'ORDER BY WAIT_TIME_SECS DESC'
EXEC (@SQL)
END
GO
