23rd 24-Week staging Database (TEMPDB)

In today's performance Tuning Training We discuss the Tempdb--sql server's public toilets, which I describe in SQL Server. Each of us will often use tempdb. Some people use it directly, and some people don't use it directly. Today I want to give you an overview of the usage of tempdb in SQL Server, and I'll give you some best practices for how to configure tempdb for better performance.

tempdb usage

Each of us uses tempdb. This is the fact that we have to accept. Therefore, it is important to configure tempdb on demand-that is, to achieve good performance. tempdb stores the following 2 classes of objects:

• User Objects ( User Objects)
• Inner Object ( Internal Objects)
• Version Store ( Version Store)

Let's take a look at these objects in detail. When we talk about user objects, we are referring to temporary tables, table variables, and table-valued functions. There are 2 types of temporary tables in SQL Server: Local temporary tables, global temporary tables. The local temporary table is created with the "#" prefix and is limited to the session that created it. As soon as you close your session, the local temp table disappears in tempdb. Benefits of local temporary tables: using them, you can overcome locking and blocking problems, because each session has its own, dedicated temporary table.

There is also a global temporary table in SQL Server, which is created with the "# #" prefix. These temporary tables can be accessed in all sessions because they are created globally. Finally, SQL Server provides you with a table variable, which is physically persistent in tempdb, but is confined to the batch where you define the table variable. Table variables are an in-memory structure in SQL Server that is a misconception. They are always persistent in tempdb. Tables returned from table-valued functions are also persistent in tempdb. Therefore, when using these objects in SQL Server, it is important to configure them on demand.

including you as a developer or DBA,--sql server itself always uses tempdb for internally created object stores. When you use DBCC CHECKDB or runs a consistency check, SQL Server allocates worksheets in tempdb, and the execution of a scheduled or hash operation also spreads to tempdb, which is also physically persistent in tempdb. When you use cursors, even service Broker, you are consuming the space in tempdb. If you use the

Also for internal objects, SQL Server supports the so-called version store, which is used in SQL Server when you use optimistic concurrency control (optimistic concurrency) or online index operations. The internal SQL Server sub-version is stored as 2 different stores: for triggers (triggers), snapshot Isolation (Snapshot isolation), read snapshot Isolation (read Committed Snapshot isolation), There is also a majority result set (multiple Active result sets (MARS)), which uses the generic version store (Common). In SQL Server , the online index rebuilds the version store ( online index Rebuild version store) is used by the inline index operation.

tempdb configuration

It's a good idea to run tempdb in the default configuration. The default configuration for tempdb gives you only one data file and one transaction log file. In SQL Server 2014, the initial size of the data file is 8 m, which is 1M for the transaction log. 2 files are set to 10% auto-grow. This configuration poses several problems:

• Too many auto-grow operations with timeouts
• Log file Fragmentation
• Latch-Up competition (Latch contention)

Let's take a look at these questions in detail. With the initial size of the default 8M, your tempdb will have a time-out increase using expensive autogrow operations. If you know that your tempdb needs a certain amount of MB in size, you need to set it to the initial size, because tempdb is always recreated from the model data during SQL Server startup. That way you can avoid auto-grow operations. If you rely on autogrow settings, you should also use a fixed size instead of a percent value. This also allows you to estimate how long the autogrow operation will take. Using percent values, based on the current size of your file, will take more and more time.

You also need to carefully tempdd the size of the transaction log, because there is an auto-grow operation that is very expensive. For any transaction log, SQL Server cannot use instant file initialization (Instant files initialization). This means that your database cannot access transactions during the automatic growth of the transaction log. For performance-critical systems, the autogrow operation on the transaction log is largely infeasible.

In the end you will also encounter the latch contention in tempdb because only one data file is available. When SQL Server allocates a new object in tempdb, SQL Server needs to read a specific page (SGAM,GAM,PFS). These pages must be competed during the write-down. When you run a job that is highly dependent on tempdb, there is a competition problem on these hot pages in tempdb.

The workaround for this problem is to use multiple data files for tempdb, since SQL Server uses a cyclic allocation algorithm (round-robin allocation algorithm) over multiple data files, which reduces latch contention. If you use multiple data files, you also need to make sure that the initial size (a possible autogrow value) is set to the same value, so that they grow at the same time.

Summary

In today's performance Tuning Training we discussed the specific database--tempdb in SQL Server. As you can see, everyone in SQL Server always uses tempdb--directly or indirectly. Therefore, it is important to adjust and plan tempdb on demand. In the 2nd part we give you some suggestions on how to configure tempdb. You will receive a week after the performance tuning training last week, I talk about database maintenance, and this week I wish you a good time!

23rd 24-Week staging Database (TEMPDB)