July 23, 2008 at 10:04 am
When I run the all sql statements of this stored procedure in SQL management studio - All of them take about 17/18 sec. (no change in the sql statements - the batch of sql statements)
But if run the exec SP it takes anywhere between 18 sec to 22 minutes. Running this stored procedure from RS is a nightmare.
I know it is difficult to make anything out of these statements without looking at SQL or Execution plans.
Most often I notice IO_COMPLETION Wait types in Activity monitor while executing Stored Procedure. Also in the Execution Plan of SP the insert into TEMPDB has higher percentage.
The relative cost of the some statements are varying between two types of execution.
If any one of you had run into this kind of situation, please share the steps you taken to resolve it.
Thanks.
July 23, 2008 at 10:23 am
There should be no significant difference that I am aware of when running them as a stored procedure or as individual statements. In certain cases it can affect whether and which execution plans are reused, but that should only add a few seconds at most, not cause it to jump from seconds to over 20 minutes.
To ask a few questions:
1. Are you using the same data sets when testing it as a stored procedure and as straight scripts?
2. Are you doing something that would change the isolation level? Perhaps you have it at read uncommitted when executing directly and you let it reset to the default of read committed when you run it as a procedure? (Depending on what else is happening on the server, read uncommitted can read through locks which can eliminate a lot of the wait time)
3. Could you post a (possibly sanitized) version of the query?
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
July 23, 2008 at 10:51 am
There are lots of things that can cause this. Parameter sniffing, poor cached execution plans, etc.
If you cannot post the procedure, DDL, and execution plans, it will be nearly impossible to tell you what the issue is. However, this same question gets asked about once a week on this site. It is often a different cause, but if you search the forums a bit you may find other postings in which execution plans and DDL were available that can give you some ideas of what to look for.
July 23, 2008 at 11:17 am
Make sure you're using exactly the same parameters against exactly the same data set (always compare apples to apples).
Get the statistics IO for the set of queries and the stored procedure (again, same parameters, same data set). See which ones are running slower. Then focus on their execution plans to see what that statement is doing.
Classic stuff, look for scans, fat pipes, etc.
As others have already stated, your symptoms are vague, so it's hard to give you specific information in return.
"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
July 23, 2008 at 12:18 pm
The one thing I noticed different is the estimated number of rows is 1 in Stored procedure, which made the node Merge/Join instead of Nested Loop and everything from that node onwards has different execution path.
Sorry I could not place the query and execution plan. Being programmer myself I can easily understand the difficulty of debugging or giving information on half baked info.
There are only couple of lookup(s) in the whole plan but the those table sizes are 200 Rows, which is understandable.
Thanks.
July 24, 2008 at 10:30 am
I added a parameter to SP. For a different value, only one SQL statement get executed. All sql statements are going against same tables. What is puzzling is execution of the Stored procedure took 1 sec for one type of parameter (let's the value is 'XYZ') and for another parameter (let's say the value is 'ABC'). The sql statement that getting executed is same in both cases but the where clause value getting is either 'XYZ' or 'ABC' (like column = 'abc' or column = 'xyz').
The behavior of these statements in management studio are consistent. Any ideas??
Thanks.
July 24, 2008 at 11:39 am
HYDNIZ (7/24/2008)
I added a parameter to SP. For a different value, only one SQL statement get executed. All sql statements are going against same tables. What is puzzling is execution of the Stored procedure took 1 sec for one type of parameter (let's the value is 'XYZ') and for another parameter (let's say the value is 'ABC'). The sql statement that getting executed is same in both cases but the where clause value getting is either 'XYZ' or 'ABC' (like column = 'abc' or column = 'xyz').The behavior of these statements in management studio are consistent. Any ideas??
Thanks.
Aside from execution plan reuse which should add only a second or two at most, I can't think of why it would run slower as a procedure than run directly.
The different execution times could be caused by having different sizes of result sets when you use the different parameters though.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
July 24, 2008 at 2:12 pm
After analysis of both execution plans, the query when executing in Management studio is using Merge Join instead of Nested Loop.
So I included the hint "Option Merge(join)" and everything is hunky dory. Now the whole stored proc runs less 30 sec.
Thanks for the input.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply