SQL Server 2005性能排错(8)

您所在的位置：首页>>开发>>微软平台和开发技术专区>>SQL Server>>

SQL Server 2005性能排错(8)

http://developer.51cto.com 2007-02-28 17:25 Sunil Agarwal/Boris Baryshnikov/Tom Davidson/Keit. msdn 我要评论(0)

摘要：很少会有偶然的性能下降。设计不良的数据库或工作负载配置不正确的系统会经常导致性能问题。管理员需要能预先阻止或最小化问题的影响，当管理员遇到问题时，应该诊断问题并采取正确操作来修复问题。本文提供了按部就班的指导，通过使用可用的工具例如SQL Server Profiler，System Monitor和在SQL Server 2005中新的Dynamic Management View来为一般的性能问题诊断和排错。
标签：SQL Server 2005 性能排错

外部虚拟内存压力

你需要确定是否页面文件为当前内存的分配能提供足够的空间。为了检查，可以打开任务管理器中的性能视图，并检查Commit Charge节。如果Total接近于Limit则说明可以被提交的最大数量内存没有扩展页面的空间。注意任务管理器中的Commit Charge Total指出潜在使用的页面文件，而不是实际使用值。实际使用的页面文件将增加物理内存压力。

同样可以通过下列技术起获取相关信息：Memory: Commit Limit, Paging File: %Usage, Paging File: %Usage Peak。

你可以通过每个进程的Process: Working Set减去Process Private Bytes计数器来评估内存总数。

如果Paging File: %Usage Peak（或Peak Commit Charge）很高，检查系统日志中是否有指出页面文件增长或通知“running low on virtual memory”的信息。你可能需要增加你的页面文件大小。High Paging File: %Usage指出物理内存超过要提交的值并也要考虑外部物理内存压力（大量的内存需求，没有足够的RAM）。

内部物理内存压力

内部内存压力来自于SQL Server自身，应首先通过检查在缓存分布中的异常来考虑在SQL Server内存分布。通常在SQL Server中缓存会占用最多提交的内存。为了确定在缓存池中的内存总数，我们可以使用DBCC MEMROYSTATUS命令。在Buffer Counts节，可以找到Target值。下列输出显示了在服务器达到正常负载时DBCC MEMORYSTATUS的结果。

Buffer Counts                  Buffers
------------------------------ --------------------
Committed                      201120
Target                         201120
Hashed                         166517
Reserved Potential             143388
Stolen Potential               173556
External Reservation           0
Min Free                       256
Visible                        201120
Available Paging File          460640

Target是SQL Server计算出它在不导致分页时可以提交的8-KB每页的页数。

Target是被定期的重新计算的来反映内存的低或高。在常规服务负载下target页面过低可能预示出现了外部内存压力。

如果SQL Server占用了大量的内存(通过Process: Private Bytes或任务管理器中Mem Usage 列显示)，请查看是否Target的数值。注意，如果启用AWE，你还要从sys.dm_of_memory_clerks或DBCC MEMORYSTATUS输出计算AWE分配的总量。

考虑上面的示例（没有启用AWE），Target*8KB=1.53GB，而服务器的Process: Private Bytes大约是1.62GB或缓存池用SQL Server占用了94%的内存。注意，如果服务器没有过载，Target是应该超过Process: Private Bytes性能计数器报告的数量。

如果Target过低，但是服务器的Process: Private Bytes或任务管理器中Mem Usage 值很高，我们也许要面对从缓存池外使用内存的组件带来的内部内存压力。被加载到SQL Server进程中的组件，例如COM对象，连接服务器，扩展存储过程，SQLCLR或其他会从缓存池外占用内存。如果不使用SQL Server内存接口，将没有方法跟踪组件在缓存池外占用的内存。

适用于SQL Server内存管理机制的组件使用在缓存池中分配很少的内存。如果分配的大于8KB，这些组将将通过多页分配器借口使用缓存池外的内存。

下列方法可以快速检查通过多业分配器接口占用的内存数量。

-- amount of mem allocated though multipage allocator interface
select sum(multi_pages_kb) from sys.dm_os_memory_clerks

你可以这样获得通过多页分配器分发内存的详细信息：

select
type, sum(multi_pages_kb)
from
sys.dm_os_memory_clerks
where
multi_pages_kb != 0
group by type
type

------------------------------------------ ---------
MEMORYCLERK_SQLSTORENG                     56
MEMORYCLERK_SQLOPTIMIZER                   48
MEMORYCLERK_SQLGENERAL                     2176
MEMORYCLERK_SQLBUFFERPOOL                  536
MEMORYCLERK_SOSNODE                        16288
CACHESTORE_STACKFRAMES                     16
MEMORYCLERK_SQLSERVICEBROKER               192
MEMORYCLERK_SNI                            32

如果通过多页分配器分发了过大的内存( 100-200MB或更多)，应该做进一步的研究。

如果你看到了通过多页分配器分发的大量内存，检查服务器的配置并尝试使用之前或后续的查询确定哪个组件占用的最多的内存。

如果Target值低，但是在百分比上它占用了最多的内存，请在前面部分中查找描述外部内存压力的部分(External Physical Memory Pressure)，或查看服务器内存配置参数。

如果你设置了max server memory 和/或min server memory，你应该用这些值和Target值进行比较。max server memory选项限制了在缓存池中占用内存的最大值，而服务器还可以占用其他的部分。min server memory选项告诉服务器当小于该值时不能释放缓存池的内存。如果Target小于min server memory设置并且服务器没有过载，这可能预示服务器遇到了外部内存压力并且不能获得这个设置大小的内存。它也可能预示着从内部组件的内存压力，就像上面描述的那样。Target 数值不能超过max server memory选项的设置。

首先，检查从DBCC MEMORYSTATUS输出中Stolen页面数量

Buffer Distribution            Buffers
------------------------------ -----------
Stolen                         32871
Free                           17845
Cached                         1513
Database (clean)               148864
Database (dirty)               259
I/O                            0
Latched                        0

相对于Stolen和Target页面的高百分比(>75-80%)预示着内部内存压力。

更多关于服务器组件内存分配的信息可以使用sys.dm_of_memory_clerks DMV获取。

-- amount of memory consumed by components outside the Buffer pool
-- note that we exclude single_pages_kb as they come from BPool
-- BPool is accounted for by the next query
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb) as [Overall used w/o BPool, Kb]
from
sys.dm_os_memory_clerks
where
type <> 'MEMORYCLERK_SQLBUFFERPOOL'

-- amount of memory consumed by BPool
-- note that currenlty only BPool uses AWE
select
sum(multi_pages_kb
+ virtual_memory_committed_kb
+ shared_memory_committed_kb
+ awe_allocated_kb) as [Used by BPool with AWE, Kb]
from
sys.dm_os_memory_clerks
where
type = 'MEMORYCLERK_SQLBUFFERPOOL'