第十四周学习笔记
第十四周学习笔记
本周主要概括索引碎片、计划缓存、统计信息的内容
一、索引碎片
（一）碎片的定义
碎片可以定义为：任何情况下，在访问一个表时，造成比适量更多的磁盘IO操作或更长的磁盘IO操作。SELECT查询的最佳操作发生在，表的数据页是尽可能连续的，而且页是尽可能完整打包（fully packed）的。碎片会破坏这个规则，降低查询的性能。碎片可以发生在2个级别。一个是文件系统级，称为逻辑/物理磁盘碎片，还有一个是索引级碎片。
（二）逻辑、物理碎片
逻辑碎片是在文件系统里，数据库文件的碎片，这和其他任何文件一样。这在文件系统不能分配给数据库文件连续空间时产生。这会造成磁头从数据库文件读时需要来回移动。SQL Server对此毫不知情，也不能用任何脚本去检查逻辑磁盘碎片。逻辑磁盘碎片可以因下列原因产生：1.数据库文件与其它文件（系统文件和其他应用程序文件）放在同个磁盘；2.数据库文件以小块大小频繁增长。
移除逻辑碎片我们可以使用系统碎片整理工具，但是注意，在进行碎片整理时，我们需要停止SQL Server，不然的话碎片整理工具会跳过数据库文件，因为它正被SQL Server使用。避免逻辑碎片的最佳方式是:1.将数据库文件放在独立的硬盘，与其他应用程序和日志文件分开。2.创建新数据库的时候，估计下数据库文件的大小并分配足够的空间避免数据库文件的频繁增长。3.指定数据库文件增长选项为大块而不是频繁的小块增长。
（三）索引层碎片
索引层碎片有2类：内部碎片和外部碎片。如果索引层碎片高的话，优化器将不能最优的使用索引。
（四）内部碎片
内部碎片用索引的平均页饱和度来衡量（页密度）。一个100%满的页没有内部碎片。换句话说，内部碎片在索引页里有可用空间时发生，这个可以由insert/update/delete等DML语句操作造成。每一页基于索引的大小可以保存一定数量的记录，但这并不保证这个页总是保存最大数量的记录。内部碎片通常以字节的饱和度来统计，不是记录。一个索引页有90%的内部碎片可能已经插满记录了。剩下的10% bytes可能没有足够空间保存1条记录。在8K的页里，最大有8060bytes用来保存数据。剩下的空间被页头和行偏移数组使用。假设我们有100bytes定长的索引，共有800个索引条目。因此我们每页可以保存 8060/100=80条索引，剩下的60 bytes没有足够的空间保存更多的索引，这就需要10页来保存全部的索引结构。如果你要计算这个索引的平均饱和度，这个情况下是992.26%（8000/8060），下图对其进行展示：
假设我们随机删除表里一半的索引，即将索引条目减小为400。那页的分布情况会如下图所示。这里有40600 bytes空余可用。这时候我们计算下平均饱和度 4000 * 10 /80600=49.62%。可以看到近一半的页面是空的，且索引有了内部碎片。
（五）外部碎片
外部碎片发生在页的逻辑顺序和页的物理顺序不一致。外部碎片指的是索引的逻辑顺序和物理顺序缺少修正。它以索引叶子层页的无序占比来衡量。无序页就是分配给索引的下一个物理页和当前叶子页里里下一页指针指向的页不一致。
二、计划缓存
（一）即席SQL语句
每个提交到SQL Server的逻辑查询，会编译为物理执行计划。这个执行计划然后会缓存为所谓的计划缓存，用于后期重用。每次提交一个即席SQL语句到SQL Server，对于每个特定查询，都会生成一个执行计划。特定查询：SQL Server对每个完整的SQL语句（包括你的参数值）生成一个哈希值，并使用这个哈希值作为计划缓存的查找值。如果使用这个哈希值找到一个执行计划，计划就会重用，否则在计划缓存里会编译一个新的执行计划。假设提交下列3个查询到SQL Server：
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID = 11000
GO
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID = 30052
GO
SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID = 11223
GO
对于这3个查询，SQL Server编译3个不同的执行计划，因为提供了硬编码的参数值。因此3个查询间会计算不同的的哈希值，不会找到已缓存的计划。作为副作用，在计划缓存里，现在有近3个近乎一样的查询有3个不同的计划。这个特定问题被称为计划缓存污染。刚用不同的执行计划污染了计划缓存，这很难重用（因为硬编码的参数值），而且浪费了可以被SQL Server里其它组件使用的有用内存。缓存的目的应该提高重用数，但使用即席SQL语句就做不到。
（二）计划稳定性
假设为SQL语句使用参数值，甚至使用存储过程。在这个情况下，SQL Server非常容易重用缓存的执行计划。但即使重用缓存的执行计划，将引入性能问题。例如SQL Server为一个查询编译了一个执行计划，它回进行书签查找，因为非聚集索引没有覆盖查询：
我们提过，书签查找只有在从表里获取一些数据才有意义。如果你越过了临界点，使用全表扫描或聚集索引扫描更高效。但如果SQL Server冲了缓存的执行计划，这个选项就不会考虑太多，SQL Server会盲目重用计划，这时缓存性能就会很差，可以看下面的例子：
这里SQL盲目重用了有书签查找的缓存计划。SQL Server在假设从查询里只返回一条记录来编译和缓存计划。但实际上从SQL Server我们拿回1499条记录。你看到的执行计划，是假设只有一条记录返回的情况下优化——考虑下这个情况。这里潜在的根源是计划不稳定。基于估计行数，得到有书签查找的缓存计划，如果你越过临界点，会是表/聚集索引扫描。这是我们经常碰到的常见SQL Server性能问题。
三、统计信息
sqlserver是根据表中的统计信息进行行数估计，按照脚本语义来确定物理操作步骤生成执行计划，再按照该执行计划访问数据。表和视图都有统计信息，统计信息对象是根据索引或表列的列表创建的。当某列第一次最为条件查询时，将创建单列的统计信息。当创建索引时，将创建同名的统计信息。索引中，统计信息只统计首列，因此索引除了按首列排序存储数据外，其统计信息也是按首列计算统计的，所以索引设置时定义的第一列非常重要。每个统计信息对象都在包含一个或多个表列的列表上创建，并且包括显示值在第一列中的分布的直方图。