July 30, 2013 at 1:39 am
Hi all,
I am facing a weird problem since couple of days, When we tried to execute the any SP (Randomly) sometimes throws the error "The definition of object has changed since it was compiled" and if I recompile it or alter the procedure it works fine.
My Doubts :
1. First of all why this error occurs and how can I tackle this.
2. Is there any auto process I can set, so this problem never occurs.
3. I read somewhere in the forum that sp_recompile running of whole database will solve it, but running it every time is right approach ? as this will clear the execution plan of that object.
Thanks,
Bhaskar Shetty
July 30, 2013 at 8:18 am
Bhaskar.Shetty (7/30/2013)
Hi all,I am facing a weird problem since couple of days, When we tried to execute the any SP (Randomly) sometimes throws the error "The definition of object has changed since it was compiled" and if I recompile it or alter the procedure it works fine.
My Doubts :
1. First of all why this error occurs and how can I tackle this.
2. Is there any auto process I can set, so this problem never occurs.
3. I read somewhere in the forum that sp_recompile running of whole database will solve it, but running it every time is right approach ? as this will clear the execution plan of that object.
Thanks,
Bhaskar Shetty
Do you have either dynamic T-SQL within your queries, or, are you referencing objects within the queries where the structure of those objects is subject to change. Either of those situations can lead to the situation you're describing. It shouldn't be random though. It should correlate to you modifying objects within your database or your code. Not seeing your code or your processes, I can't tell you exactly where to look, but that's absolutely going on.
No, there's not an automatic thing you can set. You need to control when and how you make structural changes to the system.
Objects modifying after a compile can't be fixed by adding another compile statement. What happens if it compiles but the object changes? Isn't that precisely what's happening now? You need to identify when and how you're getting structural changes.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 30, 2013 at 6:24 pm
I'm not familiar with this error at all. Can you post your procedure code?
September 26, 2014 at 10:55 am
Hi folks
I've just seen this happen, the application went haywire and starting logging errors of
"The definition of object 'my_stored_procedure' has changed since it was compiled."
This isn't something I've ever seen before so I was slightly excited and alarmed at once. My monitoring was showing a massive increase from around 2 recompiles a second to over 350, the procedure being executed now showed up as a new item in my hit list and the number of transactions per second had whacked up too.
The application reported the above so this explained things, the procedure was being recompiled over and over again as something wasn't right. The default trace shows nothing worthy and the logs are clean.
Where do I go to find out what went wrong with the compilation and mitigate the chances of it reoccurring?
Thanks in advance folks!
September 27, 2014 at 3:11 am
hi there,
meanwhile I got this error too in some situations.
The definition of object has changed since it was compiled
It occurs when you run a ALTER PROCEDURE command while the altered procedure is currently running.
Could this be a reason in your case too?
If not, again, please post your procedure code, otherwise helping is quite difficult.
September 29, 2014 at 2:12 am
Hi Wolfgang
Thanks for the reply. I've checked the default trace and the only time anything was done on this database was when I ran an alter statement to fix the issue, nothing prior.
As I mentioned, the number of recompiles went up on the instance quite dramatically. When I scripted out the ALTER statement, and simply ran that without change, the recompiles dropped back to baseline and all was well again.
Looking around the internet, this seems to have happened to many people but I can't, as yet, find reason that fits my scenario.
Here's an anonymised copy of the code so you can see how simple it is:
BEGIN
SET NOCOUNT ON;
SELECT table1.ID
FROM table1 WITH(READPAST)
WHERE table1.ID2 = 1000000
AND
table1.ID3 = 0;
END;
Again thanks for taking time to look over this issue.
Mart
October 3, 2014 at 1:57 pm
hmm... sorry, no idea...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply