|
| 1 | +/* |
| 2 | +Author: Bert Wagner |
| 3 | +Source link: https://blog.bertwagner.com/how-to-search-and-destroy-non-sargable-queries-on-your-server-ff9f57c7268e |
| 4 | +*/ |
| 5 | + |
| 6 | +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; |
| 7 | + |
| 8 | +DECLARE @dbname SYSNAME; |
| 9 | +SET @dbname = QUOTENAME(DB_NAME()); |
| 10 | + |
| 11 | +--WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') |
| 12 | + |
| 13 | +SELECT |
| 14 | + stmt.value('(@StatementText)[1]', 'varchar(max)') AS [Query], |
| 15 | + query_plan AS [QueryPlan], |
| 16 | + sc.value('(.//Identifier/ColumnReference/@Schema)[1]', 'varchar(128)') AS [Schema] |
| 17 | + , sc.value('(.//Identifier/ColumnReference/@Table)[1]', 'varchar(128)') AS [Table] |
| 18 | + , sc.value('(.//Identifier/ColumnReference/@Column)[1]', 'varchar(128)') AS [Column] |
| 19 | + , CASE WHEN s.exist('.//TableScan') = 1 THEN 'TableScan' ELSE 'IndexScan' END AS [ScanType], |
| 20 | + sc.value('(@ScalarString)[1]', 'varchar(128)') AS [ScalarString] |
| 21 | +FROM |
| 22 | + sys.dm_exec_cached_plans AS cp |
| 23 | + CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp |
| 24 | + CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS batch(stmt) |
| 25 | + CROSS APPLY stmt.nodes('.//RelOp[TableScan or IndexScan]') AS scan(s) |
| 26 | + CROSS APPLY s.nodes('.//ScalarOperator') AS scalar(sc) |
| 27 | +WHERE |
| 28 | + s.exist('.//ScalarOperator[@ScalarString]!=""') = 1 |
| 29 | + AND sc.exist('.//Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]') = 1 |
| 30 | + AND sc.value('(@ScalarString)[1]', 'varchar(128)') IS NOT NULL; |
0 commit comments