SQL Server最佳化

標籤：

轉：http://www.cnblogs.com/lyhabc/archive/2013/01/13/2858916.htmlSQL Server讀懂語句啟動並執行統計資訊 SET STATISTICS TIME IO PROFILE ON

對於語句的運行，除了執行計畫本身，還有一些其他因素要考慮，例如語句的編譯時間、執行時間、做了多少次磁碟讀等。

如果DBA能夠把問題語句單獨測試回合，可以在運行前開啟下面這三個開關，收集語句啟動並執行統計資訊。

這些資訊對分析問題很有價值。

1 SET STATISTICS TIME ON2 SET STATISTICS IO ON3 SET STATISTICS PROFILE ON

 

SET STATISTICS TIME ON

請先來看看SET STATISTICS TIME ON會返回什麼資訊。先運行語句：

 1 DBCC DROPCLEANBUFFERS 2 --清除buffer pool裡的所有快取資料 3 DBCC freeproccache 4 GO 5  6 --清除buffer pool裡的所有緩衝的執行計畫 7 SET STATISTICS TIME ON 8 GO 9 USE [AdventureWorks]10 GO11 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]12 WHERE [ProductID]=77713 GO14 SET STATISTICS TIME OFF15 GO

 

除了結果集之外，SQLSERVER還會返回下面這兩段資訊

 1 SQL Server 分析和編譯時間:  2    CPU 時間 = 15 毫秒，佔用時間 = 104 毫秒。 3 SQL Server 分析和編譯時間:  4    CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。 5  6 (4 行受影響) 7  8 SQL Server 執行時間: 9    CPU 時間 = 171 毫秒，佔用時間 = 1903 毫秒。10 SQL Server 分析和編譯時間: 11    CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。

大家知道SQLSERVER執行語句是分以下階段：分析-》編譯-》執行

根據表格的統計資訊分析出比較合適的執行計畫，然後編譯語句，最後執行語句

 

下面說一下上面的輸出是什麼意思：

1、CPU時間 ：這個值的含義指的是在這一步，SQLSERVER所花的純CPU時間是多少。也就是說，語句花了多少CPU資源

2、佔用時間 ：此值指這一步一共用了多少時間。也就是說，這是語句啟動並執行時間長短，有些動作會發生I/O操作，產生了I/O等待，

或者是遇到阻塞、產生了阻塞等待。總之時間用掉了，但是沒有用CPU資源。所以佔用時間比CPU時間長是很正常的 ，但是CPU時間是

語句在所有CPU上的時間總和。如果語句使用了多顆CPU，而其他等待幾乎沒有，那麼CPU時間大於佔用時間也是正常的

3、分析和編譯時間：這一步，就是語句的編譯時間。由於語句運行之前清空了所有執行計畫，SQLSERVER必須要對他編譯。

這裡的編譯時間就不為0了。由於編譯主要是CPU的運算，所以一般CPU時間和佔用時間是差不多的。如果這裡相差比較大，

就有必要看看SQLSERVER在系統資源上有沒有瓶頸了。

這裡他們是一個15毫秒，一個是104毫秒

4、SQLSERVER執行時間： 語句真正啟動並執行時間。由於語句是第一次運行，SQLSERVER需要把資料從磁碟讀到記憶體裡，這裡語句的

運行發生了比較長的I/O等待。所以這裡的CPU時間和佔用時間差別就很大了，一個是171毫秒，而另一個是1903毫秒

總的來講，這條語句花了104+1903+186=2193毫秒，其中CPU時間為15+171=186毫秒。語句的主要時間應該是都花在了I/O等待上

 

現在再做一遍語句，但是不清除任何緩衝

1 SET STATISTICS TIME ON2 GO3 4 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]5 WHERE [ProductID]=7776 7 GO8 SET STATISTICS TIME OFF9 GO

這次比上次快很多。輸出時間統計資訊是：

 1 SQL Server 分析和編譯時間:  2    CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。 3 SQL Server 分析和編譯時間:  4    CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。 5  6 (4 行受影響) 7  8 SQL Server 執行時間: 9    CPU 時間 = 156 毫秒，佔用時間 = 169 毫秒。10 SQL Server 分析和編譯時間: 11    CPU 時間 = 0 毫秒，佔用時間 = 0 毫秒。

由於執行計畫被重用，“SQL分析和編譯時間” CPU時間是0，佔用時間是0

