I’m running this survey to help the SQL Server team at Microsoft, who would like to get a broad view of current tempdb configurations. I’ll editorialize the results as well in a week or two.
Feel free to run the code below any way you want, and also add a single preceding column to the result set (e.g. server name or number) if you want, but PLEASE do not add any *rows* of data apart from what I’ve asked for otherwise it makes the data processing very time consuming, especially if you send results from hundreds of servers. I know people that do that are trying to be helpful, but I really don’t need any other data apart from what I’ve asked for.
You can send me results in email in a text file or spreadsheet, or leave a comment below. The code will work on SQL Server 2005 onwards.
Thanks!
IF EXISTS (SELECT * FROM [tempdb].[sys].[objects]
WHERE [name] LIKE N'#PSR_tracestatus%')
DROP TABLE [#PSR_tracestatus];
GO
CREATE TABLE [#PSR_tracestatus] (
[TraceFlag] INT, [Status] INT, [Global] INT, [Session] INT);
INSERT INTO #PSR_tracestatus EXEC ('DBCC TRACESTATUS (1117) WITH NO_INFOMSGS');
INSERT INTO #PSR_tracestatus EXEC ('DBCC TRACESTATUS (1118) WITH NO_INFOMSGS');
SELECT
[os].[cores],
(SELECT [Global] FROM #PSR_tracestatus WHERE [TraceFlag] = 1117) AS [1117],
(SELECT [Global] FROM #PSR_tracestatus WHERE [TraceFlag] = 1118) AS [1118],
[file_id], [type_desc], [size], [max_size], [growth], [is_percent_growth]
FROM
tempdb.sys.database_files AS [df],
(
SELECT COUNT (*) AS [cores]
FROM sys.dm_os_schedulers
WHERE status = 'VISIBLE ONLINE'
) AS [os];
DROP TABLE [#PSR_tracestatus];
GO
27 thoughts on “Survey: tempdb file configuration (code to run)”
8 0 0 1 ROWS 393216 -1 10 1
8 0 0 2 LOG 65536 -1 10 1
8 0 0 3 ROWS 393216 -1 10 1
8 0 0 4 ROWS 393216 -1 10 1
8 0 0 5 ROWS 393216 -1 10 1
8 0 0 6 ROWS 393216 -1 10 1
8 0 0 7 ROWS 393216 -1 10 1
8 0 0 8 ROWS 393216 -1 10 1
8 0 0 9 ROWS 393216 -1 10 1
2 0 0 1 ROWS 131072 -1 65536 0
2 0 0 2 LOG 131072 -1 65536 0
2 0 0 3 ROWS 131072 -1 65536 0
1 4 1 1 1 ROWS 64000 -1 64000 0
1 4 1 1 2 LOG 12800 -1 12800 0
1 4 1 1 3 ROWS 64000 -1 64000 0
2 2 1 1 1 ROWS 64000 -1 64000 0
2 2 1 1 2 LOG 12800 -1 12800 0
2 2 1 1 3 ROWS 64000 -1 64000 0
3 8 0 0 1 ROWS 57256 -1 10 1
3 8 0 0 2 LOG 94656 -1 10 1
3 8 0 0 3 ROWS 52048 -1 10 1
3 8 0 0 4 ROWS 52048 -1 10 1
3 8 0 0 5 ROWS 52048 -1 10 1
3 8 0 0 6 ROWS 52048 -1 10 1
3 8 0 0 7 ROWS 52048 -1 10 1
3 8 0 0 8 ROWS 57256 -1 10 1
4 0 0 1 ROWS 917504 -1 131072 0
4 0 0 2 LOG 1310720 -1 65536 0
4 0 0 3 ROWS 917504 -1 131072 0
4 0 0 4 ROWS 917504 -1 131072 0
4 0 0 5 ROWS 786432 -1 131072 0
4 0 0 1 ROWS 256000 -1 16384 0
4 0 0 2 LOG 131072 -1 32768 0
4 0 0 3 ROWS 256000 -1 16384 0
4 0 0 4 ROWS 256000 -1 16384 0
4 0 0 5 ROWS 256000 -1 16384 0
2 0 0 1 ROWS 10272 -1 10 1
2 0 0 2 LOG 256 -1 10 1
8 0 0 1 ROWS 2432 -1 10 1
8 0 0 2 LOG 96 -1 10 1
8 0 0 1 ROWS 504728 -1 12800 0
8 0 0 2 LOG 47424 -1 12800 0
8 0 0 3 ROWS 128000 -1 12800 0
8 0 0 4 ROWS 128000 -1 12800 0
8 0 0 5 ROWS 128000 -1 12800 0
8 0 1 1 ROWS 1083800 -1 256 0
8 0 1 2 LOG 7744 -1 256 0
8 0 1 3 ROWS 1059536 -1 256 0
8 0 1 4 ROWS 1305072 -1 256 0
8 0 1 5 ROWS 1320192 -1 256 0
8 0 1 6 ROWS 1200136 -1 256 0
8 0 1 7 ROWS 1075712 -1 256 0
8 0 1 8 ROWS 1180680 -1 256 0
8 0 1 9 ROWS 1343360 -1 256 0
8 0 1 10 LOG 7808 268435456 256 0
8 0 1 11 LOG 7808 268435456 256 0
8 0 1 12 LOG 7808 268435456 256 0
8 0 1 13 LOG 7808 268435456 256 0
8 0 1 14 LOG 7808 268435456 256 0
8 0 1 15 LOG 7552 268435456 256 0
8 0 1 16 LOG 7552 268435456 256 0
4 0 0 1 ROWS 131072 -1 10 1
4 0 0 2 LOG 392 -1 10 1
8 0 0 1 ROWS 111592 -1 10 1
8 0 0 2 LOG 9592 -1 10 1
160 0 0 1 ROWS 14080000 -1 0 0
160 0 0 2 LOG 6400000 -1 0 0
160 0 0 3 ROWS 14080000 -1 0 0
160 0 0 4 ROWS 14080000 -1 0 0
160 0 0 5 ROWS 14080000 -1 0 0
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
160 0 0 1 ROWS 385024 -1 64000 0
160 0 0 2 LOG 15456 -1 10 1
160 0 0 3 ROWS 320128 -1 64000 0
160 0 0 4 ROWS 320128 -1 64000 0
160 0 0 5 ROWS 320128 -1 64000 0
160 0 0 6 ROWS 320128 -1 64000 0
160 0 0 7 ROWS 320128 -1 64000 0
160 0 0 8 ROWS 320128 -1 64000 0
160 0 0 9 ROWS 320128 -1 64000 0
160 0 0 10 ROWS 320128 -1 64000 0
160 0 0 11 ROWS 320128 -1 64000 0
160 0 0 12 ROWS 320128 -1 64000 0
160 0 0 13 ROWS 320128 -1 64000 0
160 0 0 14 ROWS 384128 -1 64000 0
160 0 0 15 ROWS 384128 -1 64000 0
160 0 0 16 ROWS 384128 -1 64000 0
160 0 0 17 ROWS 384128 -1 64000 0
160 0 0 18 ROWS 384128 -1 64000 0
160 0 0 19 ROWS 384128 -1 64000 0
160 0 0 20 ROWS 384128 -1 64000 0
160 0 0 21 ROWS 384128 -1 64000 0
160 0 0 22 ROWS 384128 -1 64000 0
160 0 0 23 ROWS 384128 -1 64000 0
160 0 0 24 ROWS 384128 -1 64000 0
160 0 0 25 ROWS 384128 -1 64000 0
4 0 0 1 ROWS 5051856 -1 10 1
4 0 0 2 LOG 478568 -1 10 1
24 1 0 1 ROWS 262144 -1 32768 0
24 1 0 2 LOG 393216 -1 32768 0
24 1 0 3 ROWS 262144 -1 32768 0
24 1 0 4 ROWS 262144 -1 32768 0
24 1 0 5 ROWS 262144 -1 32768 0
24 1 0 6 ROWS 262144 -1 32768 0
24 1 0 7 ROWS 262144 -1 32768 0
24 1 0 8 ROWS 262144 -1 32768 0
24 1 0 9 ROWS 262144 -1 32768 0
16 0 1 1 ROWS 14320120 -1 12800 0
16 0 1 2 LOG 776520 -1 12800 0
16 0 1 3 ROWS 640000 -1 12800 0
16 0 1 4 ROWS 7001760 -1 12800 0
16 0 1 5 ROWS 640000 -1 12800 0
16 0 1 6 ROWS 4749608 -1 12800 0
16 0 1 7 ROWS 640000 -1 12800 0
16 0 1 8 ROWS 7173480 -1 12800 0
16 0 1 9 ROWS 4993552 -1 12800 0
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
160 0 0 1 ROWS 2621440 -1 65536 0
160 0 0 2 LOG 7077888 -1 65536 0
160 0 0 3 ROWS 2621440 -1 65536 0
160 0 0 4 ROWS 2621440 -1 65536 0
160 0 0 5 ROWS 2621440 -1 65536 0
160 0 0 6 ROWS 2621440 -1 65536 0
160 0 0 7 ROWS 2621440 -1 65536 0
160 0 0 8 ROWS 2621440 -1 65536 0
160 0 0 9 ROWS 2621440 -1 65536 0
160 0 0 10 ROWS 2621440 -1 65536 0
160 0 0 11 ROWS 2621440 -1 65536 0
160 0 0 12 ROWS 2621440 -1 65536 0
160 0 0 13 ROWS 2621440 -1 65536 0
160 0 0 14 ROWS 2621440 -1 65536 0
160 0 0 15 ROWS 2621440 -1 65536 0
160 0 0 16 ROWS 2621440 -1 65536 0
160 0 0 17 ROWS 2621440 -1 65536 0
160 0 0 18 ROWS 2621440 -1 65536 0
160 0 0 19 ROWS 2621440 -1 65536 0
160 0 0 20 ROWS 2621440 -1 65536 0
160 0 0 21 ROWS 2621440 -1 65536 0
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
16 0 0 1 ROWS 6553600 -1 131072 0
16 0 0 2 LOG 9830400 -1 131072 0
16 0 0 3 ROWS 6553600 -1 131072 0
16 0 0 4 ROWS 6553600 -1 131072 0
16 0 0 5 ROWS 6553600 -1 131072 0
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
24 0 0 1 ROWS 1280000 -1 1280000 0
24 0 0 2 LOG 395504 -1 640000 0
24 0 0 3 ROWS 1280000 -1 1280000 0
24 0 0 4 ROWS 1280000 -1 1280000 0
24 0 0 5 ROWS 1280000 -1 1280000 0
Also, SQLGuzel: 160cores? Daaaaaaaang!
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
160 0 1 1 ROWS 1835008 -1 131072 0
160 0 1 2 LOG 8960000 -1 65536 0
160 0 1 3 ROWS 1835008 -1 131072 0
160 0 1 4 ROWS 1835008 -1 131072 0
160 0 1 5 ROWS 1835008 -1 131072 0
160 0 1 6 ROWS 1835008 -1 131072 0
160 0 1 7 ROWS 1835008 -1 131072 0
160 0 1 8 ROWS 1835008 -1 131072 0
160 0 1 9 ROWS 1835008 -1 131072 0
160 0 1 10 ROWS 1835008 -1 131072 0
160 0 1 11 ROWS 1835008 -1 131072 0
160 0 1 12 ROWS 1835008 -1 131072 0
160 0 1 13 ROWS 1835008 -1 131072 0
160 0 1 14 ROWS 1835008 -1 131072 0
160 0 1 15 ROWS 1835008 -1 131072 0
160 0 1 16 ROWS 1835008 -1 131072 0
160 0 1 17 ROWS 1835008 -1 131072 0
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
40 0 1 1 ROWS 2621440 -1 131072 0
40 0 1 2 LOG 4194304 -1 65536 0
40 0 1 3 ROWS 2621440 -1 131072 0
40 0 1 4 ROWS 2621440 -1 131072 0
40 0 1 5 ROWS 2621440 -1 131072 0
40 0 1 6 ROWS 2621440 -1 131072 0
40 0 1 7 ROWS 2621440 -1 131072 0
40 0 1 8 ROWS 2621440 -1 131072 0
40 0 1 9 ROWS 2621440 -1 131072 0
40 0 1 10 ROWS 2621440 -1 131072 0
40 0 1 11 ROWS 2621440 -1 131072 0
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
8 0 1 1 ROWS 524288 -1 131072 0
8 0 1 2 LOG 524288 268435456 131072 0
8 0 1 3 ROWS 524288 -1 131072 0
8 0 1 4 ROWS 524288 -1 131072 0
8 0 1 5 ROWS 524288 -1 131072 0
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
———– ———– ———– ———– ———————————————————— ———– ———– ———– —————–
20 0 0 1 ROWS 6407168 -1 25600 0
20 0 0 2 LOG 921664 -1 12800 0
20 0 0 3 ROWS 6400000 -1 25600 0
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
———– ———– ———– ———– ———————————————————— ———– ———– ———– —————–
8 0 0 1 ROWS 262144 -1 25600 0
8 0 0 2 LOG 338944 -1 25600 0
8 0 0 3 ROWS 262144 -1 25600 0
8 0 0 4 ROWS 262144 -1 25600 0
8 0 0 5 ROWS 262144 -1 25600 0
one of our 3 monsters:
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
———– ———– ———– ———– ——————————– ———– ———– ———– —————–
78 0 1 1 ROWS 10485760 -1 0 0
78 0 1 2 LOG 50331648 83886208 16777216 0
78 0 1 3 ROWS 10485760 -1 0 0
78 0 1 4 ROWS 10485760 -1 0 0
78 0 1 5 ROWS 10485760 -1 0 0
78 0 1 6 ROWS 10485760 -1 0 0
78 0 1 7 ROWS 10485760 -1 0 0
78 0 1 8 ROWS 10485760 -1 0 0
78 0 1 9 ROWS 10485760 -1 0 0
78 0 1 10 ROWS 10485760 -1 0 0
78 0 1 11 ROWS 10485760 -1 0 0
78 0 1 12 ROWS 10485760 -1 0 0
78 0 1 13 ROWS 10485760 -1 0 0
78 0 1 14 ROWS 10485760 -1 0 0
78 0 1 15 ROWS 10485760 -1 0 0
78 0 1 16 ROWS 10485760 -1 0 0
78 0 1 17 ROWS 10485760 -1 0 0
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
8 0 0 1 ROWS 10800888 -1 10 1
8 0 0 2 LOG 359544 -1 10 1
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
16 0 0 1 ROWS 1463328 -1 10 1
16 0 0 2 LOG 297136 -1 10 1
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
4 0 0 1 ROWS 2851632 -1 10 1
4 0 0 2 LOG 30152 -1 10 1
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
24 0 0 1 ROWS 899544 -1 10 1
24 0 0 2 LOG 12768 -1 10 1
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
24 0 0 1 ROWS 2680 -1 10 1
24 0 0 2 LOG 320 -1 10 1
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
8 0 0 1 ROWS 10272 -1 10 1
8 0 0 2 LOG 480 -1 10 1
16 0 1 1 ROWS 491520 -1 32768 0
16 0 1 2 LOG 191920 -1 10 1
16 0 1 3 ROWS 458752 -1 32768 0
16 0 1 4 ROWS 458752 -1 32768 0
16 0 1 5 ROWS 491520 -1 32768 0
16 0 1 6 ROWS 491520 -1 32768 0
16 0 1 7 ROWS 491520 -1 32768 0
16 0 1 8 ROWS 491520 -1 32768 0
16 0 1 9 ROWS 491520 -1 32768 0
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
2 0 0 1 ROWS 846272 -1 12800 0
2 0 0 2 LOG 35904 -1 5120 0
4 0 0 1 ROWS 197712 -1 10 1
4 0 0 2 LOG 22648 -1 10 1
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
—— —– —– ——– ——— ——– ———- ——- —————–
12 1 1 1 ROWS 2304000 -1 25600 0
12 1 1 2 LOG 276904 -1 12800 0
12 1 1 3 ROWS 2304000 -1 25600 0
12 1 1 4 LOG 277704 268435456 12800 0
12 1 1 5 ROWS 2304000 -1 128 0
12 1 1 6 ROWS 2304000 -1 128 0
12 1 1 7 ROWS 2304000 -1 128 0
12 1 1 8 ROWS 2304000 -1 128 0
12 1 1 9 ROWS 2304000 -1 128 0
12 1 1 10 ROWS 2304000 -1 128 0
cores 1117 1118 file_id type_desc size max_size growth is_percent_growth
8 0 0 1 ROWS 1310720 -1 10 1
8 0 0 2 LOG 20584 -1 10 1
8 0 0 3 ROWS 1310720 -1 10 1
8 0 0 4 ROWS 1310720 -1 10 1
8 0 0 5 ROWS 1310720 -1 10 1
8 0 0 6 ROWS 1310720 -1 10 1
8 0 0 7 ROWS 1310720 -1 10 1
8 0 0 8 ROWS 1310720 -1 10 1
8 0 0 9 ROWS 1310720 -1 10 1
4 0 0 1 ROWS 6400 -1 6400 0
4 0 0 2 LOG 6400 -1 6400 0