SQL Server - How to identify queries that use a particular index through plan cache

Views: 866
Reading Time: 4 minutes

Hey guys!
In this article I would like to share with you a query that allows you to identify which queries are using a particular index. This type of analysis can be especially useful for identifying which queries that are indexing have the most scan operations you are looking for, for example.

The idea of ​​this post came from a doubt from a Whatsapp group that I participate.

This post is part of a series of performance-related articles, which contain the following posts:
Understanding Index Functioning in SQL Server
SQL Server - Performance Comparison between Scalar Function and CLR Scalar Function
SQL Server - Introduction to Performance Tuning Study
SQL Server - How to Identify a Slow or Heavy Query in Your Database
SQL Server - How to identify queries that use a particular index through plan cache

Creating data for the tests

To demonstrate how this query returns data, I will create a new table, insert some records, and perform some simple queries so that the bank statistics capture these operations and I can check which ones used which indexes:

Identifying Index Usage

Since we created the data for testing and made some queries, let's identify which queries used the clustered index. Before that, I need to identify the name of this index:

Now let's look at the queries that used this clustered index (PK__Test__3214EC075F9CA38E):

Result:

Let's also look at queries that used the non-clustered index SK01_Test:

Result:

By clicking on the “plan_xml” column, we can view the execution plan of this query and analyze how the index appeared in the plan cache:

Some observations

It is important to always remember that the plan cache is “lost” when the SQL Server service is restarted or failed over, that is, you will not be able to have a very long history of queries using a given index.

Another nice point I would like to highlight is that the plan cache has an amount of memory that it can use to store the plans. SQL Server cannot (and should not) save all plans for all queries, so it should optimize to save only those plans that it deems to be the most important. As such, you will not always be able to retrieve information from a particular query in the plan cache, as the plan may already have been dropped for memory and storage limit reasons, even if SQL has not been restarted.

Finally, it is legal to note and confirm that INSERT / DELETE / UPDATE operations end up “using” all indexes in the table. Any specific indexes you refer to with the above query will have INSERT / UPDATE / DELETE records between the commands that used the index (as long as those plans are still in the plan cache), since the indexes need to be updated in write operations in the index. table.

Well guys, that's it! I hope you enjoyed this article.
A big hug, see you later!