February 23, 2016 at 12:31 pm
I was asked to determine how many queries were run against a particular database in the last month. I have no idea how to do this - any help would be greatly appreciated!!
February 23, 2016 at 12:40 pm
Start looking at this: sys.dm_exec_query_stats
...not sure how much closer you'll be able to get.
February 23, 2016 at 3:13 pm
if you really need to know exactly how many queries are run against the database, you need to set that up ahead of time. With SQL Server 2012 or better, use extended events to set this up.
After the fact, you can look at the DMV sys.dm_exec_query_stats as was mentioned. But, understand that it only shows counts for the queries that are currently in the cache. If anything has aged out of cache over the last month or the cache was cleared, then the values you see there will not be accurate.
"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
February 23, 2016 at 4:34 pm
Adding to what Grant said, if you restarted the SQL Server service, the cache is gone.
February 25, 2016 at 6:44 am
What exactly do you mean by this: "if you really need to know exactly how many queries are run against the database, you need to set that up ahead of time. With SQL Server 2012 or better, use extended events to set this up."
What do I need to setup and configure?
February 25, 2016 at 6:53 am
mcopa (2/25/2016)
What exactly do you mean by this: "if you really need to know exactly how many queries are run against the database, you need to set that up ahead of time. With SQL Server 2012 or better, use extended events to set this up."What do I need to setup and configure?
Your best bet is to create a table in a database (or create a DBA Admin type database, then create the table) to track this information. Check Books Online for the variety of system views and event items, pick out what you think you'll need, then insert the data into the table. You'll probably want to run this INSERT as a job every day or every X hours (depending on how often you think the cache clears out).
Then you can query off that table anytime you need to.
February 25, 2016 at 7:13 am
mcopa (2/25/2016)
What exactly do you mean by this: "if you really need to know exactly how many queries are run against the database, you need to set that up ahead of time. With SQL Server 2012 or better, use extended events to set this up."What do I need to setup and configure?
Here is a link to the Books Online documentation about Extended Events. It's a mechanism for gathering all sorts of different metrics about your server. One of the things you can capture is, each execution of a query against your database. You'll need to capture the rpc_complete and sql_batch_complete events. Be prepared, there is quite a bit of work to set all this up. You can use the target (look through the documentation for what that is) event_counter if all you want to know is the number of times these events are triggered. However, if the requirement is "tell me exactly how many queries are run" this is how to do it.
"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
February 25, 2016 at 7:40 am
Thanks for the information. It does seem like a lot of work. I think I will try scheduling the following query to run every couple of hours instead and see if it gets the information I need.
set transaction isolation level read uncommitted
insert into FANSQUERIES
select
creation_time,
last_execution_time,
case
when sql_handle IS NULL then ' '
else(substring(st.text,(qs.statement_start_offset+2)/2,(
case
when qs.statement_end_offset =-1 then len(convert(nvarchar(MAX),st.text))*2
else qs.statement_end_offset
end - qs.statement_start_offset)/2 ))
end as query_text,
db_name(st.dbid)as db_name,
object_schema_name(st.objectid, st.dbid)+'.'+object_name(st.objectid, st.dbid) as object_name
FROM sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(sql_handle) st
where db_name(st.dbid) = 'FANS'
ORDER BY last_execution_time
February 25, 2016 at 8:43 am
And that'll work, sort of. You'll have to resolve the counts on executions between each set of data collection. You'll have to sweat queries that aren't stored in cache so don't have accurate counts. I don't think setting up extended events is actually more work. It's probably less, but you will have to learn extended events. However, if you're working on SQL Server 2012 or greater, you should anyway.
"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 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply