Stored Procedure Performance

Question

Post reply

Stored Procedure Performance

jgljgl

SSC Veteran

Points: 286
More actions
November 30, 2005 at 11:44 am

#112823

Hello. I have a stored procedure that is the bread and butter of the website I administer. A few days ago users were getting script timeouts. Upon further review I determined that the execution time had increased on this procedure from 1 second to 43 seconds.

I did some db maintainence, purged records, updated statistics, re-indexed and it was still running at 43 seconds. I re-booted the server. Still at 43 seconds.

In a bind, due to holiday web traffic, I decied to re-write the stored proc as the original developer was somewhat sloppy and it had unneccessary self-joins. Before commencing the re-write, I created a backup. I tested the backup in query analyzer, and presto 1 second! I ran the old procedure and it still ran like crap at 43 seconds. Yet the code for the 2 procs is identical.

Can someone explain:

1)Why this is happening.

2) How do I monitor stored procedure cache?

3) How do I flush stored procedure cache?

Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

Ian Yates SSCoach Points: 19738 More actions · Answer 1

Ian Yates

SSCoach

Points: 19738

December 1, 2005 at 5:23 am

#607004

Not sure about 1, except maybe recompiling may help?

I can answer #3 easily -

Run dbcc freeproccache

For benchmarking always run

dbcc dropcleanbuffers

dbcc freeproccache

This will ensure that SQL is not caching data from disk between reads so you can accurately compare subsequent executions of stored procedures.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

I had a similar issue - i'll describe - if same follow instrauctions < grin >

stored proc ran badly, if I cut and pasted the code in QA and ran as a query it ran great.

in my case it was table scanning as the proc and index seeking in the query ( 3ok i/o vs 6 i/o ).

So run the proc and query with showplan and stats i/o and compare.

The solution was wierd and I mean weird !!! If you script the proc out through EM check the set options either side of the proc create code. For my problem proc ansi nulls were set to off .. recreating the proc with ansi nulls on solved the problem. - I posted on this previously

e.g.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FindText]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[sp_FindText]

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

Create procedure sp_FindText

my problem proc had the ansi_nulls OFF.

Kalen Delany has a posting somewhere about "sticky options" .. very weird < grin >

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

esmith21 SSC Journeyman Points: 87 More actions · Answer 3

Another cause may be that the stored procedure is getting a bad plan stuck in the cache due to the parameters passed in.

SQL Server does what's called "parameter sniffing." This is essentially building a query plan based on the parameters passed in if a query is not in the cache. This is helpful unless your parameters could greatly effect the plan which means that at least some of the time you'll be getting less than optimal plans.

In the past I've been successful solving this type of problem by determining what parameters were getting the bad plan and then either modifying the query or adding indexes to force the query to get a better plan all of the time.

Here is a good blog article by Ken Henderson about parameter sniffing. There are lots of resources about this all over the web.

http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx

peterhe SSChampion Points: 11469 More actions · Answer 4

I used to have a SP that had performance issue. My trick is adding WITH RECOMPILE option in the create procedure statement. It finished within 1s and very stable.

Recompiling does not affect performance so seriousely as we imagined. With recompiling, the SP can always get the latest/optimized plan to run , so in some cases it's may not a bad thing anymore.

esmith21 SSC Journeyman Points: 87 More actions · Answer 5

esmith21

SSC Journeyman

Points: 87

December 1, 2005 at 8:20 am

#607075

Be careful using WITH RECOMPILE since SQL will not cache a plan for the proc. Recompiling can be very expensive depending on how complicated the proc is. You could wind up spending more time compiling then running queries.

This may be a good work-around until you figure out the real problem.