After upgrading the Spotlight Diagnostic Server from v9.2 to 10.0, in the Repository Database there is now a DELETE job that has automatically started, with the statement :
DELETE FROM dbo.spotlight_stat_names WHERE statistic_class_id = @P0 and datasource_id = @P1.
This job takes a long time to run.
Is this Delete job required?
Is it, as part of the upgrade, automatically started?
Do you lose any important information from stat_names?
Why does this job make page life expentancy worse?
This delete is done as part of the Spotlight Statistics Repository cleanup which deletes data for any collections that were in a previous version but are no longer present in the current version. This statement is causing high I/O because it does a scan of the spotlight_perfdata table. This could be fixed by adding an index on statistic_class_id to that table but this would add unnecessary overhead and space to the table given that this type of deletion is only done once after an upgrade.
Page life expectancy shows how long on average a page is expected to stay in the buffer cache.
Buffer cache hit rate shows how what percentage of logical page reads are obtained from the buffer cache.
Low page life expectancy means that lots of pages are being removed from the cache and being replaced by pages read from disk. That would seem to imply that the buffer cache hit rate should also be low because the logical reads are not finding pages in the cache and thus causing physical reads. But in the screen shot that is not the case. This is caused by SQL Server doing read aheads. A read ahead is done by SQL Server in anticipation that the pages will be required in the future. So when SQL Server reads some pages from disk it might also do a read ahead to read more pages from the same table or index for possible use later on. This explains the high buffer hit rate; the read ahead mechanism is making sure the pages are in the buffer cache. However page turnover is high which explains why the Page life expectancy is low.
So although there is an alarm that page life expectancy is low, the low page life expectancy is not having a negative effect on the throughput of the system because read ahead is ensuring that pages are in the buffer cache when required.
