x

The Gestalt of Performance? Just curious...

Using SQL-2000, I have seen cases where I have had performance problems with a SP, isolated the query that caused the issue, then run the query by itself and the performance was fine. It was only when the query was run in the context of the SP was there a problem.

My apologies for not having a specific query to show you. Unfortunately, I can't show that. I can tell you it was a complex query with multiple joins and a where clause with an "IN" or an inner join involved. (Both methods were tried there.)

I'm mainly hoping someone else has experienced this and knows why it happens.

more ▼

asked Aug 10, 2010 at 09:21 AM in Default

Mark gravatar image

Mark
2.6k 23 25 27

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first
Parameter sniffing is usually the cause of something like this. When you run the query as just a query, all the parameters are local, so SQL Server can look at them, sniff them, and determine an execution plan based on the values. As soon as you put parameters in a stored procedure, SQL Server assumes an unknown value in the parameter, correctly, and creates a different execution plan. In most cases, this works well. In some cases it doesn't. Then you get into using the common work-arounds, like setting the procedure parameters to local parameters inside the query after initiating the local parameter with a known value. This is basically the same as using the OPTIMIZE FOR query hint in SQL Server 2005/2008.
more ▼

answered Aug 10, 2010 at 09:49 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.1k 19 21 74

Thanks Grant (+1), I remember reading about this behavior before, but didn't think to apply it to this situation. Interesting, I'll look into that more. Could this also apply to later versions of SQL too then?
Aug 10, 2010 at 10:55 AM Mark
Yes, but you get a lot more ways to fix the issue. Just remember, most of the time, parameter sniffing is a good thing. Just not all the time.
Aug 10, 2010 at 11:08 AM Grant Fritchey ♦♦

I see that Gail Shaw wrote some informative articles on parameter sniffing:

http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/

So far, she has the best articles I've been able to find on them.
Aug 10, 2010 at 03:05 PM Mark
Gail's articles are all good.
Aug 11, 2010 at 04:32 AM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

Do you have any conditional statements in your procedure? Do you alter any of the input paramaters? If you do, the optimizer give you an incorrect execution plan. The statement below may cause an incorrect execution plan.

create proc spTest @id int 
as 
select @id=id from someTable where someCol = someValue

SELECT * FROM someOtherTable WHERE someOtherCol=@id

You can compare the execution plans to see if there is any differences.

How many joins are involved? More than 4 joins in SQL server 2000 will cause the optimizer to give up, but that doesn't explain why the code works when you run the code outside the procedure.

Do you execute all the code from the procedure? I have seen cases when the compilation of the procedure took 20 seconds, but the execution of the statements took less than a second. It was caused by some complex statements and some "constants". When I replaced the constants with the values the compilation was also less then a second. I will try to add some pseudo code for you to consíder.

    CREATE PROC spTest2
    AS
    declare @x int, @y --"constant"
    SET @x=10
SET @y=20
    SELECT * from table1 t1 
    INNER JOIN table2 t2 ON t1.x=t2.x
        INNER JOIN table3 t3 ON t2.z=t3.z
WHERE t2.y IN (@x, @y) --replaced this with values and it worked
Personally I think it was really strange, but the procedure was recompiled for every execution.
more ▼

answered Aug 10, 2010 at 09:37 AM

Håkan Winther gravatar image

Håkan Winther
15.6k 34 37 48

@Hakan, sorry but none of those apply. The SP itself was fairly simple.
Aug 10, 2010 at 09:43 AM Mark
OK, since you've edited your answer, maybe some of those conditions apply. I didn't know that more that 4 joins in SQL 2000 would make the optimizer give up for example. (+1)
Aug 10, 2010 at 10:53 AM Mark
(comments are locked)
10|1200 characters needed characters left

Another culprit is the ARITHABORT setting which may be set differently for the stored proc and your SSMS session. [more here][1]

[1]: http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx
more ▼

answered Aug 10, 2010 at 04:14 PM

Scot Hauder gravatar image

Scot Hauder
6k 13 15 18

Thanks Scot, I didn't know about the ARITHABORT setting.
Aug 11, 2010 at 08:37 AM Mark
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x473
x407
x245

asked: Aug 10, 2010 at 09:21 AM

Seen: 1434 times

Last Updated: Aug 10, 2010 at 09:23 AM