第十章——维护索引（9）——监控索引消耗的空间

前言：

性能优化是DBA的工作之一，但是同时，DBA还需要关心SQLServer实例、数据库消耗的空间、提供数据库用户高可用性、管理数据库备份策略等等。

磁盘空间的管理是一件非常重要的事情，DBA需要关注磁盘空间和未使用索引的空间消耗情况，管理数据文件和日志文件的大小等等。在其他章节和本章都有提到，索引会消耗磁盘空间。所以，是时候去管理这部分的内容。

 

准备工作：

在开始之前，先要确保：

1、 用户具有VIEW DATABASE STATE权限。

2、 了解sys.dm_db_partition_stats动态管理视图。

 

步骤：

1、 运行下面语句：

--显示索引消耗的磁盘情况
SELECT  CASE InnerTable.index_id
          WHEN 0 THEN 'HEAP'
          WHEN 1 THEN 'Clustered Index'
          ELSE 'Non-Clustered Index'
        END AS Index_Type ,
        SUM(CASE WHEN FilledPage > PageToDeduct
                 THEN ( FilledPage - PageToDeduct )
                 ELSE 0
            END) * 8 Index_Size
FROM    ( SELECT    partition_id ,
                    index_id ,
                    SUM(used_page_count) AS FilledPage ,
                    SUM(CASE WHEN ( index_id < 2 )
                             THEN ( in_row_data_page_count
                                    + lob_used_page_count
                                    + row_overflow_used_page_count )
                             ELSE lob_used_page_count
                                  + row_overflow_used_page_count
                        END) AS PageToDeduct
          FROM      sys.dm_db_partition_stats
          GROUP BY  partition_id ,
                    index_id
        ) AS InnerTable
GROUP BY CASE InnerTable.index_id
           WHEN 0 THEN 'HEAP'
           WHEN 1 THEN 'Clustered Index'
           ELSE 'Non-Clustered Index'
         END 
GO

分析： 

          Sys.dm_db_partition_stats提供了有用的信息给DBA，例如每个表的每个分区的总行数、已经使用的页、预留给LOB、in-row、overflow的页，每个页占用8K，所以统计所有的页并乘以8，就可以知道总的KB数。

          我们可以使用Used_Page_Count列，显示一个对象总共用了多少页，如果是堆或者聚集索引，就去除in_row_data_page_count、lob_used_page_count和row_overflow_used_page_count，否则只需要移除上面的最后两个即可。