ITPub博客

首页 > 数据治理 > 数据治理 > 记一次SQL Server删除SQL调优

记一次SQL Server删除SQL调优

原创 数据治理 作者:宅慕思_ 时间:2019-10-15 09:59:11 0 删除 编辑

今天测试反应问题,性能测试环境一个脚本执行了大约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分钟完成。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69950462/viewspace-2659989/,如需转载,请注明出处,否则将追究法律责任。

全部评论

注册时间:2019-10-10

  • 博文量
    18
  • 访问量
    8879