tempdb--Caching of temporary tables

--==========================================================================

In the blog Park to see an article "SQL Server Temp Tables data Query", the article asked in the absence of any load situation, there are a large number of temporary tables, why?

--==========================================================================

Let's explore the experiment.

First select any user database and execute the following script:

CREATE PROCEDUREusp_temptabletest asBEGIN    SETNOCOUNT on; CREATE TABLE#TB1 (C11INT, C22INT)    INSERT  into#TB1SELECT 1,1    SELECT *  from#TB1ENDGOEXECUsp_temptabletest

As a general understanding, temporary tables in a stored procedure are created in the call, released after the end of the stored procedure call, and we should not find a temporary table at the beginning of #tb1 in tempdb after the execution of the stored procedure.

Let's check it out.

 use   tempdb  go  -- ======================================  --  View the columns of the staging table  select  object_name  (object_id ) as   objname,  *  from   Sys.all_columns  where  object_name  (object_id ) like    %#%    

Running the above code makes it easy to find:

Column names C11 and C22 are the same as the temporary table column names that are fixed in our stored procedure, just a vest, don't tell me you don't know "it" when you change your vest.

--================================================================

Explain:

The #a2206doc that we see above is not the #tb1 used in our stored procedure, but there is a certain correlation between the two. Imagine that every time a stored procedure executes, a temporary table needs to be created and freed, and the creation and release of temporary tables involves the modification of data from many system tables, and if the temporary table is cached, the next time the stored procedure is called, it avoids creating and releasing the resources consumed by the temporary table, thereby achieving a certain performance boost.

Professional explanation:

When SQL Server deletes a temporary object, the entry for that object is not removed, and when used again, there is no need to recreate the temporary object, SQL Server caches a data page and an IAM page for the temporary object, and reclaims the remaining pages, and if the size of the temporary table exceeds 8MB, the recycle occurs asynchronously.

--==================================================================

Question 1: What happens when multiple stored procedures are called concurrently?

When a concurrent call is made, several similar temporary table "caches" are generated for invocation, and a temporary table "cache" is guaranteed to be accessed only by a stored procedure executed by a reply at a point in time, after which the temporary table "cached" Data is cleaned up and then used by the next callback.

Question 2: What kind of temporary table will be "cached"

Of course not all temporary tables will be cached and need to meet certain conditions:

1. No naming constraints created

2. Temporary objects do not perform data definition language DDL operations that affect temporal tables after they are created (such as creating indexes and creating statistics)

3. No temporary objects created using dynamic SQL

4. The temporary object is created inside another object, such as a stored procedure, trigger, user-defined function, or temporary object is a chapter table of a user-defined table-valued function

--=====================================================================

Still sister lead the Wolf (recently small busy, can not guarantee the quality of sister, do not blame)