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
(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;
I'll take as many results as you can be bothered gathering for me – anywhere from 1 to 100s!
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.]