由於資料已經緩衝在記憶體裡，不需要從磁碟上讀取，SQL執行時間 CPU時間是156，佔用時間這次和CPU時間非常接近，是169。

這裡省下已耗用時間1903-169=1734毫秒，從這裡可以再次看出，緩衝對語句執行效能起著至關重要的作用

為了不影響其他測試，請運行下面的語句關閉SET STATISTICS TIME ON

1 SET STATISTICS TIME OFF2 GO

 

 

 

SET STATISTICS IO ON

這個開關能夠輸出語句做的物理讀和邏輯讀的數目。對分析語句的複雜度有很重要的作用

還是以剛才那個查詢作為例子

1 DBCC DROPCLEANBUFFERS2 GO3 SET STATISTICS IO ON4 GO5 6 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]7 WHERE [ProductID]=7778 GO

他的返回是：

1 (4 行受影響)2 表 ‘SalesOrderDetail_test‘。掃描計數 5，邏輯讀取 15064 次，物理讀取 0 次，預讀 15064 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

各個輸出的含義是：

表：表的名稱。這裡的表就是SalesOrderDetail_test

 

掃描計數：執行的掃描次數。按照執行計畫，表格被掃描了幾次。一般來講大表掃描的次數越多越不好。唯一的例外是如果執行計畫選擇了並發運行，

由多個thread線程同時做一個表的讀取，每個thread讀其中的一部分，但是這裡會顯示所有thread的數目。也就是有幾個thread在並發做，

就會有幾個掃描。這時數目大一點沒問題的。

 

邏輯讀取：從資料緩衝讀取的頁數。頁數越多，說明查詢要訪問的資料量就越大，記憶體消耗量越大，查詢也就越昂貴。

可以檢查是否應該調整索引，減少掃描的次數，縮小掃描範圍

順便說一下這個邏輯讀取的統計原理：為什麽顯示出來的結果的單位不是Page，也不是K或KB。SQLSERVER

裡在做讀和寫的時候，會運行到某一段特定的代碼。每調用一次這個代碼，Reads/Write就會加1。所以這個值比較大

那語句一定做了比較多的I/O，但是不能通過這個值計算出I/O的絕對數量，這個值反映的是邏輯讀寫量不是物理讀寫量

1 邏輯讀取 15064 次

 

 

物理讀取：從磁碟讀取的頁數

 

預讀：為進行查詢而預讀入緩衝的頁數

 

物理讀取+預讀：就是SQLSERVER為了完成這句查詢而從磁碟上讀取的頁數。如果不為0，說明資料沒有緩衝在記憶體裡。運行速度一定會受到影響

 

LOB邏輯讀取：從資料緩衝讀取的text、ntext、image、大實值型別（varchar(max)、nvarchar(max)、varbinary(max)）頁的數目

 

LOB物理讀取：從磁碟讀取的text、ntext、image、大實值型別頁的數目

 

LOB預讀：為進行查詢而放入緩衝的text、ntext、image、大實值型別頁的數目

 

然後再來運行一遍，不清空緩衝

1 SET STATISTICS IO ON2 GO3 4 SELECT DISTINCT([ProductID]),[UnitPrice] FROM [dbo].[SalesOrderDetail_test]5 WHERE [ProductID]=7776 GO

結果集返回：

1 表 ‘SalesOrderDetail_test‘。掃描計數 5，邏輯讀取 15064 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，2 lob 物理讀取 0 次，lob 預讀 0 次。

這次邏輯讀取不變，還是15064頁。但是物理讀取和預讀都是0了。說明資料已經緩衝在記憶體裡
第二次運行不需要再從磁碟上讀一遍，節省了時間

 

為了不影響其他測試，請運行下面語句關閉SET STATISTICS IO ON

1 SET STATISTICS IO OFF2 GO

 

 

SET STATISTICS PROFILE ON

這是三個設定中返回最複雜的一個，他返回語句的執行計畫，以及語句運行在每一步的實際返回行數統計。

通過這個結果，不僅可以得到執行計畫，理解語句執行過程，分析語句調優方向，也可以判斷SQLSERVER是否

選擇了一個正確的執行計畫。

1 SET STATISTICS PROFILE ON2 GO3 SELECT COUNT(b.[SalesOrderID])4 FROM [dbo].[SalesOrderHeader_test] a5 INNER JOIN [dbo].[SalesOrderDetail_test] b6 ON a.[SalesOrderID]=b.[SalesOrderID]7 WHERE a.[SalesOrderID]>43659 AND a.[SalesOrderID]<536608 GO

