Description
This procedure, when executed, will return all SQL statements that have not executed prior to the reference date, but have since, i.e. new SQL statements. This is a good report to use after an application upgrade or patch has been installed to see any new SQL statements ranked by wait time. 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 'TopNewSQL') = 1
DROP PROC TopNewSQL
GO
-- TopNewSQL PROC
--
-- PARMS '@REF_DATE';, '@DBNAME';
-- EXAMPLE:
--
-- EXEC TopNewSQL '2008-12-28 00:00',WT10A_DEMO'
CREATE PROC TopNewSQL
(@REF_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 SQLHASH, SUM(TIMESECS) WAIT_TIME_SECS ' +
'FROM CON_SQL_SUM_' + @DBID + ' SS1 ' +
'WHERE DATEHOUR >= CONVERT(DATETIME,' + '''' + @REF_DATE + '''' + ', 101) ' +
'AND SQLHASH NOT IN ( ' +
'SELECT SQLHASH ' +
'FROM CON_SQL_SUM_' + @DBID + ' SS2 ' +
'WHERE DATEHOUR < CONVERT(DATETIME,' + '''' + @REF_DATE + '''' + ', 101)) ' +
'GROUP BY SQLHASH ' +
'ORDER BY WAIT_TIME_SECS DESC'
EXEC (@SQL)
END
GO
