Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
Another alternative is to use sp_WhoIsActive and log the output into a table .. especially the wait_info. This way you keep a baseline and keep a history of your sql server tempdb contention over time to see if adding extra tempdb file has alleviated the contention or not.
How can I measure when should I add another tempdb data file?
When you experience LATCH contention which can occur due to 3 “special” page types: GAM (Global Allocation Map), SGAM (Shared Global Allocation Map), and PFS (Page Free Space).
If you see a lot of lines of output where the wait_type is PAGELATCH_UP or PAGELATCH_EX, and the resource_description is 2:1:1 then that’s the PFS page (database ID 2 – tempdb, file ID 1, page ID 1), and if you see 2:1:3 then that’s another allocation page called an SGAM
.
And how many tempdb data files are required?
KB2154845 gives an official guidance, but below is from Paul Randal
Figure out the number of logical processor cores you have (e.g. two CPUS, with 4 physical cores each, plus hyperthreading enabled = 2 (cpus) x 4 (cores) x 2 (hyperthreading) = 16 logical cores. Then if you have less than 8 logical cores, create the same number of data files as logical cores. If you have more than 8 logical cores, create 8 data files and then add more in chunks of 4 if you still see PFS contention. Make sure all the tempdb data files are the same size too.
So below are the takeaways:
References :
