SQL Server 效能調優4 之書寫高效的查詢，sqlserver調優

SQL Server 效能調優4 之書寫高效的查詢，sqlserver調優
限制查詢的行和列來提高效能

這條規則非常簡單，這裡就不細說了。

使用搜尋可參數化判斷(sargable conditions)來提高效能

Sargable 由 Search ARGument Able 簡寫而來，字面意思是搜尋可參數化？還是比較晦澀哎...

總之使用Sargable判斷可以協助查詢最佳化工具更有效地利用索引，並提高採用 index seek 的可能性，我們先把所有的操作符分一下組。

• Sargable操作符
  • =
  • >
  • >=
  • <
  • <=
  • BETWEEN
  • LIKE (萬用字元必須出現在最後，比如‘R%’)

• 非Sargable操作符
  • !=
  • !>
  • !<
  • <>
  • NOT EXISTS
  • IN
  • NOT IN
  • BETWEEN
  • LIKE (萬用字元出現在前面，比如'%R')
  • NOT LIKE
  • 針對某列的函數

知道了這些你就可以在允許的場合下盡量使用Sargable操作（譬如用多個 OR 來替代 IN）在判定中巧妙地利用計算操作來提高效能

WHERE 子句中針對列的計算操作會導致非Sargable，間接導致列上的索引無法被有效利用，所以應當盡量避免。不幸的是沒有什麼自動方法來協助你找到這些地方，你必須手工檢查查詢指令碼並利用效能工具來定位問題所在。

我們用 AdventureWorks2012 資料庫中的兩張表來做個例子：

• [AdventureWorks2012].[HumanResources].[Employee]
• [AdventureWorks2012].[HumanResources].[EmployeePayHistory]

我們第一個查詢抽出 Employee 表的基本資料，並取得對應的 EmployeePayHistory 表中最新的資料，並且滿足 HourlyRate*8<=152

SELECT  E.LoginID  ,E.JobTitle  ,E.BirthDate  ,E.MaritalStatus  ,E.Gender  ,E.HireDate  ,EP.HourlyRate  ,EP.RateChangeDateFROM [AdventureWorks2012].[HumanResources].[Employee] AS EJOIN(  Select    Max(BusinessEntityID) AS BusinessEntityID    ,Max(RateChangeDate) AS RateChangeDate    ,Rate AS HourlyRate  FROM    [AdventureWorks2012].[HumanResources].[EmployeePayHistory]  GROUP BY    Rate) as EPON E.BusinessEntityID=EP.BusinessEntityIDWHERE EP.HourlyRate*8<=152

上面的查詢在 WHERE 中進行<=判斷前對列進行了一次數字計算（*8），該列上的索引就無法起效了。

對 WHERE 子句稍作修改，就可避免這個問題：

....WHERE EP.HourlyRate<=152/8

修改後在 <= 判斷前沒有對列進行操作，所以列上的索引會起效。看一下前後的效能指標：

前一個查詢佔用了53%的開銷，後一個佔用了47%，兩者相差數量級雖然不是非常大，但是隨著表資料的增加，差異將逐漸擴大。

不在判定中對列進行函數操作來提高效能

與前一段的原理一樣，WHERE 子句中針對列的函數操作會導致“非Sargable”，導致效能下降。這裡就不重複舉例了。

利用定義參照的完整性來改善效能

定義參照的完整性 （Declarative Referential Integrity），簡稱 DRI，指利用主鍵和外鍵來保證資料庫的完整性/一致性。

經常遇到這樣的情況：開發人員在主表中建立了主鍵，並用這個主鍵到子表中去取關聯資料，但卻沒有在子表中建立外鍵。實際上建立外鍵不但能協助你保證資料庫的完整性/一致性，還能提高查詢關聯資料時的效能，我們用一個樣本來驗證這個說法：

1. 我們建立兩張表

IF OBJECT_ID('ProductDemo') IS NOT NULL  DROP TABLE ProductDemoGOIF OBJECT_ID('ProductModelDemo') IS NOT NULL  DROP TABLE ProductModelDemoGOselect * into ProductModelDemo from Production.ProductModelselect * into ProductDemo from Production.Product WHERE ProductModelID is not nullGO

2. 在子表 ProductDemo 上聲明 ProductModelID 為非空欄位，並建立為主鍵

ALTER TABLE ProductDemoALTER COLUMN ProductModelID INT NOT NULLGOALTER TABLE ProductDemo ADD CONSTRAINT [PK_ProductDemo_ProductID]PRIMARY KEY CLUSTERED(  [ProductID] ASC)GO

3. 在主表 ProductModelDemo: 上建立主鍵

ALTER TABLE ProductModelDemo ADD CONSTRAINT [PK_ProductModelDemo_ProductModelID] PRIMARY KEY CLUSTERED(  ProductModelID ASC)GO

4. 執行測試查詢

SELECT  P.ProductID  ,P.ProductModelIDFROM  ProductDemo AS PJOIN  ProductModelDemo AS PMON  P.ProductModelID=PM.ProductModelIDWHERE  P.ProductID=680GO

執行計畫如下：

5. 建立子父之間的外間關聯

ALTER TABLE ProductDemoWITH CHECKADD CONSTRAINT  FK_ProductDemo_ProductModelDemo_ProductModelIDFOREIGN KEY  (ProductModelID)REFERENCES  ProductModelDemo(ProductModelID)GO

再次執行第4步的查詢，執行計畫如下：

修改後的查詢只需要在 ProductDemo 表上執行一次索引檢索。

在外鍵欄位上定義 NOT NULL，保證了子表中的資料必在父表中存在關聯資訊，最佳化器從而“信任”該子表（在不檢索父表資料的情況下，不需要再去訪問/檢查父表）。

“信任”外鍵來擷取效能改進

使用 sys.foreign_keys 系統檢視表能擷取外鍵的資訊，is_not_trusted 欄位表示該外鍵是否被“信任”。

要建立被“信任”的外鍵可以在建立指令碼中使用 WITH CHECK 參數，具體可參照前文的指令碼。

備忘

最後再添加幾點：

• 儘可能多的指定查詢篩選條件
• 可以不用 ORDER BY 盡量不用
• GROUP BY 子句中的項目儘可能少，並儘可能取自同一個表
• GROUP BY 子句中盡量用數字類型的欄位，避免用文本
• GROUP BY 和 ORDER BY 中的欄位盡量取自同一個表

SQL Server 2005 效能最佳化1.索引功能加強聯機索引選項允許執行索引資料定義語言 (Data Definition Language)時對基礎資料表或叢集索引資料及任何關聯索引進行並發修改使用者再幹預對錶或其現有索引訪問情況下添加索引
2.收縮性改進對大型表和索引分割區功能顯著地增強了大型資料庫查詢效能
3.表和索引分割區吧資料庫分成更小、更易管理塊從而簡化了大型資料管理
4.Transact-SQL增強
sql語句效能如何最佳化？？查詢速度慢原因多常見下幾種：

1、沒有索引或者沒有用索引(查詢慢常見問題程式設計缺陷)

2、I/O輸送量小形成了瓶頸效應

3、沒有建立計算資料行導致查詢最佳化

4、記憶體足

5、網路速度慢

6、查詢出資料量過大（採用多次查詢其方法降低資料量）

7、鎖或者死結(也查詢慢常見問題程式設計缺陷)

8、sp_lock,sp_who,活動使用者查看,原因讀寫競爭資源

9、返回了必要行和列

10、查詢語句好沒有最佳化