Sql-server – How to tell if the tempdb database has a performance issue/need to add more data files

performanceperformance-tuningsql servertempdb

SQL Server works in database called tempdb to handle a lot of tasks. Query operations like joins and aggregations happen there. Online index rebuilds, order by, aggregate function, triggers are also done in tempdb.

How can I measure when should I add another tempdb data file? And how many tempdb data files are required?

Best Answer

Paul Randal explains how many tempDB files to create here Correctly adding data files to tempdb

If your server has less than 8 logical cores (e.g. a one CPU server with 4 physical cores and hyperthreading enabled has 8 logical cores), use # tempdb data files = # logical cores, equally sized

If your server has more than 8 logical cores, start with 8 tempdb data files, and add sets of four at a time, equally sized, until the contention is alleviated

Note that it is critical to first alter all existing tempDB files so that they are the same size and with the same AUTOGROW settings before you begin. Then create all new TempDB files with the same size and autogrow settings as the existing files.

As for do you currently have a problem? View your wait statistics and determine your top waits, if PAGELATCH_UP or PAGELATCH_EX are up there then you need to investigate further. Glen Berry has a good query for that included here SQL Server Diagnostic Information Queries for September 2016

When you notice a slow down on your SQL Server you should return sys.dm_exec_requests wait type and wait resource information, and keep an eye out for pagelatch_* wait types. You can then pass the wait resource through DBCC PAGE to determine the database and internal pages experiencing page latching. You can also use the very useful query by SQLSoldier here Breaking Down TempDB Contention (part 2) to better determine which, if any, internal database pages are experiencing issues and if adding additional tempDB files is the answer. Assuming that you are seeing pagelatches on the internal GAM, SGAM or PFS pages then you want to keep adding files, based on the above advice until the PAGELATCH_ contention reduces.

Related Question