Description
This procedure, when executed, will return all SQL statements for the given date range 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 'TopSQLByMetric') = 1
DROP PROC TopSQLByMetric
GO
-- TopSQLByMetric PROC
--
-- PARMS '@DBNAME', '@METRIC', '@FROM_DATE', '@TO_DATE', '@SQL_HASH'
-- @DBName - name as shown in the Ignite GUI
-- @METRIC - Executions, Logical Reads, Logical Writes, Physical Reads, LIO / Exec, LIO Writes / Exec, PIO / Exec
-- @FROM_DATE - start date for the report in SQL Server format which is typically YYYY-MM-DD HH24:MI
-- @TO_DATE - end date for the report in SQL Server format which is typically YYYY-MM-DD HH24:MI
-- @SQL_HASH (optional) - if provided limits the data to a list of SQL statements by Ignite hash value
--
-- EXAMPLE:
--
-- EXEC TopSQLByMetric 'SEBOX1\SE12005', 'LIO / Exec', '2011-1-1 00:00','2011-1-10 23:59'
-- EXEC TopSQLByMetric 'SEBOX1\SE12005', 'LIO / Exec', '2011-1-1 00:00','2011-1-10 23:59', '746746777, 78676876'
CREATE PROC TopSQLByMetric (
@DBNAME VARCHAR(50),
@METRIC VARCHAR(20),
@FROM_DATE VARCHAR(16),
@TO_DATE VARCHAR(16),
@SQL_HASH VARCHAR(100) = NULL)
AS
DECLARE
@SQL VARCHAR(4000),
@DBID VARCHAR(3),
@MetricColumnName VARCHAR(30)
BEGIN
SELECT @DBID = ID FROM COND WHERE NAME = @DBNAME
SELECT @MetricColumnName = CASE @METRIC
WHEN 'Executions' THEN 'SUM(EXECS)'
WHEN 'Logical Reads' THEN 'SUM(BGETS)'
WHEN 'Logical Writes' THEN 'SUM(PARSES)'
WHEN 'Physical Reads' THEN 'SUM(DREADS)'
WHEN 'LIO / Exec' THEN 'SUM(BGETS)/SUM(EXECS)'
WHEN 'LIO Writes / Exec' THEN 'SUM(PARSES)/SUM(EXECS)'
WHEN 'PIO / Exec' THEN 'SUM(DREADS)/SUM(EXECS)'
ELSE 'Invalid Metric' END
IF @MetricColumnName = 'Invalid Metric'
PRINT 'Invalid Metric Name Provided. Valid values are Executions, Logical Reads, Logical Writes, Physical Reads, LIO / Exec, LIO Writes / Exec, PIO / Exec'
ELSE
BEGIN
SELECT @SQL = 'SELECT ISNULL(N.NAME, SS.SQLHASH) "NameOrHash", ' +
'SUM(EXECS) as "Executions", ' +
'SUM(BGETS) as "Logical Reads", ' +
'SUM(BGETS)/SUM(EXECS) as "LIO / Exec", ' +
'SUM(DREADS) as "Physical Reads", ' +
'SUM(DREADS)/SUM(EXECS) as "PIO / Exec", ' +
'SUM(PARSES) as "Logical Writes", ' +
'SUM(PARSES)/SUM(EXECS) as "LIO Writes / Exec" ' +
'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) '
-- if a hash value was passed in, only do this for that SQL
IF ISNULL(@SQL_HASH,'*') <> '*'
BEGIN
SELECT @SQL = @SQL + ' AND SS.SQLHASH IN (' + @SQL_HASH + ') '
END
SELECT @SQL = @SQL + 'GROUP BY ISNULL(N.NAME, SS.SQLHASH) HAVING SUM(EXECS)<>0 ' +
'ORDER BY ' + @MetricColumnName + ' DESC'
EXEC (@SQL)
END
END
GO
