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

28 thoughts on “Survey: page life expectancy

  1. (No column name) (No column name) (No column name)
    Microsoft SQL Server 2008 Cores available: 8
    SQLServer:Buffer Node Page life expectancy 18001
    SQLServer:Buffer Manager Page life expectancy 18001

  2. Microsoft SQL Server 2008 Cores available: 24
    SQLServer:Buffer Node Page life expectancy 27920
    SQLServer:Buffer Node Page life expectancy 29126
    SQLServer:Buffer Manager Page life expectancy 28510

  3. Did you intend to only get the basic version?
    Using left(@@version,25) drops off the R2 (SP3) from version string where it is present.

  4. Microsoft SQL Server 2005 Cores available: 16
    SQLServer:Buffer Node Page life expectancy 8954
    SQLServer:Buffer Node Page life expectancy 13074
    SQLServer:Buffer Manager Page life expectancy 10629

    Microsoft SQL Server 2008 Cores available: 8
    SQLServer:Buffer Node Page life expectancy 27
    SQLServer:Buffer Manager Page life expectancy 27

  5. Microsoft SQL Server 2008 Cores available: 32
    SQLServer:Buffer Node Page life expectancy 31927
    SQLServer:Buffer Node Page life expectancy 35196
    SQLServer:Buffer Manager Page life expectancy 33482

  6. Microsoft SQL Server 2008 Cores available: 32
    SQLServer:Buffer Node Page life expectancy 31927
    SQLServer:Buffer Node Page life expectancy 35196
    SQLServer:Buffer Manager Page life expectancy 33482

  7. (No column name) (No column name) (No column name)
    Microsoft SQL Server 2012 Cores available: 4
    SQLServer:Memory Node Target Node Memory (KB) 10239976
    SQLServer:Memory Node Total Node Memory (KB) 10091920
    SQLServer:Buffer Node Page life expectancy 6802
    SQLServer:Buffer Manager Page life expectancy 6802

  8. Microsoft SQL Server 2008 Cores available: 32
    SQLServer:Buffer Node Page life expectancy 97796
    SQLServer:Buffer Node Page life expectancy 538227
    SQLServer:Buffer Node Page life expectancy 97788
    SQLServer:Buffer Node Page life expectancy 193885
    SQLServer:Buffer Manager Page life expectancy 145629

    Microsoft SQL Server 2008 Cores available: 4
    Buffer Node Page life expectancy 80919
    Buffer Manager Page life expectancy 80919

    Microsoft SQL Server 2008 Cores available: 4
    Buffer Node Page life expectancy 34088
    Buffer Manager Page life expectancy 34088

    Microsoft SQL Server 2008 Cores available: 1
    SQLServer:Buffer Node Page life expectancy 18042
    SQLServer:Buffer Manager Page life expectancy 18042

  9. (No column name) (No column name) (No column name)
    Microsoft SQL Server 2005 Cores available: 24
    SQLServer:Buffer Node Page life expectancy 7807
    SQLServer:Buffer Node Page life expectancy 15344
    SQLServer:Buffer Manager Page life expectancy 10349

  10. (No column name) (No column name) (No column name)
    Microsoft SQL Server 2008 Cores available: 16
    SQLServer:Buffer Node Page life expectancy 763
    SQLServer:Buffer Manager Page life expectancy 763

    (No column name) (No column name) (No column name)
    Microsoft SQL Server 2008 Cores available: 32
    SQLServer:Buffer Node Page life expectancy 21720
    SQLServer:Buffer Node Page life expectancy 21692
    SQLServer:Buffer Node Page life expectancy 21737
    SQLServer:Buffer Node Page life expectancy 11475
    SQLServer:Buffer Manager Page life expectancy 17755

  11. Microsoft SQL Server 2014 Cores Available: 24
    SQLServer: Buffer Node Page Life Expectancy 214288
    SQLServer: Buffer Node Page Life Expectancy 214288
    SQLServer: Buffer Manager Page Life Expectancy 214288

  12. (No column name) (No column name) (No column name)
    Microsoft SQL Server 2008 Cores available: 32
    SQLServer:Buffer Node Page life expectancy 6652
    SQLServer:Buffer Node Page life expectancy 7759
    SQLServer:Buffer Manager Page life expectancy 7163

  13. Microsoft SQL Server 2012 Cores available: 32
    MSSQL$:Memory Node Target Node Memory (KB) 84208296
    MSSQL$:Memory Node Target Node Memory (KB) 84208296
    MSSQL$:Memory Node Total Node Memory (KB) 81929248
    MSSQL$:Memory Node Total Node Memory (KB) 81922192
    MSSQL$:Buffer Node Page life expectancy 23146
    MSSQL$:Buffer Node Page life expectancy 26173
    MSSQL$:Buffer Manager Page life expectancy 24567

  14. Microsoft SQL Server 2008 Cores available: 32
    SQLServer:Buffer Node Page life expectancy 1209571
    SQLServer:Buffer Node Page life expectancy 604567
    SQLServer:Buffer Node Page life expectancy 699158
    SQLServer:Buffer Node Page life expectancy 94355
    SQLServer:Buffer Manager Page life expectancy 275685

    Microsoft SQL Server 2014 Cores available: 1
    SQLServer:Memory Node Target Node Memory (KB) 3710208
    SQLServer:Memory Node Total Node Memory (KB) 3501536
    SQLServer:Buffer Node Page life expectancy 724
    SQLServer:Buffer Manager Page life expectancy 724

  15. Microsoft SQL Server 2012 Cores available: 80
    SQLServer:Memory Node Target Node Memory (KB) 93440000
    SQLServer:Memory Node Target Node Memory (KB) 93440000
    SQLServer:Memory Node Target Node Memory (KB) 93440000
    SQLServer:Memory Node Target Node Memory (KB) 93440000
    SQLServer:Memory Node Total Node Memory (KB) 93440920
    SQLServer:Memory Node Total Node Memory (KB) 93440000
    SQLServer:Memory Node Total Node Memory (KB) 93440000
    SQLServer:Memory Node Total Node Memory (KB) 93440000
    SQLServer:Buffer Node Page life expectancy 6395
    SQLServer:Buffer Node Page life expectancy 7135
    SQLServer:Buffer Node Page life expectancy 5336
    SQLServer:Buffer Node Page life expectancy 2588
    SQLServer:Buffer Manager Page life expectancy 4596

  16. Microsoft SQL Server 2012 Cores available: 32
    SQLServer:Memory Node Target Node Memory (KB) 117759992
    SQLServer:Memory Node Target Node Memory (KB) 117759992
    SQLServer:Memory Node Total Node Memory (KB) 117759592
    SQLServer:Memory Node Total Node Memory (KB) 117759704
    SQLServer:Buffer Node Page life expectancy 646672
    SQLServer:Buffer Node Page life expectancy 517673
    SQLServer:Buffer Manager Page life expectancy 575027

  17. (Отсутствует имя столбца) (Отсутствует имя столбца) (Отсутствует имя столбца)
    Microsoft SQL Server 2008 Cores available: 42
    SQLServer:Buffer Node Page life expectancy 955
    SQLServer:Buffer Node Page life expectancy 1759
    SQLServer:Buffer Node Page life expectancy 1431
    SQLServer:Buffer Node Page life expectancy 1758
    SQLServer:Buffer Node Page life expectancy 1490
    SQLServer:Buffer Node Page life expectancy 1559
    SQLServer:Buffer Node Page life expectancy 2058
    SQLServer:Buffer Node Page life expectancy 2226
    SQLServer:Buffer Manager Page life expectancy 1559

  18. Microsoft SQL Server 2008 Cores available: 4
    SQLServer:Buffer Node Page life expectancy 2031
    SQLServer:Buffer Manager Page life expectancy 2031

    Microsoft SQL Server 2005 Cores available: 15
    SQLServer:Buffer Node Page life expectancy 771
    SQLServer:Buffer Manager Page life expectancy 771

  19. Microsoft SQL Server 2008 Cores available: 8
    SQLServer:Buffer Node Page life expectancy 340
    SQLServer:Buffer Node Page life expectancy 219
    SQLServer:Buffer Manager Page life expectancy 266

  20. Microsoft SQL Server 2005 Cores available: 8
    SQLServer Buffer Node Page life expectancy: 23880
    SQLServer Buffer Manager Page life expectancy: 23880

  21. Microsoft SQL Server 2008 Cores available: 32
    SQLServer:Buffer Node Page life expectancy 1355
    SQLServer:Buffer Node Page life expectancy 1425
    SQLServer:Buffer Manager Page life expectancy 1389

  22. (No column name) (No column name) (No column name)
    Microsoft SQL Server 2012 Cores available: 16
    SQLServer:Memory Node Target Node Memory (KB) 65011704
    SQLServer:Memory Node Target Node Memory (KB) 65011704
    SQLServer:Memory Node Total Node Memory (KB) 65011704
    SQLServer:Memory Node Total Node Memory (KB) 65000232
    SQLServer:Buffer Node Page life expectancy 57006
    SQLServer:Buffer Node Page life expectancy 24697
    SQLServer:Buffer Manager Page life expectancy 34464

  23. Microsoft SQL Server 2012 Cores available: 12
    SQLServer:Memory Node Target Node Memory (KB) 60397544
    SQLServer:Memory Node Total Node Memory (KB) 60397544
    SQLServer:Buffer Node Page life expectancy 36136
    SQLServer:Buffer Manager Page life expectancy 36137

  24. Microsoft SQL Server 2012 Cores available: 32
    SQLServer:Memory Node Target Node Memory (KB) 2024
    SQLServer:Memory Node Target Node Memory (KB) 69224
    SQLServer:Memory Node Target Node Memory (KB) 33518800
    SQLServer:Memory Node Target Node Memory (KB) 33518800
    SQLServer:Memory Node Total Node Memory (KB) 2024
    SQLServer:Memory Node Total Node Memory (KB) 69224
    SQLServer:Memory Node Total Node Memory (KB) 33518744
    SQLServer:Memory Node Total Node Memory (KB) 33518632
    SQLServer:Buffer Node Page life expectancy 2550
    SQLServer:Buffer Node Page life expectancy 9907
    SQLServer:Buffer Manager Page life expectancy 4056

  25. Microsoft SQL Server 2014 Cores available: 8
    MSSQL$CW:Memory Node Target Node Memory (KB) 92159992
    MSSQL$CW:Memory Node Target Node Memory (KB) 92159992
    MSSQL$CW:Memory Node Total Node Memory (KB) 92157368
    MSSQL$CW:Memory Node Total Node Memory (KB) 92151768
    MSSQL$CW:Buffer Node Page life expectancy 13587
    MSSQL$CW:Buffer Node Page life expectancy 76547
    MSSQL$CW:Buffer Manager Page life expectancy 23078

  26. Microsoft SQL Server 2008 Cores available: 2
    SQLServer:Buffer Node Page life expectancy 6359
    SQLServer:Buffer Manager Page life expectancy 6359

  27. Microsoft SQL Server 2012 – Cores available: -24
    SQLServer:Memory Node – Target Node Memory (KB) – 49151992
    SQLServer:Memory Node – Target Node Memory (KB) – 49151992
    SQLServer:Memory Node – Total Node Memory (KB) – 49081504
    SQLServer:Memory Node – Total Node Memory (KB) – 40276336
    SQLServer:Buffer Node – Page life expectancy – 295850
    SQLServer:Buffer Node – Page life expectancy – 254887
    SQLServer:Buffer Manager – Page life expectancy – 273845

Comments are closed.

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.