SQL Server 2005性能排错（3）

　　排错

　　不论是否错误是持续的和可重复的（同样状态）或是随机的（显示为随机的不同状态），当你看到这个错误时你需要研究服务器内存分布。当这个错误出现时，可能导致诊断查询失败。可以开始从外部进行评估。接下来的步骤在一般的内存错误排错步骤中描述。

　　可能的解决方案包括：除去外部内存压力；增加max server memory设置。使用下列语句释放缓存dbcc freesystemcache，dbcc freesessioncache，或 dbcc freeproccache。如果问题还是出现，则应减少工作负载。

　　802 - there is insufficient memory available in the buffer pool.

　　原因

　　这个错误不是引起内存不足的必要条件。它可能预示缓存池内存被其他的一些组件使用。在sql server 2005中很少出现。

　　排错

　　使用常规的排错步骤和701错误的建议。

　　8628 - a time out occurred while waiting to optimize the query. rerun the query.

　　原因

　　这个错误指出查询编译过程失败因为它不能获取完成编译所需的内存。当查询经历了这种编译过程，包括解析，代数求解和优化，它的内存需求会增加。因而查询将和其他的查询争夺内存资源。如果查询超过的预定的超时时间（查询增加内存的占用） 当等待资源时，这个错误会返回。这种情况的最可能的原因是在服务器上出现很多大型查询的编译。

　　排错

　　1.在常规的排错步骤后查看是否服务器内存占用正常。

　　2.检查负载。确认被不同组件占用的内存数量（具体信息请参考内部物理内存压力）。

　　3.检查dbcc memorystatus输出的在每个gateway上的等待数量（这些信息将告诉你是否有其他的查询在占用大量内存）。

　　4.尽可能减少工作负载。

　　8645 - a time out occurred while waiting for memory resources to execute the query. rerun the query.

　　原因

　　这个错误指出在服务器上有很多内存敏感的查询被同时执行。使用排序（order by）和连接的查询可以在执行过程中会占用大量的内存。如果有高度并行或如果查询操作一个非联合索引的分区表时，查询内存需求会有很大的增加。无法或取所需内存资源的查询在预先确定的超时时间时（默认，超时是25倍的评估查询时间或sp_configure 设置的’query wait’）将收到这个错误。通常，收到这个错误的查询占用内存的那个查询。

　　排错

　　1.根据一般步骤评估内存的情况。

　　2.识别有问题的查询：确认有大量的在分区表上的查询，检查是否他们使用非联合索引，检查是否有大量包括连接和排序的查询。

　　3.检查sp_configure参数中degree of parallelism 和min memory per query 。尝试降低并行度并验证是否min memory per query没有设置很高的值。如果设置了很高的值，即使少量的查询也会占用指定查询的数量。

　　4.为了找到查询是否等待resource_semaphore，请查看本白皮书中阻塞的部分。

　　8651 - could not perform the requested operation because the minimum query memory is not available. decrease the configured value for the 'min memory per query' server configuration option.

　　原因

　　原因和8645错误类似；它也预示服务器内存过低。min memory per query太高也可能导致这个错误。

　　排错

　　1.根据一般内存错误排错步骤。

　　2.验证sp_configure min memory per query 选项设置。

　　i/o 瓶颈

　　sql server性能非常依赖于i/o子系统。除非你的数据库适合物理内存，sql server经常地会有数据库页面进出缓存池。这样就发生了实质的i/o流量。同样，在事务被明确的提交前，日志记录需要写入磁盘。sql server为各种目的可以使用tempdb，例如存储中间结果，排序，保持行的版本或其他。所以好的i/o子系统对于sql server性能非常重要。

　　除了当事务需要回滚时，访问日志文件是非常频繁的，而包括访问数据文件，包括tempdb，是随机访问的。所以作为一个通常的规则，为获取更好的性能，你最好将日志文分布不到不同的物理磁盘。本文重点于不是在如何配置你的i/o设备，而是描述识别你的i/o瓶颈的方法。一旦识别了i/o瓶颈，你也许需要重新配置你的i/o子系统。

　　如果你有一个慢速的i/o子系统，你的用户会遇到性能问题，例如很长的响应时间，任务由于超时而中断。

　　你可以使用下列性能参数来识别i/o瓶颈。注意，如果你的收集间隔过长，这些平均值可能会倾向于过小。例如，很难通过60秒一次的快照获取正确的i/o值。所以，你不能依赖于一个计数器来确定瓶颈；要考虑多个技术起来反复校验。

　　◆physicaldisk object: avg. disk queue length表现在采样周期中所选择的物理磁盘队列中的物理读和写平均请求数量。如果你的i/o系统过载，更多的读/写操作将被等待。如果在很少使用sql server时，你的磁盘队列长度经常超过2，这样你可能遇到了i/o瓶颈

