SQL Server tempdb best practices increase performance
Author : Denny Cherry
One way to greatly improve your SQL Server’s performance is to properly optimize the tempdb database. To punch up its efficiency, try looking at the physical disk configuration, file configuration, as well as some settings within the database.
Physical files countdown
With standard user databases, it’s recommended that you set the number of physical files for each database at .25 to one physical file per CPU core. With the tempdb database, you should have one physical file per CPU core in the server. So, if you have a dual-chip, dual-core server, you should have four physical database files for the tempdb database.
By having the larger number of files, you can increase the number of physical I/O operations that SQL Server can push to the disk at any one time. The more I/O that SQL Server can push down to the disk level, the faster the database will run. With standard databases, SQL Server can cache a large amount of the data that it needs into memory. Because of the high-write nature of the tempdb, the data needs to be written to the disk before it can be cached back up into memory.
When adding more database files, it’s important to configure the files at the same initial size and with the same growth settings. That way, SQL Server will write the data across the files as evenly as possible. If the database files end up being 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.
Storage array configuration
Most databases are high read and low write and, usually, database files are placed on RAID 5 arrays, which give good disk performance at a reasonable cost. However, because the tempdb is a very high-write database, a RAID 5 array isn’t the proper place for it. You should put the tempdb on either a RAID 1 or RAID 10 array as they’re optimized for high-write applications. If you can afford additional RAID 1 or RAID 10 arrays for each physical database file for the tempdb, you’ll get increased performance.
For best performance, put the tempdb on its own RAID array or arrays that are not shared with any other parts of the database or system. This includes having other LUNs on the same SAN drives as other LUNs, and it’s especially important because if you put other LUNs on the same spindles as the tempdb LUN, it places additional load on those disks. That makes it extremely difficult to troubleshoot from the server side because the server doesn’t know about the extra I/O on the disks.
Modifying database settings
You can further increase tempdb performance by disabling the auto update stats, which will save your tempdb some work. Usually objects created in the tempdb are fairly small and, as such, the statistics will not reach the threshold that causes the statistics to update automatically. By disabling the setting, you stop SQL Server from having to check to see if it needs to update the statistics. However, you’ll need to manually update statistics if you occasionally use large temporary objects.
You can also set the auto create statistics option to false. That, too, will increase tempdb performance. By disabling the setting, you stop SQL Server from having to produce the statistics when the object is first created.
Settings should be changed with care. Depending on the kind of load you place on your tempdb, changing settings could adversely impact system performance. You should test the settings in a non-production environment before they are deployed to the production environment. After deployment, monitor these changes closely to ensure the system is running at peak performance.
Indexing temporary database objects
If you have large temp tables — thousands of rows or more – you’ll find that indexing your temporary tables greatly increases your tempdb performance, just as indexing permanent tables will increase the performance of queries on these tables. To index temp tables and global temp tables, use the normal CREATE INDEX command after the table has been created.
Unlike local and global temporary tables, table variables cannot have indexes created on them. The exception is that table variables can have a primary key defined upon creation using the DECLARE @variable TABLE command. This creates a clustered or non-clustered index on the table variable. The CREATE INDEX command does not recognize table variables. Therefore, the only index available to you is the index that accompanies the primary key and is created upon table variable declaration.
With careful planning and testing, you can get some excellent system performance improvements by making some fairly easy changes to the tempdb. Take care, however. Even simple changes to the tempdb can have an adverse impact on system performance.
Author : Denny Cherry
One way to greatly improve your SQL Server’s performance is to properly optimize the tempdb database. To punch up its efficiency, try looking at the physical disk configuration, file configuration, as well as some settings within the database.
Physical files countdown
With standard user databases, it’s recommended that you set the number of physical files for each database at .25 to one physical file per CPU core. With the tempdb database, you should have one physical file per CPU core in the server. So, if you have a dual-chip, dual-core server, you should have four physical database files for the tempdb database.
By having the larger number of files, you can increase the number of physical I/O operations that SQL Server can push to the disk at any one time. The more I/O that SQL Server can push down to the disk level, the faster the database will run. With standard databases, SQL Server can cache a large amount of the data that it needs into memory. Because of the high-write nature of the tempdb, the data needs to be written to the disk before it can be cached back up into memory.
When adding more database files, it’s important to configure the files at the same initial size and with the same growth settings. That way, SQL Server will write the data across the files as evenly as possible. If the database files end up being 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.
Storage array configuration
Most databases are high read and low write and, usually, database files are placed on RAID 5 arrays, which give good disk performance at a reasonable cost. However, because the tempdb is a very high-write database, a RAID 5 array isn’t the proper place for it. You should put the tempdb on either a RAID 1 or RAID 10 array as they’re optimized for high-write applications. If you can afford additional RAID 1 or RAID 10 arrays for each physical database file for the tempdb, you’ll get increased performance.
For best performance, put the tempdb on its own RAID array or arrays that are not shared with any other parts of the database or system. This includes having other LUNs on the same SAN drives as other LUNs, and it’s especially important because if you put other LUNs on the same spindles as the tempdb LUN, it places additional load on those disks. That makes it extremely difficult to troubleshoot from the server side because the server doesn’t know about the extra I/O on the disks.
Modifying database settings
You can further increase tempdb performance by disabling the auto update stats, which will save your tempdb some work. Usually objects created in the tempdb are fairly small and, as such, the statistics will not reach the threshold that causes the statistics to update automatically. By disabling the setting, you stop SQL Server from having to check to see if it needs to update the statistics. However, you’ll need to manually update statistics if you occasionally use large temporary objects.
You can also set the auto create statistics option to false. That, too, will increase tempdb performance. By disabling the setting, you stop SQL Server from having to produce the statistics when the object is first created.
Settings should be changed with care. Depending on the kind of load you place on your tempdb, changing settings could adversely impact system performance. You should test the settings in a non-production environment before they are deployed to the production environment. After deployment, monitor these changes closely to ensure the system is running at peak performance.
Indexing temporary database objects
If you have large temp tables — thousands of rows or more – you’ll find that indexing your temporary tables greatly increases your tempdb performance, just as indexing permanent tables will increase the performance of queries on these tables. To index temp tables and global temp tables, use the normal CREATE INDEX command after the table has been created.
Unlike local and global temporary tables, table variables cannot have indexes created on them. The exception is that table variables can have a primary key defined upon creation using the DECLARE @variable TABLE command. This creates a clustered or non-clustered index on the table variable. The CREATE INDEX command does not recognize table variables. Therefore, the only index available to you is the index that accompanies the primary key and is created upon table variable declaration.
With careful planning and testing, you can get some excellent system performance improvements by making some fairly easy changes to the tempdb. Take care, however. Even simple changes to the tempdb can have an adverse impact on system performance.
