高併發下,SQL Server 鎖升級(Row → Page → Table)易導致死鎖、封鎖鏈,嚴重拖慢查詢。
SELECT COUNT(UserID) FROM Users WITH (NOLOCK) WHERE Username LIKE 'football%';
UPDATE Users WITH (ROWLOCK) SET Username = 'admin' WHERE Username = 'football';
-- 無 NOLOCK DECLARE @s DATETIME = GETDATE(); SELECT * FROM Captions_t; SELECT DATEDIFF(MS, @s, GETDATE()); -- 34720 ms -- 有 NOLOCK DECLARE @s DATETIME = GETDATE(); SELECT * FROM Captions_t18 WITH (NOLOCK); SELECT DATEDIFF(MS, @s, GETDATE()); -- 2563 ms

WITH (NOLOCK) 標準語法。WITH (NOLOCK) 易於搭配索引提示:WITH (NOLOCK, INDEX(idx_name))。WITH (NOLOCK),需用 NOLOCK(舊語法)。WITH (NOLOCK)。ROWLOCK,避免鎖升級。sys.dm_tran_locks、sys.dm_os_waiting_tasks 發現熱點再針對性加提示。