Description
This procedure, when executed, will return all SQL statements for the given date range ordered by the Logical I/O per execution. 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 'TopSQLByLIO') = 1
DROP PROC TopSQLByLIO
GO
-- TopSQLByLIO PROC
--
-- PARMS '@FROM_DATE';, '@TO_DATE';, '@DBNAME';
-- EXAMPLE:
--
-- EXEC TopSQLByLIO '2008-12-23 00:00','2008-12-30 23:59','WT10A_DEMO'
CREATE PROC TopSQLByLIO (
@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 ISNULL(N.NAME, SS.SQLHASH) "NameOrHash", ' +
'SUM(EXECS) "Executions", ' +
'SUM(BGETS) "LIO", ' +
'SUM(BGETS)/SUM(EXECS) "LIO / Execution" ' +
'FROM CON_STATS_SUM_' + @DBID + ' SS '+
'LEFT OUTER JOIN CON_SQL_NAME N ON SS.SQLHASH = N.HASH ' +
'WHERE DATEHOUR BETWEEN ' +
'CONVERT(DATETIME,''' + @FROM_DATE + ''', 101) AND ' +
'CONVERT(DATETIME,''' + @TO_DATE + ''', 101) ' +
'GROUP BY ISNULL(N.NAME, SS.SQLHASH) ' +
'HAVING SUM(EXECS) > 0 ' +
'ORDER BY SUM(BGETS)/SUM(EXECS) DESC'
EXEC (@SQL)
END
GO
