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”
(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
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
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.
Yup – only care about the major version – and 2008 and R2 are the same for this stuff.
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
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
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
(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
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
(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
(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
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
(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
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
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
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
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
(Отсутствует имя столбца) (Отсутствует имя столбца) (Отсутствует имя столбца)
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
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
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
Microsoft SQL Server 2005 Cores available: 8
SQLServer Buffer Node Page life expectancy: 23880
SQLServer Buffer Manager Page life expectancy: 23880
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
(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
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
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
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
Microsoft SQL Server 2008 Cores available: 2
SQLServer:Buffer Node Page life expectancy 6359
SQLServer:Buffer Manager Page life expectancy 6359
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.