Description
This procedure, when executed, will return all SQL statements for the given date range ordered by hour and 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
-- PARMS '@FROM_DATE';, '@TO_DATE';, '@DBNAME'; , '@SDB';
-- EXAMPLE:
--
-- EXEC HourlySQLbySDB '2009-01-15 00:00','2009-01-16 00:00','SEBOX1\SE12008','ignite_jg'
CREATE PROC HourlySQLbySDB
(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @DBNAME VARCHAR(30), @SSDB VARCHAR(50))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @SQL = 'SELECT SUBSTRING(CONVERT(VARCHAR,D,113),1,14) "Day_Hour", ' +
'O.NAME SS_DB, 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 CONO_' + @DBID + ' O ON SW.IXOY = O.ID ' +
'WHERE SW.D BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +
'AND UPPER(O.NAME) = UPPER(' + '''' + @SSDB + '''' + ') ' +
'GROUP BY SUBSTRING(CONVERT(VARCHAR,D,113),1,14), ' +
'O.NAME, ISNULL(N.NAME, SW.IZHO) ' +
'ORDER BY SUBSTRING(CONVERT(VARCHAR,D,113),1,14),SUM(SW.QP/100) DESC '
EXEC (@SQL)
END
GO