如何确定Server索引统计信息上次更新的时间

Question

我想要检查哪些索引统计数据还没有更新到页面数为500 mb或更多的表中。我有这样的剧本：

SELECT OBJECT_NAME(object_id) AS [ObjectName]
      ,[name] AS [StatisticName]
      ,STATS_DATE([object_id], [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats
order by StatisticUpdateDate desc

它没有给我所有我想要的信息。有人能修改我的脚本或者教我如何达到我想要的位置吗？

提前谢谢。

Answer 1

以下是500页的解决方案，即使我认为500 if更合理，finter：

with cte as
(
select p.object_id,
       object_name(p.object_id) as obj,
       sum(total_pages) as tot_pages
from sys.partitions p join sys.allocation_units au 
        on au.container_id = p.hobt_id
group by p.object_id
)

SELECT c.* ,
       OBJECT_NAME(st.object_id) AS [ObjectName]
      ,[name] AS [StatisticName]
      ,STATS_DATE(st.[object_id], 
      [stats_id]) AS [StatisticUpdateDate]
FROM sys.stats st join cte c
        on st.object_id = c.object_id
where tot_pages >= 500;