服务器 Gind.cn
  1. 服务器 Gind.cn
  3. SQL数据库物理文件碎片
Intereting Posts
Rsync不断要求密码，即使configuration了密码文件，可能是什么原因？ 哪个英特尔CPU更快？ SharePoint（2010） – 无法删除服务应用程序 如何从Azure存储资源pipe理器中删除旧会话？ 我有什么select链接目录 使用PowerShell和WMI备份IIS6 configurationiptables允许来自WAN（networking）的入站/出站绑定连接，并且只允许局域网上的几台PC同时阻塞其他任何东西 htaccess filesMatch排除 远程pipe理客户的Windows服务器，无需pipe理员访问 我们公司的防火墙是否阻止HTTP PROPFIND？ ApacheconfigurationFollowSymlinks-适用于PHP脚本？ 寻找一个开源的邮件归档应用程序 ASP.NET临时文件占用太多空间 针对HTTPS / SSL的nginx反向代理：如何传递证书？ Colocate Exchange Server

SQL数据库物理文件碎片

我知道，作为一名DBA，我真的需要担心三种分裂：

  1. 索引 SQL数据文件中的碎片，包括聚簇索引（表）碎片。 使用DBCC SHOWCONTIG（在SQL 2000中）或sys.dm_ db_ index_ physical_ stats（在2005+中）来识别此问题。

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

  3. 硬盘上的数据库文件的物理文件碎片。 通过使用Windows中的“磁盘碎片整理程序”实用程序进行诊断。 （由这个优秀的博客文章启发）

很多关注索引碎片（请参阅Paul Randall的优秀Serverfault答案 ），所以这不是我的问题的焦点。

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

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

  • SQL性能上的物理碎片有多大？ （让我们假设一个内部驱动器arrays，等待前面的问题的结果。）这是一个比内部索引碎片更大的交易？ 或者是真的是一个类似的问题（驱动器必须做随机读取，而不是顺序读取）

  • 碎片整理（或重build）索引浪费时间，如果驱动器物理碎片？ 在我解决其他问题之前，我需要修复吗？

  • 在生产SQL框中修复物理文件碎片的最好方法是什么？ 我知道我可以closuresSQL服务并运行Windows Defrag，但是我也听说过一种技术，在这种技术中，您可以执行完整备份，删除数据库，然后从备份恢复到空驱动器。 这是后者的技术推荐？ 从这样的备份还原还从头开始build立索引，消除内部索引碎片？ 还是只是将页面顺序返回到与备份时相同？ （如果有问题的话，我们使用Quest Lightspeed压缩备份。）

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

任何人都在考虑权衡实际进行碎片整理的最佳方法吗？ 或者估计你想要花费的时间来碎片化一个大型的驱动器，比如500GB左右？ 相关的，显然，因为那是我的SQL服务器将closures的时间！

另外，如果有人对SQL性能改进有任何轶事的信息，那么通过修复物理碎片就可以提高性能，那也是很好的。 迈克的博客文章谈到揭露这个问题，但没有具体说明它做了什么样的改进。

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

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

基本要点是，不build议在SAN存储上进行碎片整理，因为当SAN提供LUN时，SAN的位置已被虚拟化时，难以关联磁盘上块的物理位置。

如果您使用的是RAW设备映射，或者您可以直接访问正在使用的LUN的RAID集，则可以看到消除碎片效应，但如果从共享的RAIDarrays中获得“虚拟”LUN， 5套，没有。

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

正如Kevin已经指出的那样，物理文件碎片与Enterprise SAN存储没有任何关系，因此没有什么可以添加的。 这确实是归结于I / O子系统，在执行扫描时执行扫描到更顺序的I / O时，您有多大能力使驱动器从更多的随机I / O转移。 对于DAS来说，更可能的是，对于一个复杂的slice-n-dice SAN，可能不会。

文件系统级碎片整理 – 只有在SQLclosures时才能执行。 我从来没有遇到过自己的问题（因为我从来没有执行SQL数据库文件的在线，开放式碎片整理），但是我听到很多来自客户和客户的奇怪的腐败问题的轶事证据。 一般的看法是不要在线上使用SQL。

索引碎片与文件碎片完全正交。 SQL Server不知道文件碎片 – 虚拟化层之间有太多的虚拟化层，因此它有任何希望制定出实际的I / O子系统几何结构的希望。 索引碎片，然而，SQL确实知道一切。 不用重复自己已经提到的答案，索引碎片将阻止SQL执行有效的范围扫描预读，而不pipe文件在文件系统级别是多么分散（或不是）。 所以 – 如果你看到降级的查询性能，你绝对应该减轻索引碎片。

您不必以任何特定顺序执行这些操作，但是如果您处理文件系统碎片，然后重新构build所有索引，并通过在碎片整理卷上生成多个文件来导致更多的文件系统碎片，那么您可能会被勾掉。 它会导致任何性能问题呢？ 如上所述，它取决于： – D.

希望这可以帮助！

在生产SQL框中修复物理文件碎片的最好方法是什么？

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

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

我会build议适当调整数据库，closuresSQL服务器，将数据库文件复制到另一个磁盘arrays，然后将其复制到碎片整理。 根据我的经验，使用windows碎片整理软件要快得多。

我试图在一个scsi解决scheme中对物理磁盘进行碎片整理，但几乎没有性能提升。 我学到的教训是，如果你经历了由于磁盘系统而导致的性能下降，那么就我们所说的数据文件而言，它与分片没有任何关系，因为它使用的是随机访问。

如果您的索引经过了碎片整理并且统计信息已更新（非常重要），但您仍然将I / O视为瓶颈，那么除了物理碎片以外，您还会遇到其他问题。 你使用了超过80％的驱动器？ 你有足够的驱动器？ 你的查询是否足够优化？ 你是否做了很多的表扫描，甚至更糟糕的索引search，然后聚集索引查找？ 查看查询计划并使用“set statistics io on”来查找查询的真实情况。 （寻找大量的逻辑或物理读取）

请让我知道，如果我完全错了。

/HåkanWinther

也许这些索引对于您的应用程序来说还不够优化，而您没有Veritas I3来优化您的数据库，那么您可以使用像这样的语句来查找缺less的索引： 

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语句，但是这两个是我认为的一个很好的开始。

（我知道，这篇文章是一个小题目，但是我认为你可能会感兴趣，因为它与索引策略有关）

/HåkanWinther