主页 / server / 问题 / 31011
Accepted
BradC
Asked: 2009-06-25 06:29:58 +0800 CST

SQL 数据库物理文件碎片

  • 772

我知道作为 DBA，我确实需要关注三种碎片：

  1. SQL 数据文件中的索引碎片，包括聚集索引（表）碎片。使用 DBCC SHOWCONTIG（在 SQL 2000 中）或 sys.dm_db_index_physical_stats（在 2005+ 中）识别这一点。

  2. SQL 日志文件中的VLF 碎片。运行 DBCC LOGINFO 以查看每个 SQL 日志文件中有多少 VLF。

  3. 硬盘驱动器上数据库文件的物理文件碎片。使用 Windows 中的“磁盘碎片整理程序”实用程序对此进行诊断。（受这篇优秀博文的启发）

很多注意力都放在了索引碎片上（请参阅Paul Randall 提供的这个出色的 Serverfault 答案），所以这不是我的问题的重点。

我知道在最初创建数据库时，我可以通过规划合理的预期数据文件和日志大小来防止物理碎片（和 VLF 碎片），因为这种碎片最常发生在频繁的增长和缩小中，但我有一些关于如何修复的问题一旦识别出物理碎片：

  • 首先，物理碎片是否与企业 SAN 相关？我可以/应该在 SAN 驱动器上使用 Windows Defragmenter，还是 SAN 团队应该使用内部碎片整理实用程序？在 SAN 驱动器上运行时，我从 Windows 工具获得的碎片分析是否准确？

  • 物理碎片对 SQL 性能的影响有多大？（让我们假设一个内部驱动器阵列，等待上一个问题的结果。）它比内部索引碎片更重要吗？还是真的是同一种问题（驱动器必须进行随机读取而不是顺序读取）

  • 如果驱动器是物理碎片的，那么碎片整理（或重建）索引是否浪费时间？我需要先解决一个问题，然后再解决另一个问题吗？

  • 在生产 SQL 机器上修复物理文件碎片的最佳方法是什么？我知道我可以关闭 SQL 服务并运行 Windows Defrag，但我也听说过一种技术，您可以进行完整备份，删除数据库，然后从备份恢复到空驱动器。推荐后一种技术吗？从这样的备份中恢复是否也会从头开始构建索引，从而消除内部索引碎片？或者它只是将页面顺序返回到与进行备份时相同的顺序？（如果重要的话，我们正在使用带有压缩功能的 Quest Lightspeed 备份。）

更新：到目前为止，关于是否对 SAN 驱动器进行碎片整理（否）以及索引碎片整理是否仍然值得在物理碎片驱动器上进行（是）的良好答案。

还有其他人关心实际进行碎片整理的最佳方法吗？或者估计您预计对大型碎片驱动器进行碎片整理所需的时间长度，例如 500GB 左右？很明显，这是相关的，因为那是我的 SQL 服务器将关闭的时间！

此外，如果有人有任何关于您通过修复物理碎片来改进 SQL 性能的轶事信息，那也很好。Mike 的博客文章谈到了发现问题，但没有具体说明它做了什么样的改进。

sql-server fragmentation

