Number of queries executed by all users

Question

Number of queries executed by all users

Lord Slaagh

SSCertifiable

Points: 5710
More actions
March 21, 2016 at 9:40 am

#325997

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.
Everyone has a plan until they get punched in the mouth. --Mike Tyson

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

tcronin 95651 SSCertifiable Points: 6735 More actions · Answer 1

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

Lord Slaagh SSCertifiable Points: 5710 More actions · Answer 2

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

Everyone has a plan until they get punched in the mouth. --Mike Tyson

Eric M Russell SSC Guru Points: 125255 More actions · Answer 3

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

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software