SQL SERVER – Find Count of Table Used in Query From Cache

3 years ago

Recently during Comprehensive Database Performance Health Check, we had an interesting situation where we wanted to Find the Count of Table Used in Query From Cache. Let us see how we can do that in today’s blog post.

Real-World Scenario for Find Count of Table

While we were working together one of the questions which we had was how many times any table was accessed in queries and what were the queries. Today we will see a very simple example of the same.

Demonstration

Here are two simple queries that I have written for the sample database. I have executed the first query 5 times and the second query 7 times. You can see that from the digit specified after the GO keyword.

SELECT TOP 1 *
FROM [Sales].[Invoices]
GO 5
SELECT *
FROM [Sales].[Invoices] i
INNER JOIN [Sales].[InvoiceLines] il ON i.InvoiceID = il.InvoiceID
WHERE i.InvoiceID = 23
GO 7

Both of the queries listed above is using the invoice table.

Once the queries are run you can run the following simple script which will list all the queries where the invoice table is used and their count.

SELECT dest.text
, deqs.execution_count
FROM sys.dm_exec_query_stats deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.plan_handle) dest
WHERE dest.text LIKE '%Invoices%'
GO

Now over here you must remember that results are as good as the cache containing data. If you clear the cache or due to any reason the cache is automatically cleared your result may be incorrect.

In any case, this script is a good little handy script that you can use when you want to know how your table is used in various queries. You can always reach out to me via Twitter if you have any questions.

Reference: Pinal Dave (https://blog.sqlauthority.com)