Speak 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
IF (OBJECT_ID('dbo.Teste') IS NOT NULL) DROP TABLE dbo.Teste GO CREATE TABLE dbo.Teste ( Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED, -- Índice clustered no ID Nome VARCHAR(100) NOT NULL ) WITH(DATA_COMPRESSION=PAGE) -- Índice non-clustered no nome CREATE NONCLUSTERED INDEX SK01_Teste ON dbo.Teste(Nome) WITH(DATA_COMPRESSION=PAGE) INSERT INTO dbo.Teste(Nome) VALUES ('Dirceu'), ('Resende'), ('Tiago'), ('Neves'), ('Fabricio'), ('Lima'), ('Luiz'), ('Lima'), ('Alan'), ('Mairink'), ('Arthur'), ('Luz'), ('Fabiano'), ('Amorim'), ('Rodrigo'), ('Ribeiro') SELECT * FROM dbo.Teste WHERE Id = 2 -- Deve utilizar o índice clustered (seek) SELECT * FROM dbo.Teste WHERE Id = 99 -- Deve utilizar o índice clustered (seek) SELECT * FROM dbo.Teste WHERE Nome = 'Dirceu' -- Deve utilizar o índice non-clustered "SK01_Teste" (seek) SELECT * FROM dbo.Teste WHERE Nome = 'Resende' -- Deve utilizar o índice non-clustered "SK01_Teste" (seek) SELECT * FROM dbo.Teste -- Deve utilizar o índice non-clustered (scan) |
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:
|
1 2 3 |
SELECT * FROM sys.indexes WHERE [object_id] = OBJECT_ID('dbo.Teste') |
Now let's look at the queries that used this clustered index (PK__Test__3214EC075F9CA38E):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT SUBSTRING(C.[text], ( A.statement_start_offset / 2 ) + 1, ( CASE A.statement_end_offset WHEN -1 THEN DATALENGTH(C.[text]) ELSE A.statement_end_offset END - A.statement_start_offset ) / 2 + 1) AS sqltext, A.execution_count, A.total_logical_reads / execution_count AS avg_logical_reads, A.total_logical_writes / execution_count AS avg_logical_writes, A.total_worker_time / execution_count AS avg_cpu_time, A.last_elapsed_time / execution_count AS avg_elapsed_time, A.total_rows / execution_count AS avg_rows, A.creation_time, A.last_execution_time, CAST(query_plan AS XML) AS plan_xml, B.query_plan, C.[text] FROM sys.dm_exec_query_stats AS A CROSS APPLY sys.dm_exec_text_query_plan(A.plan_handle, A.statement_start_offset, A.statement_end_offset) AS B CROSS APPLY sys.dm_exec_sql_text(A.[sql_handle]) AS C WHERE B.query_plan LIKE '%PK__Teste__3214EC075F9CA38E%' AND B.query_plan NOT LIKE '%dm_exec_text_query_plan%' ORDER BY A.last_execution_time DESC OPTION(RECOMPILE) |
Result:
Let's also look at queries that used the non-clustered index SK01_Test:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
SELECT SUBSTRING(C.[text], ( A.statement_start_offset / 2 ) + 1, ( CASE A.statement_end_offset WHEN -1 THEN DATALENGTH(C.[text]) ELSE A.statement_end_offset END - A.statement_start_offset ) / 2 + 1) AS sqltext, A.execution_count, A.total_logical_reads / execution_count AS avg_logical_reads, A.total_logical_writes / execution_count AS avg_logical_writes, A.total_worker_time / execution_count AS avg_cpu_time, A.last_elapsed_time / execution_count AS avg_elapsed_time, A.total_rows / execution_count AS avg_rows, A.creation_time, A.last_execution_time, CAST(query_plan AS XML) AS plan_xml, B.query_plan, C.[text] FROM sys.dm_exec_query_stats AS A CROSS APPLY sys.dm_exec_text_query_plan(A.plan_handle, A.statement_start_offset, A.statement_end_offset) AS B CROSS APPLY sys.dm_exec_sql_text(A.[sql_handle]) AS C WHERE B.query_plan LIKE '%SK01_Teste%' AND B.query_plan NOT LIKE '%dm_exec_text_query_plan%' ORDER BY A.last_execution_time DESC OPTION(RECOMPILE) |
Result:
By clicking on the “plan_xml” column, we can view the execution plan for 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 made a failover, that is, you will not be able to have a very long history of the queries that use a certain 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 observe and confirm that INSERT / DELETE / UPDATE operations end up “using” all indexes in the table. Any specific index that you consult with the query above, will have INSERT / UPDATE / DELETE records among 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 table.
Well guys, that's it! I hope you enjoyed this article.
A big hug, see you later!
Hi Dirceu.
Regarding the execution plan that is stored in the plan cache, you mention that SQL Server determines when a plan should be dropped from the plan cache.
I have a Windows Server 2016 VM and SQL Server 2017 configured with memory 2Gb and 2 vcpu for personal testing. And I noticed that the stored plans that are viewed by the sys.dm_exec_query_stats and sys.dm_exec_procedure_stats DMVs disappear after a few minutes (or even seconds) that I have just executed a query or a store procedure. Considering that only I am working, it should not happen.
This sounds a little contradictory to the claim that the plan must be in memory to be reused, especially when it comes to procedures.
Is there a way to keep these plans longer in memory?
Does the amount of memory I left configured interfere, say, at this time that the execution plan is in the plan cache?
Thank you for your attention.
Wolney
Hello Wolney, how are you?
This problem is really complex, it requires a more detailed analysis to understand your scenario.
But the more memory you free up for sql, the longer the plans will be cached, but there are other factors that imply this.
If still in doubt, call in private