記一次SQL Server刪除SQL調優

今天測試反應問題，效能測試環境一個指令碼執行了大約3個小時沒有出結果，期間其他dba已經建立了一些索引但是沒有效果。

語句：

DELETE T  from License T  WHERE exists (select 1 from  #i_RollbackTrans r where r.LicenseID= T.LicenseID)

表 License資料量4千萬

表 #i_RollbackTrans資料量5萬

最終刪除資料量5萬

部分執行計劃：

可以發現表上有很多索引， 臨時表經過distinct之後和索引進行nested loop，問題不大。

透過以下sql查詢會話的read write和cpu 變化：

;WITH task_space_usage AS 
(
    -- SUM alloc/delloc pages
    SELECT session_id,
           request_id,
           SUM(internal_objects_alloc_page_count) AS internal_alloc_pages,
           SUM(user_objects_alloc_page_count) AS user_alloc_pages
    FROM sys.dm_db_task_space_usage WITH (NOLOCK)
    WHERE session_id <> @@SPID
    GROUP BY session_id, request_id
)
SELECT 
r.session_id
,r.percent_complete 
--,r.query_hash
--,r.sql_handle
--,r.plan_handle
,r.start_time
,getdate()
,datediff(ss,r.start_time,getdate()) duration
, DB_NAME(r.database_id) AS dbname
,[Parent Query] = st.text
--  ,[Individual Query] = SUBSTRING (st.text, (r.statement_start_offset/2) + 1,  
								--((CASE WHEN r.statement_end_offset = -1
								--THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
								-- ELSE r.statement_end_offset 
								-- END - r.statement_start_offset)/2) + 1
,[Individual Query] = SUBSTRING (st.text, (r.statement_start_offset/2),  
								((CASE WHEN r.statement_end_offset = -1
									THEN DATALENGTH(st.text)
								ELSE r.statement_end_offset 
								END - r.statement_start_offset)/2))  									  
,r.wait_type											   
,r.cpu_time, r.reads, r.writes
,r.blocking_session_id,r.session_id
--,dbo.udf_GetHeadBlocker(r.session_id) AS 'blockChain' 
,r.percent_complete ,r.wait_resource,r.status, r.command, r.transaction_isolation_level
,s.host_name, s.login_name 
,case when s.program_name like 'SQLAgent - TSQL JobStep%' then 
		--substring(s.program_name,30,34)
		(select name from msdb.dbo.sysjobs where cast(job_id as binary(16)) = 
		convert(varbinary(16),substring(s.program_name,30,34),1))
	  else s.program_name
 end as program_name
, s.host_process_id, c.client_net_address
--,deqp.query_plan
,blocking_cache.text AS blocking_text		
,	waitstats.wait_duration_ms 
,TSU.internal_alloc_pages * 1.0 / 128 AS [internal object MB space]
,TSU.user_alloc_pages * 1.0 / 128 AS [user object MB space]
FROM sys.dm_exec_requests AS r
left JOIN task_space_usage AS TSU ON TSU.session_id = r.session_id AND TSU.request_id = r.request_id
left JOIN sys.dm_exec_sessions AS s ON s.session_id=r.session_id
left JOIN sys.dm_exec_connections AS c ON s.session_id=c.session_id
LEFT JOIN sys.dm_exec_connections AS blocking ON blocking.session_id = r.blocking_session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
OUTER APPLY sys.dm_exec_sql_text(blocking.most_recent_sql_handle) blocking_cache
--OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) deqp
LEFT OUTER JOIN sys.dm_os_waiting_tasks waitstats ON waitstats.session_id = r.session_id
WHERE r.session_id>50
AND r.session_Id NOT IN (@@SPID) 
ORDER BY r.cpu_time DESC,r.reads DESC, r.blocking_session_id DESC 
GO

觀察結果發現，read很少只有300，write到30萬，但是執行期間一直沒有變化，cpu_time持續增加，會話狀態是running並且沒有等待事件。

透過分析得出，執行計劃查詢部分和上面分析一樣不存在問題，雖然表上有很多索引，但是刪除資料佔比表整體資料較少0.1%， 而且writes始終沒變化，證明不是阻塞再索引的刪除上面。那麼是什麼消耗cpu_time但是沒有任何IO變化？

下面看被表引用的外來鍵關係：

可以看到相當多的外來鍵引用該表，並且是強制檢查狀態，其中一部分還是自引用。

最終透過臨時禁用外來鍵檢測，刪除，再啟用的方式將3個小時沒有執行完的sql最佳化再20分鐘完成。