Description
This procedure, when executed, will return all SQL statements for the given date range ordered by wait time. It also does this for all monitored instances and provides a list of top SQL statements globally. 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 'TopSQLAllDatabases') = 1
DROP PROC TopSQLAllDatabases
GO
-- TopSQLAllDatabases PROC
--
-- PARMS '@BEGIN_DATE';, '@END_DATE';
-- EXAMPLE:
--
-- EXEC TopSQLAllDatabases '2008-12-29 00:00','2008-12-31 00:00'
CREATE PROC TopSQLAllDatabases
(@BEGINDATE VARCHAR(16), @ENDDATE VARCHAR(16))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(5)
CREATE TABLE #TOPSQLALLDB
(DBNAME VARCHAR(50),
SQL_HASH_OR_NAME VARCHAR(100),
TIMESECS INT)
DECLARE GETDB CURSOR FOR
SELECT CAST(ID AS VARCHAR(4)) FROM COND
BEGIN
OPEN GETDB
FETCH NEXT FROM GETDB INTO @DBID
@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'INSERT INTO #TOPSQLALLDB ' +
'SELECT D.NAME, ISNULL(N.NAME,CAST(SS.SQLHASH AS VARCHAR(20))) SQL_HASH_OR_NAME, ' +
'SUM(SS.TIMESECS) TIMESECS ' +
'FROM CON_SQL_SUM_' + @DBID + ' SS LEFT OUTER JOIN CON_SQL_NAME N ' +
'ON SS.SQLHASH = N.HASH INNER JOIN COND D ' +
'ON D.ID = CAST(' + '''' + @DBID + '''' + ' AS INT) ' +
' WHERE SS.DATEHOUR BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @BEGINDATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @ENDDATE + '''' + ', 101) ' +
'GROUP BY D.NAME, ISNULL(N.NAME,CAST(SS.SQLHASH AS VARCHAR(20))) '
EXEC (@SQL)
FETCH NEXT FROM GETDB
INTO @DBID
END
CLOSE GETDB
DEALLOCATE GETDB
SELECT DBNAME, SQL_HASH_OR_NAME, TIMESECS
FROM #TOPSQLALLDB
ORDER BY TIMESECS DESC
DROP TABLE #TOPSQLALLDB
END
GO