　　◆avg. disk sec/read 是平均每次从磁盘读取数据的时间

　　小于10 ms – 很好

　　在 10 - 20 ms 之间- 正常

　　在20 - 50 ms 之间- 缓慢，需要注意

　　大于 50 ms – 严重的i/o 瓶颈

　　◆avg. disk sec/write 是平均每次从磁盘读取数据的时间。请引用之前的指导数据。

　　◆physical disk: %disk time 是所选磁盘驱动器用于服务于读或写请求的总共时间的百分比。一般推荐是如果该值大于50%，则表现为i/o瓶颈。

　　◆avg. disk reads/sec 表现磁盘上读操作的速度。你需要确认该值小于85%的磁盘设计能力。磁盘访问时间指数高于能力的85%。

　　◆avg. disk writes/sec表现在磁盘上写操作的速度。确认该值小于85%的磁盘设计能力。磁盘访问时间指数高于能力的85%。

　　当使用以上计数器，你在使用raid配置时，你需要使用下列公式调整结果值。

　　raid 0 -- i/os per disk = (reads + writes) / number of disks

　　raid 1 -- i/os per disk = [reads + (2 * writes)] / 2

　　raid 5 -- i/os per disk = [reads + (4 * writes)] / number of disks

　　raid 10 -- i/os per disk = [reads + (2 * writes)] / number of disks

　　例如，你有带有2个物理磁盘的raid-1系统和下列计数器值。

　　disk reads/sec　　　　　　80

　　disk writes/sec　　　　　 70

　　avg. disk queue length　　5

　　这种情况下，你遇到了(80 + (2 * 70))/2 = 110 i/os per disk，你的磁盘队列长度为5/2=2.5，表明系统接近于i/o瓶颈。

　　你也可以通过考察锁等待来识别i/o瓶颈。当数据页通过读或写访问并且在缓存池中页不可用时，这些锁等待占用了大量的物理i/o等待。当页面没有在缓存池中找到时，一个异步i/o请求被发出，i/o的状态是被选中的。如果i/o已经完成，工作进程处理正常。否则，依赖于请求的类型，它会等待pageiolatch_ex 或 pageiolatch_sh。从下列的dmv查询可以用于找到i/o锁等待的统计。

　　下划线的是有兴趣的锁等待。当i/o完成时，工作者被放置到可用队列中。从i/o完成到工作者被实际的调度的时间在signal_wait_time_ms列说明。如果你得waiting_task_counts and wait_time_ms偏离正常值太多，你可以识别为i/o问题。为了这样，通过使用性能计数器和关键dmv建立正常时运行时的性能基线就十分重要。wait_types可以识别是否你的i/o子系统处在瓶颈状态，但是他们不能提供任何关于物理磁盘遇到问题的信息。

　　你可以使用下列dmv查询找到当前等待的i/o请求。你可以周期性的执行这些请求检查i/o子系统的健康情况，并找到涉及i/o瓶颈的物理磁盘。

　　下面是示例的输出。它展示当时在给定的数据库上有3个未决定的i/o 。你可以使用database_id 和 file_id 来找到映射的物理磁盘文件。

　　io_pending_ms_ticks 表现了所有等待在未决定队列中的个别的i/o。

　　database_idfile_id io_stallio_pending_ms_ticksscheduler_address

　　----------------------------------------------------------------------

　　6　1　10804　78　0x0227a040

　　6　1　10804　78　0x0227a040

　　6　2　10145131　0x02720040

　　解决

　　当你识别到i/o瓶颈时，你可以下列方法解决：

　　◆检查sql server的内存配置。如果sql server配置的内存不足，将导致更多的i/o开销。你可以考察下列计数器来识别内存压力。

　　◆buffer cache hit ratio

　　◆page life expectancy

　　◆checkpoint pages/sec

　　◆lazywrites/sec

　　更多关于内存压力的信息，请查看内存瓶颈。

　　◆增加i/o带宽。

　　◆为当前的磁盘阵列添加更多的物理驱动器和/或使用更快的磁盘代替当前的磁盘。这帮助提升读写访问时间。但是添加的磁盘数量不能比当前i/o控制器所支持的数量大。

　　◆添加快速或额外的i/o控制器。考虑为当前的控制器添加更多的缓存（如果有可能）

　　◆考察执行计划并查看那个计划占用了更多的i/o。这样可以找到更好的查询进化（例如，索引）可以减少i/o。如果缺少索引，你可以运行database engine tuning advisor找到缺失的索引。

