What use TempDB in SQL Server?
- Temporary user objects, such as global (##temp) or local (#temp) temporary tables, temporary indexes and stored procedures, table variables, tables returned in table-valued functions or cursors.
- Internal objects are created by database engine to process a SQL server statement
o Work tables for cursor or spool operations and temporary large object (LOB) storage, such as varchar(max), nvarchar(max), varbinary(max) text, ntext, image, xml) data type variables and parameters.
o Work files for hash join or hash aggregate operations.
o intermediate results for particular GROUP BY, ORDER BY, or UNION queries.
- Row versioning values for online index processes, Multiple Active Result Sets (MARS) sessions, AFTER triggers and index operations (SORT_IN_TEMPDB).
- Row versions that are generated by data modification transactions in a database that uses snapshot or read committed using row versioning isolation levels.
In many cases, Tempdb has been left to default configurations (initial default sizes are 8MB for data file and 1MB for log file) or not been configured with insufficient file sizes. Unfortunately, these configurations are not necessarily ideal in many environments. For those busy servers, Tempdb plays a very important role. I have seen so many times that Tempdb grew significantly in a short period, which caused insufficient disk space and slow running server. In order to improve the server performance, there are 2 basic perspectives to look into.
Storage array configuration
Considering Tempdb normally is high read-write database, RAID 5 wouldn’t be proper solution for Tempdb and it should be placed either on RAID1+0 or on RAID1, which depends on its real usage and the cost.
Ideally, Tempdb needs to have its own storage array and physical disk drive. This is because if the other parts of the systems or databases share the same physical disk(s) with Tempdb, it would add extra workload on the CPUs and use disk I/O, which would slow down Tempdb performance. If Tempdb is installed on the system partition, at a minimum move the TempDB from the system partition to another set of disks.
Physical file configuration
1. Add one data file per CPU (per core, not per socket), which increases the number of physical I/O operations that SQL Server can push to the disk at any one time and database will run faster.
2. Configure all the files properly at the same initial size and with the same growth settings, so that SQL Server can write the data across the files as evenly as possible. If the database files are with different sizes, it will attempt to fill the files with the most free space first in order to balance the amount of free space within all of the files. The pre-allocated size needs to be large enough space that allows to process typical workloads in environment without constantly expanding the size.
3. Set autogrow on with a reasonable increment growth size for Tempdb files. Auto growing that manages files grow causes all applications or internal operations that use Tempdb to pause, and also leads to data fragmentation. All of those could impact on server performance. For example, if the file growth is too small, but the large amount of data are being written to Tempdb, which mean Tempdb has to constantly expand and the SQL Server performance will be slow down. There increment growth size for Tempdb files is based on the speed of the I/O subsystem on which the Tempdb files are located.
No comments:
Post a Comment