question

Chris 2 avatar image
Chris 2 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
sql-server-2012
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Chris, Can you provide the SP and execution plan so that someone could offer assistance
0 Likes 0 ·
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.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hussainpatel avatar image
Hussainpatel answered
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%'
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

"...If stored procedure have multiple sections it kills the performance..." I'd have to disagree with this as a hard and fast rule. Sometimes it might be the case. A lot of the time it won't be. It just depends on the query in question.
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.