Description
This procedure will return all SQL statements for the given date range and stored procedure 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 'TopSQLforProcedure') = 1
DROP PROC TopSQLforProcedure
GO
-- TopSQLforProcedure PROC
--
-- PARMS '@FROM_DATE';, '@TO_DATE';, '@IBNAME'; , '@PROC';
-- EXAMPLE:
--
-- EXEC TopSQLforProcedure '2013-04-20 00:00','2013-04-24 00:00','SEBOX1\SE2008R2', 'DELIVERY'
-- Note: the procedure can be a portion of the proc name and will be wildcarded in the query
CREATE PROC TopSQLforProcedure(
@FROM_DATE VARCHAR(16),
@TO_DATE VARCHAR(16),
@INAME VARCHAR(50),
@PROC VARCHAR(100))
AS
DECLARE @SQL VARCHAR(4000),@DBID VARCHAR(5)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @INAME
SELECT @SQL = '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 CONST_' + @DBID + ' ST ON SW.IZHO = ST.H ' +
'WHERE SW.D BETWEEN ' +
'CONVERT(DATETIME,''' + @FROM_DATE + ''', 101) AND ' +
'CONVERT(DATETIME,''' + @TO_DATE + ''', 101) ' +
'AND UPPER(ST.PNAME) LIKE UPPER(''%' + @PROC + '%'') ' +
'GROUP BY ISNULL(N.NAME, SW.IZHO) ' +
'ORDER BY SUM(SW.QP/100) DESC '
EXEC (@SQL)
END
GO
