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