SQL Server 2005性能排错（3）

核心提示：排错不论是否错误是持续的和可重复的（同样状态）或是随机的（显示为随机的不同状态），当你看到这个错误时你需要研究服务器内存分布，SQL Server 2005性能排错（3），当这个错误出现时，可能导致诊断查询失败，你可以通过执行exec sp_spaceused @objname='<user-object

排错

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

可能的解决方案包括：除去外部内存压力；增加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上的等待数量（这些信息将告诉你是否有其他的查询在占用大量内存）。

Small Gateway　　　　　　　　　Value
------------------------------ --------------------
Configured Units　　　　　　　 8
Available Units　　　　　　　　8
Acquires　　　　　　　　　　　 0
Waiters　　　　　　　　　　　　0
Threshold Factor　　　　　　　 250000
Threshold　　　　　　　　　　　250000
(6 row(s) affected)
Medium Gateway　　　　　　　　 Value
------------------------------ --------------------
Configured Units　　　　　　　 2
Available Units　　　　　　　　2
Acquires　　　　　　　　　　　 0
Waiters　　　　　　　　　　　　0
Threshold Factor　　　　　　　 12
(5 row(s) affected)
Big Gateway　　　　　　　　　　Value
------------------------------ --------------------
Configured Units　　　　　　　 1
Available Units　　　　　　　　1
Acquires　　　　　　　　　　　 0
Waiters　　　　　　　　　　　　0
Threshold Factor　　　　　　　 8

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锁等待的统计。

Select　wait_type,
waiting_tasks_count,
wait_time_ms
fromsys.dm_os_wait_stats　
wherewait_type like 'PAGEIOLATCH%'　
order by wait_type
wait_type　　　 waiting_tasks_count　wait_time_ms　 signal_wait_time_ms
-----------------------------------------------------------------------
PAGEIOLATCH_DT　0　　　　　　　　　　0　　　　　　　　　　0
PAGEIOLATCH_EX　1230　　　　　　　　 791　　　　　　　　　11
PAGEIOLATCH_KP　0　　　　　　　　　　0　　　　　　　　　　0
PAGEIOLATCH_NL　0　　　　　　　　　　0　　　　　　　　　　0
PAGEIOLATCH_SH　13756　　　　　　　　7241　　　　　　　　 180
PAGEIOLATCH_UP　80　　　　　　　　　 66　　　　　　　　　 0

下划线的是有兴趣的锁等待。当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瓶颈的物理磁盘。

select
database_id,
file_id,
io_stall,
io_pending_ms_ticks,
scheduler_address
fromsys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
wheret1.file_handle = t2.io_handle

下面是示例的输出。它展示当时在给定的数据库上有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完成）。因此整体系统性能被提高了。

select top 5
(total_logical_reads/execution_count) as avg_logical_reads,
(total_logical_writes/execution_count) as avg_logical_writes,
(total_physical_reads/execution_count) as avg_phys_reads,
Execution_count,
statement_start_offset as stmt_start_offset,
sql_handle,
plan_handle
from sys.dm_exec_query_stats　
order by
(total_logical_reads + total_logical_writes) Desc

当然你可以改变查询或取数据的不同视角。例如，在一个查询中声称最多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个批。

Avg_logical_reads Avg_logical_writes Avg_phys_reads Execution_count stmt_start_offset
--------------------------------------------------------------------------------------
2794　　　　　　　　1　　　　　　　　385　　　　　　　　1　　　　　　　　　　0　　　
1005　　　　　　　　0　　　　　　　　0　　　　　　　　　1　　　　　　　　　　146　　
sql_handle　　　　　　　　　　　　　　　　　　　　 plan_handle
------------------------------------------------------------------
0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB
x0600050099EC8520A8619803000000000000000000000000
0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB
x0600050099EC8520A8619803000000000000000000000000

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

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

select text
from sys.dm_exec_sql_text(
0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB)
Here is the output.
select
c1,
c5
from t1 INNER HASH JOIN t2 ON t1.c1 = t2.c4
order by c2

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

select
substring(text,
(<statement_start_offset>/2),
(<statement_end_offset> -<statement_start_offset>)/2)　
from sys.dm_exec_sql_text　　　　　　　　
(0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB)

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。

select *
from sys.dm_exec_query_plan
(0x0600050099EC8520A8619803000000000000000000000000)

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个主要的类别。

用户对象	这些对象被用户会话显示创建并在系统目录中被跟踪。这包括： ·　　　　 表和索引 ·　　　　 全局临时表(##t1)和索引 ·　　　　 本地临时表(#t1)和索引 ·　　　　 会话范围 ·　　　　 存储过程范围内创建 ·　　　　 表变量(@t1). ·　　　　 会话范围 ·　　　　 存储过程范围内创建
内部对象	这有语句范围的对象，通过SQL Server处理的查询创建和销毁。这些对象不能被系统目录跟踪。这包括： ·　　　　 工作文件(hash join) ·　　　　 排序 ·　　　　 工作表 (游标, 池 和临时大对象数据类型 (LOB)存储 ) 作为优化，当工作表被删除，一个IAM页和一个扩展被保存到一个新的工作表。 这有2个例外：临时LOB存储是批范围的和游标工作表示会话范围的
版本存储	用于存储行版本。MARS，在索引因操作，触发器和快照隔离级别都是基于行版本。这是SQL Server 2005中新的特性。
可用空间	这表示在tempdb数据库可用的磁盘空间。

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

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

监视tempdb空间

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

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

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

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

Select
SUM (user_object_reserved_page_count)*8 as user_objects_kb,
SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
SUM (version_store_reserved_page_count)*8　as version_store_kb,
SUM (unallocated_extent_page_count)*8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id = 2

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

user_objets_kb　 internal_objects_kb　 version_store_kb　 freespace_kb
---------------- -------------------- ------------------ ------------
8736　　　　　　　 128　　　　　　　　　　64　　　　　　　　　　448

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

磁盘空间问题排错

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

用户对象

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

DECLARE userobj_cursor CURSOR FOR
select
sys.schemas.name + '.' + sys.objects.name
from sys.objects, sys.schemas
where object_id > 100 and
type_desc = 'USER_TABLE'and
sys.objects.schema_id = sys.schemas.schema_id
go
open userobj_cursor
go
declare @name varchar(256)
fetch userobj_cursor into @name
while (@@FETCH_STATUS = 0)
begin
exec sp_spaceused @objname = @name
fetch userobj_cursor into @name
end
close userobj_cursor