Post reply

Different execution plan between stored proc and ad-hoc query

  • October 24, 2006 at 12:56 am

    #173258

    We have just encountered a problem with a particular stored procedure which I would appreciate anyone's thoughts on.

    The proc in question contains a single select statement which simply joins four tables and filters based on two parameters. It normally runs in well under a second but this morning we came in to find it was running in anything up to a minute.

    After investigation we found that it was running a clustered index scan on one of the larger tables (approx 10.5m rows) rather than using the appropriate index. However, when we took the select statement out and ran it as an ad-hoc query it was utilising the index and performing correctly.

    We tried recompiling the proc and also ran sp_updatestats on the database but we were still getting the different execution plans being generated. It was only after we ran UPDATE STATISTICS WITH FULLSCAN on the table in question that the proc went back to performing normally.

    Can anyone shed any light on why the proc and query were using different execution plans, even after recompiling the proc? The table is heavily utilised in all areas of the system so I could understand if the stats got out of date (although autostats is on and sp_updatestats does get run on the database 2 or 3 times a day through an automated process).

    Also, this is the second week in a row where this behaviour has occured so we need to try and mitigate the risks next week. Obviously we could schedule a stats update with fullscan early Monday morning but we would like to understand what is causing this and try and fix it "properly".

    cheers

    James

    -----
    JL

  • October 24, 2006 at 3:15 am

    #667511

    It's probably set options, if you look in the proc cache are the setops different?

    script out your offending proc, the first lines should be

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE   PROC.................

     

    if it isn't then make it so and recreate your proc - should solve the problem.

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

  • October 24, 2006 at 3:29 am

    #667513

    Thanks for this. You are right, both options are set OFF for the proc.

    Can you explain why this would affect the generated execution plan?

    -----
    JL

Viewing 3 posts - 1 through 2 (of 2 total)

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