This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Custom Report - SQL Plan Analyzer

FormerMember
FormerMember

Introduction

This script will create a stored procedure on your SQL Server Ignite Repository and can be used to query against SQL plans stored in the Ignite Repo looking for Clustered Index Scans, Table Scans and any other physical operator you with to search for in the plans. It is a good way to set a date range and see if scans took place during that time. You can also use the stored procedure parameters to filter results by the number of rows scanned according to the plan step.

SQL Script

USE [ignite_repository]
GO

IF (
          SELECT COUNT(*)
          FROM SYS.OBJECTS
          WHERE NAME LIKE 'SQLPlanAnalyzer'
          ) = 1
     DROP PROC SQLPlanAnalyzer
GO

/****** Object:  StoredProcedure [dbo].[SQLPlanAnalyzer]    Script Date: 3/24/2014 1:35:57 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- SQLPlanAnalyzer PROC
--
-- PARMS '@FROM_DATE', '@TO_DATE', '@DBNAME' , '@PHYSICALOP', '@NumberOfRows  If @NumberOfRows = -1 then return for all
-- EXAMPLE:
-- See this guide for other Physical Operators to set the @PHYSCIALOP parameter
--     http://technet.microsoft.com/en-us/library/ms186954(v=sql.105).aspx
--
-- EXEC SQLPlanAnalyzer '2/22/2014', '3/25/2014', 'Instance Name Here', '"Clustered Index Scan","Index Scan","Table Scan"', -1
CREATE PROC [dbo].[SQLPlanAnalyzer] (
     @FROM_DATE VARCHAR(16)
     , @TO_DATE VARCHAR(16)
     , @DBNAME VARCHAR(50)
     , @PHYSICALOP VARCHAR(4000)
     , @NumberOfRows BIGINT
     )
AS
DECLARE @SQL VARCHAR(max)
     , @DBID VARCHAR(3)

BEGIN
     SELECT @DBID = ID
     FROM ignite.COND
     WHERE NAME = @DBNAME

     SELECT @SQL = 'DECLARE @Temp1 Table (RowNumber bigint, Plan_Hash_Value numeric(38,0), SQL_Hash_Value bigint) ' + 'DECLARE @Temp2 Table (Plan_Hash_Value numeric(38,0), SQL_Hash_Value bigint, query_plan XML) ' + 'DECLARE @RecCount int = 1 ' + --Insert Plan Hashes to Temp1 so we have unique RowNumber
          'Insert Into @Temp1 ' + 'Select ROW_NUMBER() OVER(ORDER BY sph.Plan_Hash_Value DESC) AS Row, sph.Plan_Hash_Value, sw.IZHO from (Select distinct(IZHO), ORPH from ignite.consw_' + @DBID + ' where d >= ''' + @FROM_DATE + ''' and d <= ''' + @TO_DATE + ''') sw inner join ignite.consph_' + @DBID + ' sph on sw.orph = sph.PLAN_HASH_VALUE where sph.Plan_Hash_Value in (Select Plan_Hash_Value from ignite.conspt_' + @DBID + ') ' + --Start first While loop through Temp1
          'WHILE @RecCount <= (Select count(*) from @Temp1) ' + 'BEGIN ' + 'Declare @Counter int = 0 ' + 'Declare @MaxPiece int = (SELECT max(coalesce(Piece,0)) FROM @Temp1 tmp1 inner join ignite.conspt_' + @DBID + ' spt on tmp1.Plan_Hash_Value = spt.PLAN_HASH_VALUE where @RecCount = tmp1.RowNumber) ' + 'Declare @String varchar(max) = '''' ' + 'Declare @Plan_Hash_Value numeric(38,0) ' + 'Declare @SQL_Hash_Value bigint ' + --This inner While will piece together the XML for a specific Plan_Hash_Value
          'WHILE @Counter <= @MaxPiece ' + 'BEGIN ' + 'Set @String = @String + (Select coalesce(Query_Plan_XML,'''') from @Temp1 tmp1 inner join ignite.conspt_' + @DBID + ' spt on tmp1.Plan_Hash_Value = spt.PLAN_HASH_VALUE  where @RecCount = tmp1.RowNumber and spt.Piece = @Counter) ' + 'Set @Counter = @Counter + 1 ' + 'END ' + 'Select @Plan_Hash_Value = tmp1.Plan_Hash_Value,  @SQL_Hash_Value = tmp1.SQL_Hash_Value from @Temp1 tmp1 where @RecCount = tmp1.RowNumber ' + --Insert full XML into second Temp2 table
          'Insert Into @Temp2 Values(@Plan_Hash_Value, @SQL_Hash_Value, CONVERT(XML, @String)) ' + --Reset innert While counters
          'Set @String = '''' ' + 'Set @Counter = 0 ' + --Increment outter While counter
          'Set @RecCount = @RecCount + 1 ' + 'END; ' + --Look through Query Plan XML for matching conditions
          'WITH XMLNAMESPACES ' + '(DEFAULT ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'') ' + 'SELECT ' + '[xml].Plan_Hash_Value, ' + '[xml].SQL_Hash_Value as SQL_Hash_Value, ' + 'CASE ' + 'when st.p = 0 then substring(st.st,st.ss, CASE When st.se = -1 then DATALENGTH(st.st) else (st.se - st.ss) END) ' + 'else st.st ' + 'END AS Ignite_sql_text, ' + 'c.value(''(@StatementText)[1]'', ''VARCHAR(4000)'') AS plan_sql_text, ' + 'd.value(''(@PhysicalOp)[1]'', ''VARCHAR(128)'') AS PhysicalOp, ' + 'd.value(''(@EstimateRows)[1]'', ''float'') AS EstimateRows, ' + 'd.value(''(@EstimateIO)[1]'', ''VARCHAR(128)'') AS EstimateIO, ' + 'd.value(''(@EstimateCPU)[1]'', ''VARCHAR(128)'') AS EstimateCPU, ' + 'coalesce(d.value(''(./TableScan/Object/@Schema)[1]'', ''VARCHAR(128)''),d.value(''(./IndexScan/Object/@Schema)[1]'', ''VARCHAR(128)'')) AS SchemaName, ' + 'coalesce(d.value(''(./TableScan/Object/@Database)[1]'', ''VARCHAR(128)''),d.value(''(./IndexScan/Object/@Database)[1]'', ''VARCHAR(128)'')) AS DatabaseName, ' + 
          'coalesce(d.value(''(./TableScan/Object/@Table)[1]'', ''VARCHAR(128)''),d.value(''(./IndexScan/Object/@Table)[1]'', ''VARCHAR(128)'')) AS TableName, ' + 'd.value(''(./IndexScan/Object/@Index)[1]'', ''VARCHAR(128)'') as IndexName ' + 'FROM @Temp2 [xml] ' + 'CROSS APPLY [xml].query_plan.nodes(''/ShowPlanXML/BatchSequence/Batch/Statements/*'') AS a(c) ' + 'CROSS APPLY c.nodes(''.//RelOp[@PhysicalOp=(' + @PHYSICALOP + ')]'') as b(d) ' + 'outer apply (Select st,p,ss,se from ignite.const_' + @DBID + ' st where [xml].SQL_Hash_Value = st.H) st ' + 'where (d.value(''(@EstimateRows)[1]'', ''float'') > ' + cast(@NumberOfRows AS VARCHAR(50)) + ' or ' + cast(@NumberOfRows AS VARCHAR(50)) + ' = -1) ' + 'order by [xml].Plan_Hash_Value, [xml].SQL_Hash_Value ' + 'OPTION(RECOMPILE, MAXDOP 1); '

     EXEC (@SQL)
END