Hey , 

I have been seeing TempDB contention in memory on our SQL server 2012 Enterprise Edition with SP2 and I need to split TempDB Data file into multiple files .

Could someone please help me to verify the following information:

1]

We are on SQL server 2012 Enterprise Edition with service pack2 but as per SQL Server 2012 Enterprise Edition under CAL Licensing We are limited to use 20 logical processors instead 40 logical processors. Our SQL is configured on NUMA nodes and with the limitation SQL uses only 2 NUMA nodes on live .There are 10 logical CPUs are evenly assigned to each NUMA nodes. Microsoft recommends that if SQL server configured on NUMA node and we have 2 NUMA nodes, then we may add two data files at a time. Please let me know should I add two TempDB data file at a time?

2] We have TempDB Data and log files both on the same Drive of SQL server  .When I split TempDB into two Data files, I can get them on the same Drive .What your recommendation should I need to create TempDB Data files on the same drive or on separate disks?

3] What would be the blackout plan for splitting the tempdb into multiple files? Please let me know if someone has a better back out plan ?

            1] Run script that create tempdb Database with a single file

2] Reboot SQL service in order to apply change   

Your help will be apprecited .

Thanks ,

Daizy

Hi, create TempDb on a separate disk is better. More better if is an SSD disk :-)

for more informations you can find a lot of informations here:

http://www.brentozar.com/sql/tempdb-performance-and-configuration/

http://www.sqlservercentral.com/blogs/robert_davis/2010/03/05/Breaking-Down-TempDB-Contention/

hope this will help you

What your recommendation should I need to create TempDB Data files on the same drive or on separate disks?

Hello Daizy,

It don't make any sense to create multiple data file for TempDB on the same drive, you won't have any benefit with this.

Although adding multiple tempdb files can have an impact on performance, adding multiple tempdb data files, on the same drive, will not likely have a significant impact on speed of the server.  Creating tempdb files based on CPU count is a myth and should not be done.

What exactly is your issue and what are you seeing?

Hi,

1. I did not get your first question. Its good to add two or 4 data files when you see contention. More than that

1. Enable Trace flag 1118

2. Make sure autogrowth of all tempdb data files are same

3 Make sure they are on separate physical disks otherwise you wont see much benefit

What do you man by Backout plan ?

Tom , I am seeing TempDB contention on Production server when there is a heavily load on sql server . We also experiencing the overall system slowness.Please look at Pagelatch wait statistics on our server ,Please advise .

Thanks ,

Daizy

>Microsoft recommends that if SQL server configured on NUMA node and we have 2 NUMA nodes, then we may add two data files at a time.

I know of no such recommendation.

>What your recommendation should I need to create TempDB Data files on the same drive or on separate disks?

For TempDb page latch contention, there is absolutely no reason to put the tempdb files on different disks or on SSD.  Latch contention is entirely in memory.  There is no IO involved.

>What would be the blackout plan for splitting the tempdb into multiple files?

Run a script to remove the additional files.  Then restart SQL.