一些最佳化你的SQL語句的TIPs

簡介

    對於寫出實現功能的SQL語句和既能實現功能又能保證效能的SQL語句的差別是巨大的。很多時候開發人員僅僅是把精力放在實現所需的功能上，而忽略了其所寫代碼的效能和對SQL Server執行個體所產生的影響(也就是IO,CPU,記憶體方面的消耗).這甚至有可能使整個SQL Server執行個體跪了。本文旨在提供一些簡單的步驟來協助你最佳化SQL語句。

    市面上已經有很多關於如何最佳化SQL Server效能的書籍和白皮書。所以本文並不打算達到那種深度和廣度，而僅僅是為開發人員提供一個快速檢測的列表來找到SQL語句中導致瓶頸產生的部分。

    在開始解決效能問題之前，合適的診斷工具是必須的。除去眾所周知的SSMS和SQL Profiler,SQL Server 2008還帶有眾多DMV來提供關鍵資訊。本篇文章中，我將使用SSMS和一些DMV來找到SQL的瓶頸

 

那麼，我們從哪開始

    我的第一步是查看執行計畫。這一步既可以通過SMSS也可以通過SQL Profiler實現，為了簡便起見，我將在SMSS中擷取執行計畫。

    1) 檢查你是否忽略掉了某些表的串連的條件,從而導致了笛卡爾積（Cross)串連(Join)。比如，在生產系統中有兩個表，每個表中有1000行資料。這其中絕大多數資料並不需要返回，如果你在這兩個表上應用了Cross Join,返回的結果將會是100萬行的結果集!返回如此數量的資料包括將所有資料從實體儲存體介質中讀取出來，因而佔用了IO。然後這些資料將會被匯入記憶體，也就是SQL Server的緩衝區。這會將緩衝區內的其它頁Flush出去。

 

    2)查看你是否忽略了某些Where子句，缺少Where子句會導致返回額外不需要的行。這產生的影響和步驟一所產生的影響是一樣的。

 

    3)查看統計資訊是否是自動建立和自動更新的，你可以在資料庫的屬性裡看到這些選項

   

    在預設條件下建立一個新資料庫，Auto Create Statistics和Auto Update Statistics選項是開啟的，統計資訊是用於協助查詢最佳化工具產生最佳執行計畫的。這份白皮書對於解釋統計資訊的重要性以及對於執行計畫的作用解釋的非常到位。上面那些設定可以通過右鍵資料庫，選擇屬性，在“選項”中找到。

 

    4)檢查統計資訊是否已經到期，雖然統計資訊是自動建立的，但是更新統計資料從而反映出資料的變化也同樣重要。在一個大表中，有時候雖然Auto Update Statistics 選項已經開始，但統計資訊依然無法反映出資料的分布情況。預設情況下，統計資訊的更新是基於抽取表中的隨機資訊作為樣本產生的。如果資料是按順序儲存的，那麼很有可能資料樣本並沒有反映出表中的資料情況。因此，推薦在頻繁更新的表中，統計資訊使用Full Scan選項來定期更新。這種更新可以放到資料庫閑時來做。

    
DBCC SHOW_STATISTICS命令可以用於查看上次統計資訊的更新時間，行數以及樣本行數.在這個例子中，我們可以看到Person.Address表上的AK_Address_rowguid索引的有關資訊:

USE AdventureWorks;GODBCC SHOW_STATISTICS ("Person.Address", AK_Address_rowguid);GO

    下面是輸出結果，請注意Updated,Rows,Rows Sampled這三個列

 

    如果你認為統計資訊已經到期，則可以使用sp_updatestats這個預存程序來更新當前資料庫中的所有統計資訊:

Exec sp_updatestats

    或者使用FULLSCAN選項，則關於表Person.Address上的所有統計資訊將會被更新:

UPDATE STATISTICS Person.Address WITH FULLSCAN

 

    5)查看執行計畫是否出現任何錶或者索引的掃描（譯者註：不是尋找),在大多數情況下（這裡假設統計資訊是最新的),這意味著索引的缺失。下面幾個DMV對於尋找缺失索引很有協助:

i)
sys.dm_db_missing_index_details

ii)
sys.dm_db_missing_index_group_stats

iii)
sys.dm_db_missing_index_groups

    接下來的幾個語句使用了上面的DMV，按照索引缺失對於效能的影響，展現出資訊:

