Survey: how is your tempdb configured?

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;

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.]


Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.