March 30, 2015 at 2:24 pm
Our monitoring tool shows that our production system periodically experiencing large rate - up to 800 memory pages/sec. How to find out which particular queries, S.P., processes that initiate this?
Thanks.
March 30, 2015 at 3:02 pm
The best way is to capture queries using extended events. You can see how much memory the query consumes. You can also look at sys.dm_exec_query_stats to see some aggregation information about queries that are currently in cache. Assuming the query hasn't aged out of cache since you ran it, you might see it there (but possibly not).
"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
March 30, 2015 at 3:27 pm
Thanks Grant for these leads.
I am not experienced with extended events, so I started digging with sys.dm_exec_query_stats. Particularly with total_physical_reads. But what I can use to join two snapshots of this view (I will run this view twice and then calculate the delta between total_physical_reads) ? The only good candidate looks sql_handle.
About XE, can you point me to a query/script that I can use ?
Thanks
March 30, 2015 at 6:57 pm
You'll have to combine that view with other functions and views to get a complete picture. You can use sys.dm_exec_sql_text to get the query and sys.dm_exec_query_plan to get the plan.
I'd still probably go for the extended events instead of using the view. It only has stuff that's in cache, and you could lose stuff due to recompiles, or, even queries with a recompile hint never going into cache.
"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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply