SQL Server 2005性能排错(14)

解决

当你识别到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的值，来确认语句覆盖了所有的情况。通过这种方法，长时间运行或资源消费语句可以被过滤出大型的存储过程或批。