SQL Server tempdb principle-caching mechanism parsing practice

Tempdb is like a temporary repository for SQL Server, with a wide variety of objects, where data is frequently calculated and manipulated. A large number of operations can put tempdb under a lot of pressure, The cache design in tempdb is designed to alleviate these stresses. This time we will introduce the caching mechanism for tempdb.

Before introducing the caching mechanism, let's take a quick look at the tempdb object

Generally we divide the tempdb object into two types of user objects and internal objects. User objects are objects created by explicit T-SQL, such as temporary tables, that are objects created through implicit T-SQL (Worktables)

Note: After you introduce version control, you can also do this separate classification (DMV sys.dm_db_file_space_usage separately)

User objects

Temp table

Table variables (contains table-valued function return values and table-valued parameters)

Temporary stored procedures

User-defined objects

User Online-Create INDEX space

Internal objects

Sorts (sort overflow)

Worktables (CHECKDB, cursor, Merge joins, spool, parallel query Exchange overflow, LOB object, etc.)

Workfiles (hash join overflow)

Version store (versioning line control)

Viewing object usage

When the tempdb data file is large or has an exception, we can look at the appropriate usage. How to see how much of the tempdb space a query uses in the Yun Sword Blog has a detailed script.

tempdb caching mechanism

Many of the object caching mechanisms in tempdb are different, and some operations, such as sort, take the internal mechanism, which is not controllable for the user, and here we mainly introduce the caching mechanism of common object temporary table/table variables (which are also frequently causing problems).

temporary table caching mechanism (#t)

Only use stored procedures, triggers, functions to cache

Cache an IAM page and a datepage page as plan cache

Disable the use of DDL operations after create

Prohibit naming constraints

You can see that the cache for the staging table is implemented in the form of a proc execution plan cache. So batch, dynamic SQL is not cacheable. It is important to note that since the execution plan cache, we cannot add the WITH recompile keyword in Proc.

Note: When you cache objects, local temporary tables do not include global temporary tables.

We analyze the cache implementation through a simple example.

First we look at the general batch operation (non-cache) execution two times, observe the log condition 1-1

Batch Create code

 UsetempdbGoCheckpointGoCreate Table#t (IDint)Insert  into#tSelect 1Drop Table#tSelectOperation,context,[Transaction ID], Allocunitid,allocunitname,[Page ID],[Transaction Name], Description fromFn_dblog (NULL,NULL)

Figure 1-1

In fact, we can see that the first and second executions of the log records are the same.

And look at the proc way.

Proc Code

 UsetempdbGoCheckpointGoCreate procP_tstcache asCreate Table#t (IDint)Insert  into#tSelect 1execP_tstcache------Observe logging after the first execution 1-2SelectOperation,context,[Transaction ID], Allocunitid,allocunitname,[Page ID],[Transaction Name], Description fromFn_dblog (NULL,NULL)CheckpointGoexecP_tstcache------Observe logging information after the second execution 1-3SelectOperation,context,[Transaction ID], Allocunitid,allocunitname,[Page ID],[Transaction Name], Description fromFn_dblog (NULL,NULL)

Figure 1-2

Figure 1-3

You can see that when the stored procedure executes the second time, the cache is used and the number of log records is significantly reduced. Continue caching after use is complete.

Benefits of Caching

We look at the effect of caching with a simple stress test.

We use sqlquerystress open 100 threads to perform 1000 times to see the effect of the DDL after create in Batch,proc,proc. (Interested friends can observe the corresponding counter temp tables creation rate) Figure 1-4

Note: Pre-set the tempdb data log file size to avoid test deviations due to file growth.

Code 1 Batch

Create Table  int)
Drop table #t

CODE2 proc

Create proc P_tstcache  as Create Table  int)

Code 3 proc DDL after create script

Create proc P_TSTCACHE_DDL  as Create Table  int)createindexon–--ddl after create 

Figure 1-4

You can see that because of the caching mechanism, in some instances where applications frequently create temporary tables, we can mitigate the competition by completing the construction of temporary tables in Proc. However, you should be aware of the limitations of the proc temporary table cache.

About table variables.

Table variables have the same caching mechanism as temporary tables (note: table-valued parameters do not support caching)

Table variables cannot be indexed, but can have a default constraint

Table variable does not have statistical information

Table variable does not support transactions

About the explicit drop temporary table in Proc.

Microsoft claims that the explicit drop temporary table in Proc is not affected by the Create post-DDL, but in a real-world production environment, an explicit drop still has an impact. Moreover, the proc does not have to be explicitly drop when the word auto-cache is processed.

About temporary table/table variable cache apps

As we can see from the above example, there are a number of limitations to using proc cache caches. In a real-world production environment we may use temporary tables (table variables) You need to create an index to improve query efficiency. In fact, we need to weigh the balance. Temporary tables with large data volumes in high concurrency queries at this point we can create the index in the script that creates the temporary table. But is the pressure under the high-concurrency big temporary table the creation process?

Conclusion: Sometimes the work of a DBA is a trade-off, and it needs to be polished and weighed. In a stable business situation, if we cannot adjust the hardware environment, we need to create a reasonable balance of business needs.

SQL Server tempdb principle-caching mechanism parsing practice