Sql Server CPU 效能排查及最佳化的相關 Sql 語句

來源:互聯網
上載者:User

Sql Server CPU 效能排查及最佳化的相關 Sql 語句,非常好的SQL語句,記錄於此:

--Begin Cpu 分析最佳化的相關 Sql --使用DMV來分析SQL Server啟動以來累計使用CPU資源最多的語句。例如下面的語句就可以列出前50名。select     c.last_execution_time,c.execution_count,c.total_logical_reads,c.total_logical_writes,c.total_elapsed_time,c.last_elapsed_time,     q.[text]from     (select top 50 qs.*    from sys.dm_exec_query_stats qs    order by qs.total_worker_time desc) as c    cross apply sys.dm_exec_sql_text(plan_handle) as qorder by c.total_worker_time descgo-- 返回最經常啟動並執行100條語句SELECT TOP 100 cp.cacheobjtype,cp.usecounts,cp.size_in_bytes,qs.statement_start_offset,qs.statement_end_offset,qt.dbid    ,qt.objectid                        ,SUBSTRING(qt.text,qs.statement_start_offset/2,                         (case when qs.statement_end_offset = -1                         then len(convert(nvarchar(max), qt.text)) * 2                         else qs.statement_end_offset end -qs.statement_start_offset)/2)     as statement FROM sys.dm_exec_query_stats qscross apply sys.dm_exec_sql_text(qs.sql_handle) as qtinner join sys.dm_exec_cached_plans as cp on qs.plan_handle=cp.plan_handlewhere cp.plan_handle=qs.plan_handleand cp.usecounts>4ORDER BY [dbid],[Usecounts] DESC-- 返回做IO數目最多的50條語句以及它們的執行計畫select top 50     (total_logical_reads/execution_count) as avg_logical_reads,    (total_logical_writes/execution_count) as avg_logical_writes,    (total_physical_reads/execution_count) as avg_phys_reads,     Execution_count,     statement_start_offset as stmt_start_offset, statement_end_offset as stmt_end_offset,substring(sql_text.text, (statement_start_offset/2), case when (statement_end_offset -statement_start_offset)/2 <=0 then 64000else (statement_end_offset -statement_start_offset)/2 end) as exec_statement,  sql_text.text,plan_text.*from sys.dm_exec_query_stats  cross apply sys.dm_exec_sql_text(sql_handle) as sql_textcross apply sys.dm_exec_query_plan(plan_handle) as plan_textorder by  (total_logical_reads + total_logical_writes) /Execution_count Desc-- 計算signal wait占整wait時間的百分比-- 指令等待 CPU 資源的時間佔總時間的百分比。如果超過 25% ,說明 CPU 緊張select convert(numeric(5,4),sum(signal_wait_time_ms)/sum(wait_time_ms)) from Sys.dm_os_wait_stats -- 計算'Cxpacket'占整wait時間的百分比-- Cxpacket:Sql Server 在處理一句代價很大的語句,要不就是沒有合適的索引或篩選條件沒能篩選足夠的記錄,使得語句要返回大量的結果,當 >5% 說明有問題declare @Cxpacket bigintdeclare @Sumwaits bigintselect @Cxpacket = wait_time_msfrom Sys.dm_os_wait_statswhere wait_type = 'Cxpacket'select @Sumwaits = sum(wait_time_ms)from Sys.dm_os_wait_statsselect convert(numeric(5,4),@Cxpacket/@Sumwaits)-- 查詢當前資料庫上所有使用者表格在Row lock上發生阻塞的頻率declare @dbid intselect @dbid = db_id()Select dbid=database_id, objectname=object_name(s.object_id), indexname=i.name, i.index_id    --, partition_number, row_lock_count, row_lock_wait_count, [block %]=cast (100.0 * row_lock_wait_count / (1 + row_lock_count) as numeric(15,2)), row_lock_wait_in_ms, [avg row lock waits in ms]=cast (1.0 * row_lock_wait_in_ms / (1 + row_lock_wait_count) as numeric(15,2))from sys.dm_db_index_operational_stats (@dbid, NULL, NULL, NULL) s,     sys.indexes iwhere objectproperty(s.object_id,'IsUserTable') = 1and i.object_id = s.object_idand i.index_id = s.index_idorder by row_lock_wait_count desc--End Cpu 分析最佳化的相關 Sql 
相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.