How to quickly position tempdb to produce problems

Step 1. tempdb Pressure Diagnostics Wait Type diagnostics

The contention pressure in Tempdb has been briefly described in the waiting chapter, waiting for the Pagelatch_ class to wait, and the waiting resource is "2:x: X"

Response time of the disk on which tempdb resides

There is only one tempdb under an instance, that is, when you create 100 databases under one instance, these 100 databases can only use this one tempdb.

Tempdb is required for operations such as temporary tables that you create, or the sort required by SQL to execute statements. Therefore, tempdb has a higher response time requirement for disks.

Step 2: Resolve the issue

Set tempdb to multiple to apportion this pressure.

split into multiple files

　　　　As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as the logical processor. if the number of logical processors is greater than 8 o'clock, use 8 data Files , and then if contention persists, increase the number of data files by a multiple of 4 (the maximum number of logical processors) until the contention is lowered to an acceptable level or changes to the workload/code.

file size and growth rate are the same

　　　There is a small detail to note, and the files you allocate must be the same size, and the growth rate will be the same if the settings are automatically increased.

　　　　

tempdb disk Partitioning

In most cases, tempdb files do not need to be split disks on the same disk, and if the pressure is large you can choose to place it on a separate disk so that it does not compete with other files (such as data read and write) that occur on disk resources.

　　　　

If the tempdb read response time is high, consider the disk-related optimizations for tempdb, such as putting the tempdb file in a relatively fast disk separately.

Step 3. Statement tuning

　　It is one of the common kick to use temporal tables or table changes to reduce the complexity of statements and to improve the efficiency of statements, but it is necessary to balance the statements. Excessive use of statements can cause the tempdb pressure mentioned in the article. So how to balance it? Here are a few suggestions:

1. Remember not to overuse temporary tables! The use of temporary tables consists of two scenarios, and split statements reduce complexity. The other is to cache intermediate results to avoid duplication of operations.
2. Reduce the time to use temporary table lock system tables! "Select field into #临时表 from" if the statement executes too long it will be a disaster, try to create first, then insert the practice.

      principle: Where does the tempdb pressure come from?

When the database creates a new table, SQL Server allocates a storage page for the table, and SQL Server modifies the SGAM, PFS, and GAM pages to mark the already allocated page as used. So each of these system pages that create a new table, SGAM, PFS, and GAM will have a modification action. This behavior does not have a problem with the general user database, because the normal application does not toss and stop to build the table, delete the table. But tempdb is different. If a stored procedure uses a temporary table, and the stored procedure is widely used by concurrent users, it is natural that many concurrent users create tables at the same time in tempdb, and then delete the tables after they have finished. This way, at a point in time, there will be a lot of tasks to modify Sgam, PFS, or GAM pages. But to maintain physical consistency, for the same page, SQL Server allows only one user to modify it at a time point. So for tempdb, if there are many people who want to allocate space in the same data file, the Sgam, PFS, or GAM page of this data file can become a system bottleneck. We can only one one do, the degree of concurrency does not go.

　　　　It's like you're going into the parking lot to register a fee! One by one, no hurry ~

　　　　

Wait for the resource: "2:1:3" what does that mean? The 1th number file with the ID 2 database (TempDB) has a page number of 3 (Sgam page)!

　　　　

Here about the System page is not too much introduction, want to learn more about friends see: The GAM page and the SGAM page in SQL Server

Do I have to create a temp table that's related to the system page?

An example is shown below:

　　　　When you create a temporary table, you insert and update the system tables, and the reverse process of deleting the temporary table deletes or updates the system tables!

Use [adventureworks2012]gocheckpointgocreate table #t (id int) drop table #tuse tempdbgoselect operation,context,[ Transaction id],allocunitid,allocunitname,[page id],[transaction name],description from Fn_dblog (null,null)

　　　　

　　　　

　　　　so There is a lot of contention when you are too high and frequently create delete temporary tables.

How to quickly position tempdb to produce problems