Why does this SP require recompilation?

  • Using SQL Server 2000 running under W2K server with two procs and 2GB of RAM.

    Server load is minimal, less than 50 users connected (using connection pooling so physical connections are much less).

    Database is small (under 500MB).

    sProc in question is very simple SELECT with two inner joins. Primary table is about 150K records, each record is under 2K. Table has approximately 200 records added per business day (records are never deleted). Weekly maintenance plan is run with 'Reorganize data and index pages'.

    Normally this sProc returns in under 50ms. Every 14-21 days, a speed degradation becomes apparent, increasing as time passes where it is over 1000ms.

    If I simply recompile the sProc, performance again returns to sub 50ms.

    Only thing I can think of is that reorganizing the data and index pages during the weekly maintenance is the culprit - is this not something you should do regularly and if this is the cause, should you routinely recompile all of your stored procedures as well.

  • As more data be added into your tables and changing data in indexed columns everyday,  the original query plans used to access its tables may not be effecient than before. At this time, The SPs should be recompiled.

    Do you have the execution plans when it runs in normal and poor performance? By review the execution plans, we may find more information whether it keeps use the original plan or the query has been re-optimized by SQL Server after 14-21 days.

     

     

     

  • Are your stats being updated? Check the db properties and be sure that update stats is checked.

     

     

  • Yes, Update Stats is checked for this db.

    This is the only sProc that appears to have this issue. I had been under the impression that MSSQL would recompile the sProcs as needed as records were added/removed/modified vs it being a manual process.

    As for execution plans: No, I did not keep one from the lower performance, but I will next time (if no solution is found in the interim).

  • Run Profiler, add EventSubClass column and you should be able to find the reason of recompilation.

    http://support.microsoft.com/default.aspx?scid=kb;en-us;Q308737

  • chopeen's suggestion is what I use........

  • Thanks, I will try that next time I experience the issue, however - does not MSSQL normally recompile sProcs as the stats change and dictate? I would imagine the problem is simply with the added records and reorganization during maintenace, the plan is no longer effiicent. Most of my tables have many more records and I have not had to manually recompile sProcs for them.

  • Yes, MS SQL Server does recompile a SP when statistics change. Profiler can tell you if this is the reason of a particular recompilation.

       EventSubClass  Reason for recompilation

       -----------------------------------------------------------------------

       1              Schema, bindings, or permissions changed between compile

                      or execute.

       2              Statistics changed.

       3              Object not found at compile time, deferred check to

                      run time.

       4              Set option changed in batch.

       5              Temp table schema, binding, or permission changed.

       6              Remote rowset schema, binding, or permission

                      changed.

  • I understand, however the problem is that it is not being recomplied by SQL Server, I am having to do it manually.

  • Oh, OK, I did not fully understand you.

    I'm not sure if this will help you, but take a look at:

    http://www.perfectxml.com/downloads/Ch1_SQLSP.pdf

  • This problem still plagues me, but I do have additional info.

    The sproc contains 4 different SELECT statements that returns different resultsets of the same data based upon a specified input parameter.

    Of these 4 Selects only one is effected-

    This select uses TOP 100 eg:

    SELECT TOP 100 Col1, Col2, Col3 FROM Table WHERE...

    Plus the sproc use IF...Else with the input param.

    The other selects are similar (only the WHERE changes) and do NOT include the TOP 100.

    Once narrowed to an issue with TOP, further research found suggestions to

    1) use "WITH RECOMPILE" within the sproc (not my first choice since this sproc is heavily used).

    2) Recode and get rid of the IF...ELSE

    3) A post from Celko stated: "SELECT TOP (n)is a poorily defined, strictly proprietary "feature", but it was easy to implement in SQL Server because of the use of a file system under the covers." He goes on to list two suggestions on recoding the query without the use of TOP.

    I am currently testing these in reverse order 3, then 2 then 1 and will post my results in the hopes this may be of benefit to someone else.

    I appreciate any additional suggestions anyone may have.

  • Final results:

    I modified the code so that the sProc with the SELECT TOP 100... is in a separate stored procedure. This removed the issue of a potential problem with a branching IF/ELSE with multiple SELECTs in a single sProc.

    However, this did not resolve the issue.

    The problem appears to be related solely to the use of the SELECT TOP 100.

    I also tried recoding as suggested in the Celko post, but that required the use of SELECT Distinct and the pefromance was demonstrably worse then using WITH RECOMPILE.

    So, does anyone know of any inherint issues using SELECT TOP that would cause this type of problem?

  • remove TOP 100 and use following

    SET ROWCOUNT 100

    -- ur select query here

    SET ROWCOUNT 0

     

    What is performance impact of this ? Well as per BOL

    If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set. The entire result set is built in the specified order and the top n rows in the ordered result set are returned.

    The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP in these ways:

    • The SET ROWCOUNT limit applies to building the rows in the result set after an ORDER BY is evaluated. When ORDER BY is specified, the SELECT statement is terminated when n rows have been selected from a set of values that has been sorted according to specified ORDER BY classification.

    -- Amit


    -- Amit



    "There is no 'patch' for stupidity."



    Download the Updated SQL Server 2005 Books Online.

  • I have not tried that, will give it a shot and see if the plan stays updated, thanks.

Viewing 14 posts - 1 through 13 (of 13 total)

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