Post reply

Corrupted Execution Plan

  • October 21, 2011 at 7:56 am

    #246809

    I have a stored procedure using VARCHAR(MAX) in one of the parameters. The stored procedure is called by the web (ASP.NET) to produce a report. The stored procedure just select data from a few tables but those tables grow at least 10% a day.

    Every few days the execution plan got corrupted, it used to run in 1 sec and it ran 50 min. I recompiled the stored procedure and it went back to normal.

    I never experienced corrupted execution plan before. What would cause this to happen?

    Is it because the tables grow too fast, or the procedure called by the web or I used varchar(MAX) as one of the parameter?

    Please Help.

    Thanks

  • October 21, 2011 at 8:06 am

    #1397987

    I'm thinking it's because of statisitcs..the plan itself is fine, but the statistics used get more and more out of date as that 10% per day occurs.

    when you recompile the proc, it masks the issue with stats for a while,a s it makes a fresh query plan.

    if you were to add a job that runs, say, twice a day that ran UPDATE STATISTICS ThatGrowingTable WITH FULLSCAN, you will not need to recompile the proc anymore.

    you probably have a stats job that runs once a day or per week, but there's times when specific, busy tables need to their stats updated more often.

    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!

  • October 21, 2011 at 8:06 am

    #1397988

    Plans don't get corrupted.

    Could be parameter sniffing, could be (but probably isn't) stale statistics, could be a bunch of other related things.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 21, 2011 at 8:08 am

    #1397989

    Do you have Auto Stats on for the tables? Was the plan the same both the times or was it different?

    What are you passing in as parameter that needs to be Varchar(max)?

    -Roy

  • October 21, 2011 at 8:11 am

    #1397994

    Please post the good & bad actual execution plan.

  • October 21, 2011 at 8:23 am

    #1398007

    I have auto create stat on and auto update stat on the database.

    I used varchar(max) because the parameter may exceed 8000 bytes.

    I re-compiled the procedure so I don't have the bad execution plan anymore.

    Could it be something else besides corrupted execution plan to cause the procedure to run from 1 sec to 50 min?

  • October 21, 2011 at 8:25 am

    #1398010

    GilaMonster (10/21/2011)

    Plans don't get corrupted.

    Could be parameter sniffing, could be (but probably isn't) stale statistics, could be a bunch of other related things.

    It's hard-impossible to say more without seeing at least the query, preferably the execution plans.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 21, 2011 at 8:36 am

    #1398016

    Ninja's_RGR'us (10/21/2011)

    Please post the good & bad actual execution plan.

  • October 21, 2011 at 9:00 am

    #1398033

    I also used table variables to store large amount of data, would it be the problem.

    How do I post the execution plan?

    1

  • October 21, 2011 at 9:02 am

    #1398035

    Loner (10/21/2011)

    I also used table variables to store large amount of data, would it be the problem.

    How do I post the execution plan?

    1

    Save it as .sqlplan then when you post bottom right there's an edit attachement button (might need to hit a + button to see it).

    table variable is definitely an issue if you are using them like I think you are :-).

  • October 21, 2011 at 9:07 am

    #1398041

    Off topic, the in parameter Varchar(MAX) does not give a good feeling. Are you sending a comma separated string?

    Also are you checking for SQL Injection on the parameter that is being passed through?

    -Roy

  • October 21, 2011 at 9:09 am

    #1398044

    Is there any way to determine what the problem is?

    How do I find out if it is parameter sniffing?

  • October 21, 2011 at 9:10 am

    #1398047

    Ninja's_RGR'us (10/21/2011)

    Ninja's_RGR'us (10/21/2011)

    Please post the good & bad actual execution plan.

  • October 21, 2011 at 9:10 am

    #1398048

    Table variables and large amounts of data? Hell yes that's a problem unless all you're doing is a direct select from the table variable, no joins, no filters.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 21, 2011 at 9:11 am

    #1398049

    Loner (10/21/2011)

    Is there any way to determine what the problem is?

    How do I find out if it is parameter sniffing?

    GilaMonster (10/21/2011)

    It's hard-impossible to say more without seeing at least the query, preferably the execution plans.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 22 total)

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