Description
This procedure, when executed, will return all SQL statements for the given date range ordered by average execution time. Some have called this the "Log Running Query" 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 'TopSQLByAvgExec') = 1
DROP PROC TopSQLByAvgExec
GO
-- TopSQLByAvgExec PROC
--
-- PARMS '@BEGIN_DATE';, '@END_DATE';, '@DBNAME';
-- EXAMPLE:
--
-- EXEC TopSQLByAvgExec '2008-12-23 00:00','2008-12-30 00:00','WT10A_DEMO'
CREATE PROC TopSQLByAvgExec
(@BEGINDATE VARCHAR(16), @ENDDATE VARCHAR(16), @DBNAME VARCHAR(50))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @SQL = 'SELECT HASHORNAME "Name/Hash Value", EXECS "Executions", ' +
'TIMESECS "Seconds of Wait", ' +
'CASE WHEN EXECS = 0 THEN 0 ' +
' ELSE ROUND(TIMESECS/EXECS, 4) ' +
'END "Avg Execution" ' +
'FROM ( ' +
'SELECT ISNULL(N.NAME, SS.SQLHASH) HASHORNAME, ' +
'SUM(EXECS) EXECS, ' +
'SUM(TIMESECS) TIMESECS '+
'FROM CON_STATS_SUM_' + @DBID + ' SS LEFT OUTER JOIN CON_SQL_NAME N ' +
'ON SS.SQLHASH = N.HASH ' +
'WHERE DATEHOUR BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @BEGINDATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @ENDDATE + '''' + ', 101) ' +
'GROUP BY ISNULL(N.NAME, SS.SQLHASH)) DVW ' +
'ORDER BY 4 DESC'
EXEC (@SQL)
END
GO
