SQL Server 2005怎样进行性能排错
很少会有偶然的性能下降。设计不良的数据库或工作负载配置不正确的系统会经常导致性能问题。管理员需要能预先阻止或最小化问题的影响，当管理员遇到问题时，应该诊断问题并采取正确操作来修复问题。本文提供了按部就班的指导，通过使用可用的工具例如SQL Server Profiler，System Monitor和在SQL Server 2005中新的Dynamic Management View来为一般的性能问题诊断和排错。
[b]目录[/b]
简介
目标
方法
资源瓶颈
解决资源瓶颈的工具
CPU 瓶颈
过多的编译和重编译
检测
解决
效率低的查询计划
检测
解决
内部查询的并行
检测
解决
拙劣游标使用
检测
解决
内存瓶颈
背景
虚拟地址空间和物理内存
Windows地址扩展和SQL Server
内存压力
检测内存压力
外部物理内存压力
外部虚拟内存压力
内部物理内存压力
高速缓存和内存压力
Ring buffers
内部虚拟内存压力
一般的内存错误排错步骤
内存错误
I/O 瓶颈
解决
Tempdb
监视tempdb空间
磁盘空间问题排错
用户对象
版本存储
内部对象
过多的DLL和分配操作
解决
运行缓慢的查询
阻塞
识别长时间的阻塞
通过sys.dm_db_index_operational_stats查看阻塞的每个对象
使用SQL waits阻塞对整体性能的影响
监视索引的使用
总结
附录A: DBCC MEMORYSTATUS 描述
附录B: 阻塞脚本
分析操作的索引统计
等待状态
[b]简介[/b]
很多客户偶尔会遇到SQL Server 数据库性能下降。原因可能涉及从不良好的数据库设计到不正确的负载配置。作为一个管理员，你应该预先阻止或最小化问题，并当问题发生时，诊断原因并尽可能的做出正确的操作来解决问题。这片白皮书所述的问题通常来源于Microsoft® Corporation 的Customer Support Service（CSS or PSS）部门所遇到的，因为将所有可能的问题都详尽的分析是不合实际的。我们提供了按部就班的指导，通过使用可用的工具例如SQL Server Profiler，System Monitor和在SQL Server 2005中新的Dynamic Management View来为一般的性能问题诊断和排错。
[b]目标[/b]
.这篇文章的主要目标是提供一套常规的方法通过使用公开的工具在一般的客户场景中诊断和排错SQL Server性能问题。
SQL Server 2005在用户支持上有了很大的提高。内核层（SQL-OS）被重新架构过，内部结构和统计数据通过动态管理视图(DMVs)暴露为关系型行集。SQL Server 2000通过像sysprocesses这样的系统表暴露一些信息，但是有时你需要将SQL Server进程内存映射为物理文件并从中抽取内部结构的相关信息。这里有2个主要的问题。第一，客户不能总是提供物理映射文件，因为文件的尺寸原因，并且这个过程很耗时。第二，这将花费更长的时间诊断问题，因为文件必须传回Microsoft Corporation来分析。
这带给我们本文的第二个目标，展示DMVs的优点。DMVs通过除去大多数情况下需要的生成和分析物理映射步骤可以加速调试的过程。本文尽可能的提供了和SQL Server 2000中同样问题的比较。DMVs提供为获取关键系统信息的简单而熟悉的界面。这些信息也可以用于监视目的，警告管理员潜在的问题。或者也可以被周期性的收集为以后的分析所用。
[b]方法[/b]
这里有很多降低SQL Server速度的原因。我们使用下列3个主要症状来诊断问题。
• 资源瓶颈: CPU，内存，和I/O瓶颈是在本文中主要涉及的。这里我们不考虑网络因素。对每种资源瓶颈，我们会描述如何识别问题并阐述可能的原因。例如，内存瓶颈可以导致过多的分页，最后影响性能。
• Tempdb 瓶颈:因为每个SQL Server 实例只有一个tempdb，这可以产生性能和磁盘空间的瓶颈。不好的应用程序在过多的DDL和DML操作会使tempdb过载。这导致其他在这台服务器上运行的不相关的应用程序运行缓慢或失败。
• 缓慢运行的用户查询:现有的查询性能下降或新的查询显示比预期时间更长。这可能有很多原因。例如：
• 改变统计信息可以导致现有查询的较差的查询计划。
• 制表扫描，降低查询性能。
• 即使资源利用正常由于阻塞也可以导致应用程序运行缓慢。
过多的阻塞可能是由于不良的应用程序设计或架构设计或者是选择了错误的事务隔离级别的原因。
这些症状的原因不需要每个都独立出来。不良的查询计划选择可以使系统资源加重并导致整体性能的下降。所以，如果大表缺失的有用的索引，或查询优化器没有选择它，这样不仅导致查询缓慢，也会导致将不需要数据页读取到内存（buffer pool）中在缓存中存储，这样会加重I/O子系统的压力。同样的，频繁运行查询的重编译可以导致CPU的压力。
[b]资源瓶颈[/b]
接下来的部分讨论了CPU，内存和I/O子系统资源及这些瓶颈如何产生。（网络问题不在本文的讨论范围）每个资源瓶颈，我们描述了如何识别问题并找到原因。例如，一个内存瓶颈可以导致过渡分页，将会影响性能。
在决定你遇到资源瓶颈前，你需要了解在普通环境下资源是如何使用的。你可以使用在这片白皮书中所列出的方法收集有关资源使用的基线信息（即使你没有遇到性能问题）。
你也许找到问题是一个资源运行到设计容量并且SQL Server当前的配置不能支持这种负载。为了解决这问题，你也许需要添加处理能力，内存或增加I/O的带宽或网络通道。但是，在你进行下一步之前，理解这种资源瓶颈的通常原因是非常重要的。有一些解决方案不需要添加额外资源，例如重新配置。
[b]解决资源瓶颈的工具[/b]
下列工具是在解决特殊性能瓶颈时经常使用的工具。
• 系统监视器(PerfMon)：这是Windows所带的一个工具。更多信息，请见系统监视器文档。
• SQL Server Profiler：在SQL Server 2005程序组中的Performance Tools组中可以看到SQL Server Profiler。
• DBCC 命令：详细信息请见SQL Server联机丛书中和附录A。
• DMVs: 详细信息请见SQL Server联机丛书。
[b]CPU 瓶颈[/b]
当没有额外负载，突然发生的CPU瓶颈通常由于没有查询计划，不良的配置或设计因素和不足的硬件资源所引起。在购买更快或更多处理器前，你需要首先确定CPU的最大处理能力带宽并查看是否他们都在使用中。
系统监视器一般是确定CPU资源的最好工具。你应该查看Processor:% Processor Time计数器是否偏高；该计数器值超过80%一般被认为是瓶颈。你也可以使用sys.dm_os_schedulers视图来监视是否正在运行的任务不是0。非0的值预示有任务需要等待时间切片来运行；这个数值高表明一个CPU瓶颈的征兆。你可以用下列查询列出所有的调度器并产看等待运行的任务数量。
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255
下列查询给你一个查看当前缓存中最耗费CPU的批或过程高级别视图。查询根据具有相同plan handle（意味着属于同一个批或过程）的语句聚合CPU的调用。如果给出的plan handle对应多个语句，你将不得不继续找到在整个CPU使用中最占用资源的查询。
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
本节剩下的部分将讨论一些其他的在SQL Server 中CPU敏感的操作，也有有效的检查和解决这些问题的方法。
[b]过多的编译和重编译[/b]
当批或远端过程调用(RPC)被提交到SQL Server，在开始执行前，服务器检查查询计划的有效性和正确性。如果这些检查中的一个失败时，批将被再次编译声称不同的查询计划。这种编译就是所说的重编译。当服务器确认当底层数据改变时有更优化的查询计划时，重编译确认正确性再完成编译。编译是CPU敏感的，因此过多的重编译将导致在系统中的CPU性能问题。
在SQL Server 2000中，当SQL Server重编译一个存储过程，整个存储过程都被重编译，不仅是触发重编译的语句。SQL Server 2005引入了语句级的存储过程的重编译。当SQL Server 2005重编译存储过程，只有导致重编译的语句被编译-而不是整个存储过程。这减少了CPU的负载并减少了对例如COMPILE锁的资源争用。重编译可以有多种原因出发，例如：
• 架构变化
• 统计变化
• 延期编译
• Set属性改变
• 临时表改变
• 存储过程创建时使用了RECOMPLIE查询提示或使用了OPTION （RECOMPILE）。
[b]检测[/b]
你可以使用系统监视器（PerfMon）或SQL Trace(SQL Server Profiler)来检测过多的编译和重编译。
[b]系统监视器(Perfmon)[/b]
SQL Statistics对象提供了监视重编译的计数器和发送到SQL Server实例的请求类型。你必须监视查询编译和重编译的数量以及相关联的接受的批的数量来找出是否这个编译是最耗费CPU资源。理想环境下，SQL Recompilations/sec和Batch Request/sec的比值应该很低，除非用户提交了大量的单独查询。
下列显示了关键的数据计数器。
• SQL Server: SQL Statistics: Batch Requests/sec
• SQL Server: SQL Statistics: SQL Compilations/sec
• SQL Server: SQL Statistics: SQL Recompilations/sec
更多信息请看SQL Server联机丛书中的 “SQL Statistics Object”
[b]SQL 跟踪[/b]
如果PerfMon计数器显出了很高的重编译数量，编译将在SQL Server中占用很多的CPU资源。我们将需要查看Profiler 跟踪并从中找到找到被重编译的存储过程。SQL Server Profiler跟踪给出我们重编译原因的信息。你可以使用下列事件。
SP:Recompile和SQL:StmtRecompile事件类指出了哪个存储过程和语句被重编译。当你编译一个存储过程，一个事件为这个存储过程生成，其中每条语句将被编译。然而，当存储过程重编译时，只有导致重编译的语句被重编译（在SQL Server 2000中将是整个存储过程）。下面列出了SP:Recompile事件类更多重要的数据列。特别是EventSubClass数据列决定重编译的原因。SP:Recompile当存储过程或触发器被重编译被触发一次，但不会被独立查询引发。在SQL Server 2005中，监视SQL:StmtRecompiles也非常有用，该事件类在所有类型的重编译中都会被触发，包括批，独立查询，存储过程和触发器。如下是我们关系的事件中关键的数据列：
• EventClass
• EventSubClass
• ObjectID (represents stored procedure that contains this statement)
• SPID
• StartTime
• SqlHandle
• TextData
更多信息，请见SQL Server 联机丛书中“SQL:StmtRecompile Event Class”。
如果你有保存的跟踪文件，你可以使用下列查询查看所有捕捉的重编译事件。
select
spid,
StartTime,
Textdata,
EventSubclass,
ObjectID,
DatabaseID,
SQLHandle
from
fn_trace_gettable ( 'e:\recompiletrace.trc' , 1)
where
EventClass in(37,75,166)
事件类37是 Sp:Recompile， 75 是 CursorRecompile， 166是SQL:StmtRecompile
你可以通过SqlHandle和ObjectID列或其他列将这个查询的结果分组，也可以查看是否最多的重编译类型是存储过程或其他原因（例如SET选项改变等）。
[b]Showplan XML For Query Compile. [/b]
Showplan XML For Query Compile事件发生于Microsoft SQL Server编译或重编译一段SQL语句时。该事件有关于语句编译或重编译的信息。信息包括查询计划和过程的对象ID。捕获这些事件是有性能开销的，因为它捕获了每次编译或重编译。如果你在系统监视其中看到很高的SQL Compilations/sec计数器值，你应该监视这个事件。通过这些信息，你可以看到那条语句被频繁的重编译。你可以使用这些信息改变这些语句的参数。这将影响重编译的数量。
[b]DMVs.[/b]
当你使用sys.dm_exec_query_optimizer_info DMV，你可以得到SQL Server花费在优化的时间。如果获取了这个DMV的2个快照，你可以得到在给定的时间段内花费在查询优化的时间。
select *
from sys.dm_exec_query_optimizer_info
counter occurrence value
---------------- -------------------- ---------------------
optimizations 81 1.0
elapsed time 81 6.4547820702944486E-2
特别是查看elapsed time，该时间由于优化而产生。因为优化过程的时间基本上就是用户优化操作的CPU时间（因为优化处理是CPU时间的主要部分），你可以得到一个好的度量，找到那段编译时间占用了大量的CPU时间。
其他包含有用信息的DMV有：
sys.dm_exec_query_stats.
你希望查看的数据列有 :
• Sql_handle
• Total worker time
• Plan generation number
• Statement Start Offset
更多信息请查看SQL Server 联机丛书相关主题
[b]sys.dm_exec_query_stats.[/b]
特别是plan_generation_num预示了查询编译时的次数。下面是示例给你展示了被重编译次数最多的25个存储过程。
select *
from sys.dm_exec_query_optimizer_info
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc
[b]解决[/b]
如果你监测到过多的编译/重编译，考虑下列选项。
• 如果重编译因为SET选项改变而发生，使用SQL Server Profiler确定哪个SET选项被改编。避免在存储过程中改变SET选项。如果改变最好在连接级别设置。确认在该连接的生存周期内不要改变SET选项。
在临时表上重编译的阀值比在普通表上的低。在临时表上的重编译时由于统计改变而引起，你可以降临时表改为使用表变量。表变量的改变不会引起重编译。这种方法的确定是查询优化器不识别表变量，因为统计不会被创建或维护表变量。这将导致没有查询计划。你可以测试不同的选项，并选择最好的方法。另外一个选项时使用KEEP PLAN查询提示。设置临时表的这个阀值与使用永久表相同。EventSubclass 列预示了在临时表上的”Statistics Changed”的操作。
• 为避免由于改变统计而产生的重编译（例如，因为数据统计导致计划不理想），特别是KEEPFIXED PLAN查询提示。根据设置的影响，重编译可以仅因为相关正确的原因（例如，当底层表结构改变导致计划不再适用），而不根据统计的变化。如果语句引用的表的架构改变时或者表是被标记为sp_recompile的存储过程，重编译将发生。
• 关闭自动更新索引统计和表或视图的统计，防止由于对象的统计改变而产生的重编译。注意，通过使用这种方法关闭”auto-stats”特性不是一个好的想法。这是因为查询优化器不再为在这些对象上的数据改变而敏感，将导致不良的查询计划。使用这种方法仅在尝试了所有其他选择之后，做为最后的手段。
• 批应该尽量使用对象全名（例如，dbo.Table1）避免重编译并避免不明确的对象。
• 为避免由于延期编译导致的重编译，不要混杂DML和DDL或从条件结构创建DDL，例如IF语句。
• 运行Database Engine Tuning Advisor(DTA)查看改变索引是否可以改善编译时间和查询的执行时间。
• 检查是否存储过程通过WITH RECOMPILE选项创建或使用了RECOMPILE查询提示。如果过程通过WITH RECOMPILE选项创建，在SQL Server 2005中，如果在过程中特殊的语句需要被重编译，我们可以利用语句级的RECOMPILE提示。这将避免在每次执行的时候对整个过程重编译，而同时允许个别语句被编译。更多有关RECOMPILE提示的信息，请查看SQL Server联机丛书。
[b]效率低的查询计划[/b]
当为一个查询生成查询计划时，SQL Server查询优化器尝试选择一个计划为查询提供最快的响应时间。注意最快的查询时间并不意味最小的I/O开销，也不意味使用最少的CPU资源-它会在各种资源中平衡。
某些操作类型比其他操作对CPU更敏感。Hash操作和Sort操作扫描他们各自的输入数据。使用扫描向前读取（prefetch）时，在需要操作页面前，页面几乎都在缓存中。因此可以减少或消除物理I/O操作。这使这些操作的类型将不被物理I/O所限制。与之相比，嵌套循环连接有很多索引查找，如果索引查找使用很多不同的表以至于页面不适合缓存的大小，将导致生成I/O负载。
最有意义的输入优化用于评估为每中操作生成不同查询计划开销的评估，你可以在Showplan（EstimateRows和EstimateExecution属性）中看到结果。没有精确的评估，用于优化的主输入是有缺陷的。
[b]检测[/b]
低效率的查询计划通常可以被检测出来。低效率的查询计划可以导致增加CPU的消耗。
查询sys.dm_exec_query_stats是确定哪个查询累计使用CPU时间最多的有效方法。
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
还可以选择，也可以查询sys.dm_exec_cached_plans并通过使用过滤器查找可疑的类似于‘%Hash Match%’, ‘%Sort%’这样CPU敏感的各种操作。
[b]解决[/b]
如果你监测到效率低的查询计划，考虑下列选项。
• 使用Database Engine Tuning Advisor调节查询，查看是否生成对修改索引的建议
• 检查有问题的评估。
编写的查询中使用的更有限制性的WHERE从句是否合适？无限制条件的查询是资源敏感的。
在查询中涉及的表上运行UPDATE STATISTICS，检查是否还有这种问题。
是否查询使用的构造导致优化器不能精确的评估？
考虑是否可以将查询修改为其他的方法，避免这种问题。
• 如果不能修改架构或查询，SQL Server 2005有一个新的查询计划特性，允许你将指定查询提示添加到满足某种文本的查询中。这可以用在独立查询中，也可以用在存储过程内。例如OPTION(OPTIMIZE FOR)这样的提示允许你影响评估而忘记所有列出的潜在计划。其他的提示，类似OPTION(FORCE ORDER)或 OPITON(USE PLAN)允许你改变控制查询计划的程度。
[b]内部查询的并行[/b]
当为一个查询生成执行计划时，SQL Server优化器尝试为该查询选择最快的相应计划。如果查询的开销超过了在cost threshold for parallelism选项中指定的值，并行不会被禁用，优化器尝试生成一个可以用于并行的计划。并行查询计划使用多线程处理查询，每个线程分布在可用的CPU上并同时利用每个CPU的时间资源。最大的并行度可以通过服务器上的max degree of parallelism选项或每个查询使用OPTION(MAXDOP)提示限制。
用于执行实际并行度（DOP）的结果——度量有多少线程将在给定的操作上并行——是知道执行时才能确定。在执行查询前，SQL Server 2005决定有多少个调度器未充分利用并为查询选择DOP来充分利用剩余的调度器。一旦一个DOP被选择了，直到完成，查询将使用这个选择的并行度来运行。并行查询的使用时CPU有一些偏高，但是它在elapsed time上的时间很短。如果没有其他瓶颈，类似于物理I/O等待，并行计划将会使用所有处理器的100%资源。
查询开始执行后，一个关键的因素（系统有多空闲）可以导致运行并行计划的改变。例如，如果查询运行在空闲时间，服务器可以选择使用并行计划并使用DOP为4，在4个不同的处理器上产生线程。一旦这种线程开始执行，现存的连接可以提交其他需要大量CPU的查询。在这种情况，所有不同的线程将共享可用的CPU的时间切片，导致更高的查询持续时间。
通过并行计划运行不是一定是不好的，并行可以为查询提供最快的响应时间。然而，给定查询的响应时间必须与整体的吞吐量和系统其他查询的响应进行衡量。并行查询一般最适合批处理和决策支持系统，而不适合一个事务处理环境。
[b]检测[/b]
内部查询的并行问题可以通过下列方法检测。
[b]系统监视器(Perfmon)[/b]
考虑SQL Server:SQL Statistics – Batch Requests/sec 计数器，并查看SQL Server联机丛书中的“SQL Statistics Object”获取更多信息。
因为在考虑使用并行计划前，查询必须评估开销超过为并行配置设置的开销阀值（默认被设置为5），服务器每秒处理的批小于运行在并行计划中的批。运行很多并行查询的服务器一般配置为较小的每秒批请求数（例如，小于100的值）。
[b]DMVs[/b]
在运行的服务器上，你可以使用下列查询确认在给定会话中是否可以并行运行任何活动的请求。
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0
通过这些信息，查询的文本可以通过使用sys.dm_exec_sql_text轻松获取，而查询计划可以使用sys.dm_exec_cached_plan获取。
你也可以搜索符合运行在并行的计划。这可以通过搜索缓存的计划来查看如果关系操作符有Parrallel属性为非零的值。这些计划也许可以不运行在平行中，但是他们如果系统不忙，他们也适合这样做。
--
-- Find query plans that may run in parallel
--
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
一般来说，查询的持续时间长于CPU时间总量，因为一些时间花费在等待资源上例如锁或物理I/O。查询使用CPU时间长于持续时间的唯一场景是当查询运行在并行计划例如多线程并发使用CPU。注意并不是所有并行查询将证明这种行为（CPU时间大于持续时间）。
select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time
SQL Trace
Look for the following signs of parallel queries, which could be either statements or batches that have CPU time greater than the duration.
select
EventClass,
TextData
from
::fn_trace_gettable('c:\temp\high_cpu_trace.trc', default)
where
EventClass in (10, 12) -- RPC:Completed, SQL:BatchCompleted
and CPU > Duration/1000 -- CPU is in milliseconds, Duration in microseconds
Or can be Showplans (un-encoded) that have Parallelism operators in them
select
EventClass,
TextData
from
::fn_trace_gettable('c:\temp\high_cpu_trace.trc', default)
where
TextData LIKE '%Parallelism%'
[b]解决[/b]
任何运行在并行计划的查询被查询优化器认为是成本昂贵的，并会超过并行阀值，默认为5（粗略的是在涉及的机器上5秒执行一次）。任何通过上述方法确认的查询都是以后要调节的候选者。
• 使用Database Engine Tuning Advisor查看是否任何索引改变，改变索引视图或分区改变能减少查询的开销
• 检查实际值和评估集的重要不同因为评估集在评估查询开销中是重要因素。如果找到重要的不同：
如果auto create statistics数据库设置被禁用，确认在Showplan输出的Warnings列中没有MISSING STATS项。
尝试在关闭评估的表上运行UPDATE STATISTICS。
验证查询没有使用优化器无法精确评估的查询构造，例如多语句表值函数或CLR函数，表值或Transact-SQL变量比较（参数比较是可以的）。
• 评估是否可以使用不同的Transact-SQL语句或表达式将查询写的更有效率
[b]拙劣游标使用[/b]
SQL Server 2005之前的SQL Server 版本仅支持在每个连接上有单个活动的操作。一个查询正在执行或有了结果等待发送到客户端时将被认为是活动的。在一些情形中，客户端应用程序也许需要从结果中读取并向SQL Server提交其他基于刚刚从结果集中读取的行的查询。这在默认的结果集中是不能实现的，因为还有其他等待的结果。一般的解决方法是改变连接属性是用服务器端游标。
当使用服务器端游标，数据库客户端软件(OLE DB提供者或ODBC驱动)显然会封装客户端请求在特殊的扩展存储过程中，例如sp_cursoropen，sp_cursorfetch等等。这提到了API游标（而不是TSQL游标）。当用户执行查询，查询文本通过sp_cursoropen被发送到服务器，请求读取从sp_cursorfetch指示服务器进发送某些数量的行。通过控制获取行的数量，可以为ODBC驱动或OLE DB提供者缓存行。这阻止发生服务器等待客户端都区所有发送的行的情形。因此，服务器可以在这个连接上接受新的请求。
一次性打开游标并获取1行（或少量行）的应用程序能被网络延时的网络瓶颈影响，特别是在广域网（WAN）。在有快速网络并有不同用户连接时，处理很多游标请求的开销变得更重要。因为开销来自于游标位置的变化来适应在结果集上的位置改变，预请求的处理开销，类似的处理，服务器处理1个请求返回100行必处理100不同请求相同的100行但是每次1行更有效率。
[b]检测[/b]
你可以使用下列方法为拙劣游标使用排错。
系统监视器(Perfmon)
通过考虑SQL Server:Cursor Manager By Type – Cursor Requests/Sec计数器，你可以通过这个性能计数器知道有多少游标在系统中使用。系统还有很高的CPU利用率，因为小量的读取通常会有每秒数百个游标请求。这里没有特殊的计数器告诉你关于获取的缓存大小。
[b]DMVs[/b]
接下来的查询可以用于测定使用API游标（不是TSQL游标）连接获取一行使用的缓存大小。它对于大的获取缓存更有效，例如100行。
select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1
and cur.properties LIKE 'API%' -- API cursor (TSQL cursors always have fetch buffer of 1)
[b]SQL 跟踪[/b]
使用包括RPC:Completed事件类的跟踪用于搜索sp_cursorfetch语句。第4个参数的值是通过获取返回的行数。请求返回的最大行数是被指定为与RPC:Starting事件类关联的参数。
解决
• 确定游标是完成操作的最佳方法或是否基于集合这种更有效的操作是可行的。
• 当连接到SQL Server 2005，考虑使用多活动结果集(MARS)
• 参考你使用的API文档决定如何指定游标的获取缓存大小：
ODBC - SQL_ATTR_ROW_ARRAY_SIZE
OLE DB – IRowset::GetNextRows or IRowsetLocate::GetRowsAt
内存瓶颈
这部分给出了低缓存的条件和对不同内存错误诊断方法，可能的原因和排错方法。
[b]背景[/b]
引用不同的内存资源通过使用简单的术语内存。但是却有一些内存资源类型，对于理解和区分特殊的内存资源这是很重要的。
[b]虚拟地址空间和物理内存[/b]
在Microsoft Windows®，每个进程都有自己的虚拟地址空间(VAS)。进程可用所有虚拟地址有VAS的大小决定。VAS的大小依赖于架构（32位或64位）和操作系统。在排错的上下文中，理解虚拟地址空间使用内存资源，了解应用程序可以超出VAS即使在64位平台只要物理地址可能一直可用，这些很重要。
[b]Windows地址扩展和SQL Server[/b]
Windows地址扩展(AWE)是允许32位应用程序跨越32位地址限制操作内存的API。AWE机制技术上不需要64位平台。然而它出现了。内存页通过AWE机制涉及在64位平台上的锁定页。
在32位和64位平台上，内存通过AWE机制分配不能分页出界。这可以有益于应用程序（这是在64位平台上使用AWE机制的原因）。这也影响了系统和其他应用程序可用RAM总数，这可能是有害的影响。因为这个原因为了使用AWE，Lock Pages in Memory权利必须分配该运行SQL Server的账号。
从排错的角度来看，要点是SQL Server缓存池使用AWE映射内存；然而，只有数据库（hash过的）页面可以利用AWE分配内存。通过AWE机制内存分配将不会在任务管理器或在Process: Private Bytes性能计数器中看到。你需要使用SQL Server特殊的计数器或动态管理视图来获取这些信息。
[b]内存压力[/b]
内存压力表示当可用内存数量受到限制。识别SQL Server何时运行在内存压力下将帮助你排除内存相关的问题。SQL Server依赖于不同类型的内存压力特征也不一样。下表汇总了内存压力类型，和他们潜在的原因。在所有的情况下，你可以更多的会见到超时或显示的内存不足错误消息。
Windows有通知的机制 如果物理内存运行在过高或过低的情况下。SQL Server在他的内存管理决策中使用这种机制。
一般排错的步骤显示在表3中。
[b]工具[/b]
下列工具和资源可以用于排错。
• 内存相关的DMVs
• DBCC MEMORYSTATUS 命令
• 性能计数器: 性能监视器或SQL Server指定对象的DMV
• 任务管理器
• 事件查看器: 应用程序日志和系统日志
[b]检测内存压力[/b]
内存压力自身不会预示问题。内存压力是需要的，但时不是为服务器以后遇到内存错误的充分条件。在内存压力下工作将被任务是服务器的正常操作。然而内存压力的征兆可以于是服务器运行已经接近设计容量并且潜在存在内存不足的错误。在正常运行情况下，这些信息将作为基线决定以后内存不足的原因。
[b]外部物理内存压力[/b]
打开人物管理器的性能视图，检查Physical Memory节的Available项的值。如果可用内存总数很低，这表现了有外部内存压力。这个准确值依赖于很多因素，然而你可以在当这个值降低到50-100MB开始查找问题。当这个总数小于10MB时，外部内存压力将表现得很明显。
相同信息也可以使用在系统监视器中的Memory: Available Bytes计数器获取。
如果存在外部内存压力并且你看到了内存相关的错误，你需要确认在系统中主要的内存消耗者。为了这个，考虑Process: Working Set性能计数器或在任务管理器中Process栏中的Mem Usage列，找到最大的内存消耗者。
系统中所有使用的物理内存可以通过汇总下列计数器获取。
• Process 对象，每个进程的 Working Set计数器
• Memory 对象
• 系统的Cache Bytes计数器
• 未分页池的Pool Nonpaged Bytes 计数器
• Available Bytes (等于任务管理其中的Available 值)
如果没有外部压力，Process: Private Bytes计数器或在任务管理器中虚拟内存将接近工作集的大小（Process: Working Set或任务管理器中的Mem Usage），意味着我们没有内存用于分页了。
注意任务管理器中的Mem Usage列和相应的性能计数器不能计算通过AWE分配的内存。这样如果使用AWE，信息将是不完整的。这种情况下，你需要考虑在SQL Server内分配的内存来获取完整的信息。
你可以使用sys.dm_os_memory_clerks DMV找到SQL Server通过AWE机制分配了多少内存，如下所示。
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks
注意在SQL Server中，只有当前缓存池（’MEMORYCLERK_SQLBUFFERPOOL’类型）使用这种机制并且只能是在使用AWE功能时。通过识别和除去主要物理内存使用者（如果有可能）和/或 通过添加更多的内存的方法解除外部内存压力一般可以解决与此相关的内存问题。
[b]外部虚拟内存压力[/b]
你需要确定是否页面文件为当前内存的分配能提供足够的空间。为了检查，可以打开任务管理器中的性能视图，并检查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）。
[b]内部物理内存压力[/b]
内部内存压力来自于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'
每个组件的详细信息可以通过下列语句获取（这包括从缓存池内和缓存池外分配的内存）。
declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- note that this total excludes buffer pool committed memory as it represents the largest consumer which is normal
select
@total_alloc =
sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
print
'Total allocated (including from Buffer Pool): '
+ CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb)) desc
select * from @tab
注意之前的查询将Buffer Pool与通过单页分配器提供给组件的内存视为不同的部分。通过下列查询可以确定在缓存池中耗费内存最多的10个组件（通过单页分配器）。
-- top 10 consumers of memory from BPool
select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc
你通常不会控制内部组件对内存的占用。然而，确定哪个组件占用最多的内存可以帮助对问题的后续研究。
系统监视器(Perfmon)
你也可以通过检查下列计数器来确定内存压力（SQL Server联机丛书中有详细地描述）：
SQL Server: Buffer Manager vL6387D价格对象
• Low Buffer cache hit ratio
• Low Page life expectancy
• High number of Checkpoint pages/sec
• High number Lazy writes/sec
不足的内存和I/O开销经常会导致瓶颈。请查看本文的I/O 瓶颈部分。
[b]高速缓存和内存压力[/b]
查看外部和内部内存压力的方法是查看内存中高速缓存的行为。
SQL Server 2005与SQL Server 2000ISO122U价格的高速缓存设计上有一些细微的不同，其中之一就是统一了高速缓存的框架。为了从高速缓存中删除最近很少使用的项，该框架实现了一套时钟算法。现在它使用2支不同的时钟指针，一个是内部时钟指针，一个是外部时钟指针。
内部时钟指针控制与其他高速缓存相关的缓存大小。当框架预测到高速缓存要使用到尽头是它开始移动。
当SQL Server总体上陷入内存压力时，外部时钟指针开始移动。外部时钟指针的移动可以导致外部和内部的内存压力。在内部和外部内存压力时不会混乱的移动外部时钟和内部时钟。
关于时钟移动的信息可以通过sys.dm_os_memory_cache_clock_hands DMV显示，如下代码所示。每个高速缓存项在内部和外部时钟指针都有不同的行。如果你看到rounds count和removed all rounds count增加，说明服务器遇到内部/外部内存压力。
select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0
通过如下所示，通过通过sys.dm_os_cache_counters DMV你可以获得更多关于缓存的信息。
select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on (cc.cache_address = ch.cache_address)
/*
--uncomment this block to have the information only for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc
注意USERSTORE项，正在使用的页面数量将不会被报告，因为结果将是NULL。
[b]Ring buffers[/b]
更多有意义的内存调试信息可以通过sys.dm_os_ring_buffers的ring buffers DMV获取。每个ring buffer保留了之前几次某种类型的通知。指定ring buffer的详细信息将在下面描述。
[b]RING_BUFFER_RESOURCE_MONITOR[/b]
你可以使用从资源监视器的通知识别内存改变的状态。在内部，SQL Server有一个监视不同内存压力的架构。当内存状态改变，资源监视器任务生成一个通知。这个通知用于内部组件根据内存状态调整它们内存使用并通过sys.dm_os_ring_buffers DMV来暴露，如下列代码所示。
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
结果类似于
RESOURCE_MEMPHYSICAL_LOW
2
0
1646380
432388
0
0
26592
17128
17624
50
3833132
3240228
5732340
5057100
2097024
336760
0
从这些记录来看，你可以减少服务器收到的低物理内存的通知。你也可以查看内存总量（KB为单位）。你可以通过使用SQL Server的XML能力来查询这些信息，例如下列代码。
select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int') as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int') as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') as R(x)
order by
[Time Stamp] desc
上面收到了低内存的通知，缓存池重新计算target。注意target数量保持在指定的min server memory 和max server memory选项限制中。如果缓存池中心的提交比当前的提交缓存还小，缓存池将开始收缩直到外部内存压力被移除。注意，当运行在启用AWE时SQL Server 2000不是这样来缓解物理内存压力的。
[b]RING_BUFFER_OOM[/b]
下列代码示例，展示了ring buffer中包含预示服务器内存不足的记录。
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'
结果类似于：
FAIL_VIRTUAL_COMMIT
4096
这条记录告诉我们那个操作失败了（提交，保留或页面分配）并表明了请求的内存数量。
[b]RING_BUFFER_MEMORY_BROKER and Internal Memory Pressure[/b]
当监测到内部内存压力时，为组件在缓存池分配内存的低内存通知将被打开。打开低内存通知允许从使用缓存池的高速缓存和其他组件中回收页面。
内部内存压力可以通过调整max server memory选项或当stolen页面与缓存池的比例超过80%时触发。
内部内存压力通知(‘Shrink’)能通过使用下列代码查询ring buffer的调用来发现。
select
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//Notification)[1]', 'varchar(100)') as [Last Notification]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER') as R(x)
order by
[Time Stamp] desc
RING_BUFFER_BUFFER_POOL
ring buffer将包含预示严重的缓存池失败的记录，包括缓存池溢出的条件。
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'
结果类似于：
< BufferPoolFailure id="FAIL_OOM">
84344
84350
20
20345
64001
64001
这条记录告诉我们出现了什么样的失败 (FAIL_OOM, FAIL_MAP, FAIL_RESERVE_ADJUST, FAIL_LAZYWRITER_NO_BUFFERS) 基当时的缓存池状态。
[b]内部虚拟内存压力[/b]
VAS的占用可以使用sys.dm_os_virtual_address_dump DMV来跟踪。VAS汇总可以使用下列视图来查询。
-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation base, don't take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with zero allocation base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size
下列查询可以用于访问VAS状态
-- available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0
-- get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
如果最大可用区域小于4MB，我们可能遇到了VAS压力。SQL Server 2005监视和响应VAS压力。SQL Server 2000不会监视从VAS带来的压力，但是当出现虚拟内存不足错误是，它会清理高速缓存。
8645 - A time out occurred while waiting for memory resources to execute the query. Rerun the query.
[b]原因[/b]
这个错误指出在服务器上有很多内存敏感的查询被同时执行。使用排序（ORDER BY）和连接的查询可以在执行过程中会占用大量的内存。如果有高度并行或如果查询操作一个非联合索引的分区表时，查询内存需求会有很大的增加。无法或取所需内存资源的查询在预先确定的超时时间时（默认，超时是25倍的评估查询时间或sp_configure 设置的’query wait’）将收到这个错误。通常，收到这个错误的查询占用内存的那个查询。
[b]排错[/b]
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.
[b]原因[/b]
原因和8645错误类似；它也预示服务器内存过低。min memory per query太高也可能导致这个错误。
排错
1. 根据一般内存错误排错步骤。
2. 验证sp_configure min memory per query 选项设置。
[b]I/O 瓶颈[/b]
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
from sys.dm_os_wait_stats
where wait_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
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
下面是示例的输出。它展示当时在给定的数据库上有3个未决定的I/O 。你可以使用database_id 和 file_id 来找到映射的物理磁盘文件。io_pending_ms_ticks 表现了所有等待在未决定队列中的个别的I/O。
Database_id File_Id io_stall io_pending_ms_ticks scheduler_address
----------------------------------------------------------------------
6 1 10804 78 0x0227A040
6 1 10804 78 0x0227A040
6 2 101451 31 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,
(/2),
( -)/2)
from sys.dm_exec_sql_text (0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB)
statement_start_offest 和statement_end_offset的值需要除以2来补偿SQL Server使用Unicode来存储这种数据的。statement_end_offse值为-1指出语句先前到了批的最后。而substring()函数不识别-1，并将其作为非法值。使用( -)/2,代替-1，一个需要输入64000的值，来确认语句覆盖了所有的情况。通过这种方法，长时间运行或资源消费语句可以被过滤出大型的存储过程或批。
同样的你可以运行下列查询，找到查询计划确定是否当选择了较差的计划时有大量的I/O。
select *
from sys.dm_exec_query_plan
(0x0600050099EC8520A8619803000000000000000000000000)
下列是一些常规步骤，有助于你排除内存错误。
1. 验证是否服务器运行在外部内存压力。如果出现外部内存压力，尝试先解决它，然后再看是否依然存在这个问题或错误。
2. 开始收集性能计数器：SQL Server: Buffer Manager, SQL Server: Memory Manager
3. 确认内存配置参数(sp_configure)， min memory per query，min/max server memory，awe enabled和 Lock Pages in Memory 权利。观察不正常的值。纠正配置。提供为SQL Server 2005增加内存的理由。
4. 检查所有可能影响服务器的非默认sp_configure参数。
5. 检查内部内存压力。
6. 当你见到内存错误消息时，观察DBCC MEMORYSTATUS输出和改变的方法。
7. 检查负载（并发会话数量，并发执行查询数量）。
[b]内存错误[/b]
701 - There is insufficient system memory to run this query.
原因
这是服务器内存不足的典型现象。它预示了内存分配失败。它可以有多种原因导致，包括在当前工作负载上的内存提示。通过增加SQL Server 2005需要的内存和一些配置上的设置（例如max server memory选项）用户可能会看到这种错误。通常失败的事务不是因为这个错误。
排错
不论是否错误是持续的和可重复的（同样状态）或是随机的（显示为随机的不同状态），当你看到这个错误时你需要研究服务器内存分布。当这个错误出现时，可能导致诊断查询失败。可以开始从外部进行评估。接下来的步骤在一般的内存错误排错步骤中描述。
可能的解决方案包括：除去外部内存压力；增加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. 尽可能减少工作负载。
[b]Tempdb[/b]
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的可用空间。
[b]监视tempdb空间[/b]
阻止问题更好的方法是随后马上解决它。你可以使用下列性能监视器来监视使用中的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
注意这些技术不包括其中的混合扩展。页面在混合扩展中可以被分配个别用户和内部对象。
[b]磁盘空间问题排错[/b]
用户对象，内部对象和存储的版本可以都可以导致在tempdb中的空间问题。这节我们会考虑针对每个类别如何排错。
[b]用户对象[/b]
因为用户对象不被特定的会话拥有，你需要理解创建该对象的应用程序的规范并调整需要的tempdb大小。你可以通过执行exec sp_spaceused @objname=''来找到个别用户对象使用的。例如，你可以运行下列脚本枚举所有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
[b]版本存储[/b]
SQL Server 2005提供了行版本架构用于实现一些特性。如下列出了使用行版本架构的特性。更多关于下列特性的信息，请参考SQL Server 联机丛书。
• 触发器
• MARS
• 联机索引
• 基于行版本隔离级别：需要在数据库级设置选项
行版本需要跨会话共享。当行版本被回收时，行版本的创建者没有控制权。你需要找到并杀掉阻止行版本清理的运行最长的事务。
下列查询返回依赖于版本存储运行最长的2个事务。
select top 2
transaction_id,
transaction_sequence_num,
elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds DESC
这是示例的输入，显示了序列号为3，事务ID为8609的事务已经运行了6523秒。
transaction_id transaction_sequence_num elapsed_time_seconds
-------------------- ------------------------ --------------------
8609 3 6523
20156 25 783
因为第2个事务运行了相对短的时间，你可以通过杀掉第1个事务来释放大量的版本存储。可是，没有方法能评估通过杀掉进能释放的版本空间。你也许需要杀掉一些事务来释放更多的空间。
你可以通过改变用于版本存储的tempdb属性或通过尽可能的消除在快照隔离级别的长事务，或在read-committed-snapshot下运行的长查询来减轻这个问题。你能使用下列公式粗略的评估行版本存储的大小。
[Size of version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]
在所有使用了基于孤立级别行版本，为一个事物每分钟生成版本存储的数据和每分钟生成的日志一样。然而这也有一些异常：只有更新的差异部分生成日志；如果使用了批量导入操作并且恢复模式不是完全恢复时，新插入的数据行不依赖日志，则不被记录版本。
你也可以使用Version Generation Rate 和Version Cleanup Rate性能计数器来调整你的计算。如果Version Cleanup Rate为0，这暗示着有长时间运行的事务阻止版本存储的清理。
附带地，在产生tempdb空间不足错误前，SQL Server 2005会做最后一次尝试强制版本存储收缩。在这个收缩过程中，没有生成行版本运行最长的事务会被标识为牺牲者。这可以释放他们使用的版本存储。在错误日志中为牺牲的事务生成一个消息3967，它能不再从版本存储中读取行版本或创建新的版本存储。如果收缩版本存储成功，这样在tempdb中会有更多的可用空间。否则tempdb将耗尽内存。
[b]内部对象[/b]
内部对象在每条语句中被创建和销毁，除非想在前面所描述的。如果你注意到有大量的tempdb空间分配，你将需要了解那个会话或任务占用了空间，然后进肯能做一些矫正的操作。
SQL Server 2005提供了2个额外的DMV：: sys.dm_db_session_space_usage 和sys.dm_db_task_space_usage 来跟踪分配给个别会话和任务所用的tempdb空间。尽管任务运行在会话的上下文，当任务完成后，任务使用的空间还会被占用。你可以使用下列查询来找到为内部对象分配最多的会话。注意这个查询只包括在会话中已完成的任务。
select
session_id,
internal_objects_alloc_page_count,
internal_objects_dealloc_page_count
from sys.dm_db_session_space_usage
order by internal_objects_alloc_page_count DESC
你可以使用下列查询找到分配对象最多的会话，包括正在运行的任务。
SELECT
t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as
deallocated
from sys.dm_db_session_space_usage as t1,
(select session_id,
sum(internal_objects_alloc_page_count)
as task_alloc,
sum (internal_objects_dealloc_page_count) as
task_dealloc
from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
order by allocated DESC
这是示例的输出。
session_id allocated deallocated
---------- -------------------- --------------------
52 5120 5136
51 16 0
一旦你隔离出生成大量对象分配的任务或会话，你能找到任务的那条Transact-SQL语句和它的查询计划来做更详细地分析。
select
t1.session_id,
t1.request_id,
t1.task_alloc,
t1.task_dealloc,
t2.sql_handle,
t2.statement_start_offset,
t2.statement_end_offset,
t2.plan_handle
from (Select session_id,
request_id,
sum(internal_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1,
sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and
(t1.request_id = t2.request_id)
order by t1.task_alloc DESC
这是示例的输出。
session_id request_id task_alloc task_dealloc
---------------------------------------------------------
52 0 1024 1024
sql_handle statement_start_offset
-----------------------------------------------------------------------
0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172 356
statement_end_offset plan_handle
--------------------------------- -1 0x06000500D490961BA8C19503000000000000000000000000
你可以利用如下语句通过sql_handle和plan_handle来得到SQL语句和查询计划：
select text from sys.dm_exec_sql_text(@sql_handle)
select * from sys.dm_exec_query_plan(@plan_handle)
注意当你访问查询计划时，查询计划可能已经不在缓存中了。为保证查询计划可以使用，你应该需要经常为查询计划缓存保存否则该计划会被清除，同时应该将结果尽可能保存在表中，这样用于以后的查询。
当SQL Server 重新启动，tempdb大小将初始化到配置的大小，并基于需求增长。这可以导致tempdb的分裂，这样会招致过多的开销，包括在数据库自动增长时分配新的扩展的阻塞。这能影响你的工作负载性能。建议你重新分配tempdb到一个适当的大小。
[b]过多的DLL和分配操作[/b]
在tempdb中2个原因可以导致这个结果。
• 创建和删除大量的临时表和标变量导致在元数据上的争夺。在SQL Server 2005中，本地的临时表和标变量被缓存来最小化元数据的争用。然而只有下列条件满足时，表才会被缓存。
• 在表中没有命名的约束。
• 在Create语句后在表中没有DDL(例如，Create Index和Create Statistics)。
• 典型情况下，大部分临时/工作表是在堆上；因此插入，删除或删除操作能引起在Page Free Space (PFS) 页面上的严重争用。如果大部分的表小于64KB并且使用了混合扩展来分配或处理位置，这能带来在Share Global Allocation Map(SGAM)页面上的争用。SQL Server 2005为本地的临时表缓存一个数据页和一个IAM页来最小化分配争用。这种缓存在SQL Server 2000中是使用在工作表上的。
因为SGAM和PFS在数据文件中分页出现在固定的间隔，这样很容易找到他们所用资源的描述。例如，2:1:1表示了在tempdb第1个PFS页面(database-id为2，file-id为1，page-id为1)，2:1:3表示了第1个SGAM页面。SGAM页面每511232个页面出现1次，PFS页面每8088个页面出现1次。你能使用这个规则在tempdb的所有文件中找到所有其他的PFS和SGAM页面。无论什么时候，当任务等待占有所有页面时，它将在sys.dm_os_waiting_tasks中显示。因为这种等待是短暂的，你需要频繁的查询这个表（大约每10秒一次）并收集这些数据以后分析。例如，你可以使用下列查询将在tempdb中所有等待页面的任务加载到在分析数据库中的waiting_task表。
-- get the current timestamp
declare @now datetime
select @now = getdate()
-- insert data into a table for later analysis
insert into analysis..waiting_tasks
select
session_id,
wait_duration_ms,
resource_description,
@now
from sys.dm_os_waiting_tasks
where wait_type like ‘PAGE%LATCH_%’ and
resource_description like ‘2:%’
当需要时你可以看到在tempdb页面中等待锁的任务，这样你可以分析是否归咎于PFS或SGAM分页。如果是，这意味着在tempdb上有分配争用。如果你在tempdb上的其他页面争用，如果你确定这个页面属于系统表，这意味着由于过度的DDL导致争用。
你也可以使用下列的性能计数器监视临时对象分配/定位操作得不正常的增加，
• SQL Server:Access Methods\Workfiles Created /Sec
• SQL Server:Access Methods\Worktables Created /Sec
• SQL Server:Access Methods\Mixed Page Allocations /Sec
• SQL Server:General Statistics\Temp Tables Created /Sec
• SQL Server:General Statistics\Temp Tables for destruction
[b]解决[/b]
如果是由于过多的DDL操作导致在tempdb争用，你需要考虑你的应用程序，并查看是否你能减少DDL操作。你可以尝试下列建议。
• 如果你使用存储过程范围内的临时表，考虑是否这些表可以移动到存储过程外。否则每次执行存储过程将会导致创建/删除临时表。
• 查看查询计划，是否一些计划创建大量的临时对象，池，排序或工作表。你需要评估一些临时对象。例如，在一个列上创建一个用于ORDER BY操作的索引可以除去查询时的排序
如果争用是由于在SGAM 和PFS页面上的争用，你可以通过尝试下列操作减轻争用：
• 通过增加tempdb数据文件将等量负载分布在所有磁盘和文件上。理论上，你应该将文件数量设置为CPU数量等同（主要考虑亲和性）。
• 使用TF-1118消除混合扩展分配。
[b]运行缓慢的查询[/b]
缓慢或长时间运行的查询可以占用过多资源并能导致阻塞查询。
过多的资源占用是没有限制CPU资源的使用，但是也能包括I/O存储带宽和内存带宽。即使SQL Server查询被设计为可以通过合理WHERE子句限制结果集的方法避免整表扫描 ，如果没有合适的索引支持这个特殊的查询，他们可能不会按照我们期望的方式执行。同样，WHERE子句能依赖于用户输入被动态的通过应用程序构建。假设存在的索引不能覆盖所有可能的约束。通过Transact-SQL语句占用过度的CPU，I/O和内存的情况在本白皮书前面已经描述了。
除了缺失索引外，也可能有索引没有被使用。当所有的索引不得不维护时，这不影响查询的性能，但是影响DML查询。
因为等待逻辑锁和系统资源的状态会阻塞查询，查询也会运行的比较缓慢。阻塞的原因可能是较差的应用程序设计，坏的查询计划，缺乏有用的索引和不正确的SQL Server实例配置。
这节主要集中在缓慢查询的2个原因-阻塞和索引问题。
[b]阻塞[/b]
阻塞主要是等待逻辑锁，例如等待在资源上获取排他锁或等待从更低级别的同步结果，例如闩。
当做出一个在已经锁定的资源上获得一个不兼容的锁的请求产生时，逻辑锁等待发生。在特殊的Transact-SQL语句运行时，通过使用锁可以基于事务隔离级别提供数据一致性的功能，这样给最终用户的感觉是SQL Server运行缓慢。当查询被阻塞时，它不占用任何系统资源，所以你将发现它运行很长时间但是资源占用却很少。更多关于并发控制和阻塞的信息请查看SQL Server联机丛书。
如果你的系统没有被配置为处理这种负载就会导致等待底层的原始同步。
一般阻塞和等待的场景是：
• 识别阻塞者
• 识别长的阻塞
• 阻塞每个对象
• 页面闭锁问题
• 阻塞影响整体性能
如果系统资源（或锁）当前不能服务于请求，这个SQL Server会话将被置于等待状态。换句话说，资源有一个等待请求的队列。DMV能提供任何等待资源的会话的信息。
SQL Server 2005提供了更详细和一致的等待信息，有大约125种等待类型而SQL Server 2000只有76种可用的等待类型。DMV提供的信息范围从sys.dm_os_wait_statistics中表现SQL Server全面和积累的等待信息，到sys.dm_os_waiting_tasks中与会话相关分解的等待信息。下列DMV提供了详细的等待某些资源的任务等待队列。它同样表现了在系统中所有的等待队列。例如你可以运行下列查询找到关于阻塞会话56的详细信息。
select * from sys.dm_os_waiting_tasks where session_id=56
waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address blocking_task_address blocking_session_id blocking_exec_context_id resource_description
-------------------- ---------- --------------- -------------------- ------------------------------------------------------------ ------------------ --------------------- ------------------- ------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x022A8898 56 0 1103500 LCK_M_S 0x03696820 0x022A8D48 53 NULL ridlock fileid=1 pageid=143 dbid=9 id=lock3667d00 mode=X associatedObjectId=72057594038321152
这个结果显示了会话56被会话54阻塞了，会话56已经为一个锁等待了1103500毫秒。
为了找到准许的锁或等待锁的会话，你可以使用sys.dm_tran_locks DMV。每行数据展现了发送到锁管理器的当前活动的请求。为了有序的锁，准许请求指出了锁已经在资源上被准许给请求者。一个等待的请求指出了请求没有被准许。例如下列查询显示会话56被阻塞在资源1:143:3，该资源被会话53的X模式锁占有。
select
request_session_id as spid,
resource_type as rt,
resource_database_id as rdb,
(case resource_type
WHEN 'OBJECT' then object_name(resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
resource_description as rd,
request_mode as rm,
request_status as rs
from sys.dm_tran_locks
Here is the sample output
spid rt rdb objname rd rm rs
----------------------------------------------------------------------------- 56 DATABASE 9 S GRANT
53 DATABASE 9 S GRANT
56 PAGE 9 t_lock 1:143 IS GRANT
53 PAGE 9 t_lock 1:143 IX GRANT
53 PAGE 9 t_lock 1:153 IX GRANT
56 OBJECT 9 t_lock IS GRANT
53 OBJECT 9 t_lock IX GRANT
53 KEY 9 t_lock (a400c34cb X GRANT
53 RID 9 t_lock 1:143:3 X GRANT
56 RID 9 t_lock 1:143:3 S WAIT
实际上，你能连接上面的2个DMV，就像使用存储过程sp_block锁展示的。在图1种阻塞报告列出了被阻塞的会话和阻塞它的会话。你可以在附录B中找到sp_block的源代码。如果你需要添加/删除在可选择列表中的列时，你可以根据需求修改存储过程。可选的@spid参数提供了在锁请求和阻塞这个spid的会话信息。
图1: sp_block 报表
在SQL Server 2000中，你能通过下列语句查看被阻塞的spid信息。
select * from master..sysprocesses where blocked <> 0.
联合锁可以通过存储过程sp_lock存储过程。
[b]识别长时间的阻塞[/b]
之前我们提到，在SQL Server中阻塞是很正常的，使用逻辑锁来维护事务一致性的。然而当等待的锁超过了阀值，它会影响响应时间。为了识别长时间运行的阻塞，你能使用BlockedProcessThreshold配置参数来建立一个用户配置的服务端阻塞阀值。阀值定义一个秒级的间隔。任何超过阀值的阻塞将出发事件并被SQL Trace捕获。
例如，1个200秒的阻塞进程阀值可以在SQL Management Studio中配置。例如：
Execute Sp_configure ‘blocked process threshold’, 200
[b]Reconfigure with override[/b]
一旦阻塞处理阀值被建立，下一步是捕获跟踪的事件。跟踪阻塞超过用户配置的阀值事件可以通过SQL Trace 或Profiler捕获。
1. 如果使用SQL Trace，使用sp_trace_setevent过程，event_id参数为137
2. 如果使用SQL Server Profiler，选择Blocked Process Report 事件类（在Error和Warnings对象下），如图2。
注意这是一个轻量级的跟踪，事件仅在当锁超过阀值，或发生死锁时被捕获。每有200秒的间隔一个锁被阻塞，1个跟踪事件被触发。图3意味着1个锁阻塞了600秒，发生了3次跟踪事件。
跟踪事件包括阻塞者和被阻塞者整个SQL语句。图中所示”Update Customer”阻塞了”Select from Customer”语句。
通过与SQL Server 2000比较，检查长期阻塞场景代码在Sysprocesses并在后续处理结果。知识库文章271509包含了一段可以用于监视阻塞的示例代码。
[b]通过sys.dm_db_index_operational_stats查看阻塞的每个对象[/b]
新的SQL Server 2005 DMV Sys.dm_db_index_operational_stats提供了全面的索引使用统计，包括阻塞。根据阻塞，它提供了每个表，索引，分区的锁统计的详细信息。例如，在给定索引和表上的访问历史，锁数量（row_lock_count），阻塞数量（row_lock_wait_count）和等待时间(row_lock_wait_in_ms)等信息。这个DMV包括的类型信息有：
• 占有的锁的数量，例如行或页。
• 阻塞或等待的数量，例如，行或页。
• 阻塞或等待持续的时间，例如行或页。
• 页面上闩的等待。
• page_latch_wait持续时间：这包括特殊页上的争用，升序键的插入。在这种情况，热点是最后的页面，所以多个写入这到最后的页面每次尝试获取高级的页面闩在同样的时间。这将作为Pagelatch waits暴露。
• page_io_latch_wait持续的时间：当用户请求一个不在缓存池的页面时发生的I/O闩。一个慢速的I/O子系统或过多工作的I/O子系统将遇到很高的PageIOlatch等待，这实际上是I/O问题。这个问题被混在于缓存清除和缺失索引中。
• 页面闩等待持续的时间。
除了阻塞相关信息外，这还有额外的信息。
• 访问类型, 包括range, singleton lookups.
• 在页级的插入，更新，删除。
• 在页级之上插入，更新或删除。在叶上的活动是索引维护。在每个叶级页面中的第一行有这个级别之上的条目。如果新的页面被分配到叶级别上，这级别之上将为新的叶级页面的第一行创建新的项。
• 在叶级别的页面合并将表现为重新分配的空页，因为行已经删除了。
• 索引维护。在叶级上页面合并就是将空白页重分配，这导致在叶上行被删除，因此留下的中间级别页面是空白的。在叶级页面的第一行有一个条目在该层上。如果在叶级别删除了足够的行，原来包含第一行叶级页面条目的中间层索引页面也会是空白的。这导致在叶结点上的合并发生。
这些信息积累了从实例启动以来的信息。信息不会一直保留直到实例被重新启动，并且没有其他的方法可以重置它。这个DMV返回的数据仅在元数据缓存对象表现的堆或索引可用的情况下存在。只要堆和索引的元数据被加载到了元数据缓存，每个列的这个值将被设置为0。统计是被累加的直到缓存对象被从元数据缓存中删除。然而，你可以周期性的查询这个表来收集在表中的信息，用于更进一步的分析。
附录B定义一套存储过程可以用于收集索引操作的数据。你能分析你感兴趣的时间短的数据。这里是如何使用定义的存储过程的步骤。
1. 使用init_index_operational_stats初始化indexstats 表
2. 使用insert_indexstats. 捕获基线数据
3. 运行负载
4. 通过使用insert_indexstats. 捕获最后索引统计
5. 为了分析收集的索引统计，运行存储过程get_indexstats来生成每个索引上锁的平均数(索引和分区的row_lock_count)，阻塞和等待。高的blocking %和/或高的平均等待指出设计不好的索引或查询公式。
这有一些示例展现了你能使用这些存储过程的类型
• 获取所有数据库上使用率最高的5个索引
exec get_indexstats
@dbid=-1,
@top='top 5',
@columns='index, usage',
@order='index usage'
• 获取前5个索引锁提示（所有列）
exec get_indexstats
@dbid=-1,
@top='top 5',
@order='index lock promotions',
@threshold='[index lock promotion attempts] > 0'
• 获取5个平均行锁等待大于2ms的独立查询，返回行包括wait,scan,singleton信息。以'singleton lookups'排序
exec get_indexstats
@dbid=5,
@top='top 5',
@columns='wait,scan,singleton',
@order='singleton lookups',
@threshold='[avg row lock wait ms] > 2'
• 获取所有数据库行锁等待大于1IRFU9024，列包含‘avg,wait’，以'row lock wait ms'排序
exec get_indexstats
@dbid=-1,
@top='top 10 ',
@columns='wait,row',
@order='row lock wait ms',
@threshold='[row lock waits] > 1'
• 获取前5个索引状态，通过'avg row lock wait ms'排序
exec get_indexstats
@dbid=-1,
@top='top 5',
@order='avg row lock wait ms'
• 获取前5个索引状态，通过'avg page latch wait ms'排序
exec get_indexstats
@dbid=-1,
@top='top 5',
@order='avg page latch wait ms'
• 获取前 5%索引状态, 通过 avg pageio latch waits.
exec get_indexstats
@dbid=-1,
@top='top 3 percent',
@order='avg pageio latch wait ms',
@threshold='[pageio latch waits] > 0'
• 获取所有在数据库5中的索引状态并且block% > 0.1,以block%排序。
exec get_indexstats
@dbid=-1,
@top='top 10',
@order='block %',
@threshold='[block %] > 0.1'
如图4示例，阻塞分析报告
SQL Server 2000部提供任何对象或索引的统计利用信息。
[b]使用SQL waits阻塞对整体性能的影响[/b]
SQL Server 2000提供了76种等待类型来提供等待报告。SQL Server 2005提供了多余100个等待类型来跟踪应用程序性能。任何时间1个用户连接在等待时，SQL Server会累加等待时间。例如应用程序请求资源例如I/O，锁或内存，可以等待资源直到可用。这些等待信息可以跨所有连接将被汇总和分类，所以性能配置可以从给定的负载获得。因此，SQL等待类型从应用程序负载或用户观点识别和分类用户（或线程）等待。
这个查询列出了在SQL Server中前10位的等待。这些等待时累积的，但是你可以使用DBCC SQLPERF ([sys.dm_os_wait_stats], clear)重置这个计数器。
select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc
下列是输出，要注意几个关键点：
• 一些等待是正常的例如后台线程的等待，例如lazy writer组件。
• 一些会话为获取共享锁等待很长时间
• 信号等待是在一个工作线程获取对资源访问到它被拿到CPU上调度执行这段时间。长时间的信号等待也许意味着很高的CPU争用。
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
------------------ -------------------- -------------------- -------------------- -------
LAZYWRITER_SLEEP 415088 415048437 1812 156
SQLTRACE_BUFFER_FLUSH 103762 415044000 4000 0
LCK_M_S 6 25016812 23240921 0
WRITELOG 7413 86843 187 406
LOGMGR_RESERVE_APPEND 82 82000 1000 0
SLEEP_BPOOL_FLUSH 4948 28687 31 15
LCK_M_X 1 20000 20000 0
PAGEIOLATCH_SH 871 11718 140 15
PAGEIOLATCH_UP 755 9484 187 0
IO_COMPLETION 636 7031 203 0
为了分析等待状态，你需要获取数据，用于以后分析。附录B提供了2个示例的存储过程。
• Track_waitstats.收集数据渴望的采样数量和采样的时间间隔。这里有一个调用的示例
exec dbo.track_waitstats @num_samples=6
,@delay_interval=30
,@delay_type='s'
,@truncate_history='y'
,@clear_waitstats='y'
• Get_waitstats.分析前面步骤收集到的数据。这有一个调用的示例。
exec [dbo].[get_waitstats_2005]
• Spid运行，需要当前不可用的资源。因为资源不可用，在T0时，它移动到资源等待列表。
• 信号指出资源是可用的，所以spid在T1时间移动到可运行队列。
• Spid等候运行状态直到T2，同样的CPU通过可运行队列处理按顺序到达等待。
你可以使用这些存储过程分析资源等待和信号等待，使用这些信息隔离资源争用。
图5 显示了示例的报告。
图5中现实的等待状态分析报告预示了由于阻塞(LCK_M_S)和内存分配(RESOURCE_SEMAPHORE)的性能问题。特定的55%的所有等待是等待共享锁，而43%是由于内存请求。阻塞每个对象的分析将是识别主要的争用点。
[b]监视索引的使用[/b]
其他方面的查询性能与DML查询，查询删除，插入和修改数据相关。在指定表上定义更多索引，在需要数据修改时就需要更多的资源。由于锁结合持续事务，时间长的修改操作可以损害并发性。因此在应用程序中使用那个索引就变得非常重要。你能计算出是否在数据库架构上有大量从未使用过的索引存在。
SQL Server 2005提供了新的sys.dm_db_index_usage_stats动态管理视图显示哪些索引是使用的，和是否他们被用于用户查询或仅用于系统操作。每次执行查询，在这个视图中的列将根据用于执行查询的查询计划将会增加。当SQL Server启动并运行，数据就被收集了。这个DMV中的数据只是保存在内存中的，没有持久化。所以当SQL Server实例关闭，数据将会丢失。你可以周期性的获取这个表，并将数据保存用于以后分析。
在索引操作被分为用户类型和系统类型。用户类型引用SELECT和INSERT/DELETE/UPDATE操作。系统类型操作是类似于DBCC这样的命令或DDL命令或是update statistics。每种类别的语句列被区分为：
• 依靠索引的SEEK操作 (user_seeks or system_seeks)
• 依靠索引的LOOKUP操作(user_lookups or system_lookups)
• 依靠索引的SCAN操作(user_scans or system_scans)
• 依靠索引的UPDATE操作(user_updates or system_updates)
每种访问索引都会记路最后一次访问的时间戳。一个索引本身通过3列识别，database_id，object_id和index_id。然而，index_id=0代表是一个堆表，index_id=1代表时集束索引，反之index_id>1但表是非集束索引。
一个整天运行的数据库应用程序，从sys.dm_db_index_usage_stats中得到的索引访问信息列表将增长。
下列是在SQL Server 2005使规则和任务的定义：
• SEEK: 识别用于访问数据的B树结构数量。不论B树结构只是访问每级只有少量页面来获取一个数据行，还是是表中使用半索引页面读取如几个G数据或百万行的数据。所以我们希望在这个类别有更多的累计。
• SCAN: 识别不使用B树索引获取数据的数据表数量。没有任何索引定义的表属于这种情况。有索引定义但是在执行语句查询时并没有使用这些说印的表也属于这种情况。
• LOOKUP: 识别在一个集束索引通过’seeking’在一个非集束索引上查询数据，2个索引都定义在同一张表上。这种场景描述在SQL Server 2000中的书签查询。它表现了这样一个场景，非集束索引被用于访问表，并且非集束索引没有覆盖查询的列和索引列没有在WHERE子句定义，SQL Server将使用非集束索引列的user_seeks值加上使用集束索引列的user_lookups值。这个值能变得很高如果多个非集束索引在这个表上定义。如果依靠集束索引的user_seeks值高，user_lookups的数量也会很高，加上一部分user_seeks也是很高，应该通过将非集束索引大量的高于集束索引。
下列DMV查询可以被用于获取在所有数据库中所有对象上关于索引使用信息。
select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id
你能看到下列结果：
object_id index_id user_seeks user_scans user_lookups
------------ ------------- -------------- -------------- -----------------
521690298 1 0 251 123
521690298 2 123 0 0
在这种情况有251次查询的执行直接访问数据层表而不使用索引。有123次查询的执行通过使用非集束索引访问表，但是没有覆盖查询选择列表或在WHERE子句指定列，因为我们看到了123次在集束索引的lookup访问。
最有趣的类别着眼于‘user type statement’类型。使用方法指出在‘system category’可以被看作为存在索引的结果。如果索引不存在，它不会更新统计，也不需要检查一致性。因此分析需要着眼于4列显示独立语句的使用或分析用户应用程序。
为了获取从上次SQL Server启动以来，关于指定表没有使用的索引信息，这种查询将在数据库上下文中执行。
select i.name
from sys.indexes i
where i.object_id=object_id('') and
i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id =)
所有没有被使用的索引仍可以通过下列语句获取信息：
select object_name(object_id), i.name
from sys.indexes i
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id =)
order by object_name(object_id) asc
在正中情况下，表名称和索引名称根据表明排序。
.动态管理视图的真正目的是在长时间运行时观察索引的使用情况。它可以提供视图的快照或查询结果集，将其存储，然后每天比较相应的改变。如果你能识别特殊的索引数月没有使用或者在很长时间没有使用，你可以最终从数据库中删除他们。
总结
更多信息请见
http://www.microsoft.com/sql/
附录A: DBCC MEMORYSTATUS 描述
这有一些使用DBCC MEMORYSTATUS命令的信息。可是，一些信息也可以使用动态管理视图(DMVs)获取。
SQL Server 2000 DBCC MEMORYSTATUS在http://support.microsoft.com/?id=271624中描述
SQL Server 2005 DBCC MEMORYSTATUS 在http://support.microsoft.com/?id=907877中描述
附录B: 阻塞脚本
附录提供在本白皮书中引用的存储过程源代码列表。你可以根据你的需求修改或裁减这些存储过程。
sp_block
create proc dbo.sp_block (@spid bigint=NULL)
as
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at -- http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- This proc reports blocks
-- 1. optional parameter @spid
--
select
t1.resource_type,
'database'=db_name(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address and
t1.request_session_id = isnull(@spid,t1.request_session_id)
分析操作的索引统计
这套存储过程可以用于分析索引的使用。
get_indexstats
create proc dbo.get_indexstats
(@dbid smallint=-1
,@top varchar(100)=NULL
,@columns varchar(500)=NULL
,@order varchar(100)='lock waits'
,@threshold varchar(500)=NULL)
as
--
-- This stored procedure is provided "AS IS" with no warranties, and confers no rights.
-- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- This proc analyzes index statistics including accesses, overhead,
-- locks, blocks, and waits
--
-- Instructions: Order of execution is as follows:
-- (1) truncate indexstats with init_indexstats
-- (2) take initial index snapshot using insert_indexstats
-- (3) Run workload
-- (4) take final index snapshot using insert_indexstats
-- (5) analyze with get_indexstats
-- @dbid limits analysis to a database
-- @top allows you to specify TOP n
-- @columns is used to specify what columns from
-- sys.dm_db_index_operational_stats will be included in the report
-- For example, @columns='scans,lookups,waits' will include columns
-- containing these keywords
-- @order used to order results
-- @threshold used to add a threshold,
-- example: @threshold='[block %] > 5' only include if blocking is over 5%
--
------ definition of some computed columns returned
-- [blk %] = percentage of locks that cause blocks e.g. blk% = 100 * lock waits / locks
-- [index usage] = range_scan_count + singleton_lookup_count + leaf_insert_count
-- [nonleaf index overhead]=nonleaf_insert_count + nonleaf_delete_count + nonleaf_update_count
-- [avg row lock wait ms]=row_lock_wait_in_ms/row_lock_wait_count
-- [avg page lock wait ms]=page_lock_wait_in_ms/page_lock_wait_count
-- [avg page latch wait ms]=page_latch_wait_in_ms/page_latch_wait_count
-- [avg pageio latch wait ms]=page_io_latch_wait_in_ms/page_io_latch_wait_count
---------------------------------------------------------------------------------------------------
--- Case 1 - only one snapshot of sys.dm_db_operational_index_stats was stored in
--- indexstats. This is an error - return errormsg to user
--- Case 2 - beginning snapshot taken, however some objects were not referenced
--- at the time of the beginning snapshot. Thus, they will not be in the initial
--- snapshot of sys.dm_db_operational_index_stats, use 0 for starting values.
--- Print INFO msg for informational purposes.
--- Case 3 - beginning and ending snapshots, beginning values for all objects and indexes
--- this should be the normal case, especially if SQL Server is up a long time
---------------------------------------------------------------------------------------------------
set nocount on
declare @orderby varchar(100), @where_dbid_is varchar(100), @temp varchar(500), @threshold_temptab varchar(500)
declare @cmd varchar(max),@col_stmt varchar(500),@addcol varchar(500)
declare @begintime datetime, @endtime datetime, @duration datetime, @mincount int, @maxcount int
select @begintime = min(now), @endtime = max(now) from indexstats
if @begintime = @endtime
begin
print 'Error: indexstats contains only 1 snapshot of sys.dm_db_index_operational_stats'
print 'Order of execution is as follows: '
print ' (1) truncate indexstats with init_indexstats'
print ' (2) take initial index snapshot using insert_indexstats'
print ' (3) Run workload'
print ' (4) take final index snapshot using insert_indexstats'
print ' (5) analyze with get_indexstats'
return -99
end
select @mincount = count(*) from indexstats where now = @begintime
select @maxcount = count(*) from indexstats where now = @endtime
if @mincount < @maxcount
begin
print 'InfoMsg1: sys.dm_db_index_operational_stats only contains entries for objects referenced since last SQL re-cycle'
print 'InfoMsg2: Any newly referenced objects and indexes captured in the ending snapshot will use 0 as a beginning value'
end
select @top = case
when @top is NULL then ''
else lower(@top)
end,
@where_dbid_is = case (@dbid)
when -1 then ''
else ' and i1.database_id = ' + cast(@dbid as varchar(10))
end,
--- thresholding requires a temp table
@threshold_temptab = case
when @threshold is NULL then ''
else ' select * from #t where ' + @threshold
end
--- thresholding requires temp table, add 'into #t' to select statement
select @temp = case (@threshold_temptab)
when '' then ''
else ' into #t '
end
select @orderby=case(@order)
when 'leaf inserts' then 'order by [' + @order + ']'
when 'leaf deletes' then 'order by [' + @order + ']'
when 'leaf updates' then 'order by [' + @order + ']'
when 'nonleaf inserts' then 'order by [' + @order + ']'
when 'nonleaf deletes' then 'order by [' + @order + ']'
when 'nonleaf updates' then 'order by [' + @order + ']'
when 'nonleaf index overhead' then 'order by [' + @order + ']'
when 'leaf allocations' then 'order by [' + @order + ']'
when 'nonleaf allocations' then 'order by [' + @order + ']'
when 'allocations' then 'order by [' + @order + ']'
when 'leaf page merges' then 'order by [' + @order + ']'
when 'nonleaf page merges' then 'order by [' + @order + ']'
when 'range scans' then 'order by [' + @order + ']'
when 'singleton lookups' then 'order by [' + @order + ']'
when 'index usage' then 'order by [' + @order + ']'
when 'row locks' then 'order by [' + @order + ']'
when 'row lock waits' then 'order by [' + @order + ']'
when 'block %' then 'order by [' + @order + ']'
when 'row lock wait ms' then 'order by [' + @order + ']'
when 'avg row lock wait ms' then 'order by [' + @order + ']'
when 'page locks' then 'order by [' + @order + ']'
when 'page lock waits' then 'order by [' + @order + ']'
when 'page lock wait ms' then 'order by [' + @order + ']'
when 'avg page lock wait ms' then 'order by [' + @order + ']'
when 'index lock promotion attempts' then 'order by [' + @order + ']'
when 'index lock promotions' then 'order by [' + @order + ']'
when 'page latch waits' then 'order by [' + @order + ']'
when 'page latch wait ms' then 'order by [' + @order + ']'
when 'pageio latch waits' then 'order by [' + @order + ']'
when 'pageio latch wait ms' then 'order by [' + @order + ']'
else ''
end
if @orderby <> '' select @orderby = @orderby + ' desc'
select
'start time'=@begintime,
'end time'=@endtime,
'duration (hh:mm:ss:ms)'=convert(varchar(50),
@endtime-@begintime,14),
'Report'=case (@dbid)
when -1 then 'all databases'
else db_name(@dbid)
end +
case
when @top = '' then ''
when @top is NULL then ''
when @top = 'none' then ''
else ', ' + @top
end +
case
when @columns = '' then ''
when @columns is NULL then ''
when @columns = 'none' then ''
else ', include only columns containing ' + @columns
end +
case(@orderby)
when '' then ''
when NULL then ''
when 'none' then ''
else ', ' + @orderby
end +
case
when @threshold = '' then ''
when @threshold is NULL then ''
when @threshold = 'none' then ''
else ', threshold on ' + @threshold
end
select @cmd = ' select i2.database_id, i2.object_id, i2.index_id, i2.partition_number '
select @cmd = @cmd +' , begintime=case min(i1.now) when max(i2.now) then NULL else min(i1.now) end '
select @cmd = @cmd +' , endtime=max(i2.now) '
select @cmd = @cmd +' into #i '
select @cmd = @cmd +' from indexstats i2 '
select @cmd = @cmd +' full outer join '
select @cmd = @cmd +' indexstats i1 '
select @cmd = @cmd +' on i1.database_id = i2.database_id '
select @cmd = @cmd +' and i1.object_id = i2.object_id '
select @cmd = @cmd +' and i1.index_id = i2.index_id '
select @cmd = @cmd +' and i1.partition_number = i2.partition_number '
select @cmd = @cmd +' where i1.now >= ''' + convert(varchar(100),@begintime, 109) + ''''
select @cmd = @cmd +' and i2.now = ''' + convert(varchar(100),@endtime, 109) + ''''
select @cmd = @cmd + ' ' + @where_dbid_is + ' '
select @cmd = @cmd + ' group by i2.database_id, i2.object_id, i2.index_id, i2.partition_number '
select @cmd = @cmd + ' select ' + @top + ' i.database_id, db_name=db_name(i.database_id), object=isnull(object_name(i.object_id),i.object_id), indid=i.index_id, part_no=i.partition_number '
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf inserts]=i2.leaf_insert_count -
isnull(i1.leaf_insert_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,@col_stmt=' ,
[leaf deletes]=i2.leaf_delete_count –
isnull(i1.leaf_delete_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf updates]=i2.leaf_update_count - isnull(i1.leaf_update_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf inserts]=i2.nonleaf_insert_count - isnull(i1.nonleaf_insert_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf deletes]=i2.nonleaf_delete_count - isnull(i1.nonleaf_delete_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf updates]=i2.nonleaf_update_count - isnull(i1.nonleaf_update_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf index overhead]=(i2.nonleaf_insert_count –
isnull(i1.nonleaf_insert_count,0)) + (i2.nonleaf_delete_count - isnull(i1.nonleaf_delete_count,0)) + (i2.nonleaf_update_count - isnull(i1.nonleaf_update_count,0))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf allocations]=i2.leaf_allocation_count - isnull(i1.leaf_allocation_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf allocations]=i2.nonleaf_allocation_count - isnull(i1.nonleaf_allocation_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[allocations]=(i2.leaf_allocation_count - isnull(i1.leaf_allocation_count,0)) + (i2.nonleaf_allocation_count - isnull(i1.nonleaf_allocation_count,0))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf page merges]=i2.leaf_page_merge_count - isnull(i1.leaf_page_merge_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf page merges]=i2.nonleaf_page_merge_count - isnull(i1.nonleaf_page_merge_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[range scans]=i2.range_scan_count - isnull(i1.range_scan_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing= @columns,
@col_stmt=' ,[singleton lookups]=i2.singleton_lookup_count - isnull(i1.singleton_lookup_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[index usage]=(i2.range_scan_count - isnull(i1.range_scan_count,0)) + (i2.singleton_lookup_count - isnull(i1.singleton_lookup_count,0)) + (i2.leaf_insert_count - isnull(i1.leaf_insert_count,0))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[row locks]=i2.row_lock_count - isnull(i1.row_lock_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[row lock waits]=i2.row_lock_wait_count - isnull(i1.row_lock_wait_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[block %]=cast (100.0 * (i2.row_lock_wait_count - isnull(i1.row_lock_wait_count,0)) / (1 + i2.row_lock_count - isnull(i1.row_lock_count,0)) as numeric(5,2))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[row lock wait ms]=i2.row_lock_wait_in_ms - isnull(i1.row_lock_wait_in_ms,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg row lock wait ms]=cast ((1.0*(i2.row_lock_wait_in_ms - isnull(i1.row_lock_wait_in_ms,0)))/(1 + i2.row_lock_wait_count - isnull(i1.row_lock_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page locks]=i2.page_lock_count - isnull(i1.page_lock_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page lock waits]=i2.page_lock_wait_count - isnull(i1.page_lock_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page lock wait ms]=i2.page_lock_wait_in_ms - isnull(i1.page_lock_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg page lock wait ms]=cast ((1.0*(i2.page_lock_wait_in_ms - isnull(i1.page_lock_wait_in_ms,0)))/(1 + i2.page_lock_wait_count - isnull(i1.page_lock_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[index lock promotion attempts]=i2.index_lock_promotion_attempt_count - isnull(i1.index_lock_promotion_attempt_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[index lock promotions]=i2.index_lock_promotion_count - isnull(i1.index_lock_promotion_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page latch waits]=i2.page_latch_wait_count - isnull(i1.page_latch_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page latch wait ms]=i2.page_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg page latch wait ms]=cast ((1.0*(i2.page_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)))/(1 + i2.page_latch_wait_count - isnull(i1.page_latch_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[pageio latch waits]=i2.page_io_latch_wait_count - isnull(i1.page_latch_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[pageio latch wait ms]=i2.page_io_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg pageio latch wait ms]=cast ((1.0*(i2.page_io_latch_wait_in_ms - isnull(i1.page_io_latch_wait_in_ms,0)))/(1 + i2.page_io_latch_wait_count - isnull(i1.page_io_latch_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
select @cmd = @cmd + @temp
select @cmd = @cmd + ' from #i i '
select @cmd = @cmd + ' left join indexstats i1 on i.begintime = i1.now and i.database_id = i1.database_id and i.object_id = i1.object_id and i.index_id = i1.index_id and i.partition_number = i1.partition_number '
select @cmd = @cmd + ' left join indexstats i2 on i.endtime = i2.now and i.database_id = i2.database_id and i.object_id = i2.object_id and i.index_id = i2.index_id and i.partition_number = i2.partition_number '
select @cmd = @cmd + ' ' + @orderby + ' '
select @cmd = @cmd + @threshold_temptab
exec ( @cmd )
go
insert_indexstats
create proc insert_indexstats (@dbid smallint=NULL,
@objid int=NULL,
@indid int=NULL,
@partitionid int=NULL)
as
--
-- This stored procedure is provided "AS IS" with no warranties, and confers no rights.
-- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
-- This stored procedure stores a snapshot of sys.dm_db_index_operational_stats into the table indexstas
-- for later analysis by the stored procedure get_indexstats. Please note that the indexstats table has an additional
-- column to store the timestamp when the snapshot is taken
--
-- T. Davidson
-- snapshot sys.dm_db_index_operational_stats
--
declare @now datetime
select @now = getdate()
insert into indexstats
(database_id
,object_id
,index_id
,partition_number
,leaf_insert_count
,leaf_delete_count
,leaf_update_count
,leaf_ghost_count
,nonleaf_insert_count
,nonleaf_delete_count
,nonleaf_update_count
,leaf_allocation_count
,nonleaf_allocation_count
,leaf_page_merge_count
,nonleaf_page_merge_count
,range_scan_count
,singleton_lookup_count
,forwarded_fetch_count
,lob_fetch_in_pages
,lob_fetch_in_bytes
,lob_orphan_create_count
,lob_orphan_insert_count
,row_overflow_fetch_in_pages
,row_overflow_fetch_in_bytes
,column_value_push_off_row_count
,column_value_pull_in_row_count
,row_lock_count
,row_lock_wait_count
,row_lock_wait_in_ms
,page_lock_count
,page_lock_wait_count
,page_lock_wait_in_ms
,index_lock_promotion_attempt_count
,index_lock_promotion_count
,page_latch_wait_count
,page_latch_wait_in_ms
,page_io_latch_wait_count
,page_io_latch_wait_in_ms,
now)
select database_id
,object_id
,index_id
,partition_number
,leaf_insert_count
,leaf_delete_count
,leaf_update_count
,leaf_ghost_count
,nonleaf_insert_count
,nonleaf_delete_count
,nonleaf_update_count
,leaf_allocation_count
,nonleaf_allocation_count
,leaf_page_merge_count
,nonleaf_page_merge_count
,range_scan_count
,singleton_lookup_count
,forwarded_fetch_count
,lob_fetch_in_pages
,lob_fetch_in_bytes
,lob_orphan_create_count
,lob_orphan_insert_count
,row_overflow_fetch_in_pages
,row_overflow_fetch_in_bytes
,column_value_push_off_row_count
,column_value_pull_in_row_count
,row_lock_count
,row_lock_wait_count
,row_lock_wait_in_ms
,page_lock_count
,page_lock_wait_count
,page_lock_wait_in_ms
,index_lock_promotion_attempt_count
,index_lock_promotion_count
,page_latch_wait_count
,page_latch_wait_in_ms
,page_io_latch_wait_count
,page_io_latch_wait_in_ms
,@now
from sys.dm_db_index_operational_stats(@dbid,@objid,@indid,@partitionid)
go
init_index_operational_stats
CREATE proc dbo.init_index_operational_stats
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
--
-- create indexstats table if it doesn't exist, otherwise truncate
--
set nocount on
if not exists (select 1 from dbo.sysobjects where id=object_id(N'[dbo].[indexstats]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table dbo.indexstats (
database_id smallint NOT NULL
,object_id int NOT NULL
,index_id int NOT NULL
,partition_number int NOT NULL
,leaf_insert_count bigint NOT NULL
,leaf_delete_count bigint NOT NULL
,leaf_update_count bigint NOT NULL
,leaf_ghost_count bigint NOT NULL
,nonleaf_insert_count bigint NOT NULL
,nonleaf_delete_count bigint NOT NULL
,nonleaf_update_count bigint NOT NULL
,leaf_allocation_count bigint NOT NULL
,nonleaf_allocation_count bigint NOT NULL
,leaf_page_merge_count bigint NOT NULL
,nonleaf_page_merge_count bigint NOT NULL
,range_scan_count bigint NOT NULL
,singleton_lookup_count bigint NOT NULL
,forwarded_fetch_count bigint NOT NULL
,lob_fetch_in_pages bigint NOT NULL
,lob_fetch_in_bytes bigint NOT NULL
,lob_orphan_create_count bigint NOT NULL
,lob_orphan_insert_count bigint NOT NULL
,row_overflow_fetch_in_pages bigint NOT NULL
,row_overflow_fetch_in_bytes bigint NOT NULL
,column_value_push_off_row_count bigint NOT NULL
,column_value_pull_in_row_count bigint NOT NULL
,row_lock_count bigint NOT NULL
,row_lock_wait_count bigint NOT NULL
,row_lock_wait_in_ms bigint NOT NULL
,page_lock_count bigint NOT NULL
,page_lock_wait_count bigint NOT NULL
,page_lock_wait_in_ms bigint NOT NULL
,index_lock_promotion_attempt_count bigint NOT NULL
,index_lock_promotion_count bigint NOT NULL
,page_latch_wait_count bigint NOT NULL
,page_latch_wait_in_ms bigint NOT NULL
,page_io_latch_wait_count bigint NOT NULL
,page_io_latch_wait_in_ms bigint NOT NULL
,now datetime default getdate())
else truncate table dbo.indexstats
go
add_column
create proc dbo.add_column (
@add_stmt varchar(500) output,
@find varchar(100)=NULL,
@cols_containing varchar(500)=NULL,
@col_stmt varchar(max))
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- @add_stmt is the result passed back to the caller
-- @find is a keyword from @cols_containing
-- @cols_containing is the list of keywords to include in the report
-- @col_stmt is the statement that will be compared with @find.
-- If @col_stmt contains @find, include this statement.
-- set @add_stmt = @col_stmt
--
declare @length int, @strindex int, @EOS bit
if @cols_containing is NULL
begin
select @add_stmt=@col_stmt
return
end
select @add_stmt = '', @EOS = 0
while @add_stmt is not null and @EOS = 0
@dbid=-1,
select @strindex = charindex(',',@cols_containing)
if @strindex = 0
select @find = @cols_containing, @EOS = 1
else
begin
select @find = substring(@cols_containing,1,@strindex-1)
select @cols_containing =
substring(@cols_containing,
@strindex+1,
datalength(@cols_containing) - @strindex)
end
select @add_stmt=case
--when @cols_containing is NULL then NULL
when charindex(@find,@col_stmt) > 0 then NULL
else ''
end
end
--- NULL indicates this statement is to be passed back through out parm @add_stmt
if @add_stmt is NULL select @add_stmt=@col_stmt
go
等待状态
这套存储过程可以在SQL Server中分析锁。
track_waitstats_2005
CREATE proc [dbo].[track_waitstats_2005] (
@num_samples int=10,
@delay_interval int=1,
@delay_type nvarchar(10)='minutes',
@truncate_history nvarchar(1)='N',
@clear_waitstats nvarchar(1)='Y')
as
--
-- This stored procedure is provided "AS IS" with no warranties, and confers no rights.
-- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- @num_samples is the number of times to capture waitstats, default is 10 times
-- default delay interval is 1 minute
-- delaynum is the delay interval - can be minutes or seconds
-- delaytype specifies whether the delay interval is minutes or seconds
-- create waitstats table if it doesn't exist, otherwise truncate
-- Revision: 4/19/05
--- (1) added object owner qualifier
--- (2) optional parameters to truncate history and clear waitstats
set nocount on
if not exists (select 1
from sys.objects
where object_id = object_id ( N'[dbo].[waitstats]') and
OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
create table [dbo].[waitstats]
([wait_type] nvarchar(60) not null,
[waiting_tasks_count] bigint not null,
[wait_time_ms] bigint not null,
[max_wait_time_ms] bigint not null,
[signal_wait_time_ms] bigint not null,
now datetime not null default getdate())
If lower(@truncate_history) not in (N'y',N'n')
begin
raiserror ('valid @truncate_history values are ''y'' or ''n''',16,1) with nowait
end
If lower(@clear_waitstats) not in (N'y',N'n')
begin
raiserror ('valid @clear_waitstats values are ''y'' or ''n''',16,1) with nowait
end
If lower(@truncate_history) = N'y'
truncate table dbo.waitstats
If lower (@clear_waitstats) = N'y'
-- clear out waitstats
dbcc sqlperf ([sys.dm_os_wait_stats],clear) with no_infomsgs
declare @i int,
@delay varchar(8),
@dt varchar(3),
@now datetime,
@totalwait numeric(20,1),
@endtime datetime,
@begintime datetime,
@hr int,
@min int,
@sec int
select @i = 1
select @dt = case lower(@delay_type)
when N'minutes' then 'm'
when N'minute' then 'm'
when N'min' then 'm'
when N'mi' then 'm'
when N'n' then 'm'
when N'm' then 'm'
when N'seconds' then 's'
when N'second' then 's'
when N'sec' then 's'
when N'ss' then 's'
when N's' then 's'
else @delay_type
end
if @dt not in ('s','m')
begin
raiserror ('delay type must be either ''seconds'' or ''minutes''',16,1) with nowait
return
end
if @dt = 's'
begin
select @sec = @delay_interval % 60, @min = cast((@delay_interval / 60) as int), @hr = cast((@min / 60) as int)
end
if @dt = 'm'
begin
select @sec = 0, @min = @delay_interval % 60, @hr = cast((@delay_interval / 60) as int)
end
select @delay= right('0'+ convert(varchar(2),@hr),2) + ':' +
+ right('0'+convert(varchar(2),@min),2) + ':' +
+ right('0'+convert(varchar(2),@sec),2)
if @hr > 23 or @min > 59 or @sec > 59
begin
select 'delay interval and type: ' + convert (varchar(10),@delay_interval) + ',' + @delay_type + ' converts to ' + @delay
raiserror ('hh:mm:ss delay time cannot > 23:59:59',16,1) with nowait
return
end
while (@i <= @num_samples)
begin
select @now = getdate()
insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
@now
from sys.dm_os_wait_stats
insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
'Total',
sum([waiting_tasks_count]),
sum([wait_time_ms]),
0,
sum([signal_wait_time_ms]),
@now
from [dbo].[waitstats]
where now = @now
select @i = @i + 1
waitfor delay @delay
end
--- create waitstats report
execute dbo.get_waitstats_2005
go
exec dbo.track_waitstats @num_samples=6
,@delay_interval=30
,@delay_type='s'
,@truncate_history='y'
,@clear_waitstats='y'
get_waitstats_2005
CREATE proc [dbo].[get_waitstats_2005] (
@report_format varchar(20)='all',
@report_order varchar(20)='resource')
as
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- this proc will create waitstats report listing wait types by
-- percentage.
-- (1) total wait time is the sum of resource & signal waits,
-- @report_format='all' reports resource & signal
-- (2) Basics of execution model (simplified)
-- a. spid is running then needs unavailable resource, moves to
-- resource wait list at time T0
-- b. a signal indicates resource available, spid moves to
-- runnable queue at time T1
-- c. spid awaits running status until T2 as cpu works its way
-- through runnable queue in order of arrival
-- (3) resource wait time is the actual time waiting for the
-- resource to be available, T1-T0
-- (4) signal wait time is the time it takes from the point the
-- resource is available (T1)
-- to the point in which the process is running again at T2.
-- Thus, signal waits are T2-T1
-- (5) Key questions: Are Resource and Signal time significant?
-- a. Highest waits indicate the bottleneck you need to solve
-- for scalability
-- b. Generally if you have LOW% SIGNAL WAITS, the CPU is
-- handling the workload e.g. spids spend move through
-- runnable queue quickly
-- c. HIGH % SIGNAL WAITS indicates CPU can't keep up,
-- significant time for spids to move up the runnable queue
-- to reach running status
-- (6) This proc can be run when track_waitstats is executing
--
-- Revision 4/19/2005
-- (1) add computation for CPU Resource Waits = Sum(signal waits /
-- total waits)
-- (2) add @report_order parm to allow sorting by resource, signal
-- or total waits
--
set nocount on
declare @now datetime,
@totalwait numeric(20,1),
@totalsignalwait numeric(20,1),
@totalresourcewait numeric(20,1),
@endtime datetime,@begintime datetime,
@hr int,
@min int,
@sec int
if not exists (select 1
from sysobjects
where id = object_id ( N'[dbo].[waitstats]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
raiserror('Error [dbo].[waitstats] table does not exist',
16, 1) with nowait
return
end
if lower(@report_format) not in ('all','detail','simple')
begin
raiserror ('@report_format must be either ''all'',
''detail'', or ''simple''',16,1) with nowait
return
end
if lower(@report_order) not in ('resource','signal','total')
begin
raiserror ('@report_order must be either ''resource'',
''signal'', or ''total''',16,1) with nowait
return
end
if lower(@report_format) = 'simple' and lower(@report_order) <> 'total'
begin
raiserror ('@report_format is simple so order defaults to ''total''',
16,1) with nowait
select @report_order = 'total'
end
select
@now=max(now),
@begintime=min(now),
@endtime=max(now)
from [dbo].[waitstats]
where [wait_type] = 'Total'
--- subtract waitfor, sleep, and resource_queue from Total
select @totalwait = sum([wait_time_ms]) + 1, @totalsignalwait = sum([signal_wait_time_ms]) + 1
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total' ,'WAITFOR',
'***total***') and
now = @now
select @totalresourcewait = 1 + @totalwait - @totalsignalwait
-- insert adjusted totals, rank by percentage descending
delete waitstats
where [wait_type] = '***total***' and
now = @now
insert into waitstats
select
'***total***',
0,@totalwait,
0,
@totalsignalwait,
@now
select 'start time'=@begintime,'end time'=@endtime,
'duration (hh:mm:ss:ms)'=convert(varchar(50),@endtime-@begintime,14),
'report format'=@report_format, 'report order'=@report_order
if lower(@report_format) in ('all','detail')
begin
----- format=detail, column order is resource, signal, total. order by resource desc
if lower(@report_order) = 'resource'
select [wait_type],[waiting_tasks_count],
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as numeric(20,1)),
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as numeric(20,1)),
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'res_wt_%' desc
----- format=detail, column order signal, resource, total. order by signal desc
if lower(@report_order) = 'signal'
select [wait_type],
[waiting_tasks_count],
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as numeric(20,1)),
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as numeric(20,1)),
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'sig_wt_%' desc
----- format=detail, column order total, resource, signal. order by total desc
if lower(@report_order) = 'total'
select
[wait_type],
[waiting_tasks_count],
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1)),
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as numeric(20,1)),
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'wt_%' desc
end
else
---- simple format, total waits only
select
[wait_type],
[wait_time_ms],
percentage=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by percentage desc
---- compute cpu resource waits
select
'total waits'=[wait_time_ms],
'total signal=CPU waits'=[signal_wait_time_ms],
'CPU resource waits % = signal waits / total waits'=
cast (100*[signal_wait_time_ms]/[wait_time_ms] as numeric(20,1)),
now
from [dbo].[waitstats]
where [wait_type] = '***total***'
order by now
go
declare @now datetime
select @now = getdate()
select getdate()
[b]目录[/b]
简介
目标
方法
资源瓶颈
解决资源瓶颈的工具
CPU 瓶颈
过多的编译和重编译
检测
解决
效率低的查询计划
检测
解决
内部查询的并行
检测
解决
拙劣游标使用
检测
解决
内存瓶颈
背景
虚拟地址空间和物理内存
Windows地址扩展和SQL Server
内存压力
检测内存压力
外部物理内存压力
外部虚拟内存压力
内部物理内存压力
高速缓存和内存压力
Ring buffers
内部虚拟内存压力
一般的内存错误排错步骤
内存错误
I/O 瓶颈
解决
Tempdb
监视tempdb空间
磁盘空间问题排错
用户对象
版本存储
内部对象
过多的DLL和分配操作
解决
运行缓慢的查询
阻塞
识别长时间的阻塞
通过sys.dm_db_index_operational_stats查看阻塞的每个对象
使用SQL waits阻塞对整体性能的影响
监视索引的使用
总结
附录A: DBCC MEMORYSTATUS 描述
附录B: 阻塞脚本
分析操作的索引统计
等待状态
[b]简介[/b]
很多客户偶尔会遇到SQL Server 数据库性能下降。原因可能涉及从不良好的数据库设计到不正确的负载配置。作为一个管理员，你应该预先阻止或最小化问题，并当问题发生时，诊断原因并尽可能的做出正确的操作来解决问题。这片白皮书所述的问题通常来源于Microsoft® Corporation 的Customer Support Service（CSS or PSS）部门所遇到的，因为将所有可能的问题都详尽的分析是不合实际的。我们提供了按部就班的指导，通过使用可用的工具例如SQL Server Profiler，System Monitor和在SQL Server 2005中新的Dynamic Management View来为一般的性能问题诊断和排错。
[b]目标[/b]
.这篇文章的主要目标是提供一套常规的方法通过使用公开的工具在一般的客户场景中诊断和排错SQL Server性能问题。
SQL Server 2005在用户支持上有了很大的提高。内核层（SQL-OS）被重新架构过，内部结构和统计数据通过动态管理视图(DMVs)暴露为关系型行集。SQL Server 2000通过像sysprocesses这样的系统表暴露一些信息，但是有时你需要将SQL Server进程内存映射为物理文件并从中抽取内部结构的相关信息。这里有2个主要的问题。第一，客户不能总是提供物理映射文件，因为文件的尺寸原因，并且这个过程很耗时。第二，这将花费更长的时间诊断问题，因为文件必须传回Microsoft Corporation来分析。
这带给我们本文的第二个目标，展示DMVs的优点。DMVs通过除去大多数情况下需要的生成和分析物理映射步骤可以加速调试的过程。本文尽可能的提供了和SQL Server 2000中同样问题的比较。DMVs提供为获取关键系统信息的简单而熟悉的界面。这些信息也可以用于监视目的，警告管理员潜在的问题。或者也可以被周期性的收集为以后的分析所用。
[b]方法[/b]
这里有很多降低SQL Server速度的原因。我们使用下列3个主要症状来诊断问题。
• 资源瓶颈: CPU，内存，和I/O瓶颈是在本文中主要涉及的。这里我们不考虑网络因素。对每种资源瓶颈，我们会描述如何识别问题并阐述可能的原因。例如，内存瓶颈可以导致过多的分页，最后影响性能。
• Tempdb 瓶颈:因为每个SQL Server 实例只有一个tempdb，这可以产生性能和磁盘空间的瓶颈。不好的应用程序在过多的DDL和DML操作会使tempdb过载。这导致其他在这台服务器上运行的不相关的应用程序运行缓慢或失败。
• 缓慢运行的用户查询:现有的查询性能下降或新的查询显示比预期时间更长。这可能有很多原因。例如：
• 改变统计信息可以导致现有查询的较差的查询计划。
• 制表扫描，降低查询性能。
• 即使资源利用正常由于阻塞也可以导致应用程序运行缓慢。
过多的阻塞可能是由于不良的应用程序设计或架构设计或者是选择了错误的事务隔离级别的原因。
这些症状的原因不需要每个都独立出来。不良的查询计划选择可以使系统资源加重并导致整体性能的下降。所以，如果大表缺失的有用的索引，或查询优化器没有选择它，这样不仅导致查询缓慢，也会导致将不需要数据页读取到内存（buffer pool）中在缓存中存储，这样会加重I/O子系统的压力。同样的，频繁运行查询的重编译可以导致CPU的压力。
[b]资源瓶颈[/b]
接下来的部分讨论了CPU，内存和I/O子系统资源及这些瓶颈如何产生。（网络问题不在本文的讨论范围）每个资源瓶颈，我们描述了如何识别问题并找到原因。例如，一个内存瓶颈可以导致过渡分页，将会影响性能。
在决定你遇到资源瓶颈前，你需要了解在普通环境下资源是如何使用的。你可以使用在这片白皮书中所列出的方法收集有关资源使用的基线信息（即使你没有遇到性能问题）。
你也许找到问题是一个资源运行到设计容量并且SQL Server当前的配置不能支持这种负载。为了解决这问题，你也许需要添加处理能力，内存或增加I/O的带宽或网络通道。但是，在你进行下一步之前，理解这种资源瓶颈的通常原因是非常重要的。有一些解决方案不需要添加额外资源，例如重新配置。
[b]解决资源瓶颈的工具[/b]
下列工具是在解决特殊性能瓶颈时经常使用的工具。
• 系统监视器(PerfMon)：这是Windows所带的一个工具。更多信息，请见系统监视器文档。
• SQL Server Profiler：在SQL Server 2005程序组中的Performance Tools组中可以看到SQL Server Profiler。
• DBCC 命令：详细信息请见SQL Server联机丛书中和附录A。
• DMVs: 详细信息请见SQL Server联机丛书。
[b]CPU 瓶颈[/b]
当没有额外负载，突然发生的CPU瓶颈通常由于没有查询计划，不良的配置或设计因素和不足的硬件资源所引起。在购买更快或更多处理器前，你需要首先确定CPU的最大处理能力带宽并查看是否他们都在使用中。
系统监视器一般是确定CPU资源的最好工具。你应该查看Processor:% Processor Time计数器是否偏高；该计数器值超过80%一般被认为是瓶颈。你也可以使用sys.dm_os_schedulers视图来监视是否正在运行的任务不是0。非0的值预示有任务需要等待时间切片来运行；这个数值高表明一个CPU瓶颈的征兆。你可以用下列查询列出所有的调度器并产看等待运行的任务数量。
select
scheduler_id,
current_tasks_count,
runnable_tasks_count
from
sys.dm_os_schedulers
where
scheduler_id < 255
下列查询给你一个查看当前缓存中最耗费CPU的批或过程高级别视图。查询根据具有相同plan handle（意味着属于同一个批或过程）的语句聚合CPU的调用。如果给出的plan handle对应多个语句，你将不得不继续找到在整个CPU使用中最占用资源的查询。
select top 50
sum(qs.total_worker_time) as total_cpu_time,
sum(qs.execution_count) as total_execution_count,
count(*) as number_of_statements,
qs.plan_handle
from
sys.dm_exec_query_stats qs
group by qs.plan_handle
order by sum(qs.total_worker_time) desc
本节剩下的部分将讨论一些其他的在SQL Server 中CPU敏感的操作，也有有效的检查和解决这些问题的方法。
[b]过多的编译和重编译[/b]
当批或远端过程调用(RPC)被提交到SQL Server，在开始执行前，服务器检查查询计划的有效性和正确性。如果这些检查中的一个失败时，批将被再次编译声称不同的查询计划。这种编译就是所说的重编译。当服务器确认当底层数据改变时有更优化的查询计划时，重编译确认正确性再完成编译。编译是CPU敏感的，因此过多的重编译将导致在系统中的CPU性能问题。
在SQL Server 2000中，当SQL Server重编译一个存储过程，整个存储过程都被重编译，不仅是触发重编译的语句。SQL Server 2005引入了语句级的存储过程的重编译。当SQL Server 2005重编译存储过程，只有导致重编译的语句被编译-而不是整个存储过程。这减少了CPU的负载并减少了对例如COMPILE锁的资源争用。重编译可以有多种原因出发，例如：
• 架构变化
• 统计变化
• 延期编译
• Set属性改变
• 临时表改变
• 存储过程创建时使用了RECOMPLIE查询提示或使用了OPTION （RECOMPILE）。
[b]检测[/b]
你可以使用系统监视器（PerfMon）或SQL Trace(SQL Server Profiler)来检测过多的编译和重编译。
[b]系统监视器(Perfmon)[/b]
SQL Statistics对象提供了监视重编译的计数器和发送到SQL Server实例的请求类型。你必须监视查询编译和重编译的数量以及相关联的接受的批的数量来找出是否这个编译是最耗费CPU资源。理想环境下，SQL Recompilations/sec和Batch Request/sec的比值应该很低，除非用户提交了大量的单独查询。
下列显示了关键的数据计数器。
• SQL Server: SQL Statistics: Batch Requests/sec
• SQL Server: SQL Statistics: SQL Compilations/sec
• SQL Server: SQL Statistics: SQL Recompilations/sec
更多信息请看SQL Server联机丛书中的 “SQL Statistics Object”
[b]SQL 跟踪[/b]
如果PerfMon计数器显出了很高的重编译数量，编译将在SQL Server中占用很多的CPU资源。我们将需要查看Profiler 跟踪并从中找到找到被重编译的存储过程。SQL Server Profiler跟踪给出我们重编译原因的信息。你可以使用下列事件。
SP:Recompile和SQL:StmtRecompile事件类指出了哪个存储过程和语句被重编译。当你编译一个存储过程，一个事件为这个存储过程生成，其中每条语句将被编译。然而，当存储过程重编译时，只有导致重编译的语句被重编译（在SQL Server 2000中将是整个存储过程）。下面列出了SP:Recompile事件类更多重要的数据列。特别是EventSubClass数据列决定重编译的原因。SP:Recompile当存储过程或触发器被重编译被触发一次，但不会被独立查询引发。在SQL Server 2005中，监视SQL:StmtRecompiles也非常有用，该事件类在所有类型的重编译中都会被触发，包括批，独立查询，存储过程和触发器。如下是我们关系的事件中关键的数据列：
• EventClass
• EventSubClass
• ObjectID (represents stored procedure that contains this statement)
• SPID
• StartTime
• SqlHandle
• TextData
更多信息，请见SQL Server 联机丛书中“SQL:StmtRecompile Event Class”。
如果你有保存的跟踪文件，你可以使用下列查询查看所有捕捉的重编译事件。
select
spid,
StartTime,
Textdata,
EventSubclass,
ObjectID,
DatabaseID,
SQLHandle
from
fn_trace_gettable ( 'e:\recompiletrace.trc' , 1)
where
EventClass in(37,75,166)
事件类37是 Sp:Recompile， 75 是 CursorRecompile， 166是SQL:StmtRecompile
你可以通过SqlHandle和ObjectID列或其他列将这个查询的结果分组，也可以查看是否最多的重编译类型是存储过程或其他原因（例如SET选项改变等）。
[b]Showplan XML For Query Compile. [/b]
Showplan XML For Query Compile事件发生于Microsoft SQL Server编译或重编译一段SQL语句时。该事件有关于语句编译或重编译的信息。信息包括查询计划和过程的对象ID。捕获这些事件是有性能开销的，因为它捕获了每次编译或重编译。如果你在系统监视其中看到很高的SQL Compilations/sec计数器值，你应该监视这个事件。通过这些信息，你可以看到那条语句被频繁的重编译。你可以使用这些信息改变这些语句的参数。这将影响重编译的数量。
[b]DMVs.[/b]
当你使用sys.dm_exec_query_optimizer_info DMV，你可以得到SQL Server花费在优化的时间。如果获取了这个DMV的2个快照，你可以得到在给定的时间段内花费在查询优化的时间。
select *
from sys.dm_exec_query_optimizer_info
counter occurrence value
---------------- -------------------- ---------------------
optimizations 81 1.0
elapsed time 81 6.4547820702944486E-2
特别是查看elapsed time，该时间由于优化而产生。因为优化过程的时间基本上就是用户优化操作的CPU时间（因为优化处理是CPU时间的主要部分），你可以得到一个好的度量，找到那段编译时间占用了大量的CPU时间。
其他包含有用信息的DMV有：
sys.dm_exec_query_stats.
你希望查看的数据列有 :
• Sql_handle
• Total worker time
• Plan generation number
• Statement Start Offset
更多信息请查看SQL Server 联机丛书相关主题
[b]sys.dm_exec_query_stats.[/b]
特别是plan_generation_num预示了查询编译时的次数。下面是示例给你展示了被重编译次数最多的25个存储过程。
select *
from sys.dm_exec_query_optimizer_info
select top 25
sql_text.text,
sql_handle,
plan_generation_num,
execution_count,
dbid,
objectid
from
sys.dm_exec_query_stats a
cross apply sys.dm_exec_sql_text(sql_handle) as sql_text
where
plan_generation_num >1
order by plan_generation_num desc
[b]解决[/b]
如果你监测到过多的编译/重编译，考虑下列选项。
• 如果重编译因为SET选项改变而发生，使用SQL Server Profiler确定哪个SET选项被改编。避免在存储过程中改变SET选项。如果改变最好在连接级别设置。确认在该连接的生存周期内不要改变SET选项。
在临时表上重编译的阀值比在普通表上的低。在临时表上的重编译时由于统计改变而引起，你可以降临时表改为使用表变量。表变量的改变不会引起重编译。这种方法的确定是查询优化器不识别表变量，因为统计不会被创建或维护表变量。这将导致没有查询计划。你可以测试不同的选项，并选择最好的方法。另外一个选项时使用KEEP PLAN查询提示。设置临时表的这个阀值与使用永久表相同。EventSubclass 列预示了在临时表上的”Statistics Changed”的操作。
• 为避免由于改变统计而产生的重编译（例如，因为数据统计导致计划不理想），特别是KEEPFIXED PLAN查询提示。根据设置的影响，重编译可以仅因为相关正确的原因（例如，当底层表结构改变导致计划不再适用），而不根据统计的变化。如果语句引用的表的架构改变时或者表是被标记为sp_recompile的存储过程，重编译将发生。
• 关闭自动更新索引统计和表或视图的统计，防止由于对象的统计改变而产生的重编译。注意，通过使用这种方法关闭”auto-stats”特性不是一个好的想法。这是因为查询优化器不再为在这些对象上的数据改变而敏感，将导致不良的查询计划。使用这种方法仅在尝试了所有其他选择之后，做为最后的手段。
• 批应该尽量使用对象全名（例如，dbo.Table1）避免重编译并避免不明确的对象。
• 为避免由于延期编译导致的重编译，不要混杂DML和DDL或从条件结构创建DDL，例如IF语句。
• 运行Database Engine Tuning Advisor(DTA)查看改变索引是否可以改善编译时间和查询的执行时间。
• 检查是否存储过程通过WITH RECOMPILE选项创建或使用了RECOMPILE查询提示。如果过程通过WITH RECOMPILE选项创建，在SQL Server 2005中，如果在过程中特殊的语句需要被重编译，我们可以利用语句级的RECOMPILE提示。这将避免在每次执行的时候对整个过程重编译，而同时允许个别语句被编译。更多有关RECOMPILE提示的信息，请查看SQL Server联机丛书。
[b]效率低的查询计划[/b]
当为一个查询生成查询计划时，SQL Server查询优化器尝试选择一个计划为查询提供最快的响应时间。注意最快的查询时间并不意味最小的I/O开销，也不意味使用最少的CPU资源-它会在各种资源中平衡。
某些操作类型比其他操作对CPU更敏感。Hash操作和Sort操作扫描他们各自的输入数据。使用扫描向前读取（prefetch）时，在需要操作页面前，页面几乎都在缓存中。因此可以减少或消除物理I/O操作。这使这些操作的类型将不被物理I/O所限制。与之相比，嵌套循环连接有很多索引查找，如果索引查找使用很多不同的表以至于页面不适合缓存的大小，将导致生成I/O负载。
最有意义的输入优化用于评估为每中操作生成不同查询计划开销的评估，你可以在Showplan（EstimateRows和EstimateExecution属性）中看到结果。没有精确的评估，用于优化的主输入是有缺陷的。
[b]检测[/b]
低效率的查询计划通常可以被检测出来。低效率的查询计划可以导致增加CPU的消耗。
查询sys.dm_exec_query_stats是确定哪个查询累计使用CPU时间最多的有效方法。
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.[text]
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time desc) as highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc
还可以选择，也可以查询sys.dm_exec_cached_plans并通过使用过滤器查找可疑的类似于‘%Hash Match%’, ‘%Sort%’这样CPU敏感的各种操作。
[b]解决[/b]
如果你监测到效率低的查询计划，考虑下列选项。
• 使用Database Engine Tuning Advisor调节查询，查看是否生成对修改索引的建议
• 检查有问题的评估。
编写的查询中使用的更有限制性的WHERE从句是否合适？无限制条件的查询是资源敏感的。
在查询中涉及的表上运行UPDATE STATISTICS，检查是否还有这种问题。
是否查询使用的构造导致优化器不能精确的评估？
考虑是否可以将查询修改为其他的方法，避免这种问题。
• 如果不能修改架构或查询，SQL Server 2005有一个新的查询计划特性，允许你将指定查询提示添加到满足某种文本的查询中。这可以用在独立查询中，也可以用在存储过程内。例如OPTION(OPTIMIZE FOR)这样的提示允许你影响评估而忘记所有列出的潜在计划。其他的提示，类似OPTION(FORCE ORDER)或 OPITON(USE PLAN)允许你改变控制查询计划的程度。
[b]内部查询的并行[/b]
当为一个查询生成执行计划时，SQL Server优化器尝试为该查询选择最快的相应计划。如果查询的开销超过了在cost threshold for parallelism选项中指定的值，并行不会被禁用，优化器尝试生成一个可以用于并行的计划。并行查询计划使用多线程处理查询，每个线程分布在可用的CPU上并同时利用每个CPU的时间资源。最大的并行度可以通过服务器上的max degree of parallelism选项或每个查询使用OPTION(MAXDOP)提示限制。
用于执行实际并行度（DOP）的结果——度量有多少线程将在给定的操作上并行——是知道执行时才能确定。在执行查询前，SQL Server 2005决定有多少个调度器未充分利用并为查询选择DOP来充分利用剩余的调度器。一旦一个DOP被选择了，直到完成，查询将使用这个选择的并行度来运行。并行查询的使用时CPU有一些偏高，但是它在elapsed time上的时间很短。如果没有其他瓶颈，类似于物理I/O等待，并行计划将会使用所有处理器的100%资源。
查询开始执行后，一个关键的因素（系统有多空闲）可以导致运行并行计划的改变。例如，如果查询运行在空闲时间，服务器可以选择使用并行计划并使用DOP为4，在4个不同的处理器上产生线程。一旦这种线程开始执行，现存的连接可以提交其他需要大量CPU的查询。在这种情况，所有不同的线程将共享可用的CPU的时间切片，导致更高的查询持续时间。
通过并行计划运行不是一定是不好的，并行可以为查询提供最快的响应时间。然而，给定查询的响应时间必须与整体的吞吐量和系统其他查询的响应进行衡量。并行查询一般最适合批处理和决策支持系统，而不适合一个事务处理环境。
[b]检测[/b]
内部查询的并行问题可以通过下列方法检测。
[b]系统监视器(Perfmon)[/b]
考虑SQL Server:SQL Statistics – Batch Requests/sec 计数器，并查看SQL Server联机丛书中的“SQL Statistics Object”获取更多信息。
因为在考虑使用并行计划前，查询必须评估开销超过为并行配置设置的开销阀值（默认被设置为5），服务器每秒处理的批小于运行在并行计划中的批。运行很多并行查询的服务器一般配置为较小的每秒批请求数（例如，小于100的值）。
[b]DMVs[/b]
在运行的服务器上，你可以使用下列查询确认在给定会话中是否可以并行运行任何活动的请求。
select
r.session_id,
r.request_id,
max(isnull(exec_context_id, 0)) as number_of_workers,
r.sql_handle,
r.statement_start_offset,
r.statement_end_offset,
r.plan_handle
from
sys.dm_exec_requests r
join sys.dm_os_tasks t on r.session_id = t.session_id
join sys.dm_exec_sessions s on r.session_id = s.session_id
where
s.is_user_process = 0x1
group by
r.session_id, r.request_id,
r.sql_handle, r.plan_handle,
r.statement_start_offset, r.statement_end_offset
having max(isnull(exec_context_id, 0)) > 0
通过这些信息，查询的文本可以通过使用sys.dm_exec_sql_text轻松获取，而查询计划可以使用sys.dm_exec_cached_plan获取。
你也可以搜索符合运行在并行的计划。这可以通过搜索缓存的计划来查看如果关系操作符有Parrallel属性为非零的值。这些计划也许可以不运行在平行中，但是他们如果系统不忙，他们也适合这样做。
--
-- Find query plans that may run in parallel
--
select
p.*,
q.*,
cp.plan_handle
from
sys.dm_exec_cached_plans cp
cross apply sys.dm_exec_query_plan(cp.plan_handle) p
cross apply sys.dm_exec_sql_text(cp.plan_handle) as q
where
cp.cacheobjtype = 'Compiled Plan' and
p.query_plan.value('declare namespace p="http://schemas.microsoft.com/sqlserver/2004/07/showplan";
max(//p:RelOp/@Parallel)', 'float') > 0
一般来说，查询的持续时间长于CPU时间总量，因为一些时间花费在等待资源上例如锁或物理I/O。查询使用CPU时间长于持续时间的唯一场景是当查询运行在并行计划例如多线程并发使用CPU。注意并不是所有并行查询将证明这种行为（CPU时间大于持续时间）。
select
qs.sql_handle,
qs.statement_start_offset,
qs.statement_end_offset,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.text
from
sys.dm_exec_query_stats qs
cross apply sys.dm_exec_sql_text(qs.plan_handle) as q
where
qs.total_worker_time > qs.total_elapsed_time
SQL Trace
Look for the following signs of parallel queries, which could be either statements or batches that have CPU time greater than the duration.
select
EventClass,
TextData
from
::fn_trace_gettable('c:\temp\high_cpu_trace.trc', default)
where
EventClass in (10, 12) -- RPC:Completed, SQL:BatchCompleted
and CPU > Duration/1000 -- CPU is in milliseconds, Duration in microseconds
Or can be Showplans (un-encoded) that have Parallelism operators in them
select
EventClass,
TextData
from
::fn_trace_gettable('c:\temp\high_cpu_trace.trc', default)
where
TextData LIKE '%Parallelism%'
[b]解决[/b]
任何运行在并行计划的查询被查询优化器认为是成本昂贵的，并会超过并行阀值，默认为5（粗略的是在涉及的机器上5秒执行一次）。任何通过上述方法确认的查询都是以后要调节的候选者。
• 使用Database Engine Tuning Advisor查看是否任何索引改变，改变索引视图或分区改变能减少查询的开销
• 检查实际值和评估集的重要不同因为评估集在评估查询开销中是重要因素。如果找到重要的不同：
如果auto create statistics数据库设置被禁用，确认在Showplan输出的Warnings列中没有MISSING STATS项。
尝试在关闭评估的表上运行UPDATE STATISTICS。
验证查询没有使用优化器无法精确评估的查询构造，例如多语句表值函数或CLR函数，表值或Transact-SQL变量比较（参数比较是可以的）。
• 评估是否可以使用不同的Transact-SQL语句或表达式将查询写的更有效率
[b]拙劣游标使用[/b]
SQL Server 2005之前的SQL Server 版本仅支持在每个连接上有单个活动的操作。一个查询正在执行或有了结果等待发送到客户端时将被认为是活动的。在一些情形中，客户端应用程序也许需要从结果中读取并向SQL Server提交其他基于刚刚从结果集中读取的行的查询。这在默认的结果集中是不能实现的，因为还有其他等待的结果。一般的解决方法是改变连接属性是用服务器端游标。
当使用服务器端游标，数据库客户端软件(OLE DB提供者或ODBC驱动)显然会封装客户端请求在特殊的扩展存储过程中，例如sp_cursoropen，sp_cursorfetch等等。这提到了API游标（而不是TSQL游标）。当用户执行查询，查询文本通过sp_cursoropen被发送到服务器，请求读取从sp_cursorfetch指示服务器进发送某些数量的行。通过控制获取行的数量，可以为ODBC驱动或OLE DB提供者缓存行。这阻止发生服务器等待客户端都区所有发送的行的情形。因此，服务器可以在这个连接上接受新的请求。
一次性打开游标并获取1行（或少量行）的应用程序能被网络延时的网络瓶颈影响，特别是在广域网（WAN）。在有快速网络并有不同用户连接时，处理很多游标请求的开销变得更重要。因为开销来自于游标位置的变化来适应在结果集上的位置改变，预请求的处理开销，类似的处理，服务器处理1个请求返回100行必处理100不同请求相同的100行但是每次1行更有效率。
[b]检测[/b]
你可以使用下列方法为拙劣游标使用排错。
系统监视器(Perfmon)
通过考虑SQL Server:Cursor Manager By Type – Cursor Requests/Sec计数器，你可以通过这个性能计数器知道有多少游标在系统中使用。系统还有很高的CPU利用率，因为小量的读取通常会有每秒数百个游标请求。这里没有特殊的计数器告诉你关于获取的缓存大小。
[b]DMVs[/b]
接下来的查询可以用于测定使用API游标（不是TSQL游标）连接获取一行使用的缓存大小。它对于大的获取缓存更有效，例如100行。
select
cur.*
from
sys.dm_exec_connections con
cross apply sys.dm_exec_cursors(con.session_id) as cur
where
cur.fetch_buffer_size = 1
and cur.properties LIKE 'API%' -- API cursor (TSQL cursors always have fetch buffer of 1)
[b]SQL 跟踪[/b]
使用包括RPC:Completed事件类的跟踪用于搜索sp_cursorfetch语句。第4个参数的值是通过获取返回的行数。请求返回的最大行数是被指定为与RPC:Starting事件类关联的参数。
解决
• 确定游标是完成操作的最佳方法或是否基于集合这种更有效的操作是可行的。
• 当连接到SQL Server 2005，考虑使用多活动结果集(MARS)
• 参考你使用的API文档决定如何指定游标的获取缓存大小：
ODBC - SQL_ATTR_ROW_ARRAY_SIZE
OLE DB – IRowset::GetNextRows or IRowsetLocate::GetRowsAt
内存瓶颈
这部分给出了低缓存的条件和对不同内存错误诊断方法，可能的原因和排错方法。
[b]背景[/b]
引用不同的内存资源通过使用简单的术语内存。但是却有一些内存资源类型，对于理解和区分特殊的内存资源这是很重要的。
[b]虚拟地址空间和物理内存[/b]
在Microsoft Windows®，每个进程都有自己的虚拟地址空间(VAS)。进程可用所有虚拟地址有VAS的大小决定。VAS的大小依赖于架构（32位或64位）和操作系统。在排错的上下文中，理解虚拟地址空间使用内存资源，了解应用程序可以超出VAS即使在64位平台只要物理地址可能一直可用，这些很重要。
[b]Windows地址扩展和SQL Server[/b]
Windows地址扩展(AWE)是允许32位应用程序跨越32位地址限制操作内存的API。AWE机制技术上不需要64位平台。然而它出现了。内存页通过AWE机制涉及在64位平台上的锁定页。
在32位和64位平台上，内存通过AWE机制分配不能分页出界。这可以有益于应用程序（这是在64位平台上使用AWE机制的原因）。这也影响了系统和其他应用程序可用RAM总数，这可能是有害的影响。因为这个原因为了使用AWE，Lock Pages in Memory权利必须分配该运行SQL Server的账号。
从排错的角度来看，要点是SQL Server缓存池使用AWE映射内存；然而，只有数据库（hash过的）页面可以利用AWE分配内存。通过AWE机制内存分配将不会在任务管理器或在Process: Private Bytes性能计数器中看到。你需要使用SQL Server特殊的计数器或动态管理视图来获取这些信息。
[b]内存压力[/b]
内存压力表示当可用内存数量受到限制。识别SQL Server何时运行在内存压力下将帮助你排除内存相关的问题。SQL Server依赖于不同类型的内存压力特征也不一样。下表汇总了内存压力类型，和他们潜在的原因。在所有的情况下，你可以更多的会见到超时或显示的内存不足错误消息。
Windows有通知的机制 如果物理内存运行在过高或过低的情况下。SQL Server在他的内存管理决策中使用这种机制。
一般排错的步骤显示在表3中。
[b]工具[/b]
下列工具和资源可以用于排错。
• 内存相关的DMVs
• DBCC MEMORYSTATUS 命令
• 性能计数器: 性能监视器或SQL Server指定对象的DMV
• 任务管理器
• 事件查看器: 应用程序日志和系统日志
[b]检测内存压力[/b]
内存压力自身不会预示问题。内存压力是需要的，但时不是为服务器以后遇到内存错误的充分条件。在内存压力下工作将被任务是服务器的正常操作。然而内存压力的征兆可以于是服务器运行已经接近设计容量并且潜在存在内存不足的错误。在正常运行情况下，这些信息将作为基线决定以后内存不足的原因。
[b]外部物理内存压力[/b]
打开人物管理器的性能视图，检查Physical Memory节的Available项的值。如果可用内存总数很低，这表现了有外部内存压力。这个准确值依赖于很多因素，然而你可以在当这个值降低到50-100MB开始查找问题。当这个总数小于10MB时，外部内存压力将表现得很明显。
相同信息也可以使用在系统监视器中的Memory: Available Bytes计数器获取。
如果存在外部内存压力并且你看到了内存相关的错误，你需要确认在系统中主要的内存消耗者。为了这个，考虑Process: Working Set性能计数器或在任务管理器中Process栏中的Mem Usage列，找到最大的内存消耗者。
系统中所有使用的物理内存可以通过汇总下列计数器获取。
• Process 对象，每个进程的 Working Set计数器
• Memory 对象
• 系统的Cache Bytes计数器
• 未分页池的Pool Nonpaged Bytes 计数器
• Available Bytes (等于任务管理其中的Available 值)
如果没有外部压力，Process: Private Bytes计数器或在任务管理器中虚拟内存将接近工作集的大小（Process: Working Set或任务管理器中的Mem Usage），意味着我们没有内存用于分页了。
注意任务管理器中的Mem Usage列和相应的性能计数器不能计算通过AWE分配的内存。这样如果使用AWE，信息将是不完整的。这种情况下，你需要考虑在SQL Server内分配的内存来获取完整的信息。
你可以使用sys.dm_os_memory_clerks DMV找到SQL Server通过AWE机制分配了多少内存，如下所示。
select
sum(awe_allocated_kb) / 1024 as [AWE allocated, Mb]
from
sys.dm_os_memory_clerks
注意在SQL Server中，只有当前缓存池（’MEMORYCLERK_SQLBUFFERPOOL’类型）使用这种机制并且只能是在使用AWE功能时。通过识别和除去主要物理内存使用者（如果有可能）和/或 通过添加更多的内存的方法解除外部内存压力一般可以解决与此相关的内存问题。
[b]外部虚拟内存压力[/b]
你需要确定是否页面文件为当前内存的分配能提供足够的空间。为了检查，可以打开任务管理器中的性能视图，并检查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）。
[b]内部物理内存压力[/b]
内部内存压力来自于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'
每个组件的详细信息可以通过下列语句获取（这包括从缓存池内和缓存池外分配的内存）。
declare @total_alloc bigint
declare @tab table (
type nvarchar(128) collate database_default
,allocated bigint
,virtual_res bigint
,virtual_com bigint
,awe bigint
,shared_res bigint
,shared_com bigint
,topFive nvarchar(128)
,grand_total bigint
);
-- note that this total excludes buffer pool committed memory as it represents the largest consumer which is normal
select
@total_alloc =
sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)
from
sys.dm_os_memory_clerks
'Total allocated (including from Buffer Pool): '
+ CAST(@total_alloc as varchar(10)) + ' Kb'
insert into @tab
select
type
,sum(single_pages_kb + multi_pages_kb) as allocated
,sum(virtual_memory_reserved_kb) as vertual_res
,sum(virtual_memory_committed_kb) as virtual_com
,sum(awe_allocated_kb) as awe
,sum(shared_memory_reserved_kb) as shared_res
,sum(shared_memory_committed_kb) as shared_com
,case when (
(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb))/(@total_alloc + 0.0)) >= 0.05
then type
else 'Other'
end as topFive
,(sum(single_pages_kb
+ multi_pages_kb
+ (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL'
THEN virtual_memory_committed_kb
ELSE 0 END)
+ shared_memory_committed_kb)) as grand_total
from
sys.dm_os_memory_clerks
group by type
order by (sum(single_pages_kb + multi_pages_kb + (CASE WHEN type <> 'MEMORYCLERK_SQLBUFFERPOOL' THEN virtual_memory_committed_kb ELSE 0 END) + shared_memory_committed_kb)) desc
select * from @tab
注意之前的查询将Buffer Pool与通过单页分配器提供给组件的内存视为不同的部分。通过下列查询可以确定在缓存池中耗费内存最多的10个组件（通过单页分配器）。
-- top 10 consumers of memory from BPool
select
top 10 type,
sum(single_pages_kb) as [SPA Mem, Kb]
from
sys.dm_os_memory_clerks
group by type
order by sum(single_pages_kb) desc
你通常不会控制内部组件对内存的占用。然而，确定哪个组件占用最多的内存可以帮助对问题的后续研究。
系统监视器(Perfmon)
你也可以通过检查下列计数器来确定内存压力（SQL Server联机丛书中有详细地描述）：
SQL Server: Buffer Manager vL6387D价格对象
• Low Buffer cache hit ratio
• Low Page life expectancy
• High number of Checkpoint pages/sec
• High number Lazy writes/sec
不足的内存和I/O开销经常会导致瓶颈。请查看本文的I/O 瓶颈部分。
[b]高速缓存和内存压力[/b]
查看外部和内部内存压力的方法是查看内存中高速缓存的行为。
SQL Server 2005与SQL Server 2000ISO122U价格的高速缓存设计上有一些细微的不同，其中之一就是统一了高速缓存的框架。为了从高速缓存中删除最近很少使用的项，该框架实现了一套时钟算法。现在它使用2支不同的时钟指针，一个是内部时钟指针，一个是外部时钟指针。
内部时钟指针控制与其他高速缓存相关的缓存大小。当框架预测到高速缓存要使用到尽头是它开始移动。
当SQL Server总体上陷入内存压力时，外部时钟指针开始移动。外部时钟指针的移动可以导致外部和内部的内存压力。在内部和外部内存压力时不会混乱的移动外部时钟和内部时钟。
关于时钟移动的信息可以通过sys.dm_os_memory_cache_clock_hands DMV显示，如下代码所示。每个高速缓存项在内部和外部时钟指针都有不同的行。如果你看到rounds count和removed all rounds count增加，说明服务器遇到内部/外部内存压力。
select *
from
sys.dm_os_memory_cache_clock_hands
where
rounds_count > 0
and removed_all_rounds_count > 0
通过如下所示，通过通过sys.dm_os_cache_counters DMV你可以获得更多关于缓存的信息。
select
distinct cc.cache_address,
cc.name,
cc.type,
cc.single_pages_kb + cc.multi_pages_kb as total_kb,
cc.single_pages_in_use_kb + cc.multi_pages_in_use_kb as total_in_use_kb,
cc.entries_count,
cc.entries_in_use_count,
ch.removed_all_rounds_count,
ch.removed_last_round_count
from
sys.dm_os_memory_cache_counters cc
join sys.dm_os_memory_cache_clock_hands ch on (cc.cache_address = ch.cache_address)
/*
--uncomment this block to have the information only for moving hands caches
where
ch.rounds_count > 0
and ch.removed_all_rounds_count > 0
*/
order by total_kb desc
注意USERSTORE项，正在使用的页面数量将不会被报告，因为结果将是NULL。
[b]Ring buffers[/b]
更多有意义的内存调试信息可以通过sys.dm_os_ring_buffers的ring buffers DMV获取。每个ring buffer保留了之前几次某种类型的通知。指定ring buffer的详细信息将在下面描述。
[b]RING_BUFFER_RESOURCE_MONITOR[/b]
你可以使用从资源监视器的通知识别内存改变的状态。在内部，SQL Server有一个监视不同内存压力的架构。当内存状态改变，资源监视器任务生成一个通知。这个通知用于内部组件根据内存状态调整它们内存使用并通过sys.dm_os_ring_buffers DMV来暴露，如下列代码所示。
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
结果类似于
从这些记录来看，你可以减少服务器收到的低物理内存的通知。你也可以查看内存总量（KB为单位）。你可以通过使用SQL Server的XML能力来查询这些信息，例如下列代码。
select
x.value('(//Notification)[1]', 'varchar(max)') as [Type],
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//AvailablePhysicalMemory)[1]', 'int') as [Avail Phys Mem, Kb],
x.value('(//AvailableVirtualAddressSpace)[1]', 'int') as [Avail VAS, Kb]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR') as R(x)
order by
[Time Stamp] desc
上面收到了低内存的通知，缓存池重新计算target。注意target数量保持在指定的min server memory 和max server memory选项限制中。如果缓存池中心的提交比当前的提交缓存还小，缓存池将开始收缩直到外部内存压力被移除。注意，当运行在启用AWE时SQL Server 2000不是这样来缓解物理内存压力的。
[b]RING_BUFFER_OOM[/b]
下列代码示例，展示了ring buffer中包含预示服务器内存不足的记录。
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_OOM'
结果类似于：
这条记录告诉我们那个操作失败了（提交，保留或页面分配）并表明了请求的内存数量。
[b]RING_BUFFER_MEMORY_BROKER and Internal Memory Pressure[/b]
当监测到内部内存压力时，为组件在缓存池分配内存的低内存通知将被打开。打开低内存通知允许从使用缓存池的高速缓存和其他组件中回收页面。
内部内存压力可以通过调整max server memory选项或当stolen页面与缓存池的比例超过80%时触发。
内部内存压力通知(‘Shrink’)能通过使用下列代码查询ring buffer的调用来发现。
select
x.value('(//Record/@time)[1]', 'bigint') as [Time Stamp],
x.value('(//Notification)[1]', 'varchar(100)') as [Last Notification]
from
(select cast(record as xml)
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER') as R(x)
order by
[Time Stamp] desc
RING_BUFFER_BUFFER_POOL
ring buffer将包含预示严重的缓存池失败的记录，包括缓存池溢出的条件。
select record
from sys.dm_os_ring_buffers
where ring_buffer_type = 'RING_BUFFER_BUFFER_POOL'
结果类似于：
< BufferPoolFailure id="FAIL_OOM">
这条记录告诉我们出现了什么样的失败 (FAIL_OOM, FAIL_MAP, FAIL_RESERVE_ADJUST, FAIL_LAZYWRITER_NO_BUFFERS) 基当时的缓存池状态。
[b]内部虚拟内存压力[/b]
VAS的占用可以使用sys.dm_os_virtual_address_dump DMV来跟踪。VAS汇总可以使用下列视图来查询。
-- virtual address space summary view
-- generates a list of SQL Server regions
-- showing number of reserved and free regions of a given size
CREATE VIEW VASummary AS
SELECT
Size = VaDump.Size,
Reserved = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 0 ELSE 1 END),
Free = SUM(CASE(CONVERT(INT, VaDump.Base)^0) WHEN 0 THEN 1 ELSE 0 END)
FROM
(
--- combine all allocation according with allocation base, don't take into
--- account allocations with zero allocation_base
SELECT
CONVERT(VARBINARY, SUM(region_size_in_bytes)) AS Size,
region_allocation_base_address AS Base
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address <> 0x0
GROUP BY region_allocation_base_address
UNION
--- we shouldn't be grouping allocations with zero allocation base
--- just get them as is
SELECT CONVERT(VARBINARY, region_size_in_bytes), region_allocation_base_address
FROM sys.dm_os_virtual_address_dump
WHERE region_allocation_base_address = 0x0
)
AS VaDump
GROUP BY Size
下列查询可以用于访问VAS状态
-- available memory in all free regions
SELECT SUM(Size*Free)/1024 AS [Total avail mem, KB]
FROM VASummary
WHERE Free <> 0
-- get size of largest availble region
SELECT CAST(MAX(Size) AS INT)/1024 AS [Max free size, KB]
FROM VASummary
WHERE Free <> 0
如果最大可用区域小于4MB，我们可能遇到了VAS压力。SQL Server 2005监视和响应VAS压力。SQL Server 2000不会监视从VAS带来的压力，但是当出现虚拟内存不足错误是，它会清理高速缓存。
8645 - A time out occurred while waiting for memory resources to execute the query. Rerun the query.
[b]原因[/b]
这个错误指出在服务器上有很多内存敏感的查询被同时执行。使用排序（ORDER BY）和连接的查询可以在执行过程中会占用大量的内存。如果有高度并行或如果查询操作一个非联合索引的分区表时，查询内存需求会有很大的增加。无法或取所需内存资源的查询在预先确定的超时时间时（默认，超时是25倍的评估查询时间或sp_configure 设置的’query wait’）将收到这个错误。通常，收到这个错误的查询占用内存的那个查询。
[b]排错[/b]
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.
[b]原因[/b]
原因和8645错误类似；它也预示服务器内存过低。min memory per query太高也可能导致这个错误。
排错
1. 根据一般内存错误排错步骤。
2. 验证sp_configure min memory per query 选项设置。
[b]I/O 瓶颈[/b]
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
from sys.dm_os_wait_stats
where wait_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
from sys.dm_io_virtual_file_stats(NULL, NULL)t1,
sys.dm_io_pending_io_requests as t2
where t1.file_handle = t2.io_handle
下面是示例的输出。它展示当时在给定的数据库上有3个未决定的I/O 。你可以使用database_id 和 file_id 来找到映射的物理磁盘文件。io_pending_ms_ticks 表现了所有等待在未决定队列中的个别的I/O。
Database_id File_Id io_stall io_pending_ms_ticks scheduler_address
----------------------------------------------------------------------
6 1 10804 78 0x0227A040
6 1 10804 78 0x0227A040
6 2 101451 31 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,
(
(
from sys.dm_exec_sql_text (0x0200000099EC8520EFB222CEBF59A72B9BDF4DBEFAE2B6BB)
statement_start_offest 和statement_end_offset的值需要除以2来补偿SQL Server使用Unicode来存储这种数据的。statement_end_offse值为-1指出语句先前到了批的最后。而substring()函数不识别-1，并将其作为非法值。使用(
同样的你可以运行下列查询，找到查询计划确定是否当选择了较差的计划时有大量的I/O。
select *
from sys.dm_exec_query_plan
(0x0600050099EC8520A8619803000000000000000000000000)
下列是一些常规步骤，有助于你排除内存错误。
1. 验证是否服务器运行在外部内存压力。如果出现外部内存压力，尝试先解决它，然后再看是否依然存在这个问题或错误。
2. 开始收集性能计数器：SQL Server: Buffer Manager, SQL Server: Memory Manager
3. 确认内存配置参数(sp_configure)， min memory per query，min/max server memory，awe enabled和 Lock Pages in Memory 权利。观察不正常的值。纠正配置。提供为SQL Server 2005增加内存的理由。
4. 检查所有可能影响服务器的非默认sp_configure参数。
5. 检查内部内存压力。
6. 当你见到内存错误消息时，观察DBCC MEMORYSTATUS输出和改变的方法。
7. 检查负载（并发会话数量，并发执行查询数量）。
[b]内存错误[/b]
701 - There is insufficient system memory to run this query.
原因
这是服务器内存不足的典型现象。它预示了内存分配失败。它可以有多种原因导致，包括在当前工作负载上的内存提示。通过增加SQL Server 2005需要的内存和一些配置上的设置（例如max server memory选项）用户可能会看到这种错误。通常失败的事务不是因为这个错误。
排错
不论是否错误是持续的和可重复的（同样状态）或是随机的（显示为随机的不同状态），当你看到这个错误时你需要研究服务器内存分布。当这个错误出现时，可能导致诊断查询失败。可以开始从外部进行评估。接下来的步骤在一般的内存错误排错步骤中描述。
可能的解决方案包括：除去外部内存压力；增加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. 尽可能减少工作负载。
[b]Tempdb[/b]
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的可用空间。
[b]监视tempdb空间[/b]
阻止问题更好的方法是随后马上解决它。你可以使用下列性能监视器来监视使用中的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
注意这些技术不包括其中的混合扩展。页面在混合扩展中可以被分配个别用户和内部对象。
[b]磁盘空间问题排错[/b]
用户对象，内部对象和存储的版本可以都可以导致在tempdb中的空间问题。这节我们会考虑针对每个类别如何排错。
[b]用户对象[/b]
因为用户对象不被特定的会话拥有，你需要理解创建该对象的应用程序的规范并调整需要的tempdb大小。你可以通过执行exec sp_spaceused @objname='
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
[b]版本存储[/b]
SQL Server 2005提供了行版本架构用于实现一些特性。如下列出了使用行版本架构的特性。更多关于下列特性的信息，请参考SQL Server 联机丛书。
• 触发器
• MARS
• 联机索引
• 基于行版本隔离级别：需要在数据库级设置选项
行版本需要跨会话共享。当行版本被回收时，行版本的创建者没有控制权。你需要找到并杀掉阻止行版本清理的运行最长的事务。
下列查询返回依赖于版本存储运行最长的2个事务。
select top 2
transaction_id,
transaction_sequence_num,
elapsed_time_seconds
from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds DESC
这是示例的输入，显示了序列号为3，事务ID为8609的事务已经运行了6523秒。
transaction_id transaction_sequence_num elapsed_time_seconds
-------------------- ------------------------ --------------------
8609 3 6523
20156 25 783
因为第2个事务运行了相对短的时间，你可以通过杀掉第1个事务来释放大量的版本存储。可是，没有方法能评估通过杀掉进能释放的版本空间。你也许需要杀掉一些事务来释放更多的空间。
你可以通过改变用于版本存储的tempdb属性或通过尽可能的消除在快照隔离级别的长事务，或在read-committed-snapshot下运行的长查询来减轻这个问题。你能使用下列公式粗略的评估行版本存储的大小。
[Size of version store] = 2 * [version store data generated per minute] * [longest running time (minutes) of the transaction]
在所有使用了基于孤立级别行版本，为一个事物每分钟生成版本存储的数据和每分钟生成的日志一样。然而这也有一些异常：只有更新的差异部分生成日志；如果使用了批量导入操作并且恢复模式不是完全恢复时，新插入的数据行不依赖日志，则不被记录版本。
你也可以使用Version Generation Rate 和Version Cleanup Rate性能计数器来调整你的计算。如果Version Cleanup Rate为0，这暗示着有长时间运行的事务阻止版本存储的清理。
附带地，在产生tempdb空间不足错误前，SQL Server 2005会做最后一次尝试强制版本存储收缩。在这个收缩过程中，没有生成行版本运行最长的事务会被标识为牺牲者。这可以释放他们使用的版本存储。在错误日志中为牺牲的事务生成一个消息3967，它能不再从版本存储中读取行版本或创建新的版本存储。如果收缩版本存储成功，这样在tempdb中会有更多的可用空间。否则tempdb将耗尽内存。
[b]内部对象[/b]
内部对象在每条语句中被创建和销毁，除非想在前面所描述的。如果你注意到有大量的tempdb空间分配，你将需要了解那个会话或任务占用了空间，然后进肯能做一些矫正的操作。
SQL Server 2005提供了2个额外的DMV：: sys.dm_db_session_space_usage 和sys.dm_db_task_space_usage 来跟踪分配给个别会话和任务所用的tempdb空间。尽管任务运行在会话的上下文，当任务完成后，任务使用的空间还会被占用。你可以使用下列查询来找到为内部对象分配最多的会话。注意这个查询只包括在会话中已完成的任务。
select
session_id,
internal_objects_alloc_page_count,
internal_objects_dealloc_page_count
from sys.dm_db_session_space_usage
order by internal_objects_alloc_page_count DESC
你可以使用下列查询找到分配对象最多的会话，包括正在运行的任务。
SELECT
t1.session_id,
(t1.internal_objects_alloc_page_count + task_alloc) as allocated,
(t1.internal_objects_dealloc_page_count + task_dealloc) as
deallocated
from sys.dm_db_session_space_usage as t1,
(select session_id,
sum(internal_objects_alloc_page_count)
as task_alloc,
sum (internal_objects_dealloc_page_count) as
task_dealloc
from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
order by allocated DESC
这是示例的输出。
session_id allocated deallocated
---------- -------------------- --------------------
52 5120 5136
51 16 0
一旦你隔离出生成大量对象分配的任务或会话，你能找到任务的那条Transact-SQL语句和它的查询计划来做更详细地分析。
select
t1.session_id,
t1.request_id,
t1.task_alloc,
t1.task_dealloc,
t2.sql_handle,
t2.statement_start_offset,
t2.statement_end_offset,
t2.plan_handle
from (Select session_id,
request_id,
sum(internal_objects_alloc_page_count) as task_alloc,
sum (internal_objects_dealloc_page_count) as task_dealloc
from sys.dm_db_task_space_usage
group by session_id, request_id) as t1,
sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and
(t1.request_id = t2.request_id)
order by t1.task_alloc DESC
这是示例的输出。
session_id request_id task_alloc task_dealloc
---------------------------------------------------------
52 0 1024 1024
sql_handle statement_start_offset
-----------------------------------------------------------------------
0x02000000D490961BDD2A8BE3B0FB81ED67655EFEEB360172 356
statement_end_offset plan_handle
--------------------------------- -1 0x06000500D490961BA8C19503000000000000000000000000
你可以利用如下语句通过sql_handle和plan_handle来得到SQL语句和查询计划：
select text from sys.dm_exec_sql_text(@sql_handle)
select * from sys.dm_exec_query_plan(@plan_handle)
注意当你访问查询计划时，查询计划可能已经不在缓存中了。为保证查询计划可以使用，你应该需要经常为查询计划缓存保存否则该计划会被清除，同时应该将结果尽可能保存在表中，这样用于以后的查询。
当SQL Server 重新启动，tempdb大小将初始化到配置的大小，并基于需求增长。这可以导致tempdb的分裂，这样会招致过多的开销，包括在数据库自动增长时分配新的扩展的阻塞。这能影响你的工作负载性能。建议你重新分配tempdb到一个适当的大小。
[b]过多的DLL和分配操作[/b]
在tempdb中2个原因可以导致这个结果。
• 创建和删除大量的临时表和标变量导致在元数据上的争夺。在SQL Server 2005中，本地的临时表和标变量被缓存来最小化元数据的争用。然而只有下列条件满足时，表才会被缓存。
• 在表中没有命名的约束。
• 在Create语句后在表中没有DDL(例如，Create Index和Create Statistics)。
• 典型情况下，大部分临时/工作表是在堆上；因此插入，删除或删除操作能引起在Page Free Space (PFS) 页面上的严重争用。如果大部分的表小于64KB并且使用了混合扩展来分配或处理位置，这能带来在Share Global Allocation Map(SGAM)页面上的争用。SQL Server 2005为本地的临时表缓存一个数据页和一个IAM页来最小化分配争用。这种缓存在SQL Server 2000中是使用在工作表上的。
因为SGAM和PFS在数据文件中分页出现在固定的间隔，这样很容易找到他们所用资源的描述。例如，2:1:1表示了在tempdb第1个PFS页面(database-id为2，file-id为1，page-id为1)，2:1:3表示了第1个SGAM页面。SGAM页面每511232个页面出现1次，PFS页面每8088个页面出现1次。你能使用这个规则在tempdb的所有文件中找到所有其他的PFS和SGAM页面。无论什么时候，当任务等待占有所有页面时，它将在sys.dm_os_waiting_tasks中显示。因为这种等待是短暂的，你需要频繁的查询这个表（大约每10秒一次）并收集这些数据以后分析。例如，你可以使用下列查询将在tempdb中所有等待页面的任务加载到在分析数据库中的waiting_task表。
-- get the current timestamp
declare @now datetime
select @now = getdate()
-- insert data into a table for later analysis
insert into analysis..waiting_tasks
select
session_id,
wait_duration_ms,
resource_description,
@now
from sys.dm_os_waiting_tasks
where wait_type like ‘PAGE%LATCH_%’ and
resource_description like ‘2:%’
当需要时你可以看到在tempdb页面中等待锁的任务，这样你可以分析是否归咎于PFS或SGAM分页。如果是，这意味着在tempdb上有分配争用。如果你在tempdb上的其他页面争用，如果你确定这个页面属于系统表，这意味着由于过度的DDL导致争用。
你也可以使用下列的性能计数器监视临时对象分配/定位操作得不正常的增加，
• SQL Server:Access Methods\Workfiles Created /Sec
• SQL Server:Access Methods\Worktables Created /Sec
• SQL Server:Access Methods\Mixed Page Allocations /Sec
• SQL Server:General Statistics\Temp Tables Created /Sec
• SQL Server:General Statistics\Temp Tables for destruction
[b]解决[/b]
如果是由于过多的DDL操作导致在tempdb争用，你需要考虑你的应用程序，并查看是否你能减少DDL操作。你可以尝试下列建议。
• 如果你使用存储过程范围内的临时表，考虑是否这些表可以移动到存储过程外。否则每次执行存储过程将会导致创建/删除临时表。
• 查看查询计划，是否一些计划创建大量的临时对象，池，排序或工作表。你需要评估一些临时对象。例如，在一个列上创建一个用于ORDER BY操作的索引可以除去查询时的排序
如果争用是由于在SGAM 和PFS页面上的争用，你可以通过尝试下列操作减轻争用：
• 通过增加tempdb数据文件将等量负载分布在所有磁盘和文件上。理论上，你应该将文件数量设置为CPU数量等同（主要考虑亲和性）。
• 使用TF-1118消除混合扩展分配。
[b]运行缓慢的查询[/b]
缓慢或长时间运行的查询可以占用过多资源并能导致阻塞查询。
过多的资源占用是没有限制CPU资源的使用，但是也能包括I/O存储带宽和内存带宽。即使SQL Server查询被设计为可以通过合理WHERE子句限制结果集的方法避免整表扫描 ，如果没有合适的索引支持这个特殊的查询，他们可能不会按照我们期望的方式执行。同样，WHERE子句能依赖于用户输入被动态的通过应用程序构建。假设存在的索引不能覆盖所有可能的约束。通过Transact-SQL语句占用过度的CPU，I/O和内存的情况在本白皮书前面已经描述了。
除了缺失索引外，也可能有索引没有被使用。当所有的索引不得不维护时，这不影响查询的性能，但是影响DML查询。
因为等待逻辑锁和系统资源的状态会阻塞查询，查询也会运行的比较缓慢。阻塞的原因可能是较差的应用程序设计，坏的查询计划，缺乏有用的索引和不正确的SQL Server实例配置。
这节主要集中在缓慢查询的2个原因-阻塞和索引问题。
[b]阻塞[/b]
阻塞主要是等待逻辑锁，例如等待在资源上获取排他锁或等待从更低级别的同步结果，例如闩。
当做出一个在已经锁定的资源上获得一个不兼容的锁的请求产生时，逻辑锁等待发生。在特殊的Transact-SQL语句运行时，通过使用锁可以基于事务隔离级别提供数据一致性的功能，这样给最终用户的感觉是SQL Server运行缓慢。当查询被阻塞时，它不占用任何系统资源，所以你将发现它运行很长时间但是资源占用却很少。更多关于并发控制和阻塞的信息请查看SQL Server联机丛书。
如果你的系统没有被配置为处理这种负载就会导致等待底层的原始同步。
一般阻塞和等待的场景是：
• 识别阻塞者
• 识别长的阻塞
• 阻塞每个对象
• 页面闭锁问题
• 阻塞影响整体性能
如果系统资源（或锁）当前不能服务于请求，这个SQL Server会话将被置于等待状态。换句话说，资源有一个等待请求的队列。DMV能提供任何等待资源的会话的信息。
SQL Server 2005提供了更详细和一致的等待信息，有大约125种等待类型而SQL Server 2000只有76种可用的等待类型。DMV提供的信息范围从sys.dm_os_wait_statistics中表现SQL Server全面和积累的等待信息，到sys.dm_os_waiting_tasks中与会话相关分解的等待信息。下列DMV提供了详细的等待某些资源的任务等待队列。它同样表现了在系统中所有的等待队列。例如你可以运行下列查询找到关于阻塞会话56的详细信息。
select * from sys.dm_os_waiting_tasks where session_id=56
waiting_task_address session_id exec_context_id wait_duration_ms wait_type resource_address blocking_task_address blocking_session_id blocking_exec_context_id resource_description
-------------------- ---------- --------------- -------------------- ------------------------------------------------------------ ------------------ --------------------- ------------------- ------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0x022A8898 56 0 1103500 LCK_M_S 0x03696820 0x022A8D48 53 NULL ridlock fileid=1 pageid=143 dbid=9 id=lock3667d00 mode=X associatedObjectId=72057594038321152
这个结果显示了会话56被会话54阻塞了，会话56已经为一个锁等待了1103500毫秒。
为了找到准许的锁或等待锁的会话，你可以使用sys.dm_tran_locks DMV。每行数据展现了发送到锁管理器的当前活动的请求。为了有序的锁，准许请求指出了锁已经在资源上被准许给请求者。一个等待的请求指出了请求没有被准许。例如下列查询显示会话56被阻塞在资源1:143:3，该资源被会话53的X模式锁占有。
select
request_session_id as spid,
resource_type as rt,
resource_database_id as rdb,
(case resource_type
WHEN 'OBJECT' then object_name(resource_associated_entity_id)
WHEN 'DATABASE' then ' '
ELSE (select object_name(object_id)
from sys.partitions
where hobt_id=resource_associated_entity_id)
END) as objname,
resource_description as rd,
request_mode as rm,
request_status as rs
from sys.dm_tran_locks
Here is the sample output
spid rt rdb objname rd rm rs
----------------------------------------------------------------------------- 56 DATABASE 9 S GRANT
53 DATABASE 9 S GRANT
56 PAGE 9 t_lock 1:143 IS GRANT
53 PAGE 9 t_lock 1:143 IX GRANT
53 PAGE 9 t_lock 1:153 IX GRANT
56 OBJECT 9 t_lock IS GRANT
53 OBJECT 9 t_lock IX GRANT
53 KEY 9 t_lock (a400c34cb X GRANT
53 RID 9 t_lock 1:143:3 X GRANT
56 RID 9 t_lock 1:143:3 S WAIT
实际上，你能连接上面的2个DMV，就像使用存储过程sp_block锁展示的。在图1种阻塞报告列出了被阻塞的会话和阻塞它的会话。你可以在附录B中找到sp_block的源代码。如果你需要添加/删除在可选择列表中的列时，你可以根据需求修改存储过程。可选的@spid参数提供了在锁请求和阻塞这个spid的会话信息。
图1: sp_block 报表
在SQL Server 2000中，你能通过下列语句查看被阻塞的spid信息。
select * from master..sysprocesses where blocked <> 0.
联合锁可以通过存储过程sp_lock存储过程。
[b]识别长时间的阻塞[/b]
之前我们提到，在SQL Server中阻塞是很正常的，使用逻辑锁来维护事务一致性的。然而当等待的锁超过了阀值，它会影响响应时间。为了识别长时间运行的阻塞，你能使用BlockedProcessThreshold配置参数来建立一个用户配置的服务端阻塞阀值。阀值定义一个秒级的间隔。任何超过阀值的阻塞将出发事件并被SQL Trace捕获。
例如，1个200秒的阻塞进程阀值可以在SQL Management Studio中配置。例如：
Execute Sp_configure ‘blocked process threshold’, 200
[b]Reconfigure with override[/b]
一旦阻塞处理阀值被建立，下一步是捕获跟踪的事件。跟踪阻塞超过用户配置的阀值事件可以通过SQL Trace 或Profiler捕获。
1. 如果使用SQL Trace，使用sp_trace_setevent过程，event_id参数为137
2. 如果使用SQL Server Profiler，选择Blocked Process Report 事件类（在Error和Warnings对象下），如图2。
注意这是一个轻量级的跟踪，事件仅在当锁超过阀值，或发生死锁时被捕获。每有200秒的间隔一个锁被阻塞，1个跟踪事件被触发。图3意味着1个锁阻塞了600秒，发生了3次跟踪事件。
跟踪事件包括阻塞者和被阻塞者整个SQL语句。图中所示”Update Customer”阻塞了”Select from Customer”语句。
通过与SQL Server 2000比较，检查长期阻塞场景代码在Sysprocesses并在后续处理结果。知识库文章271509包含了一段可以用于监视阻塞的示例代码。
[b]通过sys.dm_db_index_operational_stats查看阻塞的每个对象[/b]
新的SQL Server 2005 DMV Sys.dm_db_index_operational_stats提供了全面的索引使用统计，包括阻塞。根据阻塞，它提供了每个表，索引，分区的锁统计的详细信息。例如，在给定索引和表上的访问历史，锁数量（row_lock_count），阻塞数量（row_lock_wait_count）和等待时间(row_lock_wait_in_ms)等信息。这个DMV包括的类型信息有：
• 占有的锁的数量，例如行或页。
• 阻塞或等待的数量，例如，行或页。
• 阻塞或等待持续的时间，例如行或页。
• 页面上闩的等待。
• page_latch_wait持续时间：这包括特殊页上的争用，升序键的插入。在这种情况，热点是最后的页面，所以多个写入这到最后的页面每次尝试获取高级的页面闩在同样的时间。这将作为Pagelatch waits暴露。
• page_io_latch_wait持续的时间：当用户请求一个不在缓存池的页面时发生的I/O闩。一个慢速的I/O子系统或过多工作的I/O子系统将遇到很高的PageIOlatch等待，这实际上是I/O问题。这个问题被混在于缓存清除和缺失索引中。
• 页面闩等待持续的时间。
除了阻塞相关信息外，这还有额外的信息。
• 访问类型, 包括range, singleton lookups.
• 在页级的插入，更新，删除。
• 在页级之上插入，更新或删除。在叶上的活动是索引维护。在每个叶级页面中的第一行有这个级别之上的条目。如果新的页面被分配到叶级别上，这级别之上将为新的叶级页面的第一行创建新的项。
• 在叶级别的页面合并将表现为重新分配的空页，因为行已经删除了。
• 索引维护。在叶级上页面合并就是将空白页重分配，这导致在叶上行被删除，因此留下的中间级别页面是空白的。在叶级页面的第一行有一个条目在该层上。如果在叶级别删除了足够的行，原来包含第一行叶级页面条目的中间层索引页面也会是空白的。这导致在叶结点上的合并发生。
这些信息积累了从实例启动以来的信息。信息不会一直保留直到实例被重新启动，并且没有其他的方法可以重置它。这个DMV返回的数据仅在元数据缓存对象表现的堆或索引可用的情况下存在。只要堆和索引的元数据被加载到了元数据缓存，每个列的这个值将被设置为0。统计是被累加的直到缓存对象被从元数据缓存中删除。然而，你可以周期性的查询这个表来收集在表中的信息，用于更进一步的分析。
附录B定义一套存储过程可以用于收集索引操作的数据。你能分析你感兴趣的时间短的数据。这里是如何使用定义的存储过程的步骤。
1. 使用init_index_operational_stats初始化indexstats 表
2. 使用insert_indexstats. 捕获基线数据
3. 运行负载
4. 通过使用insert_indexstats. 捕获最后索引统计
5. 为了分析收集的索引统计，运行存储过程get_indexstats来生成每个索引上锁的平均数(索引和分区的row_lock_count)，阻塞和等待。高的blocking %和/或高的平均等待指出设计不好的索引或查询公式。
这有一些示例展现了你能使用这些存储过程的类型
• 获取所有数据库上使用率最高的5个索引
exec get_indexstats
@dbid=-1,
@top='top 5',
@columns='index, usage',
@order='index usage'
• 获取前5个索引锁提示（所有列）
exec get_indexstats
@dbid=-1,
@top='top 5',
@order='index lock promotions',
@threshold='[index lock promotion attempts] > 0'
• 获取5个平均行锁等待大于2ms的独立查询，返回行包括wait,scan,singleton信息。以'singleton lookups'排序
exec get_indexstats
@dbid=5,
@top='top 5',
@columns='wait,scan,singleton',
@order='singleton lookups',
@threshold='[avg row lock wait ms] > 2'
• 获取所有数据库行锁等待大于1IRFU9024，列包含‘avg,wait’，以'row lock wait ms'排序
exec get_indexstats
@dbid=-1,
@top='top 10 ',
@columns='wait,row',
@order='row lock wait ms',
@threshold='[row lock waits] > 1'
• 获取前5个索引状态，通过'avg row lock wait ms'排序
exec get_indexstats
@dbid=-1,
@top='top 5',
@order='avg row lock wait ms'
• 获取前5个索引状态，通过'avg page latch wait ms'排序
exec get_indexstats
@dbid=-1,
@top='top 5',
@order='avg page latch wait ms'
• 获取前 5%索引状态, 通过 avg pageio latch waits.
exec get_indexstats
@dbid=-1,
@top='top 3 percent',
@order='avg pageio latch wait ms',
@threshold='[pageio latch waits] > 0'
• 获取所有在数据库5中的索引状态并且block% > 0.1,以block%排序。
exec get_indexstats
@dbid=-1,
@top='top 10',
@order='block %',
@threshold='[block %] > 0.1'
如图4示例，阻塞分析报告
SQL Server 2000部提供任何对象或索引的统计利用信息。
[b]使用SQL waits阻塞对整体性能的影响[/b]
SQL Server 2000提供了76种等待类型来提供等待报告。SQL Server 2005提供了多余100个等待类型来跟踪应用程序性能。任何时间1个用户连接在等待时，SQL Server会累加等待时间。例如应用程序请求资源例如I/O，锁或内存，可以等待资源直到可用。这些等待信息可以跨所有连接将被汇总和分类，所以性能配置可以从给定的负载获得。因此，SQL等待类型从应用程序负载或用户观点识别和分类用户（或线程）等待。
这个查询列出了在SQL Server中前10位的等待。这些等待时累积的，但是你可以使用DBCC SQLPERF ([sys.dm_os_wait_stats], clear)重置这个计数器。
select top 10 *
from sys.dm_os_wait_stats
order by wait_time_ms desc
下列是输出，要注意几个关键点：
• 一些等待是正常的例如后台线程的等待，例如lazy writer组件。
• 一些会话为获取共享锁等待很长时间
• 信号等待是在一个工作线程获取对资源访问到它被拿到CPU上调度执行这段时间。长时间的信号等待也许意味着很高的CPU争用。
wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
------------------ -------------------- -------------------- -------------------- -------
LAZYWRITER_SLEEP 415088 415048437 1812 156
SQLTRACE_BUFFER_FLUSH 103762 415044000 4000 0
LCK_M_S 6 25016812 23240921 0
WRITELOG 7413 86843 187 406
LOGMGR_RESERVE_APPEND 82 82000 1000 0
SLEEP_BPOOL_FLUSH 4948 28687 31 15
LCK_M_X 1 20000 20000 0
PAGEIOLATCH_SH 871 11718 140 15
PAGEIOLATCH_UP 755 9484 187 0
IO_COMPLETION 636 7031 203 0
为了分析等待状态，你需要获取数据，用于以后分析。附录B提供了2个示例的存储过程。
• Track_waitstats.收集数据渴望的采样数量和采样的时间间隔。这里有一个调用的示例
exec dbo.track_waitstats @num_samples=6
,@delay_interval=30
,@delay_type='s'
,@truncate_history='y'
,@clear_waitstats='y'
• Get_waitstats.分析前面步骤收集到的数据。这有一个调用的示例。
exec [dbo].[get_waitstats_2005]
• Spid运行，需要当前不可用的资源。因为资源不可用，在T0时，它移动到资源等待列表。
• 信号指出资源是可用的，所以spid在T1时间移动到可运行队列。
• Spid等候运行状态直到T2，同样的CPU通过可运行队列处理按顺序到达等待。
你可以使用这些存储过程分析资源等待和信号等待，使用这些信息隔离资源争用。
图5 显示了示例的报告。
图5中现实的等待状态分析报告预示了由于阻塞(LCK_M_S)和内存分配(RESOURCE_SEMAPHORE)的性能问题。特定的55%的所有等待是等待共享锁，而43%是由于内存请求。阻塞每个对象的分析将是识别主要的争用点。
[b]监视索引的使用[/b]
其他方面的查询性能与DML查询，查询删除，插入和修改数据相关。在指定表上定义更多索引，在需要数据修改时就需要更多的资源。由于锁结合持续事务，时间长的修改操作可以损害并发性。因此在应用程序中使用那个索引就变得非常重要。你能计算出是否在数据库架构上有大量从未使用过的索引存在。
SQL Server 2005提供了新的sys.dm_db_index_usage_stats动态管理视图显示哪些索引是使用的，和是否他们被用于用户查询或仅用于系统操作。每次执行查询，在这个视图中的列将根据用于执行查询的查询计划将会增加。当SQL Server启动并运行，数据就被收集了。这个DMV中的数据只是保存在内存中的，没有持久化。所以当SQL Server实例关闭，数据将会丢失。你可以周期性的获取这个表，并将数据保存用于以后分析。
在索引操作被分为用户类型和系统类型。用户类型引用SELECT和INSERT/DELETE/UPDATE操作。系统类型操作是类似于DBCC这样的命令或DDL命令或是update statistics。每种类别的语句列被区分为：
• 依靠索引的SEEK操作 (user_seeks or system_seeks)
• 依靠索引的LOOKUP操作(user_lookups or system_lookups)
• 依靠索引的SCAN操作(user_scans or system_scans)
• 依靠索引的UPDATE操作(user_updates or system_updates)
每种访问索引都会记路最后一次访问的时间戳。一个索引本身通过3列识别，database_id，object_id和index_id。然而，index_id=0代表是一个堆表，index_id=1代表时集束索引，反之index_id>1但表是非集束索引。
一个整天运行的数据库应用程序，从sys.dm_db_index_usage_stats中得到的索引访问信息列表将增长。
下列是在SQL Server 2005使规则和任务的定义：
• SEEK: 识别用于访问数据的B树结构数量。不论B树结构只是访问每级只有少量页面来获取一个数据行，还是是表中使用半索引页面读取如几个G数据或百万行的数据。所以我们希望在这个类别有更多的累计。
• SCAN: 识别不使用B树索引获取数据的数据表数量。没有任何索引定义的表属于这种情况。有索引定义但是在执行语句查询时并没有使用这些说印的表也属于这种情况。
• LOOKUP: 识别在一个集束索引通过’seeking’在一个非集束索引上查询数据，2个索引都定义在同一张表上。这种场景描述在SQL Server 2000中的书签查询。它表现了这样一个场景，非集束索引被用于访问表，并且非集束索引没有覆盖查询的列和索引列没有在WHERE子句定义，SQL Server将使用非集束索引列的user_seeks值加上使用集束索引列的user_lookups值。这个值能变得很高如果多个非集束索引在这个表上定义。如果依靠集束索引的user_seeks值高，user_lookups的数量也会很高，加上一部分user_seeks也是很高，应该通过将非集束索引大量的高于集束索引。
下列DMV查询可以被用于获取在所有数据库中所有对象上关于索引使用信息。
select object_id, index_id, user_seeks, user_scans, user_lookups
from sys.dm_db_index_usage_stats
order by object_id, index_id
你能看到下列结果：
object_id index_id user_seeks user_scans user_lookups
------------ ------------- -------------- -------------- -----------------
521690298 1 0 251 123
521690298 2 123 0 0
在这种情况有251次查询的执行直接访问数据层表而不使用索引。有123次查询的执行通过使用非集束索引访问表，但是没有覆盖查询选择列表或在WHERE子句指定列，因为我们看到了123次在集束索引的lookup访问。
最有趣的类别着眼于‘user type statement’类型。使用方法指出在‘system category’可以被看作为存在索引的结果。如果索引不存在，它不会更新统计，也不需要检查一致性。因此分析需要着眼于4列显示独立语句的使用或分析用户应用程序。
为了获取从上次SQL Server启动以来，关于指定表没有使用的索引信息，这种查询将在数据库上下文中执行。
select i.name
from sys.indexes i
where i.object_id=object_id('
i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id =
所有没有被使用的索引仍可以通过下列语句获取信息：
select object_name(object_id), i.name
from sys.indexes i
where i.index_id NOT IN (select s.index_id
from sys.dm_db_index_usage_stats s
where s.object_id=i.object_id and
i.index_id=s.index_id and
database_id =
order by object_name(object_id) asc
在正中情况下，表名称和索引名称根据表明排序。
.动态管理视图的真正目的是在长时间运行时观察索引的使用情况。它可以提供视图的快照或查询结果集，将其存储，然后每天比较相应的改变。如果你能识别特殊的索引数月没有使用或者在很长时间没有使用，你可以最终从数据库中删除他们。
总结
更多信息请见
http://www.microsoft.com/sql/
附录A: DBCC MEMORYSTATUS 描述
这有一些使用DBCC MEMORYSTATUS命令的信息。可是，一些信息也可以使用动态管理视图(DMVs)获取。
SQL Server 2000 DBCC MEMORYSTATUS在http://support.microsoft.com/?id=271624中描述
SQL Server 2005 DBCC MEMORYSTATUS 在http://support.microsoft.com/?id=907877中描述
附录B: 阻塞脚本
附录提供在本白皮书中引用的存储过程源代码列表。你可以根据你的需求修改或裁减这些存储过程。
sp_block
create proc dbo.sp_block (@spid bigint=NULL)
as
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at -- http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- This proc reports blocks
-- 1. optional parameter @spid
--
select
t1.resource_type,
'database'=db_name(resource_database_id),
'blk object' = t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
from
sys.dm_tran_locks as t1,
sys.dm_os_waiting_tasks as t2
where
t1.lock_owner_address = t2.resource_address and
t1.request_session_id = isnull(@spid,t1.request_session_id)
分析操作的索引统计
这套存储过程可以用于分析索引的使用。
get_indexstats
create proc dbo.get_indexstats
(@dbid smallint=-1
,@top varchar(100)=NULL
,@columns varchar(500)=NULL
,@order varchar(100)='lock waits'
,@threshold varchar(500)=NULL)
as
--
-- This stored procedure is provided "AS IS" with no warranties, and confers no rights.
-- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- This proc analyzes index statistics including accesses, overhead,
-- locks, blocks, and waits
--
-- Instructions: Order of execution is as follows:
-- (1) truncate indexstats with init_indexstats
-- (2) take initial index snapshot using insert_indexstats
-- (3) Run workload
-- (4) take final index snapshot using insert_indexstats
-- (5) analyze with get_indexstats
-- @dbid limits analysis to a database
-- @top allows you to specify TOP n
-- @columns is used to specify what columns from
-- sys.dm_db_index_operational_stats will be included in the report
-- For example, @columns='scans,lookups,waits' will include columns
-- containing these keywords
-- @order used to order results
-- @threshold used to add a threshold,
-- example: @threshold='[block %] > 5' only include if blocking is over 5%
--
------ definition of some computed columns returned
-- [blk %] = percentage of locks that cause blocks e.g. blk% = 100 * lock waits / locks
-- [index usage] = range_scan_count + singleton_lookup_count + leaf_insert_count
-- [nonleaf index overhead]=nonleaf_insert_count + nonleaf_delete_count + nonleaf_update_count
-- [avg row lock wait ms]=row_lock_wait_in_ms/row_lock_wait_count
-- [avg page lock wait ms]=page_lock_wait_in_ms/page_lock_wait_count
-- [avg page latch wait ms]=page_latch_wait_in_ms/page_latch_wait_count
-- [avg pageio latch wait ms]=page_io_latch_wait_in_ms/page_io_latch_wait_count
---------------------------------------------------------------------------------------------------
--- Case 1 - only one snapshot of sys.dm_db_operational_index_stats was stored in
--- indexstats. This is an error - return errormsg to user
--- Case 2 - beginning snapshot taken, however some objects were not referenced
--- at the time of the beginning snapshot. Thus, they will not be in the initial
--- snapshot of sys.dm_db_operational_index_stats, use 0 for starting values.
--- Print INFO msg for informational purposes.
--- Case 3 - beginning and ending snapshots, beginning values for all objects and indexes
--- this should be the normal case, especially if SQL Server is up a long time
---------------------------------------------------------------------------------------------------
set nocount on
declare @orderby varchar(100), @where_dbid_is varchar(100), @temp varchar(500), @threshold_temptab varchar(500)
declare @cmd varchar(max),@col_stmt varchar(500),@addcol varchar(500)
declare @begintime datetime, @endtime datetime, @duration datetime, @mincount int, @maxcount int
select @begintime = min(now), @endtime = max(now) from indexstats
if @begintime = @endtime
begin
print 'Error: indexstats contains only 1 snapshot of sys.dm_db_index_operational_stats'
print 'Order of execution is as follows: '
print ' (1) truncate indexstats with init_indexstats'
print ' (2) take initial index snapshot using insert_indexstats'
print ' (3) Run workload'
print ' (4) take final index snapshot using insert_indexstats'
print ' (5) analyze with get_indexstats'
return -99
end
select @mincount = count(*) from indexstats where now = @begintime
select @maxcount = count(*) from indexstats where now = @endtime
if @mincount < @maxcount
begin
print 'InfoMsg1: sys.dm_db_index_operational_stats only contains entries for objects referenced since last SQL re-cycle'
print 'InfoMsg2: Any newly referenced objects and indexes captured in the ending snapshot will use 0 as a beginning value'
end
select @top = case
when @top is NULL then ''
else lower(@top)
end,
@where_dbid_is = case (@dbid)
when -1 then ''
else ' and i1.database_id = ' + cast(@dbid as varchar(10))
end,
--- thresholding requires a temp table
@threshold_temptab = case
when @threshold is NULL then ''
else ' select * from #t where ' + @threshold
end
--- thresholding requires temp table, add 'into #t' to select statement
select @temp = case (@threshold_temptab)
when '' then ''
else ' into #t '
end
select @orderby=case(@order)
when 'leaf inserts' then 'order by [' + @order + ']'
when 'leaf deletes' then 'order by [' + @order + ']'
when 'leaf updates' then 'order by [' + @order + ']'
when 'nonleaf inserts' then 'order by [' + @order + ']'
when 'nonleaf deletes' then 'order by [' + @order + ']'
when 'nonleaf updates' then 'order by [' + @order + ']'
when 'nonleaf index overhead' then 'order by [' + @order + ']'
when 'leaf allocations' then 'order by [' + @order + ']'
when 'nonleaf allocations' then 'order by [' + @order + ']'
when 'allocations' then 'order by [' + @order + ']'
when 'leaf page merges' then 'order by [' + @order + ']'
when 'nonleaf page merges' then 'order by [' + @order + ']'
when 'range scans' then 'order by [' + @order + ']'
when 'singleton lookups' then 'order by [' + @order + ']'
when 'index usage' then 'order by [' + @order + ']'
when 'row locks' then 'order by [' + @order + ']'
when 'row lock waits' then 'order by [' + @order + ']'
when 'block %' then 'order by [' + @order + ']'
when 'row lock wait ms' then 'order by [' + @order + ']'
when 'avg row lock wait ms' then 'order by [' + @order + ']'
when 'page locks' then 'order by [' + @order + ']'
when 'page lock waits' then 'order by [' + @order + ']'
when 'page lock wait ms' then 'order by [' + @order + ']'
when 'avg page lock wait ms' then 'order by [' + @order + ']'
when 'index lock promotion attempts' then 'order by [' + @order + ']'
when 'index lock promotions' then 'order by [' + @order + ']'
when 'page latch waits' then 'order by [' + @order + ']'
when 'page latch wait ms' then 'order by [' + @order + ']'
when 'pageio latch waits' then 'order by [' + @order + ']'
when 'pageio latch wait ms' then 'order by [' + @order + ']'
else ''
end
if @orderby <> '' select @orderby = @orderby + ' desc'
select
'start time'=@begintime,
'end time'=@endtime,
'duration (hh:mm:ss:ms)'=convert(varchar(50),
@endtime-@begintime,14),
'Report'=case (@dbid)
when -1 then 'all databases'
else db_name(@dbid)
end +
case
when @top = '' then ''
when @top is NULL then ''
when @top = 'none' then ''
else ', ' + @top
end +
case
when @columns = '' then ''
when @columns is NULL then ''
when @columns = 'none' then ''
else ', include only columns containing ' + @columns
end +
case(@orderby)
when '' then ''
when NULL then ''
when 'none' then ''
else ', ' + @orderby
end +
case
when @threshold = '' then ''
when @threshold is NULL then ''
when @threshold = 'none' then ''
else ', threshold on ' + @threshold
end
select @cmd = ' select i2.database_id, i2.object_id, i2.index_id, i2.partition_number '
select @cmd = @cmd +' , begintime=case min(i1.now) when max(i2.now) then NULL else min(i1.now) end '
select @cmd = @cmd +' , endtime=max(i2.now) '
select @cmd = @cmd +' into #i '
select @cmd = @cmd +' from indexstats i2 '
select @cmd = @cmd +' full outer join '
select @cmd = @cmd +' indexstats i1 '
select @cmd = @cmd +' on i1.database_id = i2.database_id '
select @cmd = @cmd +' and i1.object_id = i2.object_id '
select @cmd = @cmd +' and i1.index_id = i2.index_id '
select @cmd = @cmd +' and i1.partition_number = i2.partition_number '
select @cmd = @cmd +' where i1.now >= ''' + convert(varchar(100),@begintime, 109) + ''''
select @cmd = @cmd +' and i2.now = ''' + convert(varchar(100),@endtime, 109) + ''''
select @cmd = @cmd + ' ' + @where_dbid_is + ' '
select @cmd = @cmd + ' group by i2.database_id, i2.object_id, i2.index_id, i2.partition_number '
select @cmd = @cmd + ' select ' + @top + ' i.database_id, db_name=db_name(i.database_id), object=isnull(object_name(i.object_id),i.object_id), indid=i.index_id, part_no=i.partition_number '
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf inserts]=i2.leaf_insert_count -
isnull(i1.leaf_insert_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,@col_stmt=' ,
[leaf deletes]=i2.leaf_delete_count –
isnull(i1.leaf_delete_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf updates]=i2.leaf_update_count - isnull(i1.leaf_update_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf inserts]=i2.nonleaf_insert_count - isnull(i1.nonleaf_insert_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf deletes]=i2.nonleaf_delete_count - isnull(i1.nonleaf_delete_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf updates]=i2.nonleaf_update_count - isnull(i1.nonleaf_update_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf index overhead]=(i2.nonleaf_insert_count –
isnull(i1.nonleaf_insert_count,0)) + (i2.nonleaf_delete_count - isnull(i1.nonleaf_delete_count,0)) + (i2.nonleaf_update_count - isnull(i1.nonleaf_update_count,0))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf allocations]=i2.leaf_allocation_count - isnull(i1.leaf_allocation_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf allocations]=i2.nonleaf_allocation_count - isnull(i1.nonleaf_allocation_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[allocations]=(i2.leaf_allocation_count - isnull(i1.leaf_allocation_count,0)) + (i2.nonleaf_allocation_count - isnull(i1.nonleaf_allocation_count,0))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[leaf page merges]=i2.leaf_page_merge_count - isnull(i1.leaf_page_merge_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[nonleaf page merges]=i2.nonleaf_page_merge_count - isnull(i1.nonleaf_page_merge_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[range scans]=i2.range_scan_count - isnull(i1.range_scan_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing= @columns,
@col_stmt=' ,[singleton lookups]=i2.singleton_lookup_count - isnull(i1.singleton_lookup_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[index usage]=(i2.range_scan_count - isnull(i1.range_scan_count,0)) + (i2.singleton_lookup_count - isnull(i1.singleton_lookup_count,0)) + (i2.leaf_insert_count - isnull(i1.leaf_insert_count,0))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[row locks]=i2.row_lock_count - isnull(i1.row_lock_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[row lock waits]=i2.row_lock_wait_count - isnull(i1.row_lock_wait_count,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[block %]=cast (100.0 * (i2.row_lock_wait_count - isnull(i1.row_lock_wait_count,0)) / (1 + i2.row_lock_count - isnull(i1.row_lock_count,0)) as numeric(5,2))'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[row lock wait ms]=i2.row_lock_wait_in_ms - isnull(i1.row_lock_wait_in_ms,0)'
select @cmd = @cmd + @addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg row lock wait ms]=cast ((1.0*(i2.row_lock_wait_in_ms - isnull(i1.row_lock_wait_in_ms,0)))/(1 + i2.row_lock_wait_count - isnull(i1.row_lock_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page locks]=i2.page_lock_count - isnull(i1.page_lock_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page lock waits]=i2.page_lock_wait_count - isnull(i1.page_lock_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page lock wait ms]=i2.page_lock_wait_in_ms - isnull(i1.page_lock_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg page lock wait ms]=cast ((1.0*(i2.page_lock_wait_in_ms - isnull(i1.page_lock_wait_in_ms,0)))/(1 + i2.page_lock_wait_count - isnull(i1.page_lock_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[index lock promotion attempts]=i2.index_lock_promotion_attempt_count - isnull(i1.index_lock_promotion_attempt_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[index lock promotions]=i2.index_lock_promotion_count - isnull(i1.index_lock_promotion_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page latch waits]=i2.page_latch_wait_count - isnull(i1.page_latch_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[page latch wait ms]=i2.page_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg page latch wait ms]=cast ((1.0*(i2.page_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)))/(1 + i2.page_latch_wait_count - isnull(i1.page_latch_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[pageio latch waits]=i2.page_io_latch_wait_count - isnull(i1.page_latch_wait_count,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[pageio latch wait ms]=i2.page_io_latch_wait_in_ms - isnull(i1.page_latch_wait_in_ms,0)'
select @cmd = @cmd +@addcol
exec dbo.add_column
@add_stmt=@addcol out,
@cols_containing=@columns,
@col_stmt=' ,[avg pageio latch wait ms]=cast ((1.0*(i2.page_io_latch_wait_in_ms - isnull(i1.page_io_latch_wait_in_ms,0)))/(1 + i2.page_io_latch_wait_count - isnull(i1.page_io_latch_wait_count,0)) as numeric(20,1))'
select @cmd = @cmd +@addcol
select @cmd = @cmd + @temp
select @cmd = @cmd + ' from #i i '
select @cmd = @cmd + ' left join indexstats i1 on i.begintime = i1.now and i.database_id = i1.database_id and i.object_id = i1.object_id and i.index_id = i1.index_id and i.partition_number = i1.partition_number '
select @cmd = @cmd + ' left join indexstats i2 on i.endtime = i2.now and i.database_id = i2.database_id and i.object_id = i2.object_id and i.index_id = i2.index_id and i.partition_number = i2.partition_number '
select @cmd = @cmd + ' ' + @orderby + ' '
select @cmd = @cmd + @threshold_temptab
exec ( @cmd )
go
insert_indexstats
create proc insert_indexstats (@dbid smallint=NULL,
@objid int=NULL,
@indid int=NULL,
@partitionid int=NULL)
as
--
-- This stored procedure is provided "AS IS" with no warranties, and confers no rights.
-- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
-- This stored procedure stores a snapshot of sys.dm_db_index_operational_stats into the table indexstas
-- for later analysis by the stored procedure get_indexstats. Please note that the indexstats table has an additional
-- column to store the timestamp when the snapshot is taken
--
-- T. Davidson
-- snapshot sys.dm_db_index_operational_stats
--
declare @now datetime
select @now = getdate()
insert into indexstats
(database_id
,object_id
,index_id
,partition_number
,leaf_insert_count
,leaf_delete_count
,leaf_update_count
,leaf_ghost_count
,nonleaf_insert_count
,nonleaf_delete_count
,nonleaf_update_count
,leaf_allocation_count
,nonleaf_allocation_count
,leaf_page_merge_count
,nonleaf_page_merge_count
,range_scan_count
,singleton_lookup_count
,forwarded_fetch_count
,lob_fetch_in_pages
,lob_fetch_in_bytes
,lob_orphan_create_count
,lob_orphan_insert_count
,row_overflow_fetch_in_pages
,row_overflow_fetch_in_bytes
,column_value_push_off_row_count
,column_value_pull_in_row_count
,row_lock_count
,row_lock_wait_count
,row_lock_wait_in_ms
,page_lock_count
,page_lock_wait_count
,page_lock_wait_in_ms
,index_lock_promotion_attempt_count
,index_lock_promotion_count
,page_latch_wait_count
,page_latch_wait_in_ms
,page_io_latch_wait_count
,page_io_latch_wait_in_ms,
now)
select database_id
,object_id
,index_id
,partition_number
,leaf_insert_count
,leaf_delete_count
,leaf_update_count
,leaf_ghost_count
,nonleaf_insert_count
,nonleaf_delete_count
,nonleaf_update_count
,leaf_allocation_count
,nonleaf_allocation_count
,leaf_page_merge_count
,nonleaf_page_merge_count
,range_scan_count
,singleton_lookup_count
,forwarded_fetch_count
,lob_fetch_in_pages
,lob_fetch_in_bytes
,lob_orphan_create_count
,lob_orphan_insert_count
,row_overflow_fetch_in_pages
,row_overflow_fetch_in_bytes
,column_value_push_off_row_count
,column_value_pull_in_row_count
,row_lock_count
,row_lock_wait_count
,row_lock_wait_in_ms
,page_lock_count
,page_lock_wait_count
,page_lock_wait_in_ms
,index_lock_promotion_attempt_count
,index_lock_promotion_count
,page_latch_wait_count
,page_latch_wait_in_ms
,page_io_latch_wait_count
,page_io_latch_wait_in_ms
,@now
from sys.dm_db_index_operational_stats(@dbid,@objid,@indid,@partitionid)
go
init_index_operational_stats
CREATE proc dbo.init_index_operational_stats
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
--
-- create indexstats table if it doesn't exist, otherwise truncate
--
set nocount on
if not exists (select 1 from dbo.sysobjects where id=object_id(N'[dbo].[indexstats]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
create table dbo.indexstats (
database_id smallint NOT NULL
,object_id int NOT NULL
,index_id int NOT NULL
,partition_number int NOT NULL
,leaf_insert_count bigint NOT NULL
,leaf_delete_count bigint NOT NULL
,leaf_update_count bigint NOT NULL
,leaf_ghost_count bigint NOT NULL
,nonleaf_insert_count bigint NOT NULL
,nonleaf_delete_count bigint NOT NULL
,nonleaf_update_count bigint NOT NULL
,leaf_allocation_count bigint NOT NULL
,nonleaf_allocation_count bigint NOT NULL
,leaf_page_merge_count bigint NOT NULL
,nonleaf_page_merge_count bigint NOT NULL
,range_scan_count bigint NOT NULL
,singleton_lookup_count bigint NOT NULL
,forwarded_fetch_count bigint NOT NULL
,lob_fetch_in_pages bigint NOT NULL
,lob_fetch_in_bytes bigint NOT NULL
,lob_orphan_create_count bigint NOT NULL
,lob_orphan_insert_count bigint NOT NULL
,row_overflow_fetch_in_pages bigint NOT NULL
,row_overflow_fetch_in_bytes bigint NOT NULL
,column_value_push_off_row_count bigint NOT NULL
,column_value_pull_in_row_count bigint NOT NULL
,row_lock_count bigint NOT NULL
,row_lock_wait_count bigint NOT NULL
,row_lock_wait_in_ms bigint NOT NULL
,page_lock_count bigint NOT NULL
,page_lock_wait_count bigint NOT NULL
,page_lock_wait_in_ms bigint NOT NULL
,index_lock_promotion_attempt_count bigint NOT NULL
,index_lock_promotion_count bigint NOT NULL
,page_latch_wait_count bigint NOT NULL
,page_latch_wait_in_ms bigint NOT NULL
,page_io_latch_wait_count bigint NOT NULL
,page_io_latch_wait_in_ms bigint NOT NULL
,now datetime default getdate())
else truncate table dbo.indexstats
go
add_column
create proc dbo.add_column (
@add_stmt varchar(500) output,
@find varchar(100)=NULL,
@cols_containing varchar(500)=NULL,
@col_stmt varchar(max))
as
--
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- @add_stmt is the result passed back to the caller
-- @find is a keyword from @cols_containing
-- @cols_containing is the list of keywords to include in the report
-- @col_stmt is the statement that will be compared with @find.
-- If @col_stmt contains @find, include this statement.
-- set @add_stmt = @col_stmt
--
declare @length int, @strindex int, @EOS bit
if @cols_containing is NULL
begin
select @add_stmt=@col_stmt
return
end
select @add_stmt = '', @EOS = 0
while @add_stmt is not null and @EOS = 0
@dbid=-1,
select @strindex = charindex(',',@cols_containing)
if @strindex = 0
select @find = @cols_containing, @EOS = 1
else
begin
select @find = substring(@cols_containing,1,@strindex-1)
select @cols_containing =
substring(@cols_containing,
@strindex+1,
datalength(@cols_containing) - @strindex)
end
select @add_stmt=case
--when @cols_containing is NULL then NULL
when charindex(@find,@col_stmt) > 0 then NULL
else ''
end
end
--- NULL indicates this statement is to be passed back through out parm @add_stmt
if @add_stmt is NULL select @add_stmt=@col_stmt
go
等待状态
这套存储过程可以在SQL Server中分析锁。
track_waitstats_2005
CREATE proc [dbo].[track_waitstats_2005] (
@num_samples int=10,
@delay_interval int=1,
@delay_type nvarchar(10)='minutes',
@truncate_history nvarchar(1)='N',
@clear_waitstats nvarchar(1)='Y')
as
--
-- This stored procedure is provided "AS IS" with no warranties, and confers no rights.
-- Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm
--
-- T. Davidson
-- @num_samples is the number of times to capture waitstats, default is 10 times
-- default delay interval is 1 minute
-- delaynum is the delay interval - can be minutes or seconds
-- delaytype specifies whether the delay interval is minutes or seconds
-- create waitstats table if it doesn't exist, otherwise truncate
-- Revision: 4/19/05
--- (1) added object owner qualifier
--- (2) optional parameters to truncate history and clear waitstats
set nocount on
if not exists (select 1
from sys.objects
where object_id = object_id ( N'[dbo].[waitstats]') and
OBJECTPROPERTY(object_id, N'IsUserTable') = 1)
create table [dbo].[waitstats]
([wait_type] nvarchar(60) not null,
[waiting_tasks_count] bigint not null,
[wait_time_ms] bigint not null,
[max_wait_time_ms] bigint not null,
[signal_wait_time_ms] bigint not null,
now datetime not null default getdate())
If lower(@truncate_history) not in (N'y',N'n')
begin
raiserror ('valid @truncate_history values are ''y'' or ''n''',16,1) with nowait
end
If lower(@clear_waitstats) not in (N'y',N'n')
begin
raiserror ('valid @clear_waitstats values are ''y'' or ''n''',16,1) with nowait
end
If lower(@truncate_history) = N'y'
truncate table dbo.waitstats
If lower (@clear_waitstats) = N'y'
-- clear out waitstats
dbcc sqlperf ([sys.dm_os_wait_stats],clear) with no_infomsgs
declare @i int,
@delay varchar(8),
@dt varchar(3),
@now datetime,
@totalwait numeric(20,1),
@endtime datetime,
@begintime datetime,
@hr int,
@min int,
@sec int
select @i = 1
select @dt = case lower(@delay_type)
when N'minutes' then 'm'
when N'minute' then 'm'
when N'min' then 'm'
when N'mi' then 'm'
when N'n' then 'm'
when N'm' then 'm'
when N'seconds' then 's'
when N'second' then 's'
when N'sec' then 's'
when N'ss' then 's'
when N's' then 's'
else @delay_type
end
if @dt not in ('s','m')
begin
raiserror ('delay type must be either ''seconds'' or ''minutes''',16,1) with nowait
return
end
if @dt = 's'
begin
select @sec = @delay_interval % 60, @min = cast((@delay_interval / 60) as int), @hr = cast((@min / 60) as int)
end
if @dt = 'm'
begin
select @sec = 0, @min = @delay_interval % 60, @hr = cast((@delay_interval / 60) as int)
end
select @delay= right('0'+ convert(varchar(2),@hr),2) + ':' +
+ right('0'+convert(varchar(2),@min),2) + ':' +
+ right('0'+convert(varchar(2),@sec),2)
if @hr > 23 or @min > 59 or @sec > 59
begin
select 'delay interval and type: ' + convert (varchar(10),@delay_interval) + ',' + @delay_type + ' converts to ' + @delay
raiserror ('hh:mm:ss delay time cannot > 23:59:59',16,1) with nowait
return
end
while (@i <= @num_samples)
begin
select @now = getdate()
insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
@now
from sys.dm_os_wait_stats
insert into [dbo].[waitstats] (
[wait_type],
[waiting_tasks_count],
[wait_time_ms],
[max_wait_time_ms],
[signal_wait_time_ms],
now)
select
'Total',
sum([waiting_tasks_count]),
sum([wait_time_ms]),
0,
sum([signal_wait_time_ms]),
@now
from [dbo].[waitstats]
where now = @now
select @i = @i + 1
waitfor delay @delay
end
--- create waitstats report
execute dbo.get_waitstats_2005
go
exec dbo.track_waitstats @num_samples=6
,@delay_interval=30
,@delay_type='s'
,@truncate_history='y'
,@clear_waitstats='y'
get_waitstats_2005
CREATE proc [dbo].[get_waitstats_2005] (
@report_format varchar(20)='all',
@report_order varchar(20)='resource')
as
-- This stored procedure is provided "AS IS" with no warranties, and
-- confers no rights.
-- Use of included script samples are subject to the terms specified at
-- http://www.microsoft.com/info/cpyright.htm
--
-- this proc will create waitstats report listing wait types by
-- percentage.
-- (1) total wait time is the sum of resource & signal waits,
-- @report_format='all' reports resource & signal
-- (2) Basics of execution model (simplified)
-- a. spid is running then needs unavailable resource, moves to
-- resource wait list at time T0
-- b. a signal indicates resource available, spid moves to
-- runnable queue at time T1
-- c. spid awaits running status until T2 as cpu works its way
-- through runnable queue in order of arrival
-- (3) resource wait time is the actual time waiting for the
-- resource to be available, T1-T0
-- (4) signal wait time is the time it takes from the point the
-- resource is available (T1)
-- to the point in which the process is running again at T2.
-- Thus, signal waits are T2-T1
-- (5) Key questions: Are Resource and Signal time significant?
-- a. Highest waits indicate the bottleneck you need to solve
-- for scalability
-- b. Generally if you have LOW% SIGNAL WAITS, the CPU is
-- handling the workload e.g. spids spend move through
-- runnable queue quickly
-- c. HIGH % SIGNAL WAITS indicates CPU can't keep up,
-- significant time for spids to move up the runnable queue
-- to reach running status
-- (6) This proc can be run when track_waitstats is executing
--
-- Revision 4/19/2005
-- (1) add computation for CPU Resource Waits = Sum(signal waits /
-- total waits)
-- (2) add @report_order parm to allow sorting by resource, signal
-- or total waits
--
set nocount on
declare @now datetime,
@totalwait numeric(20,1),
@totalsignalwait numeric(20,1),
@totalresourcewait numeric(20,1),
@endtime datetime,@begintime datetime,
@hr int,
@min int,
@sec int
if not exists (select 1
from sysobjects
where id = object_id ( N'[dbo].[waitstats]') and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
raiserror('Error [dbo].[waitstats] table does not exist',
16, 1) with nowait
return
end
if lower(@report_format) not in ('all','detail','simple')
begin
raiserror ('@report_format must be either ''all'',
''detail'', or ''simple''',16,1) with nowait
return
end
if lower(@report_order) not in ('resource','signal','total')
begin
raiserror ('@report_order must be either ''resource'',
''signal'', or ''total''',16,1) with nowait
return
end
if lower(@report_format) = 'simple' and lower(@report_order) <> 'total'
begin
raiserror ('@report_format is simple so order defaults to ''total''',
16,1) with nowait
select @report_order = 'total'
end
select
@now=max(now),
@begintime=min(now),
@endtime=max(now)
from [dbo].[waitstats]
where [wait_type] = 'Total'
--- subtract waitfor, sleep, and resource_queue from Total
select @totalwait = sum([wait_time_ms]) + 1, @totalsignalwait = sum([signal_wait_time_ms]) + 1
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total' ,'WAITFOR',
'***total***') and
now = @now
select @totalresourcewait = 1 + @totalwait - @totalsignalwait
-- insert adjusted totals, rank by percentage descending
delete waitstats
where [wait_type] = '***total***' and
now = @now
insert into waitstats
select
'***total***',
0,@totalwait,
0,
@totalsignalwait,
@now
select 'start time'=@begintime,'end time'=@endtime,
'duration (hh:mm:ss:ms)'=convert(varchar(50),@endtime-@begintime,14),
'report format'=@report_format, 'report order'=@report_order
if lower(@report_format) in ('all','detail')
begin
----- format=detail, column order is resource, signal, total. order by resource desc
if lower(@report_order) = 'resource'
select [wait_type],[waiting_tasks_count],
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as numeric(20,1)),
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as numeric(20,1)),
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'res_wt_%' desc
----- format=detail, column order signal, resource, total. order by signal desc
if lower(@report_order) = 'signal'
select [wait_type],
[waiting_tasks_count],
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as numeric(20,1)),
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as numeric(20,1)),
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'sig_wt_%' desc
----- format=detail, column order total, resource, signal. order by total desc
if lower(@report_order) = 'total'
select
[wait_type],
[waiting_tasks_count],
'Total wt (T2-T0)'=[wait_time_ms],
'wt_%'=cast (100*[wait_time_ms]/@totalwait as numeric(20,1)),
'Resource wt (T1-T0)'=[wait_time_ms]-[signal_wait_time_ms],
'res_wt_%'=cast (100*([wait_time_ms] -
[signal_wait_time_ms]) /@totalresourcewait as numeric(20,1)),
'Signal wt (T2-T1)'=[signal_wait_time_ms],
'sig_wt_%'=cast (100*[signal_wait_time_ms]/@totalsignalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by 'wt_%' desc
end
else
---- simple format, total waits only
select
[wait_type],
[wait_time_ms],
percentage=cast (100*[wait_time_ms]/@totalwait as numeric(20,1))
from waitstats
where [wait_type] not in (
'CLR_SEMAPHORE',
'LAZYWRITER_SLEEP',
'RESOURCE_QUEUE',
'SLEEP_TASK',
'SLEEP_SYSTEMTASK',
'Total',
'WAITFOR') and
now = @now
order by percentage desc
---- compute cpu resource waits
select
'total waits'=[wait_time_ms],
'total signal=CPU waits'=[signal_wait_time_ms],
'CPU resource waits % = signal waits / total waits'=
cast (100*[signal_wait_time_ms]/[wait_time_ms] as numeric(20,1)),
now
from [dbo].[waitstats]
where [wait_type] = '***total***'
order by now
go
declare @now datetime
select @now = getdate()
select getdate()
