Post reply

Stored Procedure Performance

  • 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

  • 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.

  • December 1, 2005 at 6:30 am

    #607013

    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/

  • December 1, 2005 at 7:40 am

    #607046

    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

  • December 1, 2005 at 7:55 am

    #607058

    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.

     

  • 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.

  • December 1, 2005 at 11:17 am

    #607146

    Thanks all for your help.  After further review and research all that was needed was to run:

    sp_recompile against a few tables.  Worked like a charm.

     

  • December 1, 2005 at 3:46 pm

    #607219

    Good to hear. 

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

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