Sunday, July 7, 2013

1 Temporary table caching


One of way to relive of the tempdb allocation contention is to change the application query to utilize the temp table cache feature.


  • Only cache in Procedure, function and trigger . No ad-hoc query or dynamic SQL . Because SQL server need to connect the cache with plan.
  • No DDL after creation (does not apply to DROP TABLE)
    No named constraints
  • Plan cache is store in the CACHESTORE_TEMPTABLES. We can use dbcc memorystatus to see the size and usage and sys.dm_os_memory_cache_entries would show the detail.
  • There is no limit for how many plan SQL Server would cache. The limitation is the size of the available  memory.


Page allocation sequence

Up to 8 Pages

  • Search SGAM and PFS, If no mix extend in SGAM, need to go out the GAM to find the page.
  • Update PFS
  • Modify IAM

Post 8 pages

  • Search PFS based on current extents in IAM
    Search and update GAM  to allocate uniform extent
    Modify PFS
    Modify IAM
  • May need new IAM page if it is not in the interval

When new temporary objects is created/insert/delete/update the row, the sql server engine needs to acquire lock and latch on the system base tables.

* system base table only accessible through the DAC

Below test is primary base on the Bob ward’s “Inside tempdb” session in the SQL SUMMIT 2011 .

No Caching

-- tempbatch.sql
create table #x (c1 int)
insert into #x values(1)
drop table #x

Lets take a look for the log for one execution for the above create temporary table .

use tempdb;
select Operation, Context, [Transaction ID], AllocUnitId, AllocUnitName, [Page ID],
[Transaction Name], [Description] from sys.fn_dblog(null, null);


It generate about 60 rows log. and you can see it needs to update a lot of system base tables ( sys.*) and also needs to split the page.



During the drop table, it also needs to de allocate them.


We can use the ostress application to simulate the load. Below command is to simulate 100 concurrent connections and each connection is run the same sql 100 times.


ostress -E -itempbatch.sql -S. -n100 -r100 –q

As you can see from perfmon, the SQL server keeps creating the temp tables and page latch is high.


It took about 1 minutes to complete.


Since there is no cache. The dbcc memory status would show no changes .



CACHING with procedure

use master
drop proc summerproc1
create proc summerproc1
create table #x (c1 int)
insert into #x values (1)
select object_id('summerproc1')


Execute the store procedure


-- execproc.sql
use master;
exec summerproc1


The first time it runs, it still needs to create the entry in system tables and add the object “dbo.#43F870CD” , During the de allocate process, FCheckAndCleanupCachedTempTable is the operation that cache the temporary.  It is about 60 rows records to execute this.


use tempdb;
select * from sys.objects where name='#43F870CD'
select OBJECT_NAME(1140355277)


43F870CD is actually the hex value of the object ID.


If we run the same store procedure again, it will only generate 25 log records and because we reuse the #43F870CD, so we don’t see the PFS,IAM page allocation .





ostress -E -iexecproc.sql -S. -n100 -r100 -q


Compete around 8 secs.


CACHESTORE_TREMPTABLES increase from 16 to 128.


When temp table is no longer needed ( session closed), it will be dropped from sys.objects as well as the dm_os_memory_cache_entries.

However, if the plan is cached, it will stay in the sys.objects and dm_os_memory_cache_entries.

use tempdb;
select name,object_id,type,type_desc from sys.objects where name like '#%'


In the example above, it create 100 temp table entry because we have 100 connections.


select name,entry_data_address,type,entry_data from sys.dm_os_memory_cache_entries
where name='tempdb'
and entry_data like '%181575685%'



SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates