Description
This procedure will return a list of stored procedures for the given date range ordered 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 'TopProcedures') = 1
DROP PROC TopProcedures
GO
-- TopProcedures PROC
--
-- PARMS '@FROM_DATE', '@TO_DATE', '@INAME'
-- EXAMPLE:
--
-- EXEC TopProcedures '2013-04-13 12:00', '2013-04-20 12:00', 'SEBOX1\SE2008R2'
-- Note: @INAME is the name of the instance shown in Ignite Home screen
CREATE PROC TopProcedures (
@FROM_DATE VARCHAR(16),
@TO_DATE VARCHAR(16),
@INAME VARCHAR(50))
AS
DECLARE @SQL VARCHAR(4000), @DBID VARCHAR(5)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @INAME
SELECT @SQL = 'SELECT COALESCE(st.PNAME,''ADHOC SQL''), SUM(sw.qp)/100 timesecs '+
'FROM CONST_'+@DBID+' st '+
'INNER JOIN CONSW_'+@DBID+' sw on sw.IZHO = st.H '+
'WHERE sw.D between '+
'CONVERT(DATETIME,''' + @FROM_DATE + ''', 101) AND ' +
'CONVERT(DATETIME,''' + @TO_DATE + ''', 101) ' +
'GROUP BY COALESCE(st.PNAME,''ADHOC SQL'') ' +
'ORDER BY SUM(sw.qp)/100 DESC'
EXEC (@SQL)
END
GO
