asked
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
You have several answers below. For each of those answers that are helpful you should click on the thumbs up next to the answers so that it turns green. If any one of the answers below lead to a solution to your problem, click on the check mark next to that one answer so that it turns green. This web site works best if you do the voting and marking of answers so that the next person who looks at your question knows what the solution was.
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%'
19 People are following this question.