返回的結果集很長，下面說一下重要欄位

注意：這裡是從最下面開始向上看的，也就是說從最下面開始一直執行直到得到結果集所以（行1）裡的rows欄位顯示的值就是這個查詢返回的結果集。

而且有多少行表明SQLSERVER執行了多少個步驟,這裡有6行，表明SQLSRVER執行了6個步驟！！

 

Rows:執行計畫的每一步返回的實際行數

Executes:執行計畫的每一步被運行了多少次

StmtText:執行計畫的具體內容。執行計畫以一棵樹的形式顯示。每一行都是啟動並執行一步，都會有結果集返回，也都會有自己的cost

EstimateRows:SQLSERVER根據表格上的統計資訊，預估的每一步的返回行數。在分析執行計畫時，

我們會經常將Rows和EstimateRows這兩列做對比，先確認SQLSERVER預估得是否正確，以判斷統計資訊是否有更新

EstimateIO:SQLSERVER根據EstimateRows和統計資訊裡記錄的欄位長度，預估的每一步會產生的I/O cost

EstimateCPU:SQLSERVR根據EstimateRows和統計資訊裡記錄的欄位長度，以及要做的事情的複雜度，預估每一步會產生的CPU cost

TotalSubtreeCost:SQLSERVER根據EstimateIO和EstimateCPU通過某種計算公式，計算出每一步執行計畫子樹的cost

（包括這一步自己的cost和他的所有下層步驟的cost總和），下面介紹的cost說的都是這個欄位值

Warnings:SQLSERVER在運行每一步時遇到的警告，例如，某一步沒有統計資訊支援cost預估等。

Parallel:執行計畫的這一步是不是使用了並行的執行計畫

 

從上面結果可以看出執行計畫分成4步，其中第一步又分成並列的兩個子步驟

步驟a1（第5行）：從[SalesOrderHeader_test]表裡找出所有a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660的值

因為表在這個欄位上有一個叢集索引，所以SQL可以直接使用這個索引的seek

SQL預測返回10000條記錄，實際也就返回了10000條記錄.。這個預測是準確的。這一步的cost是0.202(totalsubtreecost)

步驟a2（第6行）：從[SalesOrderDetail_test]表裡找出所有 a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660的值

因為表在這個欄位上有一個非叢集索引，所以SQL可以直接使用這個索引的seek

這裡能夠看出SQL聰明的地方。雖然查詢語句只定義了[SalesOrderHeader_test]表上有a.[SalesOrderID]>43659 AND a.[SalesOrderID]<53660過濾條件，

但是根據語義分析，SQL知道這個條件在[SalesOrderDetail_test]上也為真。所以SQL選擇先把這個條件過濾然後再做join。這樣能夠大大降低join的cost

在這一步SQL預估返回50561條記錄，實際返回50577條。cost是0.127，也不高

步驟b（第4行）：將a1和a2兩步得到的結果集做一個join。因為SQL通過預估知道這兩個結果集比較大，所以他直接選擇了Hash Match的join方法。

SQL預估這個join能返回50313行，實際返回50577行。因為SQL在兩張表的[SalesOrderID]上都有統計資訊，所以這裡的預估非常準確

這一步的cost等於totalsubtreecost減去他的子步驟，0.715-0.202-0.127=0.386。由於預估值非常準確，可以相信這裡的cost就是實際每一步的cost

步驟c（第3行）：在join返回的結果集基礎上算count(*)的值這一步比較簡單，count(*)的結果總是1，所以預測值是正確的。

其實這一步的cost是根據上一步（b）join返回的結果集大小預估出來的。我們知道步驟b的預估傳回值非常準確，所以這一步的預估cost也不會有什麼大問題

這棵子樹的cost是0.745，減去他的子節點cost，他自己的cost是0.745-0.715=0.03。是花費很小的一步

步驟b（第2行）：將步驟c返回的值轉換為int類型，作為結果返回

這一步是上一步的繼續，更為簡單。convert一個值的資料類型所要的cost幾乎可以忽略不計。所以這棵子樹的cost和他的子節點相等，都是0.745。

也就是說，他自己的cost是0

 

通過這樣的方法，使用者可以瞭解到語句的執行計畫、SQL Server預估的準確性、cost的分布

 

