Run below script to get pagelifeexpectancy, memoryusedpct, physpagewrites , physpagereads from Spotlight Statistic Repository.
The first line should be replaced with your own ssr database.
============
use ssr;
select timecollected, memoryusedpct from
(
select
a.timecollected,
max(case when sn.statistic_name = 'memoryusedpct' then cast(a.raw_value as nvarchar(128)) end) as 'memoryusedpct'
from
[dbo].[spotlight_perfdata] a
inner join [dbo].[spotlight_stat_names] sn on a.statistic_name_id = sn.statistic_name_id
and sn.statistic_class_id = (select statistic_class_id from dbo.spotlight_stat_classes where statistic_class_name = 'SQLMemoryManager')
group by
a.timecollected, a.statistic_key_id) t
order by
t.timecollected desc;
select timecollected, pagelifeexpectancy, physpagewrites, physpagereads from
(
select
a.timecollected,
max(case when sn.statistic_name = 'pagelifeexpectancy' then cast(a.raw_value as nvarchar(128)) end) as 'pagelifeexpectancy',
avg(case when sn.statistic_name = 'physpagewrites' then cast(a.raw_value as float) end) as 'physpagewrites',
avg(case when sn.statistic_name = 'physpagereads' then cast(a.raw_value as float) end) as 'physpagereads'
from
[dbo].[spotlight_perfdata] a
inner join [dbo].[spotlight_stat_names] sn on a.statistic_name_id = sn.statistic_name_id
and sn.statistic_class_id = (select statistic_class_id from dbo.spotlight_stat_classes where statistic_class_name = 'SQLBufferManager')
group by
a.timecollected, a.statistic_key_id) t
order by
t.timecollected desc;
