Expert Diagnostic Optimization Series------------------to cool tempdb

The previous article addresses some of the basic troubleshooting and Tuning methods of SQL Server for CPU, memory, disk, statement, and wait. For easy reading give a guide to the article links for easy reading:

SQL Server fully optimized-------Expert for SQL Server Diagnostic series

In this article we say tempdb, how the system database is optimized and how to balance his use.

First, a brief introduction to Tempdb:tempdb is an important system database in SQL Server. And there is only one tempdb in each instance, that is, when you create 100 databases under one instance, these 100 databases can only use this one tempdb. Does it feel like he's going to be under a lot of pressure? It's not finished yet! It is possible for many users to work with it. The most common of course is that users use temporary tables or table variables. Other possibilities are that users use trigger, Snapshot isolationlevel, some complex queries, and DBCC CHECKDB . It sounds like the rhythm of the explosion! He's not going to explode, so just want you to improve his attention, a lot of system performance problems on him!

As always, with an example: the statement is equivalent to "car", the hardware equivalent to "road", waiting for the equivalent of "traffic lights", then what is tempdb equivalent? " Service Area parking "

　　　　

　　　　

--------------Blog Address---------------------------------------------------------------------------------------

Expert Diagnostic Optimization Series http://www.cnblogs.com/double-K/

No more nonsense, just open the whole-----------------------------------------------------------------------------------------.

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 ~

Directly on the example:

　　　　

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.

simple handling of the tempdb problem

The description above looks as if you need to have a deep grasp of SQL Server to handle this problem. It's really simple , you just have to do one thing and you can fix most of Tempdb's problems! That is to allocate the tempdb to multiple to apportion this pressure.

　　　　"Service Area Parking" can be set up multiple charge ports to avoid congestion and queuing!

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.

　　　　The various tempdb configuration documents circulating on the Internet describe the same number of data files using logical processors. In general, there is no problem, but one thing to note: If the program has insufficient memory to spread to tempdb, or frequent use of large data volume of temporary data worktables, etc., performance will decrease, because your files are divided into multiple, But when the data is written, it needs round robin (Round-robin), which is simple to understand that there will be some time loss, and the random IO will consume more IO resources and time when reading. Interested friends can see:

A SQL Server DBA myth a Day: (12/30) tempdb should always has one data file per processor core

The crossing here don't seem to know how many I should use ... For system best practices, it may be necessary to consider the above problems in very granular optimizations, and for general systems tempdb can generally be configured to 8 or 16 temp files enough, if there is a large number of efforts to continue to increase (generally do not exceed your logical CPU number).

　　　　

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.

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"

Counter Diagnostics

In the counter we mainly look at the following counters:

1. Workfiles created/sec
2. Worktables created/sec
3. Active Temp Tables
4. Temp Tables Creation Rate
5. Temp Tables for Destruction

The standards here are not the same.

Diagnostics by Object distribution

　　

The objects in tempdb can be divided into three types:

• User objects that are explicitly created

These objects are created explicitly by the user. exists in the scope of the user session or in the scope of the routine used to create the object (stored procedure, trigger, or user-defined function).

Includes tables and indexes (System, or user-defined), temporary tables and indexes (global, or local), table variables, table-valued functions, returned tables.

• Internal objects created by the database engine

These internal objects are created by the database engine as needed to handle SQL Server statements. can be created and deleted in the scope of the statement. Each internal object uses at least 9 pages: 1 IAM pages and 1 contiguous 8-page extents.

Includes: worksheets that are stored with cursor or spool operations, and temporary large object (LOB), worksheets for hash joins or hash aggregation operations, intermediate sorting results for operations such as creating or rebuilding indexes, if sort_in_tempdb are specified, or some group by, The intermediate sort result of an order by or union query.

• Version Storage Area

The version store is a collection of data pages that contain data rows that are required to support the use of row versioning, primarily to support the snapshot transaction isolation level, and some other new features that improve database concurrency performance. There are 2 main categories: Public version store, online index build version store.

Includes row versions that are generated by data modification transactions in a database that uses snapshot isolation level or committed isolation level (row versioning-based), and row versions that are generated by data modification transactions for features such as online index operations, multiple active result sets (MARS), and after triggers.

　　

The script:

SELECT ' tempdb ' as db,getdate () as Time,sum (User_object_reserved_page_count) *8 as [user Object (KB)],----such as the use of the temporary table SUM (Internal_ Object_reserved_page_count) *8 as [internal object (KB)],-----such as the space sum (version_store_reserved_page_count) used to connect the hash *8 as  [ Record version Space (KB)],sum (unallocated_extent_page_count) *8 as [free space (KB)],sum (mixed_extent_page_count) *8 as [Mixedextent (KB)] From Sys.dm_db_file_space_usage

　　High-energy Warning: If the user object allocation space continues to be very large, it is essential to show that your program code is overly dependent on tempdb over concurrency high in stored procedures with a large number of temporary tables used. If the internal object continues to be very high, it means that there are many statements in your program that can be optimized (such as sorting, hash join overflow, cursor, etc.)

　　　　　　　

tempdb and 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 overdo it! 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.
3. temporary tables are also cached, find out which objects are not cached, and why this happens! See also:SQL Server tempdb principle-caching mechanism parsing practice

SQL SERVER 2016 version small benefits

2016 has been released in 2016 to make the following changes:

2016 when the database is created, the number of CPUs is detected to create tempdb, but the initial size is 8m,64m growth. Tempdb uses default for the unified zone, and in previous versions of SQL Server, the data pages of the staging tables are always allocated in the so-called mixed area (Mixed Extends), which is 64kb in size, which is shared among multiple database objects (like tables and indexes). This method is able to reduce latch contention on the SGAM (Shared Global Allocation map page, manage mixed area) page (Latch contention problem).

Before 2016, many people used 1117 and 1118 tracking tags to define how SQL Server allocates pages in the database, which is no longer needed in the new version!

　　

High-energy WARNING: 2016 the number of default tempdb files also matches the number of tempdb configurations described in this article ~ ~ ~

--------------Blog Address---------------------------------------------------------------------------------------

Expert Diagnostic Optimization Series http://www.cnblogs.com/double-K/

-----------------------------------------------------------------------------------------------------

Summary: tempdb can basically avoid being a bottleneck by adding multiple files.

The files added by tempdb must be of the same size, with a consistent growth rate, otherwise it will not be effective.

Using temporal tables to optimize statement optimization is a common tool, but it is important to maintain a balance and never overuse it.

using statement optimization can reduce tempdb pressure, such as checking the execution plan, and whether there are plans to create a large number of temporary objects, spooling, sorting, or worksheets. For this, you need to clean up some temporary objects. For example, creating an index in a column for an order by might consider removing the sort.

The file allocation for tempdb is an optimized general configuration.

----------------------------------------------------------------------------------------------------

Original link: http://www.cnblogs.com/double-K/archive/2016/06/02/5538249.html

Series Articles Guide: SQL Server full optimization-------Expert for SQL Server Diagnostic series

Expert Diagnostic Optimization Series------------------to cool tempdb