IF (SELECT COUNT(*) FROM SYS.OBJECTS WHERE NAME LIKE 'TopSQLforDiskIOSec') = 1
DROP PROC TopSQLforDiskIOSec
GO
-- TopSQLforDiskIOSec PROC
--
-- PARMS '@FROM_DATE';, '@TO_DATE';, '@DBNAME';
-- EXAMPLE:
--
-- EXEC TopSQLforDiskIOSec '2008-12-23 10:00:00', '2008-12-23 10:59','WT10A_DEMO'
CREATE PROC TopSQLforDiskIOSec
(@FROM_DATE VARCHAR(16), @TO_DATE VARCHAR(16), @DBNAME VARCHAR(50))
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @SQL = 'SELECT * FROM ( ' +
'SELECT ISNULL(N.NAME, SS.SQLHASH) HASH_OR_NAME, ' +
'ROUND(SUM(DREADS)/SUM(SS.TIMESECS),0) DISK_IO_SEC, ' +
'SUM(SS.TIMESECS) TIMESECS, ' +
'SUM(DREADS) DISK_READS, SUM(EXECS) EXECUTIONS, ' +
'SUM(ROW_COUNT) ROW_COUNT ' +
'FROM CON_STATS_SUM_' + @DBID + ' SS LEFT OUTER JOIN CON_SQL_NAME N ' +
'ON SS.SQLHASH = N.HASH ' +
'WHERE SS.DATEHOUR BETWEEN ' +
'CONVERT(DATETIME,' + '''' + @FROM_DATE + '''' + ', 101) AND ' +
'CONVERT(DATETIME,' + '''' + @TO_DATE + '''' + ', 101) ' +
'GROUP BY ISNULL(N.NAME, SS.SQLHASH)) TOT ' +
'ORDER BY TIMESECS DESC'
EXEC (@SQL)
END
GO
