記一次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分鐘完成。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69950462/viewspace-2659989/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL Server一次SQL調優案例SQLServer
- SQL Server效能調優札記 [zt]SQLServer
- 記一次SQL調優過程SQL
- SQL Server 批量刪除重複記錄(批量、快速、安全)SQLServer
- 在SQL Server中快速刪除重複記錄(轉)SQLServer
- sql server 中刪除內容重複的某條記錄SQLServer
- 我如何調優SQL Server查詢SQLServer
- 記一次sql優化SQL優化
- 一次捕獲SQL調優資訊的指令碼 sql9.sqlSQL指令碼
- 一次捕獲SQL調優資訊的指令碼 sql10.sqlSQL指令碼
- SQL Server表分割槽刪除詳情DSCCSQLServer
- MS SQL Server 刪除重複行資料SQLServer
- SQL Server 觸發器的修改與刪除SQLServer觸發器
- 刪除SQL Server日誌的具體方法SQLServer
- 一次sql優化小記SQL優化
- SQL SERVER中SQL優化SQLServer優化
- SQL調優SQL
- SQL Server資料庫恢復,SQL Server資料恢復,SQL Server資料誤刪除恢復工具SQLRescueSQLServer資料庫資料恢復
- 刪除sql server資料庫中所有資料SQLServer資料庫
- sql server中預設約束的通用sql指令碼的刪除方法SQLServer指令碼
- SQL SERVER優化SQLServer優化
- sql調優1SQL
- oracle sql調優OracleSQL
- sql server如何刪除前1000行資料SQLServer
- SQL SERVER 規則的建立、繫結、解綁、刪除SQLServer
- 講解刪除SQL Server日誌的具體方法SQLServer
- SQL Server優化之SQL語句優化SQLServer優化
- SQL優化--刪除表的資料來加速SQL優化
- SQL Server中刪除重複資料的幾個方法SQLServer
- SQL Server 2008 效能測試和調優SQLServer
- T-SQL 刪除重複資料SQLSQL
- Teradata SQL調優SQL
- sql刪除重複記錄只保留一條SQL
- 用SQL語言刪除表中的記錄SQL
- 一次SQL調優 聊一聊 SQLSERVER 資料頁SQLServer
- oracle筆記整理13——效能調優之SQL優化Oracle筆記SQL優化
- SQL Server DBA調優日記(一)——大資料量查詢記錄數優化及原理探討SQLServer大資料優化
- SQL Server無法刪除資料庫 "xxx",因為該資料庫當前正在使用(如何刪除一個Sql Server資料庫)SQLServer資料庫