Server 2005:比存储的数据更大的索引

Question

我创建了一个包含两个文件组的数据库:一个主数据库和一个索引。

• 主文件组包括1个数据文件(*.mdf)：存储所有表
• 索引文件组包括一个索引文件(*.ndf)：存储所有索引

大多数索引是非聚集索引。

经过短时间使用数据库，数据文件为2GB，而索引文件为12 GB。我不知道我的数据库出了什么问题。

我有一些问题：

1. 如何缩小索引文件的大小？
2. 如何知道索引文件中存储了什么？
3. 如何将所有影响跟踪到索引文件？
4. 如何限制索引文件的大小增长？

Answer 1

如何缩小索引文件的大小?

删除一些不需要的索引或减少现有索引中的列数。请记住，聚集索引列是所有非聚集索引中的“隐藏”包含列。

如果a,b,c,d上有索引，a,b,c上有索引，那么可以考虑删除第二个索引，因为第一个索引包含第二个索引。

您也可以通过查看查找潜在未使用的索引来实现sys.dm_db_index_usage_stats。

如何知道索引文件中存储了什么？

它会存储任何你定义它存储的东西！下面的查询将帮助您判断哪些索引使用的空间最大，以及出于什么原因(行数据、lob数据)

SELECT  convert(char(8),object_name(i.object_id)) AS table_name, i.name AS index_name, 
    i.index_id, i.type_desc as index_type,
    partition_id, partition_number AS pnum,  rows, 
    allocation_unit_id AS au_id, a.type_desc as page_type_desc, total_pages AS pages
FROM sys.indexes i JOIN sys.partitions p  
      ON i.object_id = p.object_id AND i.index_id = p.index_id
    JOIN sys.allocation_units a
      ON p.partition_id = a.container_id
      order by pages desc

Answer 2

我的猜测(我认为这也是marc_s的目标)是，您已经为至少一些表声明了聚集索引，以便在索引文件组中。聚集索引确定表的实际数据存储方式(以及存储位置)。

但是，发布一些代码肯定会帮助其他人找出问题所在。

我认为马丁史密斯对你的其他问题回答得很好。我再加上这个..。如果要限制索引大小，则需要计算索引。不要仅仅因为您认为可能需要索引而添加索引。在数据库上进行实际(或者理想情况下是真实世界)负载的测试，看看哪些索引实际上将为您提供所需的性能提升。索引对它们是有代价的。除了您正在看到的空间开销之外，它们还增加了插入和更新的开销，这必须使索引保持同步。因为这些成本，你应该总是有一个很好的理由添加一个指数，你应该有意识地考虑权衡。

Answer 3

考虑到索引所需的总存储量实际上要大于给定数据库中表数据所需的存储量，这是非常常见的。

然而，您的特定场景似乎有点过了。正如其他人所指出的那样，如果为给定表分配了聚集索引，使其驻留在单独的数据文件(索引数据文件)中，那么整个物理表本身也将驻留在该文件中，因为从某种意义上说，聚集索引就是表。

提供您的表模式和索引结构的详细信息将使我们能够为您提供更具体的指导。

其他海报提到：

• 应检查重复索引的索引定义。看一看识别重叠指标的布伦特·奥扎尔。
• 您应该找出未使用的索引。看看Server的文章：查找未使用的索引

其他探索的途径包括查看索引的碎片，因为这会增加存储需求。

严重的分散，特别是在包含LOB数据的表的聚集索引中，可能导致存储需求的显著增加。重新组织包含LOB数据的表的聚集索引将压缩LOB数据。

参见重新组织和重建索引