版本存储
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]
|||注册会员，创建你的web开发资料库，在所有使用了基于孤立级别行版本，为一个事物每分钟生成版本存储的数据和每分钟生成的日志一样。然而这也有一些异常：只有更新的差异部分生成日志；如果使用了批量导入操作并且恢复模式不是完全恢复时，新插入的数据行不依赖日志，则不被记录版本。
你也可以使用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_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这是示例的输出。
|||商业源码热门下载www.html.org.cn
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 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 methodsworkfiles created /sec
◆sql server:access methodsworktables created /sec
◆sql server:access methodsmixed page allocations /sec
◆sql server:general statisticstemp tables created /sec
◆sql server:general statisticstemp 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=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 |||，欢迎访问网页设计爱好者web开发。 实际上，你能连接上面的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：跟踪长时间的阻塞和死锁
|||新闻热点
疑难解答
