Description
This procedure, when executed, will return all SQL statements for the given date range along with key statistics like executions, disk reads, logical reads and others ordered by the metric of choice. 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 'QueryStatsbyDay') = 1
DROP PROC QueryStatsbyDay
GO
-- QueryStatsbyDay PROC
--
-- PARMS '@FROM_DATE';, '@TO_DATE';, '@DBNAME'; , '@HASHVALUE';
-- EXAMPLE:
--
-- EXEC QueryStatsbyDay '2008-12-23','2008-12-30','WT10A_DEMO','4011593629'
CREATE PROC QueryStatsbyDay
(@FROM_DATE VARCHAR(10), @TO_DATE VARCHAR(10), @DBNAME VARCHAR(50), @HASHVALUE VARCHAR(30))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @SQL = 'SELECT CONVERT(VARCHAR,D,101) "Day", ' +
'SUM(DREADS) "Disk_Reads", ' +
'SUM(BGETS) "Logical Reads", ' +
'SUM(EXECS) "Executions", ' +
'SUM(PARSES) "Logical Writes" ' +
'FROM CONSS_' + @DBID +
' WHERE H= CAST(' + '''' + @HASHVALUE + '''' + ' AS NUMERIC) ' +
'AND D BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @FROM_DATE + ' 00:00:00.000' + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @TO_DATE + ' 23:59:00.000' + '''' + ', 101) ' +
'GROUP BY CONVERT(VARCHAR,D,101) ' +
'ORDER BY CONVERT(VARCHAR,D,101) '
EXEC (@SQL)
END
GO
