Dividing tempdb among multiple physical files in C#

Creator ANSI/AIM Code 128 in C# Dividing tempdb among multiple physical files

Although there can only be one tempdb database per SQL Server instance, the tempdb database can be split into multiple physical files. If your tempdb is not active, then splitting it into multiple files may not buy you much additional performance. But

if your tempdb is active, splitting it into multiple files can potentially boost your server s overall performance. Unfortunately, selecting the ideal number of physical files needed to optimize tempdb s performance is not an exact science. As a starting point, you should consider creating as many physical files as there are CPU cores available to your SQL Server instance. For example, if your server has 8 CPU cores available to it, then divide the tempdb database into 8 physical files.

If you decide to use multiple files for tempdb, it is important that each of them be exactly the same size (for example, if your tempdb s normal size is 1 GB, then you should have 8 physical files of 125 MB each). This is because SQL Server uses a proportional fill strategy to fill the physical files. In addition, the autogrowth settings should be identical for each physical file in order to ensure that each physical file grows identically.

Multiple files can boost disk I/O performance and reduce contention by spreading I/O activity over multiple files. This is beneficial even if the multiple files reside on a single disk volume, although locating each physical tempdb file on its own disk volume would generally provide an even greater benefit (at a much greater cost). On the other hand, using more physical disk files can increase switching costs and file management overhead because each object created in tempdb will have to have IAM pages created in each of the physical files. This and other unknowns about your SQL Server instance complicate providing an exact recommendation for the optimum number of physical files to use for your tempdb. I recommend that you perform tests in your own environment to determine the number of physical files that optimize the tempdb for your particular needs. After testing, you may find that more or fewer tempdb physical files are needed for optimum performance. Although the tempdb MDF file should generally be split into multiple physical files, this is not the case with the tempdb LDF file. Because tempdb uses the simple recovery model, and because it uses it own optimized logging method, dividing the LDF file into multiple physical files rarely provides any benefit.

By default, when installing SQL Server, tempdb is stored in the same location as the rest of your databases. Although this may work for smaller, less busy SQL Servers, it can cause a lot of I/O contention problems on busy SQL Servers. Ideally, tempdb should be located on its own disk volume(s), separate from other disk activity.

No matter where tempdb is located, it should be located on the fastest I/O subsystem available to your SQL Server instance. Your hardware will limit what you can do, but you might want to consider the following options, assuming your hardware (and budget) permits:

Avoid putting tempdb on a RAID 5 I/O subsystem, because tempdb is subject to heavy writes, and RAID 5 often offers poor write performance. Instead, locate tempdb on a RAID 1 or RAID 10 I/O subsystem, which offer better write performance. If your SQL Server instance is storing data on a storage area network (SAN), consult with your SAN engineer to determine the best location for optimal performance. As a general rule of thumb on SANs, tempdb should be located on its own logical unit number (LUN) with its own dedicated drives.