为SQLSERVER打好地基－硬盘碎片和索引碎片

View Post

数据文件的碎片
    影响磁盘读取性能的两个主要因素：录道时间和轮询延迟。
    我们在查询数据时，有两种磁盘的读取方式：顺序读和随机读。随机读发生在对表或索引的扫描时，顺序读发生在使用索引查找数据时。当数据文件有大量碎片时，随机读不会受到太大的影响，因为SQLSERVER会根据表所占用到的数据页面，不管记录的逻辑顺序随机的读取出来，所谓的预读正是这种方式。而顺序读时，因为要按记录的逻辑顺序读取相应的记录，如果逻辑上相邻的数据页在物理分布上不连续，则会因为磁头的来回移动使性能大打折扣。这也就是为什么有时我们看到表扫描比索引查找效率更高的原因。
    我们在创建数据库时，会为数据文件和日志文件分别指定一个初始大小和增量大小。如果这些文件都在独自的逻辑分区中，那么不会有磁盘碎片的产生。但是，如果每个文件所在的分区中还有其它的数据库文件。则因为这些文件的自增长就会产生磁盘碎片了，如下图所示：

    为了防止这些碎片的产生，我们应该每次把文件自增长的大小设置的更大些，以防止产生这么多小的碎片。但是，如果每次文件增长的过大，特别是在系统繁忙的时候，势必会影响数据库的性能。为了能快速的完全文件增长的工作，SQLSERVER借助WINDOWS的即时文件初始化功能来快速的完成此项任务。若要使用即时文件初始化，必须在 Windows 帐户下运行 MSSQLSERVER 服务帐户并为该 Windows 帐户分配 Windows SE_MANAGE_VOLUME_NAME 特权。此权限默认情况下分配给 Windows 管理员组。如果拥有系统管理员权限，您可以通过将 Windows 帐户添加到“执行卷维护任务”安全策略来分配此权限。默认MSSQLSERVER是在LocalSystem帐号启动的，但此帐号的SE_MANAGE_VOLUME_NAME 特权是被禁用的。详见http://msdn.microsoft.com/en-us/library/ms684190(VS.85).aspx
