October 2008 - Posts

Temporary object re-use "nugget" at the UK SQL Server User Group
21 October 08 08:30 PM | Christian | 2 comment(s)

I presented a 5 minute nugget on temporary object re-use at the UK SQL Server User Group last week and I promised that I'd follow it up with a post so here it is!

As of SQL Server 2005 temporary objects are now cached by default as long as

  • Named constraints are NOT created
  • DDL statements that affect the temp table are NOT run after table creation
  • The object is NOT created using dynamic SQL
  • The object IS created inside another object: SP, UDF or the return table of a UDF

The nature of TempDB means that its usage pattern very often contains the creation and destruction of lots of very small tables which can cause pressure on the allocation pages in TempDB (I'll save the specifics of that for another post on PFS, SGAM, multiple files and -T1118) so anything that reduces table creation is a good thing.

If you have a stored procedure that uses a temp table the common assumption is that its deleted after the SP executes because you don't have drop it explicity in your code.  In SQL Server 2005 is actually truncated and the definition re-used on the next execution of the stored procedure if it meets the criteria above.

You can test your own code for temporary object re-use by using this script written by Sunil Agarwhal at Microsoft (I think he also put it into one of the Inside SQL Server 2005 books):

-- runs a stored procedure in a loop and prints how many temp tables were created
declare @table_counter_before_test bigint;
select @table_counter_before_test=cntr_value from sys.dm_os_performance_counters
 where counter_name = 'Temp Tables Creation Rate';
declare @i int
select @i = 0
while (@i < 10) -- number of times to execute
begin
 exec  <insert your sp here>
 select @i = @i+1
end
declare @table_counter_after_test bigint;
select @table_counter_after_test=cntr_value from sys.dm_os_performance_counters
 where counter_name = 'Temp Tables Creation Rate';
print 'Temp tables created during the test: ' +
 convert( varchar(100), @table_counter_after_test-@table_counter_before_test);

If you change the number of times to execute to 1 for the first run you'll see how many temp tables your SP uses.  Then change it back to 10 and see how many tables are created.  If the value is the same then you've got temporary object re-use, if its increased by a factor of 10 then you haven't and can investigate possible code changes to avoid breaking the above rules.

Have fun!

Christian Bolton  - MCA: Database, MCM: SQL Server, MVP
Database Architect
http://coeo.com - SQL Server Consultants

Filed under: ,
MVP Award for SQL Server
01 October 08 10:13 PM | Christian | 3 comment(s)

I was awarded SQL Server MVP status by Microsoft today in recognition of my community contributions.  Hooray!

If you don't know much about the Microsoft Most Valuable Professional progam here is an extract from the website:

"Microsoft Most Valuable Professionals (MVPs) are exceptional technical community leaders from around the world who are awarded for voluntarily sharing their high quality, real world expertise in offline and online technical communities. Microsoft MVPs are a highly select group of experts that represents the technical community's best and brightest, and they share a deep commitment to community and a willingness to help others." More here: http://mvp.support.microsoft.com/

Regards,

Christian Bolton  - MCA: Database, MCM: SQL Server, MVP
Database Architect
http://coeo.com - The SQL Server Experts

Filed under: