Description
This procedure, when executed, will return all SQL statements for the given date range ordered by the number of executions. 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 'TopSQLByExecs') = 1
DROP PROC TopSQLByExecs
GO
-- TopSQLByExecs PROC
--
-- PARMS '@FROM_DATE';, '@TO_DATE';, '@DBNAME';
-- EXAMPLE:
--
-- EXEC TopSQLByExecs '2008-12-23 00:00','2008-12-30 23:59','WT10A_DEMO'
CREATE PROC TopSQLByExecs
(@FROM_DATE VARCHAR(16), @TO_DATE 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 ISNULL(N.NAME, SS.SQLHASH) "NameOrHash", ' +
'SUM(EXECS) "Executions" ' +
'FROM CON_STATS_SUM_' + @DBID + ' SS LEFT OUTER JOIN CON_SQL_NAME N ' +
'ON SS.SQLHASH = N.HASH ' +
'WHERE DATEHOUR BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +
'GROUP BY ISNULL(N.NAME, SS.SQLHASH)' +
'ORDER BY SUM(EXECS) DESC'
EXEC (@SQL)
END
GO