Slow when runnning whole proc but fast when executing section by section
Hi, On MS SQL 2012/Windows 2008 R2, it takes less than 10 min when executing section by section from a procedure; but it takes long time to process whole procedure. Is there something I could check? Thanks. Chris
Without seeing the query and the execution plan, it's impossible to make a suggestion what the specific problem is. However, the troubleshooting steps I would take are, 1) get the execution plan and see what the heck is going on with the query. 2) Capture the metrics using extended events at the statement level. That way you can identify which of the statements within the procedure is causing the most pain. Figure out why and fix it.
If stored procedure have multiple sections it kills the performance try ti make single query. Mean time check execution plan usecounts and cache plan created time. usecounts should increment on each execution and cache plan time shouldn't change. Script : SELECT A.bucketid,A.usecounts,A.objtype,B.text,C.cached_time FROM sys.dm_exec_cached_plans A CROSS APPLY sys.dm_exec_sql_text (A.Plan_handle) B INNER JOIN sys.dm_exec_procedure_stats C ON C.plan_handle=A.plan_handle where B.text LIKE '%Pass sp name%'