March 19, 2014 at 10:02 am
Hi folks,
Figured I'd see if anyone here has some deeper insight into a surprising behavior I noted yesterday with sp_recompile on 2008R2.
We had a plan go sideways yesterday around 6PM and one of our DBAs ran sp_recompile (procname) on it. This was while the job was running…and then the job allegedly immediately failed with a “The definition of object '[stored proc name]' has changed since it was compiled.”.
My understanding of sp_recompile (based on experience and reading Books Online) is it simply marks a proc for recompilation, by dropping the plan from the cache, so when it is next invoked it has to regenerate a plan. I didn’t expect that SQL Server would consider the definition of the proc object, itself, to have changed. Looking in the sp_recompile code I am met with what I assume must be some internal MS syntax:
-- LOCK, CHECK PERMISSION, BUMP SCHEMA FOR RECOMPILE --
EXEC %%Object(MultiName = @objname).LockMatchID(ID = @objid, Exclusive = 1, BindInternal = 0)
So it looks like I can’t investigate what the code is doing any further from here. I’m trying to avoid getting into a mindset where I avoid using sp_recompile out of a superstitious attitude that it one time did something I did not expect. Does anyone have any deeper understanding of what this system stored proc is doing actually?
BTW, DBA swears up and down they didn't alter the proc, just ran sp_recompile (they did alter the proc later, so I can't look at a modify date on the proc now).
March 19, 2014 at 11:34 am
Hmmm.... I lean pretty heavily towards that proc being altered. Not arguing with you or your DBA, but that behavior is not something I've run into with sp_recompile. But, one of the requirements to running sp_recompile is that you have ALTER permissions, so... maybe it does sort of mark things as altered in the background. But... I don't think so. Everything I've read about it, the tests I've done, all the work I've seen... it just marks the object specified (and associated objects if, for example, you ran it against a table) for recompile and those procs will get recompiled the next time they're called. It's not modifying the code to my knowledge.
"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
March 19, 2014 at 11:37 am
BTW, DBA swears up and down they didn't alter the proc, just ran sp_recompile (they did alter the proc later, so I can't look at a modify date on the proc now).
This will be logged in the default trace file, if you have access to it, you can see who/what ALTERED it
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 19, 2014 at 12:03 pm
I looked there early on, via the schema changes report, but I think the cache there was overrun (one of our applications likes to deadlock every few seconds when it runs :blink:) and I lacked history. If I really want to sort this out, I could restore a full backup (fairly large DB) and then walk up through the tlogs, I suppose!
My manager was thinking it might have something to do with indexes being created on temporary tables within the proc, but so far we haven't been able to duplicate it with sample procs doing the a similar behavior.
Love your book, btw, Grant! I have the 2012 perf tuning one, good stuff.
March 19, 2014 at 12:28 pm
Thanks. Working on the update now.
"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
February 16, 2015 at 9:01 am
We are using SS2008 SP3 and I've also seen this scenario from time to time. We have a job that will attempt to detect a bad plan based on execution stats and recompile it using sp_Recompile when needed. I can say with a great deal of certainty that this job is NOT altering procedures - simply trying to recompile them. For some reason, we sporadically get the "2801, The definition of object '<procname>' has changed since it was compiled" error after the recompile.
I have also read a couple blog posts that support what your manager was saying about index maintenance being one of the causes. However, I have not been able to reproduce anything of the sort.
I found a Microsoft Connect post that indicated it was an issue in SS2005. There wasn't a lot of detail listed in the post so it looks like it was simply closed. But it sounds like we are not crazy! (Well, at least not alone! ;-))
If you have anymore information on the matter, I'd be interested in hearing it.
February 16, 2015 at 1:48 pm
I did some additional testing this morning and I was able to put together a code sample to reproduce this. (At least one way of doing so)
First, I created a sproc that has a temp table in it. The important piece here is that the temp table is referenced toward the end of the sproc. I used a WAITFOR DELAY to simulate "stuff being done" and then added a SELECT of the temp table at the end.
Next, the sproc is run for the first time since it's plan was flushed from the plan cache.
Finally, a recompile of the procedure using sp_recompile is called during the execution BEFORE the temp table is referenced. This is where the WAITFOR DELAY makes testing easier.
Code Samples:
*Stored Procedure
IF OBJECT_ID('dbo.Object_Definition_Change_Bug_Test') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.Object_Definition_Change_Bug_Test;
END;
GO
CREATE PROCEDURE dbo.Object_Definition_Change_Bug_Test
AS
CREATE TABLE dbo.#Temp
(
Col1 INT
);
-- This could be any statement but waitfor makes for a reliable duration.
WAITFOR DELAY '00:00:05';
-- If a recompile happens before this point in the execution
-- AND the procedure was compiled during the current execution, The sproc will fail with:
-- Msg 2801, Level 16, State 1, Procedure Object_Definition_Change_Bug_Test, Line 58
-- The definition of object 'Object_Definition_Change_Bug_Test' has changed since it was compiled.
SELECT *
FROM dbo.#Temp
DROP TABLE dbo.#Temp;
*Stored Procedure Execution
EXEC dbo.Object_Definition_Change_Bug_Test
*Recompile Command
EXEC sys.sp_Recompile 'dbo.Object_Definition_Change_Bug_Test';
March 6, 2015 at 11:18 am
We experienced the exact same issue generated by both the reference of temp tables and a statement that includes OPTION(RECOMPILE) for any statement executed after the point in time the sp_recompile is issued.
This is obviously a problem when a statement level recompile occurrs and there is an in flight execution of the proc when sp_recompile is issued.
In a high concurrency environment it makes issuing sp_recompile to overcome runtime stats / sniffing issues a bit of a gamble, particulary if issued against a table with many dependant procs.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply