In this week's survey, I want to know how you've got tempdb configured compared to the number of processor cores SQL Server thinks it has. I'll correlate, analyze, and present the results like the log file survey I did last year where I got results for 17000 databases.
The code I'd like you to run is as follows:
SELECT os.Cores, df.Files
FROM
(SELECT COUNT(*) AS Cores FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE') AS os,
(SELECT COUNT(*) AS Files FROM tempdb.sys.database_files WHERE type_desc = 'ROWS') AS df;
GO
It works on 2005 onwards (thanks to Robert Davis (blog|twitter) for doing a quick 2005 test for me).
I'll take as many results as you can be bothered gathering for me – anywhere from 1 to 100s!
To help you out, Eric Humphrey (blog|twitter), who's in our class this week, put together a PowerShell script to help you run the code against multiple servers. See here – very cool!
Either add your results as a comment or drop me a mail with the results. Feel free to add in any explanation you want as to the reasoning for your setup. Everything will remain anonymous as always.
I'll report on the results in a week or two.
[Edit: the survey is now closed.]
Thanks!
72 thoughts on “Survey: how is your tempdb configured?”
I have 24 cores and 24 files :)
I have 24 cores and 8 files.
Our Decision Support System has 16 cores and 8 files.
16 cores, 1 file.
In our various environments:
Dev and QA1:
Cores Files
4 1
QA2:
Cores Files
16 16
Prod:
Cores Files
8 7
8 and 8
Cores Files
4 1
Cores Files
———– ———–
8 4
In our Development instance we have 16 and 1. Based on the last 2 days we are making some changes!
All our servers are configured to use one file per core, up to a maximum of 10 files. These files are on the same file group, though (10 different disks for tempdb would be nice, but… :-))
Cores Files
———–
8 4
Live – 8 cores 4 files
Dev – 4 cores 4 files
16 cores, 16 files
Cores Files
4 1
16 1
8 1
16 1
16 1
16 4
8 1
8 1
8 1
24 cores, 12 files
Cores Files
8 8
8 1
16 1
8 1
8 1
2 1
8 1
2 1
4 1
4 1
4 1
8 1
2 1
2 1
2 1
2 1
2 1
2 1
2 1
2 1
2 and 1
Cores Files
4 1
Cores Files
4 1
1 1
4 1
4 2
4 1
4 2
16 cores, 8 files
8 cores 2 files
Production servers (2005):
8 cores, 8 files on one
8 cores, 1 file on another
Testing/Training servers (2008r2):
4 cores, 1 file on each of 2 servers
Cores Files
24 1
16 1
8 1
16 1
* 8 cores, 1 file
We have about 50 servers in our production site. In each case, the number of files equal the number of cores. The values can range from 4/4 to 48/48.
16|1 – 55GB (50GB used) TEMPDB – NO PAGELATCH OR PAGEIOLATCH
16|1 – 300GB (only 5GB used) TEMPDB – NO PAGELATCH OR PAGEIOLATCH
THE WAITS have been tested with this script (http://www.sqlservercentral.com/blogs/robert_davis/archive/2010/03/05/Breaking-Down-TempDB-Contention.aspx):
Select
session_id,wait_type,wait_duration_ms,blocking_session_id,resource_description,ResourceType =
Case When Cast(Right(resource_description, Len(resource_description) – Charindex(‘:’, resource_description, 3)) As Int) – 1 % 8088 = 0 Then ‘Is PFS Page’
When Cast(Right(resource_description, Len(resource_description) – Charindex(‘:’, resource_description, 3)) As Int) – 2 % 511232 = 0 Then ‘Is GAM Page’
When Cast(Right(resource_description, Len(resource_description) – Charindex(‘:’, resource_description, 3)) As Int) – 3 % 511232 = 0 Then ‘Is SGAM Page’
Else ‘Is Not PFS, GAM, or SGAM page’
End
From sys.dm_os_waiting_tasks
Where wait_type Like ‘PAGE%LATCH_%’
And resource_description Like ‘2:%’
4 1 in development
Cores Files
4 1
4 1
8 4
16 4
24 4
24 4
1 1
1 1
2 2
12 4
2 1
24 4
2 1
1 1
1 1
1 1
8 1
2 1
4 1
4 2
4 2
1 1
1 1
4 1
4 1
1 1
1 1
2 1
2 1
4 1
2 2
2 1
4 1
4 1
1 1
1 1
Cores Files
8 1
8 and 1
Prod 16 8
Test 8 8
Cores Files
2 8
2 1
2 1
8 1
8 1
4 1
24 cores hyperthreaded shows as 48 with 8 files.
Cores Files
16 8
16 12
8 8
24 16
16 6
16 1
24 cores, 1 file
Cores Files
4 1 (Prod – 2008 SP 2)
8 1 (Dev – 2008 R2)
16 1 (Prod – 2008 R2)
Cores Files
16 2
16 cores and 16 files.
24 cores, 24 files.
8 cores 2 files
16 cores 2 files
16 cores 2 files
12 cores, 3 files
Production
Cores Files
16 1
8 4
8 4
16 1
4 4
4 4
Reporting
Cores Files
16 8
2 and 1
Thanks to the power of Central Management Servers:
Cores Files
8 1
4 1
4 1
16 1
16 1
8 1
16 1
16 1
16 1
16 1
8 1
16 16
16 1
4 4
8 1
2 1
8 1
8 1
8 1
4 2
4 1
4 1
4 1
1 1
4 1
16 16
4 1
4 1
4 1
4 1
4 1
8 1
8 8
8 9
8 1
4 1
1 1
2 1
8 8
4 1
Instance 1 – 24 cores, 8 files
Instance 2 – 4 cores, 1 file
Instance 3 – 4 cores, 1 file
Instance 4 – 4 cores, 1 file
Say 4-4 (Number of files= Number of CPUs, standard maintained across most (if not all) servers/Instances)
Cores Files
32 1
16 1
32 1
2 1
32 1
32 1
8 1
8 1
4 1
32 1
8 1
2 1
6 4
Interestingly enough the only one that we have with more than 1 was due to a MS recommendation when looking at a performance issue but couldn’t prove that it was necessary. After adding the additional files it made no difference and of course the PAGELATCH wait counts for tempdb did not change (they weren’t bad to begin with).
I have checked all the other servers and we don’t have PAGELATCH waits for tempdb but I am doing some further assessment currently.
Cores Files
32 8
1 file for each instance regardless #of cores
Cores Files
24 1
24 4
24 4
24 4
24 4
These, perhaps perverse, results are due to migrating instances from dual-processor, quad-core servers (hence, 4 files using a 2:1 core:file ratio) to dual-processor, hexa-core hyperthreaded servers. The first example, with a single file, is a work-in-progress.
Cores Files
4 1
Because I don’t know any better?
8 16
16 16
64 16 – on Fusion IO SSF
48 16 – on Fusion IO SSF
16 16
16 16
4 1
8 cores, 6 files
For SQL 8.0 its one file per core
For SQL 9.0 and above this is variable typically between 2 files per 8 cores to 6 files per 8 core, configured after checking for allocation contention.
8 cores. 8 Files throughout live estate.
No hyperthreading.
Moving these to their own drives (Temp and Log – RAID 10)
Read only subscribers being tested with local server spec SSD for Temp and Log soon.
4 cores/1 file
2 cores/1 file
Cores Files
———– ———–
8 1
Cores Files
4 4
16 8
2 1
4 2
16 1
Cores Files
16 4
48 4
8 2
24 12
8 8
Cores Files
—– —–
16 8
16 8
16 1
16 1
16 8
16 1
16 1
16 8
16 8
16 8
8 8
8 1
16 8
16 4
8 1
4 1
8 1
4 1
8 8
4 4
8 1
8 8
8 8
8 1
16 8
16 8
16 8
16 3
4 1
1 1
24 8
24 8
16 8
16 8
4 1
16 8
8 1
16 1
16 1
16 1
8 1
8 1
8 1
Prod: 4 and 1 (We had two files up to a week ago, but found better performance with 1, we tried 4 as well, but it didn’t help.)
Dev: 8 and 1
2 and 1
2 and 1
Cores Files
———– ———–
64 8
64 8
8 1
8 4
8 4
16 8
16 8
Cores Files
———– ———–
128 7
cores file
4 1
16 cores, 1 file.
Cores Files
—– —–
4 1
8 1
8 1
8 1
8 1
4 1
16/16 <– hundreds of systems setup this way
We weren’t aware that the best practice was no longer cores=# data files, so we deployed them all with the old best practice. We haven’t found any tempdb contention with this config, so we haven’t made any changes.
Cores Files
———– ———–
7 7