结论：定期执行磁盘碎片整理并为数据文件分配合适的初始大小。并制定任务计划，在系统空闲时根据现在数据的实际大小调整数据文件的大小，减少对系统繁忙时因为文件增长带来的开销。
日志文件的碎片
不同于数据文件，日志文件不能使用即时文件初始化进行自增长。因此，在分配一个很大自增长量时就会很耗时。在这个操作期间，所有的inset、delete、update操作都会被阻塞。那么随后一断时间数据库的整体性能也会受到很大的影响。就像高速公路突然塞车被疏导之后一样。在系统内部，会把这些日志文件分成好多个虚拟的日志文件(VLF)，你可以使用DBCC LOGINFO来查看你当前的日志文件中有多少个VLF。如果返回的结果数很多，证明你应该对日志进行维护了。这就和数据文件的磁盘碎片一样，会对性能造成严重影响。这个数量是由日志文件的整体大小和扩张日志使用的增量在内部决定的，我们无法控制。
但是，因为日志是顺序写入的，真正的磁盘碎片对性能影响其实不是很大。如果你的增量设置过小，会因为频繁的调整日志文件而影响到VLF。如果你设置的增量过大，又会占用过长的文件分配时间。因此，最好的办法就是你控制你的事务尽可能的短。同时，定期的备份你的日志，以使日志可以截断。从而防止日志文件进行自增长而带来的性能开销。一直以来有种误解就是认为完整恢复模式的数据库不会自动截断事务日志。如果你从来没有对这个数据库做过完整备份，其实它也是可以对事务日志自动截断的。
结论：VLF越少越好，建议的数值是不超过5个。定时对事务日志进行备份，以最快截断以供后续使用。
索引的内部和外部碎片
这些碎片都是逻辑上的碎片。整天都在讨论索引碎片，相信这个大家应该都很清楚了。不再多罗嗦，概括如下：内部碎片受页面填充度的影响，如果碎片过多使表所占的实际页面数比无碎片时多出很多。因此在表扫描时会发生更多的I/O操作，但是索引查找时不会受到很大影响。外部碎片是因为页面的逻辑顺序和硬盘上的物理顺序不一致或是分区的不连续所造成的。这时，如果使用索引进行范围查找的话，因为要按照记录的逻辑顺序进行记取，会引起磁头来回移动。关于索引碎片的维护，请参见联机文档。
文件的目录存储及文件名要求
在目录中新建、访问、删除文件时，都会在目录的元数据中进行相应的搜索或执行Chkdsk.exe命令完成相应的任务。因此，如果文件过多或是目录层次太多，会花费更长的时间完成。建议文件数目不超过100,000，当然我们很多时候永远达不到这个数目。同时，Windwos NT之后的版本，为了提供向后兼容性，在你对目录中的任何文件修改之后，不符合8.3文件格式的长文件名都会生成一个8.3格式文件名。如果你的目录中有上百个长文件名的文件，这会带来一定的性能损失。因此，如果机器上没有运行16位的程序，可通过注册表把NtfsDisable8dot3NameCreation设置为1，禁止生成8.3文件名。注册表位置如下：HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\FileSystem\NtfsDisable8dot3NameCreation。那么日志文件和数据文件是在什么时候才会被修改呢？如果你不怕葬你的硬盘，运行每个脚本之前创建一个新的Northwind数据库。你可以运行一下下面的脚本，此例也正好演示一下insert into和select into的效率问题。
USE Northwind;
GO
select * into my_customers
from dbo.Customers where 1=0
GO
insert into my_customers
select c1.*
from dbo.Customers c1,dbo.Customers c2,dbo.Customers c3
--观察运行前后的数据文件和日志文件的增长
--insert into被完整记录于日志中，我们发现
--日志文件增长了很大，我的长到了500M多
--在新建Northwind数据库后，运行下面的脚本
--select into作为一个大批量操作，只记录了部分事务
--因此日志增长不是很大，我的长到了4M
--因此从性能上来说select into效率高于insert into
select c1.*
into my_customers
from dbo.Customers c1,dbo.Customers c2,dbo.Customers c3
硬盘格式化的簇大小设置
     客户给我们一台新的服务器，我们可以最大调整的就是硬盘。CPU、内存就摆在那了，客户说没有更好的机器了。同时，硬盘的I/O效率也是影响查询性能的关键因素。SQL2005对tempdb的要求越来越高，如果条件允许，一般把tempdb、数据文件、索引文件、全文目录都分别存放在独立的RAID5阵列中（有时MSFTESQL服务会因为磁盘I/O过高而暂停服务），日志文件则存放在RAID1+0或RAID1中，操作系统和SQLSERVER存放于RAID1中。硬盘的扇区大小默认是512个字节，那么我们在对新的硬盘进行格式化时，选择的簇的大小多少才是最合适的？阵列的条带容量大小应该设置为多少？
    因为一个数据页面是8K，数据页面在内部由扩展分区进行管理。一个扩展分区包含了8个逻辑连续的页面。分区的管理是通过全局分配映射页面（GAM，只保存超过8个页面的表，统一分区）和共享全局分配映射页面（SGAM，保存小于8个数据页面的表，混合分区）来进行管理的，一个数据文件的第2个页面是GAM，第3个页面是SGAM。每个GAM和SGAM能管理的页面范围是4G，每4G都会增加一个GAM和SGAM。在你创建一个新的数据库是，使用DBCC PAGE命令来观察这两个页面，可以看到数据库已经分配了很多扩展分区，还保留了一些分区。在创建表时，新加记录后，如果表总共占用不到8个数据页面的话会被分配到SGAM中，超过8个页面时才会被分配到GAM分区中。前面我们提到过索引的外部碎片是因为页面的逻辑顺序和硬盘上的物理顺序不一致或是分区的不连续所造成的。因此，如果我们把簇的大小设置为64K时，正好和一个分区大小一样，那么这个分区一旦被某个表所使用后，就不能被另外的表所使用了。从而减少了数据页面的外部碎片，但是分区的不连续还是不能避免。那么把簇大小设为128K呢？因为读取数据时，磁盘是按簇的大小进行读取的。设置簇过大，会一次读取出很多无用的内容。即便你只读取一条记录，SQLSERVER还是会把记录所在的整个页读取出来。这时，实际的磁盘是读取出了64K。但是因为簇是连续的扇区，因此多读取的这一部分，对性能的影响基本是可以忽略的。因为磁盘主要受寻道和轮询延迟影响。
    对于RAID中的条带容量设置，内部的工作机制我现在还不是很清楚。只是通过下面的文档得出的结论256K。但是网上很多介绍的都是说作为数据库应用时应该小于簇的大小，这和下面微软的文档描述不一致。更多内容参见：http://www.microsoft.com/whdc/archive/subsys_perf.mspx
为你的硬盘启动写入缓存
在没有专门缓存控制器时，这会提高磁盘的I/O效率，但是会增加数据丢失的风险。但是并不会造成数据的不一致。我们来看一下事务操作的过程，它采用预写事务日志(WAL)的方式来保证ACID。如图所示：

事务提交后，修改先反应到事务日志中，这时可能会还存在于磁盘缓存中。如果这时突然断电，检查点操作还没有来得急把提交的事务写入数据文件。重启服务后日志文件中的并没有真正包含所提交的事务，redo操作失败了，你提交的事务丢失了。但是如果事务日志从缓存中写入了磁盘后断电，是不会丢失数据的。如果是日志文件保存在缓存中，而数据文件已从缓存中写入了磁盘。这时数据不会丢失，只是日志中看不到你提交的事务记录了。因为写入磁盘时是以8K写入的，也就是16个扇区的操作。如果只完成了部分扇区的写入后，断电了。这时我们就会收到824错误了，因为页面的校验和发生错误致使无法读取出此页了。数据库校验和设置在page_verity选项中，有三个选项可以设置：checksum、torn_page_detection、none。开销依次减少，安全性依次减弱。每次发生校验和错误时，都会在msdb.dbo.suspect_pages中得到一条记录。如果出现这样的错误，而你没有备份，你只能冒着丢失数据的风险执行DBCC命令来忽略掉这一页了。
以上各人见解，如有异议请指正！