最後說一下：不同SQL Server版本，不同機器cost可能會不一樣，例如SQL Server 2005 、SQL Server 2008

 

 

 

二、轉：http://blog.csdn.net/kookob/article/details/8289163

（1）資料庫的設定：如果你的資料庫記錄數不會超過30萬條？如果你的資料庫記錄超過100萬條？該如何設定資料庫？一個或多個？ 
 （2）資料庫表的設定：當你的某個資料庫表記錄超過100萬層級，而且每天大量增長，這是一個不得不考慮的問題。如果你的系統瀏覽量很大，即使是30萬條記錄也是需要考慮的。 
 （3）索引的使用：索引可以大大提高資料庫訪問速度。什麼時候用？哪些欄位使用？ 
 （4）預存程序的使用：預存程序終歸是比較好的，但是如果需要維護成百上千的預存程序，未必是划算的工程。 
 （5）高效的分頁技術：資料庫記錄分頁列表是大量必須使用的基本技術，怎樣的分頁是快速的？

 宗旨你需要從上述5個方面考慮資料庫的最佳化。

 什麼時候需要資料庫最佳化？ 
 （1）編寫代碼之前； 
 （2）系統速度慢了的時候；

 下面就是一些具體的最佳化技巧了。

（1）超大量記錄資料庫的最佳化技巧

 如果你的資料庫表記錄有超過100萬層級，而且不斷增長中。可以採取兩個手段： 
 第一：將資料庫表拆分到不同的庫中，比如 tblMEMBER 就可以拆分到 DB1 與 DB2 中去。 
 實際上，可以拆分到 DB001 ... DB100 甚至更多的庫中間去。 
 DB1 與 DB2 最好不在一塊硬碟上。 
 第二：如果更大量級的資料，則最好拆分到不同的資料庫伺服器中去。

 資料庫的拆分帶來的是查詢等操作的複雜性。簡單地可以通過 hash 或者 按序號 匹配不同的資料庫。複雜一些，應該設定一個獨立的應用伺服器（軟體）協調其中的操作。

（2）中等量級資料庫的最佳化技巧

 所謂中等量級資料庫是指資料庫100萬－500萬條記錄左右（單個資料庫表）。這樣的資料庫為了提高訪問（響應）速度，可以將表拆分到更小的表。比如 tblMEMBER 可以拆分為 tblMEMBER_00 ... tblMEMBER_99 。 
 這樣可以保證每個表的記錄數不超過50萬，那速度是"相當"快了。

（3）避免使用視圖(viewport)與關聯

 視圖viewport與關聯都是為了程式員處理相對複雜的資料管理提供方便的手段。萬物有其利，必有其弊。視圖和關聯提高了編程效率，都會較大地影響資料庫的訪問效率（事實上並不像一般資料說介紹的的那樣高效），因此如果是web應用，則建議一般不要使用視圖與關聯。

（4）不要忘記索引(index)也不要濫用索引(index)

 索引是提高資料庫效率的簡單又高效的方法。只要是設定了資料庫表(table)，就不要忘記設定索引(index)。將索引設定在經常用於排序的欄位上，其他欄位就不要設定了。 
 索引不是越多越好，也不是什麼欄位都適合建立索引的。資料重複性太多的欄位不要設定索引。比如 tblMEMBER 的 iSex 欄位只有 0 1 兩個值，就不要設定索引。

（5）二進位的 text image 等欄位應該單獨設定別的表中

 一般的資料庫應用難免都需要儲存比如描述、圖片等資訊；一般描述類資訊用 text 欄位，圖片類資訊用 image 欄位；這裡要說的是，不要將這些欄位與其他欄位放在一個表中。 
 比如： 
> 純文字方式> 列印
tblMEMBER  
id (int)  
cName (varchar)(64)  
cDescription (text)  
bPhoto (image)  
dDate (datetime)  
就應該拆分為3個表  
tblMEMBER  
id (int)  
cName (varchar)(64)  
dDate (datetime)  
tblMEMBER_DESC  
id (int)  
cDescription (text)  
dDate (datetime)  
tblMEMBER_PHOTO  
id (int)  
bPhoto (image)  
dDate (datetime)  
（6）不要使用文本類型的 id

 一般的資料庫表都會以一個種子欄位作為主鍵。可以在與不少年青的程式員朋友溝通過程中，發現他們很喜歡用字串類型的作為系統的 id 號。 
 比如：id = XX XX XX XX 這樣的字串，每兩個位置代表不同的類別等含義。 
 不知道是那本教材如此誤人子弟，作出這樣的表率 :< 
 作為系統的 id 號，一定要使用數字型的。

