April 11, 2017 at 9:59 am
Hello,
If I want to search which are all procedures are recompiling part of the with recompile option in the procedure or by other means?
Is there any quick handy way to find the procedures?
Thanks.
April 11, 2017 at 12:06 pm
Best way would be to set up extended events and capture the recompile event. You can easily filter it down to particular databases or even particular procedures. You can also query sys.dm_exec_procedure_stats to see a count of recompiles for all the procedures that are currently in cache. However, remember, that's accumulated for the period that procedure has been in cache only, and some procedures are likely to not be in cache at all.
"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
April 11, 2017 at 12:13 pm
I'm running the below query but not seeing any recompile column.
Am I missing something?
SELECT TOP 1 d.object_id, d.database_id, OBJECT_NAME(object_id, database_id) 'proc name',
d.cached_time, d.last_execution_time, d.total_elapsed_time,
d.total_elapsed_time/d.execution_count AS [avg_elapsed_time],
d.last_elapsed_time, d.execution_count ,
*
FROM sys.dm_exec_procedure_stats AS d
ORDER BY [total_worker_time] DESC;
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply