April 30, 2009 at 8:55 am
I thought that Temporary Tables ALWAYS caused stored procedures to recompile because the DDL and DML was interleaved. However I've just run a test on this (SQL 2005 and SQL 2008) and found the following (I've abridged it to the relevant events):
First Run...
Exception: Error 208 (because the table doesn't exist yet)
Cache Insert: (for Stored Procedure called)
SP: StmtStarted: CREATE #TEMPTABLE
SP: StmtStarted: INSERT INTO #TEMPTABLE
SP: Recompile
SP: StmtStarted: INSERT INTO #TEMPTABLE (same statement again, after the recompile)
SP: StmtStarted: SELECT FROM #TEMPTABLE
Second Run...
SP: StmtStarted: CREATE #TEMPTABLE
SP: StmtStarted: INSERT INTO #TEMPTABLE
SP: StmtStarted: SELECT FROM #TEMPTABLE
Can anyone advice please? Thanks in anticipation.
.
April 30, 2009 at 4:54 pm
You're probably getting a statement recompile rather than a procedure recompile. If you capture the SP:Recompile event in a trace you can see, probably multiple, the event show up with an eventsubclass of 3 - Deferred compile.
"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
April 30, 2009 at 6:56 pm
Tim,
You should use the event SQL:StmtRecompile instead of SP:Recompile in 2005 and later.
See http://msdn.microsoft.com/en-us/library/ms179294.aspx
The SP:Recompile event is there to allow tracing of SQL2K instances.
Anyway, on to the answer:
SQL2K5 caches 1 data page and one index allocation map page for a temporary table with the query plan.
The table is only actually created once, even if you run the procedure in a loop.
See this brilliant article by Sunil Agarwal for a fuller explanation and a repro script:
http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/22/tempdb-basics-cont.aspx
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2009 at 7:29 pm
Paul White (4/30/2009)
Tim,You should use the event SQL:StmtRecompile instead of SP:Recompile in 2005 and later.
See http://msdn.microsoft.com/en-us/library/ms179294.aspx
The SP:Recompile event is there to allow tracing of SQL2K instances.
Hm. Interesting. I didn't know that. Too late to recall all the books though.
SP:Recompile does work and will capture the statement level recompiles. It even shows the statement where the recompiled occurred.
Learn something every day.
"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
April 30, 2009 at 7:36 pm
Grant Fritchey (4/30/2009)
Hm. Interesting. I didn't know that. Too late to recall all the books though. SP:Recompile does work and will capture the statement level recompiles. It even shows the statement where the recompiled occurred. Learn something every day.
Ha - I wouldn't worry about it. Something for the next edition 😉
Yes I must admit I still use SP:Recompile more times than not, out of habit really.
The link says you should use the new one, but stops short of explicitly saying that there's anything wrong with the old one.
Microsoft just love deprecating stuff, don't they? 🙂
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 30, 2009 at 7:51 pm
The question bugging me is, how the heck did you know that? There are so many changes between 2000/2005 and then on top of it to 2008. I catch a lot, but clearly not all. It feels like some of you guys sit around memorizing Books Online. Us mere mortals just have to read it.
:w00t:
"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
April 30, 2009 at 8:14 pm
Grant Fritchey (4/30/2009)
The question bugging me is, how the heck did you know that? There are so many changes between 2000/2005 and then on top of it to 2008. I catch a lot, but clearly not all. It feels like some of you guys sit around memorizing Books Online. Us mere mortals just have to read it.:w00t:
Hey Grant,
It's because I'm super-awesome and amazingly intelligent!
Ah, ok, maybe not.
The article I linked to was a very memorable one for me - it's such a clever optimization, and I'm not sure too many people know about it. So something about the question just reminded me of it.
The recompilation event thing was just because I was reading something Jeff wrote about MSFT deprecating things left right and centre - and so it was in my mind as an example of that. Oddly I had run a trace using the new event just yesterday, so that was kinda at the top of the stack too.
It took a while to check my recollection and google up some links, but there was no magic.
I think the reason it looks as if people memorize stuff is (a) because some do; and (b) most questions on here will trigger the memory of an experience for someone. So they post something dead specific and clever, and it makes the rest of us feel inadequate.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 1, 2009 at 3:00 am
Paul White (4/30/2009)
Tim,You should use the event SQL:StmtRecompile instead of SP:Recompile in 2005 and later.
I actually abridged my profile too much! I was capturing both events, and I still didn't see a recompile.
Paul White (4/30/2009)
Anyway, on to the answer:SQL2K5 caches 1 data page and one index allocation map page for a temporary table with the query plan.
The table is only actually created once, even if you run the procedure in a loop.
See this brilliant article by Sunil Agarwal for a fuller explanation and a repro script:
http://blogs.msdn.com/sqlserverstorageengine/archive/2008/12/22/tempdb-basics-cont.aspx
Paul
That is amazing! I worked out that this is what it seemed to be doing but had no idea why!
The blog post explains perfectly and my scenario fits in exactly with the one described.
The concept here looks quite powerful to me, and I'm sure I can leverage it to get the statistics advantages of temp tables over table variables without risking a possibly expensive recompile.
(Incidentally I noticed the statistics aren't recreated on the subsequent runs either, although they are automatically created the first time. I have auto create statistics and auto update statistics set)
Thanks for you help, very much appreciated.
Tim
.
May 1, 2009 at 3:01 am
Grant, thanks very much for your input too, I really appreciate the help.
Tim
.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply