I love working with indexes, and I need to know what’s using them to work on them intelligently. Most of that information is already there waiting for you to query it. Luckily, Jonathan Kehayias (b|t) did the hard work for us in his post Finding what queries in the plan cache use a specific index, and I could modify his code to get entire tables.
Remember that you’re querying XML and that’s a CPU intensive process. However, you’re also looking for what’s in cache which is most relevant during or just after your busiest time of day on prod. The longer you wait, the more chance a query will be flushed from cache for one reason or another, although waiting a couple hours typically isn’t a problem on a server that’s not under extreme stress.
This first script will find all references to the indexes of a table in the plan cache, including key and RID lookups. However, table scans against heaps are in the XML a little different, and that’s what my second query is for. Hopefully you have a clustered index on every table you want to run this against, but you’ll probably need both of these.
If you’re only looking for a single index in the cache, I already have that query on my Cleaning up the buffer pool post. However, I’m working on some new stuff that’s way too long to put scripts in-line, so I had to create this post to add as a reference. I’ll try to remember to update this post as I have everything done, but keep an eye out for my “Indexing Strategy” post and my presentation by the same name I hope to debut at the Cleveland SQL Saturday in February 2016.
Table Index Usage
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; DECLARE @TableName SYSNAME = '[ShiverMeTuples]'; DECLARE @DatabaseName SYSNAME; SELECT @DatabaseName = '[' + DB_NAME() + ']'; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text, --n.query('.'), cp.plan_handle, i.value('(@PhysicalOp)[1]', 'VARCHAR(128)') AS PhysicalOp, i.value('(@EstimateRows)[1]', 'VARCHAR(128)') AS EstimateRows, i.value('(@EstimateIO)[1]', 'VARCHAR(128)') AS EstimateIO, i.value('(@EstimateCPU)[1]', 'VARCHAR(128)') AS EstimateCPU, cp.usecounts, i.value('(./IndexScan/@Lookup)[1]', 'VARCHAR(128)') AS IsLookup, i.value('(./IndexScan/Object/@Database)[1]', 'VARCHAR(128)') AS DatabaseName, i.value('(./IndexScan/Object/@Schema)[1]', 'VARCHAR(128)') AS SchemaName, i.value('(./IndexScan/Object/@Table)[1]', 'VARCHAR(128)') AS TableName, i.value('(./IndexScan/Object/@Index)[1]', 'VARCHAR(128)') as IndexName, --i.query('.'), STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)') FROM i.nodes('./OutputList/ColumnReference') AS t(cg) FOR XML PATH('')),1,2,'') AS output_columns, STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)') FROM i.nodes('./IndexScan/SeekPredicates/SeekPredicateNew//ColumnReference') AS t(cg) FOR XML PATH('')),1,2,'') AS seek_columns, RIGHT(i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)'), len(i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)')) - charindex('.', i.value('(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)'))) as Predicate, query_plan FROM ( SELECT plan_handle, query_plan FROM ( SELECT DISTINCT plan_handle FROM sys.dm_exec_query_stats WITH(NOLOCK)) AS qs OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp ) as tab (plan_handle, query_plan) INNER JOIN sys.dm_exec_cached_plans AS cp ON tab.plan_handle = cp.plan_handle CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS q(n) CROSS APPLY n.nodes('.//RelOp[IndexScan/Object[@Table=sql:variable("@TableName") and @Database=sql:variable("@DatabaseName")]]' ) as s(i) --WHERE i.value('(./IndexScan/@Lookup)[1]', 'VARCHAR(128)') = 1 ORDER BY 3 OPTION(RECOMPILE, MAXDOP 4);
Table (Heap) Scans
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; DECLARE @TableName SYSNAME = '[ItsAHeapOfSomething]'; DECLARE @DatabaseName SYSNAME; SELECT @DatabaseName = '[' + DB_NAME() + ']'; WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan') SELECT n.value('(@StatementText)[1]', 'VARCHAR(4000)') AS sql_text, n.query('.'), cp.plan_handle, i.value('(@PhysicalOp)[1]', 'VARCHAR(128)') AS PhysicalOp, i.value('(./TableScan/@Lookup)[1]', 'VARCHAR(128)') AS IsLookup, i.value('(./TableScan/Object/@Database)[1]', 'VARCHAR(128)') AS DatabaseName, i.value('(./TableScan/Object/@Schema)[1]', 'VARCHAR(128)') AS SchemaName, i.value('(./TableScan/Object/@Table)[1]', 'VARCHAR(128)') AS TableName, --i.value('(./TableScan/Object/@Table)[1]', 'VARCHAR(128)') as TableName, i.query('.'), STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)') FROM i.nodes('./OutputList/ColumnReference') AS t(cg) FOR XML PATH('')),1,2,'') AS output_columns, STUFF((SELECT DISTINCT ', ' + cg.value('(@Column)[1]', 'VARCHAR(128)') FROM i.nodes('./TableScan/SeekPredicates/SeekPredicateNew//ColumnReference') AS t(cg) FOR XML PATH('')),1,2,'') AS seek_columns, RIGHT(i.value('(./TableScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)'), len(i.value('(./TableScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)')) - charIndex('.', i.value('(./TableScan/Predicate/ScalarOperator/@ScalarString)[1]', 'VARCHAR(4000)'))) as Predicate, cp.usecounts, query_plan FROM ( SELECT plan_handle, query_plan FROM ( SELECT DISTINCT plan_handle FROM sys.dm_exec_query_stats WITH(NOLOCK)) AS qs OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp ) as tab (plan_handle, query_plan) INNER JOIN sys.dm_exec_cached_plans AS cp ON tab.plan_handle = cp.plan_handle CROSS APPLY query_plan.nodes('/ShowPlanXML/BatchSequence/Batch/Statements/*') AS q(n) CROSS APPLY n.nodes('.//RelOp[TableScan/Object[@Table=sql:variable("@TableName") and @Database=sql:variable("@DatabaseName")]]' ) as s(i) --WHERE i.value('(./TableScan/@Lookup)[1]', 'VARCHAR(128)') = 1 OPTION(RECOMPILE, MAXDOP 4);
Simple SQL Server 
Thanks for this. Assessing an index situation like mine is daunting. It is, unfortunately, a combination OLAP/OLTP situation where development has thrown on indexes over the years so trying to find a place to begin is difficult.
Right now I’m looking for table scans and missing foreign key indexes and your tools will be added to the analysis.
I’m glad I was able to help. Throwing OLAP on there as well makes it difficult. I’m struggling with that same situation now, and there’s not much I can do with SELECT * FROM Table when I can’t change the code. Good luck with that!
As for the missing foreign key indexes, that’s a post I was planning on putting together in the next month or two. I’d like to make the script a little bit better and double-check all the results before I make it public, but I’d be more than happy to send it along by email. If you’re interested, my email address is Steve at Simple SQL Server.
I’ll watch for the post, currently use some of Jason Strate’s index analysis queries for that one.
I’m not getting results where expected. Tried with and without brackets on the table name and database name variables.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @TableName SYSNAME = ‘account’;
DECLARE @DatabaseName SYSNAME;
SELECT @DatabaseName =’cmsperformance’; — ‘[‘ + DB_NAME() + ‘]’;
WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/2004/07/showplan’)
SELECT
n.value(‘(@StatementText)[1]’, ‘VARCHAR(4000)’) AS sql_text,
–n.query(‘.’),
cp.plan_handle,
i.value(‘(@PhysicalOp)[1]’, ‘VARCHAR(128)’) AS PhysicalOp,
i.value(‘(@EstimateRows)[1]’, ‘VARCHAR(128)’) AS EstimateRows,
i.value(‘(@EstimateIO)[1]’, ‘VARCHAR(128)’) AS EstimateIO,
i.value(‘(@EstimateCPU)[1]’, ‘VARCHAR(128)’) AS EstimateCPU,
cp.usecounts,
i.value(‘(./IndexScan/@Lookup)[1]’, ‘VARCHAR(128)’) AS IsLookup,
i.value(‘(./IndexScan/Object/@Database)[1]’, ‘VARCHAR(128)’) AS DatabaseName,
i.value(‘(./IndexScan/Object/@Schema)[1]’, ‘VARCHAR(128)’) AS SchemaName,
i.value(‘(./IndexScan/Object/@Table)[1]’, ‘VARCHAR(128)’) AS TableName,
i.value(‘(./IndexScan/Object/@Index)[1]’, ‘VARCHAR(128)’) as IndexName,
–i.query(‘.’),
STUFF((SELECT DISTINCT ‘, ‘ + cg.value(‘(@Column)[1]’, ‘VARCHAR(128)’)
FROM i.nodes(‘./OutputList/ColumnReference’) AS t(cg)
FOR XML PATH(”)),1,2,”) AS output_columns,
STUFF((SELECT DISTINCT ‘, ‘ + cg.value(‘(@Column)[1]’, ‘VARCHAR(128)’)
FROM i.nodes(‘./IndexScan/SeekPredicates/SeekPredicateNew//ColumnReference’) AS t(cg)
FOR XML PATH(”)),1,2,”) AS seek_columns,
RIGHT(i.value(‘(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]’, ‘VARCHAR(4000)’), len(i.value(‘(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]’, ‘VARCHAR(4000)’)) – charindex(‘.’, i.value(‘(./IndexScan/Predicate/ScalarOperator/@ScalarString)[1]’, ‘VARCHAR(4000)’))) as Predicate,
query_plan
FROM ( SELECT plan_handle, query_plan
FROM ( SELECT DISTINCT plan_handle
FROM sys.dm_exec_query_stats WITH(NOLOCK)) AS qs
OUTER APPLY sys.dm_exec_query_plan(qs.plan_handle) tp
) as tab (plan_handle, query_plan)
INNER JOIN sys.dm_exec_cached_plans AS cp
ON tab.plan_handle = cp.plan_handle
CROSS APPLY query_plan.nodes(‘/ShowPlanXML/BatchSequence/Batch/Statements/*’) AS q(n)
CROSS APPLY n.nodes(‘.//RelOp[IndexScan/Object[@Table=sql:variable(“@TableName”) and @Database=sql:variable(“@DatabaseName”)]]’ ) as s(i)
–WHERE i.value(‘(./IndexScan/@Lookup)[1]’, ‘VARCHAR(128)’) = 1
ORDER BY 3
OPTION(RECOMPILE, MAXDOP 4);
It does need to have brackets on both the table and database names. Other than that, the only issue I’ve run into is on case sensitive systems which always catch me off guard.
I’ll have to double-check, but I’m not sure that the queries for foreign key enforcement ever make it into the plan cache. Instead I looked over my foreign key query enough to make it public, but it will probably get mostly rewritten before it gets its own post.
It’s not perfect. For instance, if you have a multiple column foreign key it’s only checking the first column of it, missing indexes that it might be able to use that are in a different order. There may also be some issues with a foreign key being linked to a specific index and not using a new index that is perfect for it. Use this as a guide of which foreign keys to look at and expect some false positives.
SELECT FK.name
, ParentColumn = SP.name + ‘.’ + TP.name + ‘.’ + CP.name
, ReferencedColumn = SR.name + ‘.’ + TR.name + ‘.’ + CR.name
, IndexesOnParentTable = IP.Counts
, IndexesOnReferencedTable = IR.Counts
FROM sys.foreign_keys FK
INNER JOIN sys.foreign_key_columns FKC ON FK.object_id = FKC.constraint_object_id
INNER JOIN sys.tables TP ON FKC.parent_object_id = TP.object_id
INNER JOIN sys.schemas SP ON TP.schema_id = SP.schema_id
INNER JOIN sys.columns CP ON FKC.parent_object_id = CP.object_id AND FKC.parent_column_id = CP.column_id
INNER JOIN sys.tables TR ON FKC.referenced_object_id = TR.object_id
INNER JOIN sys.schemas SR ON TR.schema_id = SR.schema_id
INNER JOIN sys.columns CR ON FKC.referenced_object_id = CR.object_id AND FKC.referenced_column_id = CR.column_id
LEFT JOIN (SELECT Counts = COUNT(1)
, I.object_id
, I.column_id
FROM sys.index_columns I
WHERE I.index_column_id = 1
GROUP BY I.object_id, I.column_id) IP ON FKC.parent_object_id = IP.object_id
AND FKC.parent_column_id = IP.column_id
LEFT JOIN (SELECT Counts = COUNT(1)
, I.object_id
, I.column_id
FROM sys.index_columns I
WHERE I.index_column_id = 1
GROUP BY I.object_id, I.column_id) IR ON FKC.referenced_object_id = IR.object_id
AND FKC.referenced_column_id = IR.column_id
WHERE FKC.constraint_column_ID = 1
I knew I had another one… Here’s a script I use before I drop an index that checks if its name is in any proc or view (index hints), and it also checks if any foreign keys are using the index. This can be used to eliminate the disclaimer above about “being linked to a specific index and not using a new index” when I rewrite the query.
DECLARE @IndexName SYSNAME = ‘IDX_SimpleSQLServer_WhereAreYou’
SELECT TOP 100 objectname = OBJECT_NAME(object_id), *
FROM sys.sql_modules
WHERE definition like ‘%’ + @IndexName + ‘%’
Select
key_name = f.name,
parent_table = object_name(f.parent_object_id)
From
sys.foreign_keys f
inner join
sys.indexes i
on f.referenced_object_id = i.object_id and
f.key_index_id = i.index_id
Where
i.name = @IndexName
Also, unlike querying the plan cache which has multiple reasons when a query wouldn’t show up there, the two scripts I put in the comments won’t vary by what’s in memory.
Cool. Changing the table and database names to upper case fixed things on the table index usage script.
http://www.jasonstrate.com/2010/06/index-those-foreign-keys/
I really like Jason’s script, and I’m surprised I don’t remember ever seeing it before. The only issue I have at first glance is that it’s creating very narrow indexes that are absolutely perfect for the foreign key, but maybe not the best indexes for the table.
Before you create one of these indexes I’d do the bare minimum of looking in the plan cache to see what plans would benefit from this index (quick easy not-so-thorough way, where is this a predicate on a scan) and consider adding more key or included columns. Just make sure that the key column(s) his script declares are the first column(s) in your final index.
Thanks. It’s help with deletes that do scans ( in the child table ) that got me checking on missing foreign key indexes.
Absolutely. I don’t mean to take away from Jason’s script with my comment. He is fixing a very common, hidden performance issue. I love that the script is available, especially with a name as good as Jason’s standing behind it. The only goal is to take something great and see if it can be even better.
Pingback: Data Compression | Simple SQL Server
But that only covers indexes currently in the cache. Before making any index mods, you’d still need to look at least at the missing index stats and the index usage stats (although cardinality and data width are often also required). Those stats include all index usage, both in the cache and already aged out of it.
I agree completely. It’s difficult to put how many things you need to look at in every post, especially with all the things you should mention on each. Missing index stats tends to recommend very wide indexes, even when it’s returning only a couple rows where key lookups would be fine. Index usage stats show the number of uses on each one, but not the quality of the seek (number of rows read / number of rows used) or the impact if it wasn’t used.
This post was actually specifically made because a post I’m working on for creating an indexing strategy was getting too long and needed to be split out and linked to. I know it still won’t be perfect, but I hope it makes a positive difference for a couple people.
Pingback: Indexing Strategy | Simple SQL Server