SELECT avg_total_user_cost,avg_user_impact,user_seeks, user_scans,ID.equality_columns,ID.inequality_columns,ID.included_columns,ID.statement FROM sys.dm_db_missing_index_group_stats GSLEFT OUTER JOIN sys.dm_db_missing_index_groups IG On (IG.index_group_handle = GS.group_handle)LEFT OUTER JOIN sys.dm_db_missing_index_details ID On (ID.index_handle = IG.index_handle)ORDER BY avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC

   你也可以使用資料引擎最佳化顧問來找出缺失的索引以及需要建立哪些索引來提高效能。

 

    6)查看是否有書籤尋找,同樣，在執行計畫中找到書籤尋找十分容易,書籤尋找並不能完全避免，但是使用覆蓋索引可以大大減少書籤尋找。

 

    7)查看排序操作，如果在執行計畫中排序操作佔去了很大一部分百分比，我會考慮以下幾種方案:

•      按照所排序的列建立叢集索引，但這種方式一直存在爭議。因為最佳實務是使用唯一列或者Int類型的列作為主鍵，然後讓SQL Server在主鍵上建立叢集索引。但是在特定情況下使用排序列建立叢集索引也是可以的
•     建立一個索引檢視表，在索引檢視表上按照排序列建立叢集索引
•     建立一個排序列的非叢集索引，把其他需要返回的列INCLUDE進去

      在我的另一篇文章中，我將會詳細闡述選擇最佳方案的方法。

 

    8)查看加在表上的鎖，如果所查的表由於一個DML語句導致上鎖，則查詢引擎需要花一些時間等待鎖的釋放。下面是一些解決鎖問題的方法:

•     讓事務儘可能的短
•     查看資料庫隔離等級，降低隔離等級以增加並發
•     在Select語句中使用表提示，比如READUNCOMMITTED 或
  
  READPAST.雖然這兩個表提示都會增加並發，但是ReadUnCommited可能會帶來髒讀的問題,而READPAST會只返回部分結果集

 

    9)查看是否有索引片段，索引片段可以使用sys.dm_db_index_physical_statsDMV輕鬆查看，如果索引片段已經大於30%,則推薦索引重建.而索引片段小於30%時，推薦使用索引整理。索引片段因為使查詢需要讀取更多的列從而增加了IO,而更多的頁意味著佔用更多的緩衝區，因此還會形成記憶體壓力。

    如下語句根據索引片段的百分比查看所有索引:

Declare @dbSysName;Set @db = '<DB NAME>';SELECT CAST(OBJECT_NAME(S.Object_ID, DB_ID(@db)) AS VARCHAR(20)) AS 'Table Name', CAST(index_type_desc AS VARCHAR(20)) AS 'Index Type', I.Name As 'Index Name', avg_fragmentation_in_percent As 'Avg % Fragmentation', record_count As 'RecordCount', page_count As 'Pages Allocated', avg_page_space_used_in_percent As 'Avg % Page Space Used'FROM sys.dm_db_index_physical_stats (DB_ID(@db),NULL,NULL,NULL,'DETAILED' ) SLEFT OUTER JOIN sys.indexes I On (I.Object_ID = S.Object_ID and I.Index_ID = S.Index_ID)AND S.INDEX_ID > 0ORDER BY avg_fragmentation_in_percent DESC

   下面語句可以重建指定表的所有索引:

ALTER INDEX ALL ON <Table Name> REBUILD;

    下面語句可以重建指定索引:

ALTER INDEX <Index Name> ON <Table Name> REBUILD;

    當然，我們也可以整理索引，下面語句整理指定表上的所有索引:

ALTER INDEX ALL ON <Table Name> REORGANIZE;

    下面語句指定特定的索引進行整理:

ALTER INDEX <Index Name> ON <Table Name> REORGANIZE;

   在重建或整理完索引之後，重新運行上面的語句來查看索引片段的情況。

 

總結

    上面的9個步驟並不是最佳化一個SQL語句必須的，儘管如此，你還是需要儘快找到是哪個步驟導致查詢效能的瓶頸從而解決效能問題。就像文中開篇所說，效能的問題往往是由於更深層次的原因,比如CPU或記憶體壓力，IO的瓶頸(這個列表會很長….),因此，更多的研究和閱讀是解決效能問題所必須的。

----------------------------------------

原文連結:http://www.sqlservercentral.com/articles/Performance+Tuning/70647/