（7）資料庫表table的欄位field不要太多

 本以為無需說明，也是發現不少的朋友，為了省事，一股腦把所有的相關欄位都放在一個表中間。這樣做的後果便是，程式寫起來簡單了，運行效率下來了。 
 無論欄位多少，有兩類欄位是必須獨立出去的：一是進程更新的欄位，比如文章的點擊次數欄位iShow，二是二進位或者是text欄位；

（8）將字串(varchar)比較變成數字型(int)比較

 每個系統都會有使用者管理，其中必然有 暱稱，密碼，郵件等的字串類型資料比較的問題。在資料庫操作中，字串比較的效率是相當低下的。因此遇到字串的比較，必須將其轉換為數字型比較。 
 具體做法是：在資料庫表中增加相應的數字欄位，比如 cNickname -> iNickNumber ，其中 iNickNumber 的數值為 cNickname 的 雜湊值（如何計算字串的雜湊值？請參閱本站的其他文章）。 
 通過這樣的轉換，系統效率可以提高 100 倍哦！！！

（9）為每個資料庫表(table)設定 datetime 欄位

 在許多情況下，很多的表是不需要 datetime 欄位用於儲存時間的。本文的建議是你應該為每個表都設定 datetime 欄位，而且預設值為 getdate()。 
 我們的經驗是，datetime 是實數，佔用位元組不多；在進行系統維護，遠程備份等環節都會發揮意想不到的效果。

（10）適當使用預存程序(Stored Processing)

 預存程序(sp)已經被大大地宣傳了，本文也不例外地讚許採用預存程序。本文的建議是只在下列情況才使用預存程序：一是一個業務處理是事務，包含了多個處理過程；二是一種處理被高頻使用，使用預存程序可以提高效率；

（11）使用高效的分頁(ination)技術

 資料庫記錄分頁列表是大量必須使用的基本技術，因此本文建議你在每個資料庫中建立下面的預存程序： 
> 純文字方式> 列印
CREATE PROCEDURE xsp_ination  
(  
@tblName   varchar(64),                  
@strGetFields varchar(256) = "*",   
@fldName varchar(64)="",                  
@PageSize   int = 20,                     
@PageIndex  int = 1,                          
@OrderType bit = 1,                       
@strWhere  varchar(256) = ""      
)  
AS   
BEGIN  
declare @strSQL   varchar(1000)     
declare @strTmp   varchar(110)       
declare @strOrder varchar(400)     
SET NOCOUNT ON  
if @OrderType != 0  
    begin  
        set @strTmp = "<(select min"   
        set @strOrder = " order by [" + @fldName +"] desc"   
    end  
else   
    begin   
        set @strTmp = ">(select max"   
        set @strOrder = " order by [" + @fldName +"] asc"   
    end   
if @PageIndex = 1  
    begin  
        if @strWhere != ""     
            set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ "  from " + @tblName + " where " + @strWhere + " " + @strOrder  
        else   
            set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ "  from "+ @tblName + " "+ @strOrder  
    end  
else   
    begin  
        set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ "  from "  
                            + @tblName + " where [" + @fldName + "]" + @strTmp + "(["+ @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["+ @fldName + "] from " + @tblName + " " + @strOrder + ") as tblTmp)"+ @strOrder  
        if @strWhere != ""   
            set @strSQL = "select top " + str(@PageSize) +" "[email protected]+ "  from "  
                            + @tblName + " where [" + @fldName + "]" + @strTmp + "(["  
                            + @fldName + "]) from (select top " + str((@PageIndex-1)*@PageSize) + " ["   
                            + @fldName + "] from " + @tblName + " where " + @strWhere + " "  
                            + @strOrder + ") as tblTmp) and " + @strWhere + " " + @strOrder   
    end  
EXEC (@strSQL)  
if @@error=0 return 1  
SET NOCOUNT OFF  
END  
GO 

 使用方法是(C#)： 
> 純文字方式> 列印
sql = "EXEC [dbo].[xsp_ination] \"tblNEWS\",\"*\",\"id\",40," + pindex.ToString() + ",1,\"iType=" + type.ToString();  
SqlDataReader sr = ExecuteReader(sql);  
while (sr.Read())  
{  
   ...  
}  
sr.Close(); 

SQL Server最佳化