SQL Server Temp DB Add Multiple Data Files to Improve Performance

Quick one today.

One point that I regularly recommend is to optimize TempDB. There are several things we can do to improve overall performance of all the applications hitting the database instance by just improving the performance of TempDB; Does not require changes to a single line of application code. Every improvement you make here, adds to performance improvement to every application that hits a database on this instance. A few days ago, I wrote a post about improving performance by correctly balancing the number of data files for TempDB. In this post, I’d like to add some ‘behind the scenes’ understanding on why we need multiple data files.

As you can imagine, when you create a temporary table, it is an actual table created in TempDB. When you have hundreds of connections executing thousands of queries, a lot of temporary tables are created. This creates a lot of activity within TempDB that needs to be effectively managed to facilitate optimal performance. So let’s take one such incident of creating temporary table.

When a query requests to create a temporary table and store some data (or records) in it, Sql Server tries to allocate some pages. These pages are allocated in extents. An Extent is a group of 8 pages. Before it can allocate pages (or extents), it has to perform some meta-data checks to see what pages are available and where. Once an extent is available, assign the extent for this temporary table request and update meta-data settings to indicate that these extents are now allocated for a particular process (and its execution context). Here lies the trick. There may be thousands of extents available, but the meta-data pages where the tracking happens (PFS — allocation bitmaps) is only one for each data file. So, if you have only one data file, then all the requests for temporary table will be fighting to update these allocation bitmaps resulting in heavy delays (resource contention). When you have multiple data files, each file will have its own allocation bitmaps resulting in reduced contention (relatively).

The number of data files you create for TempDB is an important process. Read this article to know more.

As always, make sure the TempDB data files are of the same size.