March 21, 2016 at 9:40 am
Hello,
I am trying to find either a DM view or group of system tables that will show me the number of queries executed by all users in the past 24 hours. Can anyone assist?
Thank you all in advance for your responses.
The are no problems, only solutions. --John Lennon
March 21, 2016 at 11:49 am
Depending on your system that may or may not be available. Are you looking for what is using the most resources? Look at brent ozar site and look at blitzcache, great info there and number of executions. If SQL got restarted any time all the info would be gone
March 21, 2016 at 11:58 am
Thanks for your reply.
I am trying to create a Report for my Manager which shows a chart of Users who ran most number of queries in the last 24 hours. I assume that this information is not easily accessible, unless you have some form of logging built in?
SQL profiler comes to mind, but again this is real-time and not 42 hours ago.
Thanks
The are no problems, only solutions. --John Lennon
March 21, 2016 at 12:09 pm
There are some DMV functions that will return cached exection plans, including number of executions, min, max, avg runtime, resource consumption, etc. but the problem is that not all queries are cached, and you can't tie a cached plan back to a specific user or session. The buffer cache is there for reuse of expensive plan compiles, not auditing.
I believe what you need is to create a SELECT statement Audit trace.
Auditing SELECT statements in SQL Server 2008
https://blogs.msdn.microsoft.com/sreekarm/2009/01/05/auditing-select-statements-in-sql-server-2008/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
March 22, 2016 at 4:59 am
sys.dm_exec_query_stats or sys.dm_exec_procedure_stats are the two DMVs that will at least give you a ballpark figure on how many queries are coming during the last 24 hours. They are cache dependent, so a query with a recompile statement won't be in cache, queries could age out of cache....
To see exactly which queries are in use, especially since we're on 2014 here, I'd strongly suggest looking at extended events. It's much more lightweight than trace events, there are more and better filtering mechanisms, and trace is on the deprecation list.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply