TempDB Performance Tips

TempDB - SeniorDBA

There are specific tasks you must be prepared to perform to get the best possible performance from your TempDB on your SQL Server instance. I have written about ways to get peak performance from your TempDB before. In this article by Dieter Gobeyn we hear him repeat some of these same tips.

1. Split files according number of CPUs

To fine-tune your tempdb’s performance, you first need to create additional data files of tempdb to maximize disk bandwidth. The amount of tempdb data files required should equal the number of CPUs in your server. Note that a dual-core CPU is considered to be two CPUs! In any event, the number of data files must not exceed eight, no matter how many additional cores are available on the computer.

2. Move to a separate fast disk and measure performance

Lower down the read and write latency by placing the tempdb data and log files on a separate fast (local) storage. The preferred choice here is a high rotational speed HHD disk or SSD local disk. Having lower access times to the logs and data files will impact the performance instantly. Next to that, the data should not float over the network anymore. This will save broadband as well.

3. Allocate the size of tempdb correctly

Avoid that SQL server needs to grow your log file or data file of tempdb when there’s is no free space left. This is a heavy operation, which consumes time and data fragmentation. It is therefore quintessential to allocate enough disk space to your data and log files!

Additionally, ensure that the data files used for the tempdb are of equal size. If those files are not equally created (or some file has been grown), SQL server will use the proportional fill algorithm, preferring the largest file for Global Allocation Map allocations rather than spreading the allocations over all files.

You can read the entire article here.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s