SQL Server 2005性能排错白皮书(Part 2)---From MS Customer Support Service部门
来源：互联网 发布：淘宝如何一件代发 编辑：程序博客网 时间：2024/06/01 22:28
Tempdb
Tempdb用于全局存储内部或用户对象，临时表，对象和在SQL Server操作是创建的存储过程。每鯯QL Server 实例只有1个单一的tempdb。它可能是一个性能和磁盘空间的瓶颈。有限可用空间和过多的DDL/DML会使Tempdb超过负载。这能导致运行在同一个服务器中的其他无关应用变得运行缓慢或失败。
下面列出一些tempdb的常规问题：
◆Tempdb磁盘空间不足
◆由于tempdb中的I/O瓶颈，导致查询运行缓慢。这在I/O瓶颈部分提到过。
◆过多DDL操作导致系统表的瓶颈
◆分配争夺
Before we start diagnosing problems with tempdb, let us first look at how the space in tempdb is used. It can be grouped into four main categories.当开始调试tempdb问题前，让我们先看一下在tempdb中如何使用空间。它可以分为4个主要的类别。
用户对象
这些对象被用户会话显示创建并在系统目录中被跟踪。这包括：
表和索引
全局临时表(##t1)和索引
本地临时表(#t1)和索引
会话范围
存储过程范围内创建
表变量(@t1).
会话范围
存储过程范围内创建
内部对象
这有语句范围的对象，通过SQL Server处理的查询创建和销毁。这些对象不能被系统目录跟踪。这包括：
工作文件(hash join)
排序
工作表 (游标, 池 和临时大对象数据类型 (LOB)存储 ) 作为优化，当工作表被删除，一个IAM页和一个扩展被保存到一个新的工作表。这有2个例外：临时LOB存储是批范围的和游标工作表示会话范围的版本存储用于存储行版本。MARS，在索引因操作，触发器和快照隔离级别都是基于行版本。这是SQL Server 2005中新的特性。
可用空间
这表示在tempdb数据库可用的磁盘空间。tempdb使用的总空间等于用户对象加内部对象加版本存储+可用空间。这个可用空间等于性能计数器中tempdb的可用空间。监视tempdb空间阻止问题更好的方法是随后马上解决它。你可以使用下列性能监视器来监视使用中的tempdb空间。
◆Free Space in tempdb (KB).这个计数器以KB为单位跟踪空闲空间的数量。管理员可以使用这个计数器确定是否tempdb运行在较低的磁盘空间上。
然而，识别前面所定义的不同类别如何使用在tempdb中使用的磁盘空间，是很有兴趣的并有效率的。
下列查询返回了tempdb用户使用的空间和内部组件对象。当前它提供了仅有tempdb中的信息。
SelectSUM (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_kbFrom sys.dm_db_file_space_usageWhere database_id = 2
这里有一些示例的输出（空间使用KB为单位）。
user_objets_kb internal_objects_kb version_store_kb freespace_kb---------------- -------------------- ------------------ ------------8736 128 64 448
注意这些技术不包括其中的混合扩展。页面在混合扩展中可以被分配个别用户和内部对象。
磁盘空间问题排错
用户对象，内部对象和存储的版本可以都可以导致在tempdb中的空间问题。这节我们会考虑针对每个类别如何排错。
用户对象
因为用户对象不被特定的会话拥有，你需要理解创建该对象的应用程序的规范并调整需要的tempdb大小。你可以通过执行exec sp_spaceused @objname='<user-object>'来找到个别用户对象使用的。例如，你可以运行下列脚本枚举所有tempdb对象。
DECLARE userobj_cursor CURSOR FOR select sys.schemas.name + '.' + sys.objects.name from sys.objects, sys.schemaswhere object_id > 100 and type_desc = 'USER_TABLE'and sys.objects.schema_id = sys.schemas.schema_idgoopen userobj_cursorgodeclare @name varchar(256)fetch userobj_cursor into @namewhile (@@FETCH_STATUS = 0) beginexec sp_spaceused @objname = @namefetch userobj_cursor into @name endclose userobj_cursor
版本存储
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_transactionsorder by elapsed_time_seconds DESC
这是示例的输入，显示了序列号为3，事务ID为8609的事务已经运行了6523秒。
transaction_id transaction_sequence_num elapsed_time_seconds-------------------- ------------------------ --------------------8609 3 652320156 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将耗尽内存。
内部对象
内部对象在每条语句中被创建和销毁，除非想在前面所描述的。如果你注意到有大量的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_countfrom sys.dm_db_session_space_usageorder 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 t2where t1.session_id = t2.session_id and t1.session_id >50order by allocated DESC
这是示例的输出。
session_id allocated deallocated---------- -------------------- --------------------52 5120 513651 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_handlefrom (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 t2where 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到一个适当的大小。
过多的DLL和分配操作
在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 timestampdeclare @now datetime select @now = getdate()-- insert data into a table for later analysisinsert into analysis..waiting_tasksselect session_id, wait_duration_ms, resource_description, @nowfrom sys.dm_os_waiting_taskswhere wait_type like ‘PAGE%LATCH_%’ andresource_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
解决
如果是由于过多的DDL操作导致在tempdb争用，你需要考虑你的应用程序，并查看是否你能减少DDL操作。你可以尝试下列建议。
◆如果你使用存储过程范围内的临时表，考虑是否这些表可以移动到存储过程外。否则每次执行存储过程将会导致创建/删除临时表。
◆查看查询计划，是否一些计划创建大量的临时对象，池，排序或工作表。你需要评估一些临时对象。例如，在一个列上创建一个用于ORDER BY操作的索引可以除去查询时的排序
如果争用是由于在SGAM 和PFS页面上的争用，你可以通过尝试下列操作减轻争用：
◆通过增加tempdb数据文件将等量负载分布在所有磁盘和文件上。理论上，你应该将文件数量设置为CPU数量等同（主要考虑亲和性）。
◆使用TF-1118消除混合扩展分配。
运行缓慢的查询
缓慢或长时间运行的查询可以占用过多资源并能导致阻塞查询。
过多的资源占用是没有限制CPU资源的使用，但是也能包括I/O存储带宽和内存带宽。即使SQL Server查询被设计为可以通过合理WHERE子句限制结果集的方法避免整表扫描 ，如果没有合适的索引支持这个特殊的查询，他们可能不会按照我们期望的方式执行。同样，WHERE子句能依赖于用户输入被动态的通过应用程序构建。假设存在的索引不能覆盖所有可能的约束。通过Transact-SQL语句占用过度的CPU，I/O和内存的情况在本白皮书前面已经描述了。
除了缺失索引外，也可能有索引没有被使用。当所有的索引不得不维护时，这不影响查询的性能，但是影响DML查询。
因为等待逻辑锁和系统资源的状态会阻塞查询，查询也会运行的比较缓慢。阻塞的原因可能是较差的应用程序设计，坏的查询计划，缺乏有用的索引和不正确的SQL Server实例配置。
这节主要集中在缓慢查询的2个原因-阻塞和索引问题。
阻塞
阻塞主要是等待逻辑锁，例如等待在资源上获取排他锁或等待从更低级别的同步结果，例如闩。
当做出一个在已经锁定的资源上获得一个不兼容的锁的请求产生时，逻辑锁等待发生。在特殊的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=56waiting_task_address session_id exec_context_id wait_duration_ms wait_typeresource_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存储过程。
识别长时间的阻塞
之前我们提到，在SQL Server中阻塞是很正常的，使用逻辑锁来维护事务一致性的。然而当等待的锁超过了阀值，它会影响响应时间。为了识别长时间运行的阻塞，你能使用BlockedProcessThreshold配置参数来建立一个用户配置的服务端阻塞阀值。阀值定义一个秒级的间隔。任何超过阀值的阻塞将出发事件并被SQL Trace捕获。
例如，1个200秒的阻塞进程阀值可以在SQL Management Studio中配置。例如：
Execute Sp_configure ‘blocked process threshold’, 200
Reconfigure with override
一旦阻塞处理阀值被建立，下一步是捕获跟踪的事件。跟踪阻塞超过用户配置的阀值事件可以通过SQL Trace 或Profiler捕获。
1.如果使用SQL Trace，使用sp_trace_setevent过程，event_id参数为137
2.如果使用SQL Server Profiler，选择Blocked Process Report 事件类（在Error和Warnings对象下），如图2。
图2：跟踪长时间的阻塞和死锁
注意这是一个轻量级的跟踪，事件仅在当锁超过阀值，或发生死锁时被捕获。每有200秒的间隔一个锁被阻塞，1个跟踪事件被触发。图3意味着1个锁阻塞了600秒，发生了3次跟踪事件。
图3：Reporting Blocking > block threshold
跟踪事件包括阻塞者和被阻塞者整个SQL语句。图中所示”Update Customer”阻塞了”Select from Customer”语句。
通过与SQL Server 2000比较，检查长期阻塞场景代码在Sysprocesses并在后续处理结果。知识库文章271509包含了一段可以用于监视阻塞的示例代码。
通过sys.dm_db_index_operational_stats查看阻塞的每个对象
新的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'
◆获取所有数据库行锁等待大于1，列包含‘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示例，阻塞分析报告
图4：阻塞分析报告
SQL Server 2000部提供任何对象或索引的统计利用信息。
使用SQL waits阻塞对整体性能的影响
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：等待统计分析报告
图5中现实的等待状态分析报告预示了由于阻塞(LCK_M_S)和内存分配(RESOURCE_SEMAPHORE)的性能问题。特定的55%的所有等待是等待共享锁，而43%是由于内存请求。阻塞每个对象的分析将是识别主要的争用点。
监视索引的使用
其他方面的查询性能与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('<table_name>') 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 = <dbid> )
所有没有被使用的索引仍可以通过下列语句获取信息：
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 = <dbid> )
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()
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/usher_gml/archive/2009/08/03/4403485.aspx
- SQL Server 2005性能排错白皮书(Part 2)---From MS Customer Support Service部门
- SQL Server 2005性能排错白皮书(Part 2)---From MS Customer Support Service部门
- SQL Server 2005性能排错白皮书(Part 1)---From MS Customer Support Service部门
- SQL Server 2005性能排错白皮书(Part 1)---From MS Customer Support Service部门
- SQL Server 2005的性能故障白皮书
- SQL Server 2005的性能故障白皮书
- SQL Server 2005性能排错(1)
- SQL Server 2005性能排错
- SQL Server 2005性能排错
- SQL Server 2005性能排错
- Microsoft SQL Server 2005 Beta 2 白皮书
- SQL server blogs from MS (RID lookup)
- MS-SQL SERVER(2)
- [SQL Server]性能排错之系统等待：WRITELOG
- 使用PowerShell排错----使用PowerShell调校SQL Server性能
- 关于MS Sql Server性能问题
- SQL Server 2005 Analysis Services性能指南 Part 2 - 优化维度设计
- SQL Server 2005 Analysis Services性能指南 Part 2 - 优化维度设计
- 七界传说后传第八卷阅读 手打文字版
- 样式里定义td的事件
- SQL Server 2005性能排错白皮书(Part 1)---From MS Customer Support Service部门
- 设置progressbar,能按照巨型条进行计算(递减)
- 9.5 小 结
- SQL Server 2005性能排错白皮书(Part 2)---From MS Customer Support Service部门
- JavaScript counter
- android编译单个项目
- SQLite中的时间日期函数
- 听说shell能检测硬盘，CPU，内存，模拟登录
- 一生必须知道的几条哲理！还可以学英语哦~
- MTK工作中可以使用的批处理脚本
- 【原创--荐】毕业半年之后的我
- PB通用按钮的代码與日期函數
