![](/style/images/good.png)
![](/style/images/bad.png)
SQL SERVER - Find Count of Table Used in Query From Cache - SQL Authority with P...
source link: https://blog.sqlauthority.com/2021/01/04/sql-server-find-count-of-table-used-in-query-from-cache/?utm_campaign=sql-server-find-count-of-table-used-in-query-from-cache
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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)
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK