Description
In some instances, a query will run in many different databases. This report will show how much wait time has accumulated for each database for the specified time range. 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 'SQLDetailbyDatabase') = 1
DROP PROC SQLDetailbyDatabase
GO
-- SQLDetailbyDatabase PROC
--
-- PARMS '@FROM_DATE';, '@TO_DATE';, '@INAME'; , '@SQL_HASH';
-- EXAMPLE:
--
-- EXEC SQLDetailbyDatabase '2013-01-01 00:00','2013-01-30 00:00','IGNITE-DEMO','3032575112'
CREATE PROC SQLDetailbyDatabase(
@FROM_DATE VARCHAR(16), -- begin date of report, must be less than 30 days ago
@TO_DATE VARCHAR(16), -- end date for report up to today
@INAME VARCHAR(50), -- instance name as displayed in the Ignite home page
@SQL_HASH VARCHAR(50) -- SQL hash number in Ignite
) AS
DECLARE @SQL VARCHAR(4000), @DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @INAME
SET @SQL = 'select o.NAME as "DBName", SUM(sw.qp)/100 "Seconds of Wait"' +
' from CONSW_'+@DBID+'; sw' +
' inner join CONO_'+@DBID+'; o on o.ID = sw.IXOY' +
' where sw.IZHO = ' + @SQL_HASH +
' and SW.D BETWEEN ' +
' CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +
' CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +
' group by o.NAME'
EXEC (@SQL)
END
GO
