SQL Server調優

前段時間資料庫健全狀態檢查發現SQL Server伺服器的idle時間變少，IO還是比較空閑，估計是遇到了高CPU佔用的語句了。

　　介紹一下背景，我們公司負責營運N多的應有系統，負責提供良好的軟、硬體環境，至於應用的開發品質，我們就無能為力了

　　解決這個問題，我的思路是：

　　找出CPU佔用最大的語句。

　　分析查詢計劃。

　　最佳化。

　　1、找出語句

　　使用SQL Server內建的效能報表(不是報表格服務)，找出CPU佔用最大的語句。1所示

　　
圖1 效能報表

　　我選取了“效能-按總CPU時間排在前面的查詢”，得出以下兩張報表，2所示：

　　
圖2 效能-按總CPU時間排在前面的查詢

　　在報表中不能直接把語句Copy出來，非得讓我另存新檔Excel才能Copy語句;而且經常標示不了是語句屬於哪個資料庫 。

　　費了我九牛二虎之力才找出該條語句在哪個資料庫執行，然後馬上備份資料庫，在另一個非生產資料庫上面還原，創造實驗環境。

　　廢話少說，我把語句Copy出來，順便整理了一下格式。如下：
      select *

　　from network_listen

　　where

　　node_code in

　　(

　　select distinct node_code

　　from view_Log_Network_circsByUnit

　　where status='1'

　　)

　　or

　　node_code=

　　(

　　select top 1 nodeCode

　　from TransmissionUnit_LocalInfo

　　)

　　and

　　node_code<>

　　(

　　select parentNodeCode

　　from TransmissionUnit_RouterInfo

　　where nodeCode=

　　(

　　select top 1 nodeCode from TransmissionUnit_LocalInfo

　　)

　　)

 

 

2、分析語句

　　執行計畫如下：

　　圖3 查詢計劃全圖

　
　圖4 查詢計劃1

　
　圖5 查詢計劃2

　　
圖6 查詢計劃3

　　從整個查詢計劃來看，主要開銷都花在了圖5的那個部分——兩個“叢集索引掃描”。

　　查看一下這兩個數“叢集索引掃描”，搞什麼飛機呢?

　　
奇怪了，查詢語句裡面沒有Log_Nwtwork_circs 這個表啊，再仔細分析一下這個執行計畫，嫌疑最大的就是view_Log_Network_circsByUnit這個視圖了。

　　查看一下這個試圖的定義：
　　CREATE VIEW [dbo].[view_Log_Network_circsByUnit]

　　AS

　　SELECT B.*

　　FROM (

　　SELECT node_code, MAX(end_time) AS end_time

　　FROM Log_Network_circs

　　GROUP BY node_code

　　) A

　　LEFT OUTER JOIN

　　dbo.Log_Network_circs B

　　ON

　　A.node_code = B.node_code

　　AND

　　A.end_time = B.end_time

　　看著有點暈是吧，那麼看看

 

　3、最佳化

　　SQL寫得好不好，咱不說，反正我是不能改SQL的，而且應該可以判斷出整個查詢最耗時的地方就是用在搞這張試圖了。

　　那就只能針對這個試圖調優啦。仔細觀察這個試圖，實際上就涉及到一個表 Log_Network_circs，下面是該表的表結構：

 

CREATE TABLE [dbo].[Log_Network_circs](
    [log_id] [varchar](30) NOT NULL,
    [node_code] [varchar](100) NULL,
    [node_name] [varchar](100) NULL,
    [server_name] [varchar](100) NULL,
    [start_time] [datetime] NULL,
    [end_time] [datetime] NULL,
    [status] [varchar](30) NULL,
CONSTRAINT [PK_LOG_NETWORK_CIRCS] PRIMARY KEY CLUSTERED
(
    [log_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

 

        資料量有489957條記錄，不算太大。

　　根據 3、經常與其他表進行串連的表，在串連欄位上應該建立索引;

　　感覺上得在 node_code 和 end_time 這兩欄位上建立一個複合索引，大概定義如下：

 

CREATE INDEX [idx__Log_Network]
ON Log_Network_circs
(
    node_code ASC,
    end_time ASC
)

 

　保險起見，我把需要調優的語句copy到一個檔案裡，然後開啟“Database Engine Tuning Advisor”，設定好資料庫，得出以下調優結果：
　

 

CREATE STATISTICS [_dta_stat_559341057_6_2] ON [dbo].[Log_Network_circs]([end_time], [node_code])

CREATE NONCLUSTERED INDEX [_dta_index_Log_Network_circs_24_559341057__K2_K6] ON [dbo].[Log_Network_circs]
(
    [node_code] ASC,
    [end_time] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

 

      　嗯，結果差不多，具體參數再說。

　　按照“Database Engine Tuning Advisor”給出的建議，建立 STATISTICS 和 INDEX 。

　　再看看最佳化後的執行計畫

　　明顯查詢 view_Log_Network_circsByUnit 這個視圖的執行計畫不一樣了。

　　不看廣告，看療效，使用統計功能。執行以下語句：　

 

SET STATISTICS IO on;
SET STATISTICS TIME on;

 

　　(2 行受影響)

　　表 'Log_Network_circs'。掃描計數 2，邏輯讀取 13558 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

　　表 'TransmissionUnit_RouterInfo'。掃描計數 0，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

　　表 'TransmissionUnit_LocalInfo'。掃描計數 3，邏輯讀取 6 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

　　表 'network_listen'。掃描計數 1，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

　　SQL Server 執行時間:

　　CPU 時間 = 719 毫秒，佔用時間 = 719 毫秒。

　　(2 行受影響)

　　表 'Log_Network_circs'。掃描計數 2，邏輯讀取 9 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

　　表 'TransmissionUnit_RouterInfo'。掃描計數 0，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

　　表 'TransmissionUnit_LocalInfo'。掃描計數 3，邏輯讀取 6 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

　　表 'network_listen'。掃描計數 1，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

　　SQL Server 執行時間:

　　CPU 時間 = 0 毫秒，佔用時間 = 2 毫秒。

　　邏輯讀取數，總執行時間都大大縮減，開來調優還是挺成功的。