Some things to consider about tempdb

Because the location of the database's files is so important to I/O performance that when you create a civilian for the master data file, you need to consider the impact of tempdb performance on the system, because it is the most dynamic database, and the speed needs to be the fastest.

composition : Consists of a master data file and a log file. Added support for many feature sets starting with SQL server2005 to create user objects, internal objects, or versions. such as: triggers, queries, snapshot isolation, multiple active result sets, online index creation, cursors, LOB parameters, temporary tables, table variables, table-valued functions, and so on.

Restarting SQL SERVER,TEMPDB is the only database that reverts to the original default size (8M) and will continue to grow based on usage requirements. The growth rate should generally be set to allow the file to grow within two minutes.

To monitor tempdb in real time, use the following statement to query to 5 execution tasks, which maximize tempdb:

Select Top 5 * from Sys.dm_db_session_space_usage
Order BY (User_objects_alloc_page_count+internal_objects_alloc_page_count) desc

It is recommended to use the following query to monitor the size of tempdb, which is typically run once a week to query usage, mainly showing: internal objects, free space, version stores, and space used by user objects:

Select SUM (user_object_reserved_page_count) *8 as User object, sum (internal_object_reserved_page_count) *8 as inner object,
SUM (version_store_reserved_page_count) *8 as version store, sum (unallocated_extent_page_count) *8 as free space
From Sys.dm_db_file_space_usage

Query results

Configuration steps:

Using the previous query and monitoring, according to our usage we can briefly break down into a few steps to configure tempdb.

1. Allocate pre-space for tempdb according to the above statement, and start the self-growth feature to prevent the run-out of SQL Server by running out of space.

2. Rule of thumb, each CPU creates the same number of tempdb data files for each instance, up to a maximum of 8.

3. Make sure that tempdb is set to the simple recovery model.

4. The self-growth value is generally 10% of the initial size.

5. Place tempdb on a fast, dedicated I/O subsystem.

6. Use SQL Server agent or mscom with SQL knowledge Pack to create an alert for the monitoring environment to ensure that trace 1101 or 1105 (data is full).

The main monitoring of these counters: SQLServer:Databases:LogFile (s) Size (KB), SQLServer:Databases:DataFile (s) Size (KB), sqlserver:databases : Log File (s) used (KB).

7. Initialize with an instant database file. Be sure to grant Se_manage_volume_name permissions to the service account. or run with an account with administrator privileges.

Another very useful tool is the DMV sys.dm_db_task_space_usage.

Summarize:

This article provides a brief overview of how to plan for design and early warning configuration for tempdb, and the main understanding of how to get SQL Server to stop running without the tempdb space exhaustion, and how to configure the performance to be much improved. Finally, it should be noted that the alarm task after the counter is reset to 0, also should monitor the disk counters Avg.sec/read and avg.sec/write, less than 10ms for good, 10-20ms for passing, 20-50ms is slow, need attention, Greater than 50ms is in a severe IO bottleneck.

Some things to consider about tempdb