Corrputed Stored Procedure

Question

Post reply

Corrputed Stored Procedure

Qian Lu

SSC-Addicted

Points: 425
More actions
December 27, 2006 at 8:23 am

#171419

I had a corrupted stored procedure giving the web application runs it a Timeout Error. Until I figured out something wrong with the stored procedure by running it in QA, and then forced the code to recompile.
My question is how would an existing working stored procedure be corrupted without anybody changing it? Any ideas?
Thanks!
--- Lu

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

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

MohammedU SSCoach Points: 19644 More actions · Answer 1

If procedure was corrupted then how you were able recompile it?

I think your procedure was using incorrect execution plan ... recompilation forced to generate new execution plan...

MohammedU
Microsoft SQL Server MVP

Qian Lu SSC-Addicted Points: 425 More actions · Answer 2

You're probably right. Somehow the cached excution plan was messed up.

Thanks for your help!

--- Lu

Qian Lu SSC-Addicted Points: 425 More actions · Answer 3

The execution plan of the same stored procedure was massed again today. What could be the causes to corrupt the execution plan of the same stored procedure? By my knowledge I know that nobody has changed anything on it.

Thanks again for any suggestions and comments!

--- Lu

Lowell SSC Guru Points: 323518 More actions · Answer 4

do you have auto-create statistics turned on on the database?

an execution plan may become less effective if the table(s) it queries have a lot of inserts/updates. the statistics are used to determine the best way to access the data.

the execution plan gets created based on the statistics of the table(s) at the time of the compilation, so if the tables it is using change a lot, the execution plan might become slower and slower, unless the statistics get updated, or the procedure gets recompiled.

with statistics turned off, I'd expect procedures to become slower and slower till they time out applications.

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!

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

Just for Qian Lu and anyone who know the answers of the following questions.

Could you tell me how to save an execution plan, and how do you know an execution plan is corrupted?

Many thanks

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

This is where you can see the cached plans :

Select * from master.dbo.syscacheobjects

As to know when they are corrupted, I would clear the cache, run the proc in question and make sure it runs fine (optimal). Then I would fetch and save the execution plan in another table. Once you got that you can periodically scan the system table and compare against your copy. But keep in mind that a different set of parameters may require a second execution plan. If you have that problem, you can either create a 2nd procedure and call the correct procedure depending on the parameters, or add the with recompile option in the procedure. Both will make a performance hit, you'll have to test to see which is the lightest in your environement.