Survey: tempdb file configuration (code to run)

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)

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

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

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

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

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

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

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

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

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

  11. 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!

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

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

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

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

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

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

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

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

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

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.