Hi guys.
I have a question regarding Query Store.
I want to check how much each table is used in my database in order to do some cleanup (number of select statements on tabels and views)
Normally i would do this with the Transaction log, but unfortunately I have only set this up to log one month behind. I enabled Query store in January 2021. My hope is that this feature has logged what I am after.
Do you know if this is possible?
Pretty sure that query store isn't going to help you there. Query store is for storing execution plan statistics and it auto-flushes itself depending on the configuration you have set.
What may be helpful though is to look at the execution plan counts. If I remember right, these reset with each server reboot, but as long as you haven't rebooted in a while, you should be able to see all queries that have run against the database.
This blog post has a good writeup on it: https://www.sqlshack.com/searching-the-sql-server-query-plan-cache/
So in your case, if you are looking for a specific table or view, you could use the plan cache to filter on %SELECT%<table or view name>%. Mind you, you will still need to review the query text as you could have some SELECT happening followed by an INSERT or UPDATE to your table or view which may not be what you are looking for.
The query they have on their site is:
SELECT TOP 10
databases.name,
dm_exec_sql_text.text AS TSQL_Text,
dm_exec_query_stats.creation_time,
dm_exec_query_stats.execution_count,
dm_exec_query_stats.total_worker_time AS total_cpu_time,
dm_exec_query_stats.total_elapsed_time,
dm_exec_query_stats.total_logical_reads,
dm_exec_query_stats.total_physical_reads,
dm_exec_query_plan.query_plan
FROM sys.dm_exec_query_stats
CROSS APPLY sys.dm_exec_sql_text(dm_exec_query_stats.plan_handle)
CROSS APPLY sys.dm_exec_query_plan(dm_exec_query_stats.plan_handle)
INNER JOIN sys.databases
ON dm_exec_sql_text.dbid = databases.database_id
WHERE dm_exec_sql_text.text LIKE '%SalesOrderHeader%';
Obviously, you would want to remove the TOP 10 and you'd want to adjust that WHERE clause, but otherwise I think that should give you what you are looking for.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 18, 2021 at 2:55 pm
Hi Mr Brian.
Thank you so much for this great reply!
I will try it out.
Best of luck to you!
September 1, 2021 at 9:48 am
Hello,
Please read below article, It's surely help you.
https://solutioncenter.apexsql.com/how-to-query-data-using-select-statement-in-sql-server/myaarpmedicare
September 1, 2021 at 12:33 pm
Have you read my question? What you are linking to is not related, and i already had an answer.
Thanks though!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply