SQL Server process的狀態為sleeping，
如果一個資料庫有太多的sleeping process會有影響嗎?這些process是否可能封鎖其他process呢?
前幾天朋友詢問一個blocking問題，一條執行select的process被另一條執行insert的process封鎖，
且該insert的process狀態為sleeping，但select的process有where pk條件，
怎麼還會產生blocking呢(SQL Server預設都是row lock)?
到底什麼樣情況才會造成，是否有什麼好方法可以避免這情形的blocking?
我個人覺得因該是孤兒交易且存取相同資源造成的，雖然這些交易process都是sleeping，
但之前所佔用物件資源可不會自動釋放(除非有人rollback這些孤兒交易)，
要避免交易形成孤兒，我會建議使用xact_abort來處理，由於我個人都習慣把交易包在SP，
不會直接使用EF來處理(但tinyORM卻實現這重要機制)，只要有正確控制系統所有交易的commit或rollback，
我相信絕對可以減少blocking時間，並降低deadlock發生頻率，下面我簡單重現這情況
Note:你永遠要知道一條process表示正在連接SQL Server，假設一條process需要1MB記憶體來控制，
你可想而知這些sleeping的process會不會耗用系統資源。
你可以自行測試看看，迴圈open SQL Server 800次，
且ADO.net(記得connection pool設定超過800)不要執行SqlConnection.Close(有正確close，才會正確sp_Reset Connection)，
這時你會發現產生800條process且狀態都是sleeping，同時觀察記憶體用量變化。
--session 1
begin tran
insert into cityname select 6,N'sleep'--step1
insert into cityname select 7,N'sleep'--step3
declare @myspid int = @@SPID
select
l.request_session_id as [SPID]
,object_name(p.object_id) as [Object]
,i.name as [Index]
,l.resource_type as [Lock Type]
,l.resource_description as [Resource]
,l.request_mode as [Mode]
,l.request_status as [Status]
,wt.blocking_session_id as [Blocked By]
from
sys.dm_tran_locks l join sys.partitions p on
p.hobt_id = l.resource_associated_entity_id
join sys.indexes i on
p.object_id = i.object_id and
p.index_id = i.index_id
left outer join sys.dm_os_waiting_tasks wt on
l.lock_owner_address = wt.resource_address and
l.request_status = 'WAIT'
where
resource_type = 'KEY' and
request_session_id = @myspid
該交易還未commit或rollback，我們可以看到該交易在clustered index的某一個key放上了X Lock。
開啟另一個session 執行 EXEC sp_whoisactive 54
這時交易的process狀態為sleeping。
我們再開啟另一個session執行下面4句select，大家認為那些查詢可以正常返回資料呢?
select * from cityname where id=5--a
select * from cityname where id=2--b
select * from cityname--c
select * from cityname where id=6--d
答案:a和b都能正常返回資料，c,d因為都在等待交易process釋放資源，所以會形成blocking，
或許你可能會說，發生deadlock應該就可解決原本孤兒交易的process，
但我的經驗告訴我，這機率非常低，除非你預先使用DEADLOCK_PRIORITY來決定誰該犧牲，
下面我在開啟另一個session，模擬資料順序不同所導致的deadlock
begin tran
insert into cityname select 7,N'sleep'--step2
insert into cityname select 6,N'sleep'--step4
這時該process已經犧牲了，但原來一開始交易的process依然還是會佔用資源，
而select的process發生blocking問題依然未解。
參考
[SQL SERVER][Performance]善用 DEADLOCK_PRIORITY
What does sp_reset_connection do?
sp_reset_connection Does NOT Reset TRANSACTION ISOLATION LEVEL: Unexpected Behavior By Design