Custom Report - Top SQL for Program

darichar

Description

This procedure, when executed, will return all SQL statements for the given date range and program ordered by wait time. The name of the program should match content of the Programs tab within the Ignite GUI. 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 'TopSQLforProgram') = 1

DROP PROC TopSQLforProgram

GO

-- TopSQLforProgram PROC

--

-- PARMS '@FROM_DATE';, '@TO_DATE';, '@DBNAME'; , '@PROGRAMNAME';

-- EXAMPLE:

--

-- EXEC TopSQLforProgram '2008-12-13 12:00', '2008-12-24 12:00', 'WT10A_DEMO', 'sqlplus.exe'

CREATE PROC TopSQLforProgram

(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @DBNAME VARCHAR(50), @PROGRAMNAME VARCHAR(50))

AS

DECLARE

@SQL VARCHAR(4000),

@DBID VARCHAR(3)

BEGIN

SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME

SELECT @SQL = 'SELECT HASH_OR_NAME, TIMESECS FROM ( ' +

   'SELECT ISNULL(N.NAME, SW.IZHO) HASH_OR_NAME, SUM(SW.QP/100) TIMESECS ' +

   'FROM CONSW_' + @DBID + ' SW LEFT OUTER JOIN CON_SQL_NAME N ON SW.IZHO = N.HASH ' +

   'INNER JOIN CONPR_' + @DBID + ' PR ON SW.UDPW = PR.ID ' +

   'WHERE SW.D BETWEEN ' +

   'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +

   'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +

   'AND UPPER(PR.NAME) = UPPER('+ '''' + @PROGRAMNAME + '''' + ') ' +

   'GROUP BY ISNULL(N.NAME, SW.IZHO)) TOT ' +

   'ORDER BY TIMESECS DESC'

EXEC (@SQL)

END

GO

jaminsql

Added some items to the above to get the SQL text and user name.

IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TopSQLforProgram2') = 1

DROP PROC TopSQLforProgram2

GO

-- TopSQLforProgram2 PROC

--

-- PARMS '@FROM_DATE';, '@TO_DATE';, '@DBNAME'; , '@PROGRAMNAME';

-- EXAMPLE:

--

-- EXEC TopSQLforProgram2 '2008-12-13 12:00', '2008-12-24 12:00', 'WT10A_DEMO', 'sqlplus.exe'

CREATE PROC TopSQLforProgram2 

(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @DBNAME VARCHAR(50), @PROGRAMNAME VARCHAR(50))

AS

BEGIN

DECLARE

@SQL VARCHAR(4000),

@DBID VARCHAR(3)

SELECT @SQL = 'SELECT HASH_OR_NAME, TIMESECS , SQLText, SQLPart, UserName  FROM ( ' +

       'SELECT ISNULL(N.NAME, SW.IZHO) HASH_OR_NAME, SUM(SW.QP/100) TIMESECS, ST.ST SQLText, U.NAME UserName, ST, P SQLPart ' +

       'FROM CONSW_' + @DBID + ' SW LEFT OUTER JOIN CON_SQL_NAME N ON SW.IZHO = N.HASH ' +

       'INNER JOIN CONPR_' + @DBID + ' PR ON SW.UDPW = PR.ID ' +

       'INNER JOIN CONST_' + @DBID + ' ST ON SW.IZHO = ST.H ' +

       'INNER JOIN CONU_' + @DBID + ' U ON SW.XCUW = U.ID ' +

'WHERE SW.D BETWEEN ' +

'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +

'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +

'AND UPPER(PR.NAME) = UPPER('+ '''' + @PROGRAMNAME + '''' + ') ' +

'GROUP BY st.st, U.NAME , ST.P , ISNULL(N.NAME, SW.IZHO)) TOT ' +

'ORDER BY TIMESECS DESC'

EXEC (@SQL)

END

GO