February 22, 2017 at 9:00 am
I have queries that will tell me top CPU usage but i am trying to get a query which can tell me how much CPU was utilized by the adhoc query or CPU utilized by a sproc. Would be nice to also know if query had parallel plan. Please let me know if you have such query.
February 22, 2017 at 9:16 am
You can query the cache to get aggregate information using sys.dm_exec_query_stats. It won't show individual calls, but will show, over the time the query has been in cache, how much CPU it uses on average, etc.
If you need to know individual calls, the best way is to use extended events (no GUI for this in 2008) or trace events (higher overall cost to the system).
"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 22, 2017 at 12:44 pm
John Mitchell-245523 - Wednesday, February 22, 2017 9:17 AMNot sure I understand. Does this help? You'd have to add a where clause looking for your query text in the "text" column.John
Thanks. Doesn't rollup to stored procedure. I am stress testing a stored procedure, i am trying to find how much CPU is it using.
February 22, 2017 at 5:38 pm
curious_sqldba - Wednesday, February 22, 2017 12:44 PMJohn Mitchell-245523 - Wednesday, February 22, 2017 9:17 AMNot sure I understand. Does this help? You'd have to add a where clause looking for your query text in the "text" column.John
Thanks. Doesn't rollup to stored procedure. I am stress testing a stored procedure, i am trying to find how much CPU is it using.
Take a look at sys.dm_exec_procedure_stats.
"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