網站中一些老頁面仍采用Row_number類似的開窗函數進行分頁處理,此時如果遭遇挖墳帖的情形可能就需要漫長的等待且消耗巨大.這裡給大家介紹根據Row_number()特性采用特定鎖Hint提升查詢速度.
直接上菜
腳本環境可在SQL Server優化技巧之SQL Server中的"MapReduce"找到
如下查詢在分頁中比較常見
set statistics time on
select * from
(
select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)
from [bigTransactionHistory]
) as t
where t.rn between 15631801 and 15631802 這條查詢在我的電腦上執行了15S,這還是資料全在記憶體中的情形!如圖1-1
圖1-1
一個簡單的執行計劃執行如此之長有點匪夷所思,畢竟邏輯讀才6W多,且無實體讀
,而且CPU時間與占用時間相差無幾,排除了阻塞之類的因素後我們把消耗定位在這個查詢本身上.這時提一個Row_number()的特點,它可在萬千資料中将其序列化讓我們找到我們想要的精确資料點,但就此預設的實作方式上是為每一行資料都加一個行鎖.
我們開啟Trace Flag 1200再次執行語句捕捉下執行時的鎖.可以看到Row_number()在實作上未進行鎖更新如圖1-2
Code
dbcc traceon(3604,1200,-1)
select * from
(
select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)
from [bigTransactionHistory]
) as t
where t.rn between 15631801 and 15631802 View Code
圖1-2
到此我們對此問題的解決方式也就出來了:可采用鎖hint的形式手動為其更新
這裡我采用頁鎖,如圖1-3
而兩者從執行計劃上看是相同的,預估也完全一樣如圖1-4
select * from
(
select ProductID, rn = ROW_NUMBER() OVER (ORDER BY ProductID)
from [bigTransactionHistory] with(paglock)
) as t
where t.rn between 15631801 and 15631802 圖1-3
圖1-4
可以看到我們通常的檢視執行計劃的方式在此就不太适合了,需要我們對資源消耗有更詳細的認知.
注:平時我們還可用Trace Profiler捕捉鎖,但需注意慎用.
Row_number()預設看不到鎖更新,全局性能瓶頸下可能回更新
如果你的應用不在乎髒讀,nolock方式更愉快:)
其它:當資料被更新發生阻塞時,有時業務同僚會問到底更新了哪條資料有木有?
這裡寫了個簡單的查詢以便找到具體更新被鎖住的行,如圖2-1
begin tran ttt
update dbo.[bigProduct] set size=111 where ProductID<1100
-- rollback when finish test
--rollback tran ttt
--open another session
SELECT * FROM [bigProduct] with(nolock)
WHERE
%%LOCKRES%% IN
(
SELECT
tl.resource_description
FROM sys.dm_tran_locks AS tl
INNER JOIN sys.partitions AS t2 ON
t2.hobt_id = tl.resource_associated_entity_id
WHERE
t2.object_id = OBJECT_ID('bigProduct')
AND tl.resource_type = 'KEY'
) 圖2-1
結語:系統内任何元素都有可能成為影響平衡的絆腳石.找到它,了解它,利用它.
認為有收獲的同學請點贊.
Involuntary DBA