krypto69 (7/3/2015)
Thank you so much Grant. You are always helpful and informative. Looking into your suggestions now.Capture the wait statistics when the procedure is run in order to understand what is causing it run slow.
What do you recommend to capture the wait stats?
You can use the really simple, if not entirely accurate, approach of querying sys.dm_os_wait_stats before and after the query. Or, better, to get a really accurate measure of the waits for just the execution of your query, you can use extended events. Paul Randal has a good blog post[/url] on 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