Stored Proc NOT using Index?

WayCool

I have a stored proc that runs in 1:13, but when I copy the SQL and define the parameters in SQL Server Management Studio, it runs in less than 1 second. Is there anything that would cause the SP to NOT use the existing indexes SP code is below:

SELECT DISTINCT People.People_GUID,People_Desc =

isnull(LastName,'') + ', ' + ISNULL(FirstName,'') + ' ' + cast(PersonNumber as varchar(10))

FROM People,

cpccase cc,

PeoplePersonType PPT

where People.Customer_GUID = @Customer_GUID

and CC.Customer_GUID = @Customer_GUID

and PPT.Customer_GUID = @Customer_GUID

and People.People_GUID = CC.People_GUID

and CC.Active = 1

and People.People_GUID = PPT.People_GUID

and PPT.PersonType_GUID = @PersonType_GUID

and (@Query is null or

isnull(LastName,'') + ', ' + ISNULL(FirstName,'') + ' ' + cast(PersonNumber as varchar(10)) like @Query + '%')

order by 2

Thanks!

Mike

richbrownesq

This could be as a result of parameter sniffing, SQL Servers attempt to provide an optimum query plan based upon values of parameters. Sometimes, this is not the "best" overall plan due to atypical values being passed in and therefore the cached plan for the stored procedure is less than optimal for the majority of queries causing poor performance. The code executes fast in SSMS as a new plan is generated on the fly.

Read more here:

http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx

HTH!

asiaindian

Hello,

I agree with richbrownesq. I had a similar issue. If the query is a critical/important one, try using an index hint. This would help in executing within desired time.

Regards....

Arnie Rowland

Here is some additional infomation concerning Parameter Sniffing.

Stored Procedure ¨CParameter Sniffing
http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx
http://tinyurl.com/f9r2
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/17/444.aspx

Louis Davidson

Parameter sniffing IS one example, but if you are just building the proc and then running it a few times, then that is *probably* not the problem. If it is fast sometimes and slow others, the parameter sniffing (don't shoot me) smells like a good answer. Another great test is to add the WITH RECOMPILE option and see if that helps.

Another common problem with stored procedures are that plans can be a bit more conservative than when you run straight SQL. You need to look at the actual plans that each query uses (look at the actual plan, not the estimated plan) and see what indexes are being used in each case. Then look at the values that you are passing (I am assuming that you are using the same parameter input in both cases) and see how many rows will be returned by the query.

In some cases, I will just turn the query into a dynamic sql call and that will correct things. First step though is to look closely at the plan.

Madhu K Nair

As already said , post back the execution plan... you are using lot of function which may eliminate the usage of index...

WayCool

Thanks for all the suggestions. I don't want to use the WITH RECOMPILE because this stored proc is used very often. After I edited the sp, ran the ALTER, then changed it right back to what is was and ran the ALTER, it forced the recompile and performance was great.

Is there a way to schedule some kind of recompile on all of our stored procs nightly when our users are offline This would alleviate the performance problem and not require the WITH RECOMPILE every time the SP is executed.

Bottom line is, the execution plan appears to be correct when the SP is recompiled, I just need a way to force the recompile for all of our SPs nightly.

Thanks!

richbrownesq

You can force recompliation by using the system stored procedure sp_recompile 'objname'

You could use it for just the stored procedures which are problematic. If you wanted to do all the procedures then you could create a cursor to loop through each one and schedule this via SQL Agent.. Also, running sp_recompile 'tablename' marks all objects that reference that table for recompilation.

Check Books Online for more details.

HTH!

Louis Davidson

>>Bottom line is, the execution plan appears to be correct when the SP is recompiled, I just need a way to force the recompile for all of our SPs nightly.<<

What you have described IS parameter sniffing, and the plan could be good, could be bad based on the first time statements get executed (and how much the procedure is executed).

When you say "very often" what do you mean 1000 times a day/hour/minute/second

If you know what the condition is that causes the bad plan (like when I pass 1 as a value, it gets a bad plan, any other value works great) If you can narrow that down, then make two more procedures, one to call another procedure with the offending value, one to call the main one. Then you can eliminate any kind of parameter sniffing bad plan conditions for very active procs.

If it is only used a few thousand times a day, and recompiling takes just a few milliseconds, I would use WITH RECOMPILE or possibly dynamic sql