Help needed with an SP that sometimes runs slow

Question

Help needed with an SP that sometimes runs slow

gene-704559

SSC Enthusiast

Points: 145
More actions
February 7, 2009 at 1:29 pm

#197003

I have an interesting problem that I have been trying to deal with for about 2 months now. We have a stored procedure that will run with an execution time on average of .125 seconds. Sometimes this stored procedure will jump up the execution time to an average of 20+ seconds. This execution time will stay like this until the procedure is recompiled, sometimes. It is really hit or miss if this fixes the problem.
I have a monitor running that will call the web page that calls the stored procedure, this is where I get the execution times. If the webpage begins to return slow, then I also check to see how the stored procedure runs in Managament Studio, it is still running as fast as ever through there.
This procedure is running in SQL 2005 on a database in 8.0 compatibility mode. It is being called from a classic ASP page using ADO.
I have optimized the stored procedure about as much as I think I can. That doesn't mean that there is not something else that can be done.
I have toyed with the idea of actually specifing the execution plan for it to use, but have not done that. I am researching on how to do that now.
Any help or ideas, or even just new questions would be greatly appreciated.
Thanks,
Gene D.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

It would help to have the actual execution plan and the code. It may also help to have the DDL for the tables including indexes. Just from your description of the problem it is difficult to tell you what else may be going on or what to look at.

gene-704559 SSC Enthusiast Points: 145 More actions · Answer 2

Thanks. I have included the stored procedure, tables and indexes.

matt stockham SSCrazy Eights Points: 9942 More actions · Answer 3

Too complicated for me to really take it all in, but a couple of thoughts:

1) the "with recompile" statement has been commented out - does this mean you tried it and it made no difference?

2) you are using a couple of table variables .... depending on the volume of data going into these it may be better to use temp tables.

3) are stats up to date?

Can you post actual execution plans for a fast run and a slow run?

Jeffrey Williams SSC Guru Points: 90350 More actions · Answer 4

Also, you might be running into different execution plans between SSMS and ADO. That would depend upon the connection parameters being setup in ADO. One thing I have seen is that the connection is not setting ARITHABORT - and that will cause a different plan to be generated.

So, even though it runs great in SSMS - a different (and probably bad) execution plan is being used from ADO.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Lowell SSC Guru Points: 323547 More actions · Answer 5

I agree with Jeff on the different execution plans...

take this part for example:

CREATE PROC [dbo].[s4sp_IFrames_Search]

@AffiliateId int,

@Zip varchar(12),

@curriculumid int = 0,

@ExcludeClientID int = 0

because you've assigned default values, the execution plan will assume that zero would be the expected value for those two fields and optimize the execution plan base don that assumption...but how often do you use the default? I bet that when this executes and takes a long time, the two value are non-zero. This is a side affect of what is sometimes called "parameter sniffing"...the execution plan is built on an assumption, and when the data varies outside of the assumed bounds, it takes longer because the execution plan is bad for the data provided..

Here is a link to excellent explanation someone from MS provided that I recently posted on parameter sniffing:http://www.sqlservercentral.com/Forums/FindPost652255.aspx

Jeffrey Williams (2/8/2009)
Also, you might be running into different execution plans between SSMS and ADO. That would depend upon the connection parameters being setup in ADO. One thing I have seen is that the connection is not setting ARITHABORT - and that will cause a different plan to be generated.
So, even though it runs great in SSMS - a different (and probably bad) execution plan is being used from ADO.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!