November 28, 2012 at 4:39 pm
Hi,
I need to set-up job whihc can find the connect/Query which is taking high IO or High CPU or High memory.
Please let me know if anyone has this kind of script available.
Apriciate in advcance 🙂
November 29, 2012 at 2:51 am
I'm having trouble finding the exact T-SQL code but this might help. The information you're looking for is in the "Recent Expensive Queries" table in the SQL Server Activity Monitor - except for the connection SPID.
If you have the Activity Monitor open whilst you're running a SQL Profiler Trace on the same instance, you may be able to find the query that SSMS is running to get this data each time it refreshes. From the query, I would expect one of the source tables to have SPID information in it, and you could write your own data collector.
If you can't find the source query from SQL Profiler, perhaps a little web searching will find someone else that has extracted the code. Sorry I can't source it for you - a very quick look hasn't revealed anything.
Hope that helps a little.
November 29, 2012 at 5:44 am
You can query the Dynamic Management Objects for this information. sys.dm_exec_query_stats will show you which queries, currently in cache, are using the most ram, cpu & disk. You can combine that with sys.dm_exec_sql_text to see the exact statement causing issues and sys.dm_exec_query_plan to see the execution plans for the query to figure out if you have tuning opportunities.
"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 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply