Database Reference
In-Depth Information
■
Note
We will talk how to detect such contentions in part 5 of this topic, “practical troubleshooting.”
In order to improve performance, SQL Server introduces the concept of
temporary table caching
. This term is a
bit confusing. It relates to temporary table allocation rather than data pages, which are cached in a buffer pool, similar
to regular tables.
In a nutshell, with temporary table caching, instead of dropping the table, SQL Server truncates it, keeping two
pages per index pre-allocated: one IAM and one data page. The next time the table is created, SQL Server will reuse
these pages, which helps reduce the number of modifications required in the allocation map pages.
Let's look at the example shown in Listing 12-8. In the first step, we write the stored procedure that creates and
drops the temporary table.
Listing 12-8.
Temporary tables caching: Stored procedure
create proc dbo.TempTableCaching
as
create table #T(C int not null primary key)
drop table #T
In the next step, we run the stored procedure and examine the transaction log activity it generates. You can see
the code for doing this in Listing 12-9.
Listing 12-9.
Temporary tables caching: Running the stored procedure
checkpoint
go
exec dbo.TempTableCaching
go
select Operation, Context, AllocUnitName, [Transaction Name], [Description]
from sys.fn_dblog(null, null)
When you run this code for the first time, you will see results similar to Figure
12-3
.
