Corrupted Execution Plan

Question

Corrupted Execution Plan

Loner

SSC-Insane

Points: 21279
More actions
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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Ninja's_RGR'us

SSC Guru

Points: 294069

October 21, 2011 at 8:11 am

#1397994

Please post the good & bad actual execution plan.

Loner SSC-Insane Points: 21279 More actions · Answer 5

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?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ninja's_RGR'us (10/21/2011)
Please post the good & bad actual execution plan.

Loner SSC-Insane Points: 21279 More actions · Answer 8

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

How do I post the execution plan?

1

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

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 :-).

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 10

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

Loner SSC-Insane Points: 21279 More actions · Answer 11

Is there any way to determine what the problem is?

How do I find out if it is parameter sniffing?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Ninja's_RGR'us (10/21/2011)
Ninja's_RGR'us (10/21/2011)
Please post the good & bad actual execution plan.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 13

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

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