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
- 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 .
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 .
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
drop proc summerproc1
create proc summerproc1
create table #x (c1 int)
insert into #x values (1)
Execute the store procedure
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.
select * from sys.objects where name='#43F870CD'
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.
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
and entry_data like '%181575685%'