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.
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 , (SELECT [Global] FROM #PSR_tracestatus WHERE [TraceFlag] = 1118) AS , [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