　　下列dmv查询能用于找到那个批处理/请求生成了最多的i/o。你将注意到我们没有说明物理写入。如果你了解数据库如何工作，这也没有问题。请求中的dml/ddl并不是直接将数据页写入磁盘。只有通过提交事务语句才能触发将页面物理写入磁盘。通常物理写通过checkpoint语句完成或通过sql server lazy writer完成。下列dmv查询可以用于找到生成最多i/o的5个请求。调整这些查询，使他们使用较少的逻辑度来完成操作，这样可以减少在缓存池上的压力。这允许其他的查询在缓存池中找到重复执行中所需要的重复数据（而不是通过物理i/o完成）。因此整体系统性能被提高了。

　　当然你可以改变查询或取数据的不同视角。例如，在一个查询中声称最多i/o请求的5个请求，你可以通过下列表达式排序：

　　(total_logical_reads + total_logical_writes)/execution_count

　　除此之外，你可能希望通过物理i/o排序或其他的操作。然而，逻辑读/写数量来帮助决定是否查询选择的计划被优化过。例如，它可以做一个表扫描而不是使用索引。一些查询，例如使用嵌套循环连接可能有很高的逻辑计数器但是能更好的适应缓存，因为他们重新访问了同样的页面。

　　例如:让我们考察下列2个有2个sql查询的批，每个表由1000行并且行大小大于8000（每页1行）。

　　批-1

　　select

　　c1,

　　c5

　　from t1 inner hash join t2 on t1.c1 = t2.c4

　　order by c2

　　批-2

　　select * from t1

　　为了这个示例，在运行dmv查询前，我们利用下列命名清理了缓存池和过程的缓存。

　　checkpoint

　　dbcc freeproccache

　　dbcc dropcleanbuffers

　　这是dmv查询的输出。你将注意到2行表现的这2个批。

　　你将注意到第2个批只发生了逻辑读但是没有物理i/o。这是因为需要的数据已经由第1个查询缓存到了高速缓存中（有充分的内存）。

　　你可以通过下列查询或取执行查询的文本。

　　你也可以通过下列语句找到独立语句的字符串。

　　statement_start_offest 和statement_end_offset的值需要除以2来补偿sql server使用unicode来存储这种数据的。statement_end_offse值为-1指出语句先前到了批的最后。而substring()函数不识别-1，并将其作为非法值。使用(<statement_end_offset> -<statement_start_offset>)/2,代替-1，一个需要输入64000的值，来确认语句覆盖了所有的情况。通过这种方法，长时间运行或资源消费语句可以被过滤出大型的存储过程或批。

　　同样的你可以运行下列查询，找到查询计划确定是否当选择了较差的计划时有大量的i/o。

　　tempdb

　　tempdb用于全局存储内部或用户对象，临时表，对象和在sql server操作是创建的存储过程。每个sql server 实例只有1个单一的tempdb。它可能是一个性能和磁盘空间的瓶颈。有限可用空间和过多的ddl/dml会使tempdb超过负载。这能导致运行在同一个服务器中的其他无关应用变得运行缓慢或失败。

　　下面列出一些tempdb的常规问题：

　　◆tempdb磁盘空间不足

　　◆由于tempdb中的i/o瓶颈，导致查询运行缓慢。这在i/o瓶颈部分提到过。

　　◆过多ddl操作导致系统表的瓶颈

　　◆分配争夺

　　before we start diagnosing problems with tempdb, let us first look at how the space in tempdb is used. it can be grouped into four main categories.当开始调试tempdb问题前，让我们先看一下在tempdb中如何使用空间。它可以分为4个主要的类别。

　　tempdb使用的总空间等于用户对象加内部对象加版本存储+可用空间。

　　这个可用空间等于性能计数器中tempdb的可用空间。

　　监视tempdb空间

　　阻止问题更好的方法是随后马上解决它。你可以使用下列性能监视器来监视使用中的tempdb空间。

　　◆free space in tempdb (kb).这个计数器以kb为单位跟踪空闲空间的数量。管理员可以使用这个计数器确定是否tempdb运行在较低的磁盘空间上。

　　然而，识别前面所定义的不同类别如何使用在tempdb中使用的磁盘空间，是很有兴趣的并有效率的。

　　下列查询返回了tempdb用户使用的空间和内部组件对象。当前它提供了仅有tempdb中的信息。

　　这里有一些示例的输出（空间使用kb为单位）。

　　注意这些技术不包括其中的混合扩展。页面在混合扩展中可以被分配个别用户和内部对象。

　　磁盘空间问题排错

　　用户对象，内部对象和存储的版本可以都可以导致在tempdb中的空间问题。这节我们会考虑针对每个类别如何排错。

　　用户对象

　　因为用户对象不被特定的会话拥有，你需要理解创建该对象的应用程序的规范并调整需要的tempdb大小。你可以通过执行exec sp_spaceused @objname='<user-object>'来找到个别用户对象使用的。例如，你可以运行下列脚本枚举所有tempdb对象。