6 个回答

  1. Best Answer

    我认为这篇文章很好地概述了 SAN 驱动器的碎片整理

    http://www.las-solanas.com/storage_virtualization/san_volume_defragmentation.php

    基本点是不建议在 SAN 存储上进行碎片整理，因为在呈现 LUN 时，当 SAN 虚拟化了磁盘上块的物理位置时，很难关联磁盘上的物理位置。

    如果您使用的是 RAW 设备映射，或者您可以直接访问作为您正在使用的 LUN 的 RAID 集，我可以看到碎片整理具有积极的效果，但如果您从共享 RAID 中获得“虚拟”LUN- 5套，没有。

    • 9

  2. 这个问题和答案的多个部分：

    正如 Kevin 已经指出的那样，物理文件碎片与企业 SAN 存储并不真正相关——因此无需添加任何内容。它确实归结为 I/O 子系统，以及您在执行扫描时能够使驱动器从更多随机 I/O 变为更多顺序 I/O 的可能性有多大。对于 DAS，您更有可能会，对于复杂的 slice-n-dice SAN，可能不会。

    文件系统级碎片整理——仅在 SQL 关闭的情况下进行。我自己在这里从来没有遇到过问题（因为我从来没有对 SQL 数据库文件进行过在线、打开的文件碎片整理），但我从客户和客户那里听到了大量关于发生奇怪损坏问题的轶事证据。一般的智慧是不要在网上用 SQL 来做。

    索引碎片与文件碎片完全正交。SQL Server 不知道文件碎片——中间有太多的虚拟化层，以至于它无法计算出实际的 I/O 子系统几何结构。然而，索引碎片，SQL 确实无所不知。无需从您已经引用的答案中过多地重复自己，索引碎片将阻止 SQL 执行有效的范围扫描预读，无论文件在文件系统级别有多碎片（或不碎片）。所以 - 如果您看到查询性能下降，您绝对应该减轻索引碎片。

    您不必按任何特定顺序执行这些操作，但如果您处理文件系统碎片，然后重建所有索引并通过在碎片整理卷上增加多个文件导致更多文件系统碎片，您可能会被打勾。它会导致任何性能问题吗？如上所述，这取决于:-D

    希望这可以帮助！

    • 8

  3. 在生产 SQL 机器上修复物理文件碎片的最佳方法是什么？

    我在我的数据库文件上运行 SYSINTERNALS 的 contig。

    请参阅http://technet.microsoft.com/en-us/sysinternals/bb897428.aspx

    • 3

  4. 我建议适当调整数据库的大小，关闭 sql server，将数据库文件复制到另一个磁盘阵列，然后将其复制回来进行碎片整理。根据我的经验，比使用 Windows 碎片整理快得多。

    • 2

  5. 我曾尝试在 scsi 解决方案中对物理磁盘进行碎片整理，但几乎没有或根本没有提高性能。我学到的教训是，如果由于磁盘系统而导致性能下降，就我们谈论数据文件而言，它与碎片没有任何关系，因为它使用的是随机访问。

    如果您的索引已整理碎片并更新了统计信息（非常重要）并且您仍然将 I/O 视为瓶颈，那么您会遭受除物理碎片之外的其他事情。您是否使用了超过 80% 的驱动器？你有足够的驱动器吗？您的查询是否足够优化？您是否在进行大量表扫描，或者更糟糕的是大量索引搜索，然后是聚集索引查找？查看查询计划并使用“set statistics io on”来了解您的查询到底发生了什么。（寻找大量的逻辑或物理读取）

    如果我完全错了，请告诉我。

    /哈坎·温瑟

    • 1

  6. 也许索引没有针对您的应用程序进行足够优化，并且您没有 Veritas I3 来优化您的数据库，那么您可以使用这样的语句来查找缺失的索引：

           SELECT
      mid.statement,
      mid.equality_columns,
      mid.inequality_columns,
      mid.included_columns,
      migs.user_seeks,
      migs.user_scans,
      migs.last_user_seek,
      migs.avg_user_impact,
      user_scans,
      avg_total_user_cost,
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) AS [weight]--, migs.*--, mid.*
   FROM
      sys.dm_db_missing_index_group_stats AS migs
      INNER JOIN sys.dm_db_missing_index_groups AS mig
         ON (migs.group_handle = mig.index_group_handle)
      INNER JOIN sys.dm_db_missing_index_details AS mid
         ON (mig.index_handle = mid.index_handle)
   ORDER BY
      avg_total_user_cost * avg_user_impact * (user_seeks + user_scans) DESC ;

    或者像这样的语句来查找未在 select 语句中使用并降低更新/插入性能的索引：

        CREATE PROCEDURE [ADMIN].[spIndexCostBenefit]
    @dbname [nvarchar](75)
WITH EXECUTE AS CALLER
AS
--set @dbname='Chess'
declare @dbid nvarchar(5)
declare @sql nvarchar(2000)
select @dbid = convert(nvarchar(5),db_id(@dbname))

set @sql=N'select ''object'' = t.name,i.name
        ,''user reads'' = iu.user_seeks + iu.user_scans + iu.user_lookups
        ,''system reads'' = iu.system_seeks + iu.system_scans + iu.system_lookups
        ,''user writes'' = iu.user_updates
        ,''system writes'' = iu.system_updates
from '+ @dbname + '.sys.dm_db_index_usage_stats iu
,' + @dbname + '.sys.indexes i
,' + @dbname + '.sys.tables t
where 
    iu.database_id = ' + @dbid + '
and iu.index_id=i.index_id
and iu.object_id=i.object_id
and iu.object_id=t.object_id
AND (iu.user_seeks + iu.user_scans + iu.user_lookups)<iu.user_updates
order by ''user reads'' desc'

exec sp_executesql @sql

set @sql=N'SELECT
   ''object'' = t.name,
   o.index_id,
   ''usage_reads'' = user_seeks + user_scans + user_lookups,
   ''operational_reads'' = range_scan_count + singleton_lookup_count,
   range_scan_count,
   singleton_lookup_count,
   ''usage writes'' = user_updates,
   ''operational_leaf_writes'' = leaf_insert_count + leaf_update_count + leaf_delete_count,
   leaf_insert_count,
   leaf_update_count,
   leaf_delete_count,
   ''operational_leaf_page_splits'' = leaf_allocation_count,
   ''operational_nonleaf_writes'' = nonleaf_insert_count + nonleaf_update_count + nonleaf_delete_count,
   ''operational_nonleaf_page_splits'' = nonleaf_allocation_count
FROM
   ' + @dbname + '.sys.dm_db_index_operational_stats(' + @dbid + ', NULL, NULL, NULL) o,
   ' + @dbname + '.sys.dm_db_index_usage_stats u,
    ' + @dbname + '.sys.tables t
WHERE
   u.object_id = o.object_id
   AND u.index_id = o.index_id
    and u.object_id=t.object_id
ORDER BY
   operational_reads DESC,
   operational_leaf_writes,
   operational_nonleaf_writes'

exec sp_executesql @sql

GO

    在分析生产环境中的性能问题时，我还使用了一些其他 SQL 语句，但我认为这两个是一个好的开始。

    （我知道，这篇文章有点话题，但我想你可能会感兴趣，因为它与索引策略有关）

    /哈坎·温瑟

    • 1

相关问题