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

Survey: page life expectancy

It’s been a while since I’ve done a survey, and this one is inspired by a discussion I had with Matt Slocum (b | t) yesterday that led me to update my PLE post Page Life Expectancy isn’t what you think… with the discovery that on NUMA systems, Buffer Manager: Page Life Expectancy is a harmonic mean, not an arithmetic mean – big difference.

Anyway, in this survey I want some information about your page life expectancy and other supporting information:

  • SQL Server version
  • Number of logical processor cores
  • Buffer Node: Page Life Expectancy and Target/Total Node Memory for each buffer pool partition
  • Buffer Manager: Page life Expectancy

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.

[Edit: 6/17/15 – the survey is closed – please don’t send any more results – thanks!]

I’ll editorialize the results in a week or two.

Thanks!

SELECT
	LEFT (@@version, 25),
	'Cores available:',
	COUNT (*)
FROM sys.dm_os_schedulers
WHERE [status] = 'VISIBLE ONLINE'

UNION ALL

SELECT
	[object_name],
	[counter_name],
	[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Memory Node%'
	AND [counter_name] = 'Target Node Memory (KB)'

UNION ALL

SELECT
	[object_name],
	[counter_name],
	[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Memory Node%'
	AND [counter_name] = 'Total Node Memory (KB)'

UNION ALL

SELECT
	[object_name] AS [ObjectName],
	[counter_name] AS [CounterName],
	[cntr_value] AS [CounterValue]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Node%'
	AND [counter_name] = 'Page life expectancy'

UNION ALL

SELECT
	[object_name],
	[counter_name],
	[cntr_value]
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
	AND [counter_name] = 'Page life expectancy';
GO

Survey: how much server memory on your largest machine?

[Edit 12/10/14: Survey is closed see the editorial here.]
One of the trends I’m seeing this year is a slow increase in the number of people who have servers with very large amounts of memory – so time for a survey!

I’d like to know how much memory is installed on your largest server that runs SQL Server (not what max server memory is set to, how much physical memory is installed).


I’ll editorialize the results in a week or two.

Thanks!

PS If you can’t see the vote button, it’s a browser issue so please try a different one or just hit ‘Enter’ after making a selection and it should register your vote.