This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Report - Top SQL Containing a String

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