6

I would like to split tempdb into two files with same size, to improve performance of the database.

I wonder if these two data files can be on the same disk, separately from the logs and users data files?

  • 3
    yes. Most will recommend you start with 4 or 8 files, instead of 2, depending on who you ask. sqlskills.com/blogs/paul/category/tempdb – Kevin3NF Dec 22 '16 at 19:36
  • 1
    And typically you'll want to put tempdb on separate storage from user data and log files. – db_brad Dec 22 '16 at 19:39
  • And you want to turn autogrowth off for the data files to avoid ever having to grow the files assuming the sizes for each data file you selected are sufficient. – rvsc48 Dec 22 '16 at 19:48
  • 2
    If you can, match the number of tempdb files to the number of cores working on your DB. This can significantly improve throughput, as each core can effectively have its own tempdb table. If you have two cores running, use two tempdb files, four, eight, or sixteen as well. If you are running more than 16 cores, do a bit more research, since I seem to recall that you gain no added benefit above 16 files. – Laughing Vergil Dec 22 '16 at 20:29
  • 1
    Don't forget trace flags 1118 and 1117! – SQL_Underworld Dec 30 '16 at 0:50
6

We have various installations at our shop and we tend to start low with four tempdb files and then add additional files if there is tempdb contention as pointed out by @Kevin3nfs comment, where he references an SQLSkills.com search.

Microsoft has a knowledge base article: Recommendations to reduce allocation contention in SQL Server tempdb database

[...]
As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.
[...]

SQLSKills.com has a good article The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention which has some general information about tempdb contention and a bit further down a script which will retrieve information about tempdb contention from some DMVs.

[...]
One of the most common performance problems that exists in SQL Server instances across the world is known as tempdb contention. What does that mean?

Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O.

Consider the scenario of hundreds of concurrent queries that all create, use, and then drop small temporary tables (that by their very nature are always stored in tempdb). Each time a temp table is created, a data page must be allocated, plus an allocation metadata page to keep track of the data pages allocated to the table. This requires making a note in an allocation page (called a PFS page – see here for in-depth info) that those two pages have been allocated in the database. When the temp table is dropped, those pages are deallocated, and they must be marked as such in that PFS page again. Only one thread at a time can be changing the allocation page, making it a hotspot and slowing down the overall workload. [...]

Below is a script we used for very large database server configurations. It creates a tempdb with 8 files of 11GB size with no autogrowth and a tempdb transaction log file of 11GB. This script can be adapted to your requirements.

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev',FILENAME = N'G:\tempdb.mdf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'G:\tempdb4.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev5', FILENAME = N'G:\tempdb5.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev6', FILENAME = N'G:\tempdb6.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev7', FILENAME = N'G:\tempdb7.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev8', FILENAME = N'G:\tempdb8.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = N'G:\templog.ldf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )

As you can see these files were all located on the same drive.

If you still notice contention in the tempdb database, then splitting the tempdb database files over various disks might be a possible solution, but than depends on your configuration (SAN, SSD, ...) and would have to be verified with testing.

2

Yes even on the same disk you should experience performance improvements with multiple tempdb. A rule of thumb is number of cores.

Recommendations to reduce allocation contention in SQL Server tempdb database

  • Only if tempdb was getting locked by the multiple cores though. – ajeh Dec 22 '16 at 22:36
0

Best practices is to maintain multiple sized TEMPDB data files, matching the number of processors (to a maximum of 8,which is enough for most of the environment)

--Find logical processors
SELECT cpu_count AS logicalCPUs FROM sys.dm_os_sys_info

--Add tempdb data files as per processor count from the above query.Sample query to add one file.Modify the code as per your environment

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2',
FILENAME = N'F:\Temdb\tempdev2.ndf' , SIZE =10MB , FILEGROWTH = 20MB) 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.