This is the 13th day of my participation in Gwen Challenge.

Hello everyone, I am Wukong.

This article describes the use and performance of TempDB.

What is TempDB?

1.TempDB is a system database. Has been around since SQL Server2000.

2. Only Simple recovery mode and automatic truncation mode are available.

3. Stores local variables, global temporary tables, table variables, and temporary uses (such as hash tables).

4. After the Server is restarted or the SQL Server service is restarted, the Server will be re-created based on the configuration of the Model library.

5. If temporary objects are generated in the session or stored procedure, they are automatically reclaimed after the session ends and cannot be queried or used again.

6. Access permission is granted by default.

 

What is TempDB used to store?

2.1. Temporary objects for users

(1) Display the created entity table and the index above by the user in the session. Clear after restart.

(2) global temporary table + index. Table beginning with ##.

(3) Partial temporary table and index above. # table at the beginning.

(4) Table variables. @ the beginning.

Note:

(1) The global temporary table is visible to all sessions. When the session that created the temporary table disconnects from the database and there is no active reference to the global temporary table, SQL Server automatically drops the corresponding global temporary table.

(2) Local temporary tables are visible only to the session recreations level where they were created and to the call stack internal level (internal procedures, functions, triggers, and dynamic batch processing). When the creation routine pops up the call stack, SQL Server automatically deletes the corresponding temporary table

(3) Table variables also have corresponding tables as their physical representation in tempDB database. Visible only to the batch of the current session. Internal batches of the current batch in the call stack are not visible, nor are subsequent batches in the session.

(4) According to the experience of foreign experts, temporary tables are preferred for large data, and table variables can be used for small data volume (generally less than 100 rows).

2.2. Internal temporary objects

Objects that store temporary data during queries, such as Sorts, spoolers, Hash associations, cursors, and so on.

You can view this using the following SQL statement:

SELECT * FROM sys.dm_db_session_space_usage
Copy the code

Check the internal_object_alloc_page_count column

2.3. Version storage

After the optimistic concurrency mode is enabled, Temp DB is used to store the data of the original version.

 

Note:

Version storage causes unexpected growth of the Temp DB. Therefore, you need to monitor the Temp DB file size and space usage.

3. Existing performance problems on TempDB

3.1 Space usage

TempDB is a system database that is used in many places. If the database is improperly configured or used, space is quickly consumed and errors may occur, affecting the normal operation of the server.

View the space usage of TempDB.

3.1.1 You can use the performance monitor to check the space usage of SQL Server.

 

3.1.2 Querying space Usage using SQL Statements.

(1) Check the usage of TEMPDB

Exec sp_spaceused
Copy the code

　　

(2) Check the size of the tempdb. MDF file

SELECT * FROM dbo.sysfiles
Copy the code

(3) Check the space used by TEMPDB

SELECT * FROM sys.dm_db_file_space_usage
Copy the code

(4) View the space allocation of the session, excluding the currently active task.

SELECT * FROM sys.dm_db_session_space_usage WHERE session_id > 50
Copy the code

(5) View information about the current running task in TempDB.

SELECT * FROM sys.dm_db_task_space_usage WHERE session_id > 50
Copy the code

3.1.3 Diagnosing TempDB disk problems

3.2 I/O problems

(1) Use sys.dm_io_virtual_file_stats to check disk reads and writes on TempDB on the current instance.

SELECT  DB_NAME(database_id) AS 'Database Name' ,
        file_id ,
        io_stall_read_ms / num_of_reads AS 'AVG Read Transfer/ms' ,
        io_stall_write_ms / num_of_writes AS 'AVG Write Transfer/ms' ,
        *
FROM    sys.dm_io_virtual_file_stats(-1, -1)
WHERE   num_of_reads > 0
        AND num_of_writes > 0
Copy the code

 

Reference time: 10~20ms Acceptable range.

(2) Create and delete temporary tables and table variables in large numbers and frequently

Optimize TempDB

1. Size of the configuration file

Default configuration:

Initial size 8M

Automatic 10% increase with no limit on growth.

This configuration can be modified depending on the production environment.

 

The following configuration is recommended:

2. Place to store files

Typically, the TempDB files are placed on a separate disk. If you want performance, consider RAID0, but without disaster tolerance.

RAID: indicates a disk array

RAID 0 None Stripe disk parity check. Data spans all physical disks without any Dr Feature.

RAID 1 Disk mirroring. At least two physical disks are required. Data can be read from two disks at the same time. Data to be written must be backed up to the other disk. Provides disaster recovery (Dr) features. Waste 50% of disk space.

RAID 5 Stripe disks with parity check. At least three physical disks are required, one for parity information and the other two for data. Provides disaster recovery (Dr) features. Waste 50% of disk space.

RAID 10 or RIAD 0+1. The read/write performance is the best and disaster recovery is achieved.

3. Number of files

TempDB has only one primary file group, where all data files are stored. The general recommendation is to start with 4 book files and monitor them, and add 4 more if you find that there are not enough. And so on. You are advised to limit the number of files to less than two digits.

Five, the other

1. What cannot be done to TempDB

  • Add a file group.
  • Back up or restore the database.
  • Change the collation rules. The default collation is server collation.
  • Change the database owner. The owner of TEMPDB is DBO.
  • Create a database snapshot.
  • Delete the database.
  • Delete the guest user from the database.
  • Enable change data capture.
  • Participate in database mirroring.
  • Deletes a primary file group, primary data file, or log file.
  • Renames a database or primary file group.
  • Run DBCC CHECKALLOC.
  • Run the DBCC CHECKCATALOG.
  • Make the database OFFLINE.
  • Set the database or main file group to READ_ONLY.

2. View the configuration item for TempDB

 SELECT * FROM sys.databases WHERE name = 'tempdb'
Copy the code

 

References:

Msdn.microsoft.com/zh-cn/libra… Msdn.microsoft.com/zh-cn/libra… Msdn.microsoft.com/zh-cn/libra… “The Art of SQL Server Performance Optimization and Management”

Welcome to follow my official account: “Wukong Chat Framework”

About the author: 8-year Veteran of Internet workplace | full stack engineer | super dad after 90 | open source practitioner | owner of public number 10,000 fans original number. Blue Bridge signed the author, the author of “JVM performance tuning practice” column, handwritten a set of 70,000 words SpringCloud practice summary and 30,000 words distributed algorithm summary. Welcome to follow my public account “Wukong Chat framework”, free access to learning materials.

I am Wukong, strive to become stronger, become super Saiya people!