October 8, 2014 at 2:09 pm
Hi Everyone,
I have a case where I have two conflicting performance objectives.
1. Always creating temporary tables at the beginning of the procedure and dropping at the end, to avoid additional sproc recompilations.
2. Not creating a temporary table when the stored procedure may early return before needing to use it
#1 has been a general guideline followed by my peers, based on experience possibly dating back to SQL7/2000. However, I came across a case where I do not always need the temporary table (sometimes there is an early return before usage). Being the naturally curious person I am, I attempted to re-produce recompilations in my sproc with the creation of the table just before usage. After asking around, I haven't found anyone that knows of a specific use case causing recompiles if the temp table is created in the middle of the sproc.
I have found this KB from MS, but it is pretty dated: http://support.microsoft.com/kb/243586
My sproc even has some of the items this advises against (drops for early returns after the temp table was created, a cursor referencing the temporary table - I know this should be avoided to, but is reasonable in this situation). I just executed the sproc programatically with 100 executions with varying parameters, and am not picking up recompilations in profiler.
I can trigger recompilations based on the size of the data in the temp table, but it doesn't appear to be affected by where it is created in the sproc.
I am curious if anyone is aware of additional information (related documented changes to the optimizer/execution engine, use cases, etc.). Maybe this is less/no longer applicable with statement level plans?
Thanks!
October 8, 2014 at 4:14 pm
Yeah, those are SQL Server 2000 temp table habits. There were some improvements in SQL 2000 SP4, but SQL2005 and SQL2008 introduced a variety of tempdb improvements:
- Deferred compilation and line-level recompilation cut down on the recompile chatter
- Temp-table metadata caching and table reuse eliminate the rest of your concerns, provided you follow the rules to allow it. The #temp table metadata, as well as one or more truncated #temp tables are cached and tied to the stored procedure plan. This not only prevents recompilation, but the metadata contention in tempdb from repeated table and index creates and drops. But you have to follow the rules:
1. Do not drop #tables when you are done with them - let them go out of scope at the end of the proc and allow the deferred-drop process handle the cleanup.
2. Do not alter the table schema in any way after it has been created. I f you create a table in one command, then add an index in a later command, the cached table (with an index) will not match the CREATE TABLE (without an index) statement when the proc runs again, and the #table must be recreated. If you want non-clustered indexes on the table, include them in the CREATE TABLE statement as UNIQUE constraints, which create a optimizer-usable non-clustered index under the covers. If your index isn't unique, then simply add the primary key column(s) to the UNIQUE constraint declaration to make it unique.
3. This will not work with ad-hoc T-SQL.
Important notes:
* This only works for #temp tables, not @table variables (which are always created as physical tables and go to disk in tempdb just like #temp tables).
* This also works for stored procedures only. The metadata and empty #tables are tied to the stored proc plan.
* You don't have to monitor or clean up the cached entries.
Follow these rules, and not only will you avoid recompiles, but will see some major performance improvements in environments that use temp tables frequently. Even if using the technique doesn't show immediate gains in your systems, keep using it and make it a standard for new code. You will be getting benefits for years without knowing it as your codebase grows and more use of tempdb occurs.
In other words, if you enable #temp table caching, you can create that #table wherever you like, and always creating it, even if you don't use it, is less of a concern.
Eddie Wuerch
MCM: SQL
October 8, 2014 at 11:16 pm
Eddie Wuerch (10/8/2014)
1. Do not drop #tables when you are done with them - let them go out of scope at the end of the proc and allow the deferred-drop process handle the cleanup.2. Do not alter the table schema in any way after it has been created. I f you create a table in one command, then add an index in a later command, the cached table (with an index) will not match the CREATE TABLE (without an index) statement when the proc runs again, and the #table must be recreated. If you want non-clustered indexes on the table, include them in the CREATE TABLE statement as UNIQUE constraints, which create a optimizer-usable non-clustered index under the covers. If your index isn't unique, then simply add the primary key column(s) to the UNIQUE constraint declaration to make it unique.
3. This will not work with ad-hoc T-SQL.
Eddie, can you point towards some white papers or more information for these rules and temp table cache'ing? It's rare I run into something I've never heard of at all, and you've got me stunned with this! I need to learn more!
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 9, 2014 at 3:40 am
I like Eddies rules. The one thing I would add is that, unlike 2000, in 2005 and up, you're going to see statement level recompiles, not procedure level recompiles (unless you put a procedure level hint on it). This makes a big difference on the impact of recompiles on most systems. I'm not saying you should practice what Eddie preaches, just that the horrors we used to see have been radically reduced.
"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
October 9, 2014 at 5:44 am
Thanks for the information/assistance everyone. I like Eddie's rules too.
I found the following blog post from Paul White, which has helped clarify the situation and provide some context for Eddie's rules:
http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx
This also goes into some detail on what would trigger recompilations of the statements using the #table.
It seems that neither of my points are really of GREAT concern at this point:
1. Creating #table at beginning of sproc and dropping at the end: primarily due to statement-level plans and temp table caching
2. Avoiding creating the #table when not used (ie: create just before use):
I will talk about this in much more detail in my next post, but the key point is that CREATE TABLE and DROP TABLE do not create and drop temporary tables in a stored procedure, if the temporary object can be cached. The temporary object is renamed to an internal form when DROP TABLE is executed, and renamed back to the same user-visible name when CREATE TABLE is encountered on the next execution. In addition, any statistics that were auto-created on the temporary table are also cached.
I tend to think #2 would still provide a very minor performance impact if not using the #table, or if the #table could not be cached. You wouldn't have the overhead of the internal renaming (cached) or creation (not-cached) when you will not use it.
October 9, 2014 at 6:27 am
It's hard to argue with that. If you don't need it, don't create it.
The key point is that the execution plan for a batch is created all at once. Even if you have IF statements to control the flow, if everything is in one batch, the execution plan gets created as a single step. Then, you may or may not get individual statements recompiling as Paul and Eddie have outlined.
"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
October 9, 2014 at 6:36 am
Thanks Grant, I will have to do some additional experiments as I am curious how this internal renaming/truncating of the #table works when you have concurrent executions using the #table. There would have to be multiple instances in tempdb. It seems it would cache multiple instances in this case, instances with possibly different statistics. I wonder which would get used once the concurrent executions complete and the next execution starts.
October 9, 2014 at 6:42 am
Nick Doyle (10/9/2014)
I wonder which would get used once the concurrent executions complete and the next execution starts.
Doesn't matter. Part of the 'cleanup' when the procedure ends and the temp table is renamed is deallocating all pages but 2 and removing all rows. So by the time the cached shell of the temp table is ready to be reused, there's nothing left of those differences.
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
October 9, 2014 at 3:24 pm
Nick Doyle (10/9/2014)
I found the following blog post from Paul White, which has helped clarify the situation and provide some context for Eddie's rules:http://sqlblog.com/blogs/paul_white/archive/2012/08/15/temporary-tables-in-stored-procedures.aspx
Thanks for the link Nick. I'm about 2/3's of the way through it and while I think I knew something about this, I had no idea the rabbit hole was anywhere near this deep.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply