第十章——维护索引（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，所以统计所有的页并乘以，就可以知道总的KB数。
          我们可以使用Used_Page_Count列，显示一个对象总共用了多少页，如果是堆或者聚集索引，就去除in_row_data_page_count、lob_used_page_count和row_overflow_used_page_count，否则只需要移除上面的最后两个即可。
          */