Description
This procedure, when executed, will return all SQL statements for the given date range that contain a specified string (could be table name or anything inside the SQL text) ordered by disk reads. 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 'TopSQLforTable') = 1
DROP PROC TopSQLforTable
GO
-- TopSQLforTable PROC
--
-- PARMS '@FROM_DATE', '@TO_DATE', '@DBNAME' , '@SQLTEXT'
-- EXAMPLE:
--
-- EXEC TopSQLforTable '2008-12-23 00:00','2008-12-30 23:59','WT10A_DEMO','conprm'
CREATE PROC TopSQLforTable
(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @DBNAME VARCHAR(50), @SQLTEXT 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,ST.H) SQL_STMT, ' +
'SUM(TIMESECS) TOTALSECS, ' +
'SUM(EXECS) TOTALEXECS, ' +
'AVGSECS = CASE WHEN SUM(EXECS) = 0 THEN 0 ' +
'ELSE ROUND(SUM(TIMESECS)/SUM(EXECS),4) END ' +
'FROM ( ' +
'SELECT DISTINCT H ' +
'FROM CONST_' + @DBID +
' WHERE UPPER(ST) LIKE UPPER(' + '''' + '%' + @SQLTEXT + '%' + '''' + ')) ST ' +
'INNER JOIN CON_STATS_SUM_' + @DBID + ' SS ON SS.SQLHASH = ST.H ' +
'LEFT OUTER JOIN CON_SQL_NAME N ' +
'ON SS.SQLHASH = N.HASH ' +
'WHERE SS.DATEHOUR BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +
'GROUP BY ISNULL(N.NAME,ST.H) ' +
'ORDER BY SUM(DREADS) DESC '
EXEC (@SQL)
END
GO