版本存儲
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：跟蹤長時間的阻塞和死鎖
|||新聞熱點
疑難解答
