SQL Server has four system databases by default and one of them is called TempDB. TempDB is used for many operations, such as user-created temporary objects, internal temporary objects and version stores and certain features like online re-indexing, multiple active record sets (MARS) and others. Since TempDB is shared across all databases and all connections in SQL Server, it might become a point of contention if not configured correctly. This article will cover a few important performance-related facts about TempDB.
First, let’s review a few basics. The name of the database outlines the purpose, but we need to keep in mind that this database is recreated every time the SQL Server service is started. Few DBAs use the TempDB database creation date as the “start time” for SQL Server. Here is the query:
SELECT create_date as 'SQL Service Start Time' FROM sys.databases WHERE name = 'tempdb'
The query above is almost accurate but the more accurate way would be to use this DMV query:
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
Since the database is recreated during restart, we should not create any permanent object in TempDB database. As you can imagine, they would be lost on restart and unrecoverable.
Now that we have covered the basics, let us move ahead with three things you should look out for.
Tip 1: Keep TempDB on Local drive in Cluster
This was a feature introduced in SQL Server 2012. Generally, in a clustered instance of SQL Server, database files are stored in shared storage (SAN). In SQL Server 2012 and later, however, we can keep TempDB on local attached drives. As mentioned earlier, TempDB database is shared across a whole instance and hence the IO performance of this database is very critical.
With faster drives like SSD and FusionIO cards, there’s been an increased interest in keeping TempDB on those drives in case of cluster also. Microsoft has heard this feedback and allowed SQL Server to keep TempDB files in local drive, in the case of a cluster. One advantage of placing TempDB on a local disk is that it creates separate paths of IO traffic by having other database files on the SAN and TempDB files on the local disk. By using a PCIe SSD or traditional hard drive SSDs, the IO operations performed on TempDB will bypass HBAs. This provides better performance for TempDB operations and avoids contention on a shared storage network or array.
Another advantage of this feature is to save cost. Assume that we deploy a multisite, geographically dispersed cluster. This means the SAN would be replicated from one location to another, maybe few miles or many miles apart. If the TempDB is kept on SAN, it would also be replicated and as mentioned earlier, it’s a scratchpad kind of database for SQL Server. Keeping files on local drives would mean better bandwidth usage and faster failovers.
We just need to make sure that the same local path exists on all nodes of SQL Server.
Tip 2: Configure for multiple DATA Files
When there are multiple data files in a database, all the writes to the database are striped across all files based on the proportion of free space that the file has to the total free space across all of the files. Now, each of the data files has its own set of allocation pages (called PFS, GAM, and SGAM pages) so as the writes move from file to file the page allocations occur from different allocation bitmap pages, spreading the work out across the files and reducing the contention on any individual page.
The general recommendation is that it should be equal to logical processors, if less than 8 else configure it to 8 files. For example, if we have a dual-core processor, then set the number of TempDB data files equal to two. If we have more than 8 cores, start with 8 files and add four at a time as needed. We also need to ensure that the initial size and auto-growth settings for ALL TempDB data files are configured in the same way.
Tip 3: Consider trace flag 1117 and 1118
These are two trace flags which are useful to avoid contention in TempDB database. The most common trace flag is 1118 which prevents contention on the SGAM pages by slightly changing the allocation algorithm used. When trace flag 1118 is enabled, the allocation in TempDB are changes from a single page at a time from a mixed extent (8 times) to allocate an extent of 8 pages. So when there are multiple temp tables creation in TempDB database, allocation bitmap contention would be alleviated.
Less well known, trace flag 1117 changes the auto-grow algorithm in SQL Server. It is always recommended to manually grow the data files. This is because when SQL Server performs auto-grow of data files, it is done one data file at a time in a round robin fashion. When this happens, SQL Server auto-grows the first file, writes to it until it is filled and then auto-grows the next data file. If you observed, the proportional fill is broken now. When trace flag 1117 is enabled, then when SQL Server has to perform auto-grow of a data file, it auto-grows all of the files at the same time.
To conclude, configuration of TempDB must necessarily be environment-specific. What we’ve covered here should fit most environments.
Leave a Reply