Usage and performance issues with "T-SQL performance optimization" 01.TempDB

I used to pursue new things, and found the foundation is the most important, this year's main goal is to master SQL query and SQL performance optimization.

This series of "T-SQL Fundamentals" is primarily a summary of the T-SQL Foundation.

"T-SQL Basics" 01. Single-Table query-several SQL query questions

"T-SQL Basics" 02. Join Query

"T-SQL Basics" 03. Subqueries

"T-SQL Basics" 04. Table Expression-Previous

"T-SQL Basics" 04. Table Expression-Next

"T-SQL Basics" 05. Set operation

"T-SQL Basics" 06. Perspective, inverse perspective, grouping set

"T-SQL Basics" 07. Data modification

"T-SQL Fundamentals" 08. Transactions and concurrency

"T-SQL Basics" 09. Programmable objects

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

"T-SQL Advanced" 01. Easy-to-use SQL tvp~~ exclusive [add-delete-change-check] Example

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

Usage and performance issues with "T-SQL performance Tuning" 01.TempDB

Usage and performance issues with "T-SQL Performance tuning" 02.Transaction log

"T-SQL Performance Tuning" 03. Execution plan

"T-SQL Performance Tuning" 04. Deadlock Analysis

Keep updating ... Welcome to follow me!

First, what is tempdb?

1.TempDB is a system database. has existed since SQL Server2000.

2. Only simple recovery mode. Automatic truncation mode.

3. Store local variables/global temporary tables/table variables/temporary usages (such as hash tables, etc.).

4. After the machine restarts or the SQL Server service restarts, it will be recreated according to the model library's configuration.

5. If the temporary object is generated within the scope of a session or stored procedure, it is automatically reclaimed after the session ends and cannot be queried or used again.

6. Access is granted by default.

Ii. What is tempdb used to store? 2.1. User Temporary Object

(1) The Created entity table and the index above are displayed by the user in the session. Empty after reboot.

(2) Global temp table + index. # #开头的表.

(3) The local temporary table and the above index. #开头的表.

(4) Table variable. Beginning

Attention:

(1) global temporary tables are visible to all sessions. When a session that creates a temporary table disconnects a database and there is no activity to reference the global temporary table, SQL Server automatically deletes the corresponding global temporary table.

(2) A local temporary table is visible only to the session that created it and to the call stack internal level (internal procedures, functions, triggers, and dynamic batching). When the Create routine pops up the call stack, SQL Server automatically deletes the appropriate temporary table

(3) The table variable also has a corresponding table in the tempdb database as its physical representation. Visible only for batches of the current session. The internal batching of the current batch in the call stack is not visible, and subsequent batches in the session are not visible.

(4) According to the experience of foreign experts, for big data, biased to use temporary tables, small data volume (generally less than 100 rows) can use table variables.

,		can I create an index
temp table	y	y	y
Table variable	n	n	n

2.2. Internal temporary objects

Objects that store temporary data during a query, such as sorts, spooling, hash associations, and cursors.

You can use the following SQL statement to view:

SELECT * from Sys.dm_db_session_space_usage

View Internal_object_alloc_page_count Columns

2.3. Version Storage

When optimistic concurrency mode is turned on, temp db is used to store the pre-modified version data.

Attention:

The version store will cause unexpected growth in temp db and requires monitoring of the file size and usage space of temp db.

Iii. performance issues that exist on tempdb 3.1 space usage

Tempdb is a system database, used in many places, if configured and used improperly, the space will be quickly consumed, there may be errors, affecting the normal operation of the server.

View the space usage of tempdb.

3.1.1 Can use Performance Monitor to look at the space usage of SQL Server.

3.1.2 uses SQL statements to query for space usage.

(1) Viewing the usage of tempdb

Exec sp_spaceused

　　

(2) To view the size of tempdb.mdf file

SELECT * from Dbo.sysfiles

(3) View usage space for tempdb

SELECT * from Sys.dm_db_file_space_usage

(4) View the space allocation of a session without the currently active task.

SELECT * from Sys.dm_db_session_space_usage WHERE session_id > 50

(5) View the information for the currently running task in tempdb.

SELECT * from Sys.dm_db_task_space_usage WHERE session_id > 50

3.1.3 Diagnosing tempdb disk issues

Error	condition that caused the error
1101 or 1105	Any session must allocate space in tempdb .
3959	The version store is full. This error usually occurs after error 1105 or 1101 in the log.
3967	The version store is forced to shrink because tempdb is full.
3958 or 3966	The transaction could not find the required version record in tempdb .

3.2 I/O issues

(1) Use the function sys.dm_io_virtual_file_stats to view the read and write status of the disk 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 Trans Fer/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

Reference time: 10~20ms acceptable range.

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

Iv. optimizing the TempDB1. configuration file Size

Default configuration:

Initial Size 8M

Automatically grows by 10% without limiting growth.

This configuration can be modified, depending on the circumstances of the production environment.

The following configuration is recommended

tempdb File Size	filegrowth Incremental
0 to MB	Ten MB
100 MB	MB
More than MB or more	10%*

2. Where the files are stored

It is common to put tempdb files on a separate disk. If performance is pursued, consider putting it into the RAID0, but not the disaster-tolerant nature.

RAID: Disk array

RAID 0 stripe disk without parity. The data spans all physical disks without any disaster-tolerant characteristics.

RAID 1 disk mirroring. A minimum of two physical disks is required. Data can be read from both disks simultaneously, and the write data needs to be backed up to a different disk. has disaster-tolerant characteristics. Waste 50% of your disk space.

RAID 5 has a striped disk with parity. A minimum of 3 physical disks is required, one for parity information and two for data storage. has disaster-tolerant characteristics. Waste 50% of your disk space.

RAID 10 or Riad 0+1 combination. Read-write performance is best and disaster tolerant.

3. Number of files

Tempdb has only one primary filegroup, and all data files are stored in this filegroup. The general recommendation is that 4 book files start and need to be monitored, and if found not enough, you can add another 4. In turn. It is recommended to control the number of files within two digits.

V. Others 1. What cannot be done with tempdb

• Add a filegroup.
• Back up or restore the database.
• Change the collation. The default collation is the server collation.
• Change the database owner. The owner of tempdb is the dbo.
• Create a database snapshot.
• Delete the database.
• Remove the guest user from the database.
• Enable change data capture.
• Participate in database mirroring.
• Delete the primary filegroup, master data file, or log file.
• Renames a database or primary filegroup.
• Run DBCC Checkalloc.
• Run DBCC CheckCatalog.
• Set the database to OFFLINE.
• Set the database or primary filegroup to READ_ONLY.

Usage and performance issues with "T-SQL performance optimization" 01.TempDB