故事開篇：你和你的團隊經過不懈努力，終于使網站成功上線，剛開始時，注冊用戶較少，網站性能表現不錯，但隨著注冊用戶的增多，訪問速度開始變慢，一些用戶開始發(fā)來郵件表示抗議，事情變得越來越糟，為了留住用戶，你開始著手調查訪問變慢的原因。

　　經過緊張的調查，你發(fā)現問題出在數據庫上，當應用程序嘗試訪問/更新數據時，數據庫執(zhí)行得相當慢，再次深入調查數據庫后，你發(fā)現數據庫表增長得很大，有些表甚至有上千萬行數據，測試團隊開始在生產數據庫上測試，發(fā)現訂單提交過程需要花5分鐘時間，但在網站上線前的測試中，提交一次訂單只需要2/3秒。

　　類似這種故事在世界各個角落每天都會上演，幾乎每個開發(fā)人員在其開發(fā)生涯中都會遇到這種事情，我也曾多次遇到這種情況，因此我希望將我解決這種問題的經驗和大家分享。

　　如果你正身處這種項目，逃避不是辦法，只有勇敢地去面對現實。首先，我認為你的應用程序中一定沒有寫數據訪問程序，我將在這個系列的文章中介紹如何編寫最佳的數據訪問程序，以及如何優(yōu)化現有的數據訪問程序。

　　范圍

　　在正式開始之前，有必要澄清一下本系列文章的寫作邊界，我想談的是“事務性(OLTP)SQL Server數據庫中的數據訪問性能優(yōu)化”，但文中介紹的這些技巧也可以用于其它數據庫平臺。

　　同時，我介紹的這些技巧主要是面向程序開發(fā)人員的，雖然DBA也是優(yōu)化數據庫的一支主要力量，但DBA使用的優(yōu)化方法不在我的討論范圍之內。

　　當一個基于數據庫的應用程序運行起來很慢時，90%的可能都是由于數據訪問程序的問題，要么是沒有優(yōu)化，要么是沒有按最佳方法編寫代碼，因此你需要審查和優(yōu)化你的數據訪問/處理程序。

　　我將會談到10個步驟來優(yōu)化數據訪問程序，先從最基本的索引說起吧!

　　第一步：應用正確的索引

　　我之所以先從索引談起是因為采用正確的索引會使生產系統的性能得到質的提升，另一個原因是創(chuàng)建或修改索引是在數據庫上進行的，不會涉及到修改程序，并可以立即見到成效。

　　我們還是溫習一下索引的基礎知識吧，我相信你已經知道什么是索引了，但我見到很多人都還不是很明白，我先給大家將一個故事吧。

　　很久以前，在一個古城的的大圖書館中珍藏有成千上萬本書籍，但書架上的書沒有按任何順序擺放，因此每當有人詢問某本書時，圖書管理員只有挨個尋找，每一次都要花費大量的時間。

　　[這就好比數據表沒有主鍵一樣，搜索表中的數據時，數據庫引擎必須進行全表掃描，效率極其低下。]

　　更糟的是圖書館的圖書越來越多，圖書管理員的工作變得異常痛苦，有一天來了一個聰明的小伙子，他看到圖書管理員的痛苦工作后，想出了一個辦法，他建議將每本書都編上號，然后按編號放到書架上，如果有人指定了圖書編號，那么圖書管理員很快就可以找到它的位置了。

　　[給圖書編號就象給表創(chuàng)建主鍵一樣，創(chuàng)建主鍵時，會創(chuàng)建聚集索引樹，表中的所有行會在文件系統上根據主鍵值進行物理排序，當查詢表中任一行時，數據庫首先使用聚集索引樹找到對應的數據頁(就象首先找到書架一樣)，然后在數據頁中根據主鍵鍵值找到目標行(就象找到書架上的書一樣)。]

　　于是圖書管理員開始給圖書編號，然后根據編號將書放到書架上，為此他花了整整一天時間，但最后經過測試，他發(fā)現找書的效率大大提高了。

　　[在一個表上只能創(chuàng)建一個聚集索引，就象書只能按一種規(guī)則擺放一樣。]

　　但問題并未完全解決，因為很多人記不住書的編號，只記得書的名字，圖書管理員無賴又只有掃描所有的圖書編號挨個尋找，但這次他只花了20分鐘，以前未給圖書編號時要花2-3小時，但與根據圖書編號查找圖書相比，時間還是太長了，因此他向那個聰明的小伙子求助。

　　[這就好像你給Product表增加了主鍵ProductID，但除此之外沒有建立其它索引，當使用Product Name進行檢索時，數據庫引擎又只要進行全表掃描，逐個尋找了。]

　　聰明的小伙告訴圖書管理員，之前已經創(chuàng)建好了圖書編號，現在只需要再創(chuàng)建一個索引或目錄，將圖書名稱和對應的編號一起存儲起來，但這一次是按圖書名稱進行排序，如果有人想找“Database Management System”一書，你只需要跳到“D”開頭的目錄，然后按照編號就可以找到圖書了。

　　于是圖書管理員興奮地花了幾個小時創(chuàng)建了一個“圖書名稱”目錄，經過測試，現在找一本書的時間縮短到1分鐘了(其中30秒用于從“圖書名稱”目錄中查找編號，另外根據編號查找圖書用了30秒)。

　　圖書管理員開始了新的思考，讀者可能還會根據圖書的其它屬性來找書，如作者，于是他用同樣的辦法為作者也創(chuàng)建了目錄，現在可以根據圖書編號，書名和作者在1分鐘內查找任何圖書了，圖書管理員的工作變得輕松了，故事也到此結束。

　　到此，我相信你已經完全理解了索引的真正含義。假設我們有一個Products表，創(chuàng)建了一個聚集索引(根據表的主鍵自動創(chuàng)建的)，我們還需要在ProductName列上創(chuàng)建一個非聚集索引，創(chuàng)建非聚集索引時，數據庫引擎會為非聚集索引自動創(chuàng)建一個索引樹(就象故事中的“圖書名稱”目錄一樣)，產品名稱會存儲在索引頁中，每個索引頁包括一定范圍的產品名稱和它們對應的主鍵鍵值，當使用產品名稱進行檢索時，數據庫引擎首先會根據產品名稱查找非聚集索引樹查出主鍵鍵值，然后使用主鍵鍵值查找聚集索引樹找到最終的產品。

　　下圖顯示了一個索引樹的結構

　　圖 1 索引樹結構

　　它叫做B+樹(或平衡樹)，中間節(jié)點包含值的范圍，指引SQL引擎應該在哪里去查找特定的索引值，葉子節(jié)點包含真正的索引值，如果這是一個聚集索引樹，葉子節(jié)點就是物理數據頁，如果這是一個非聚集索引樹，葉子節(jié)點包含索引值和聚集索引鍵(數據庫引擎使用它在聚集索引樹中查找對應的行)。

　　通常，在索引樹中查找目標值，然后跳到真實的行，這個過程是花不了什么時間的，因此索引一般會提高數據檢索速度。下面的步驟將有助于你正確應用索引。

　　確保每個表都有主鍵

　　這樣可以確保每個表都有聚集索引(表在磁盤上的物理存儲是按照主鍵順序排列的)，使用主鍵檢索表中的數據，或在主鍵字段上進行排序，或在where子句中指定任意范圍的主鍵鍵值時，其速度都是非常快的。

　　在下面這些列上創(chuàng)建非聚集索引：

　　1)搜索時經常使用到的;

　　2)用于連接其它表的;

　　3)用于外鍵字段的;

　　4)高選中性的;

　　5)ORDER BY子句使用到的;

　　6)XML類型。

　　下面是一個創(chuàng)建索引的例子：　

CREATEINDEX

　　NCLIX_OrderDetails_ProductIDON

　　dbo.OrderDetails(ProductID)

　　也可以使用SQL Server管理工作臺在表上創(chuàng)建索引，如圖2所示。

　　圖 2 使用SQL Server管理工作臺創(chuàng)建索引

　　第二步：創(chuàng)建適當的覆蓋索引

　　假設你在Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)的外鍵列(ProductID)上創(chuàng)建了一個索引，假設ProductID列是一個高選中性列，那么任何在where子句中使用索引列(ProductID)的select查詢都會更快，如果在外鍵上沒有創(chuàng)建索引，將會發(fā)生全部掃描，但還有辦法可以進一步提升查詢性能。

　　假設Sales表有10,000行記錄，下面的SQL語句選中400行(總行數的4%)：　

SELECTSalesDate, SalesPersonIDFROMSalesWHEREProductID=112

　　我們來看看這條SQL語句在SQL執(zhí)行引擎中是如何執(zhí)行的：

　　1)Sales表在ProductID列上有一個非聚集索引，因此它查找非聚集索引樹找出ProductID=112的記錄;

　　2)包含ProductID = 112記錄的索引頁也包括所有的聚集索引鍵(所有的主鍵鍵值，即SalesID);

　　3)針對每一個主鍵(這里是400)，SQL Server引擎查找聚集索引樹找出真實的行在對應頁面中的位置;

　　SQL Server引擎從對應的行查找SalesDate和SalesPersonID列的值。

　　在上面的步驟中，對ProductID = 112的每個主鍵記錄(這里是400)，SQL Server引擎要搜索400次聚集索引樹以檢索查詢中指定的其它列(SalesDate，SalesPersonID)。

　　如果非聚集索引頁中包括了聚集索引鍵和其它兩列(SalesDate,，SalesPersonID)的值，SQL Server引擎可能不會執(zhí)行上面的第3和4步，直接從非聚集索引樹查找ProductID列速度還會快一些，直接從索引頁讀取這三列的數值。

　　幸運的是，有一種方法實現了這個功能，它被稱為“覆蓋索引”，在表列上創(chuàng)建覆蓋索引時，需要指定哪些額外的列值需要和聚集索引鍵值(主鍵)一起存儲在索引頁中。下面是在Sales 表ProductID列上創(chuàng)建覆蓋索引的例子：　

CREATEINDEXNCLIX_Sales_ProductID--Index name

　　ONdbo.Sales(ProductID)--Column on which index is to be created

　　INCLUDE(SalesDate, SalesPersonID)--Additional column values to include

　　應該在那些select查詢中常使用到的列上創(chuàng)建覆蓋索引，但覆蓋索引中包括過多的列也不行，因為覆蓋索引列的值是存儲在內存中的，這樣會消耗過多內存，引發(fā)性能下降。

　　創(chuàng)建覆蓋索引時使用數據庫調整顧問

　　我們知道，當SQL出問題時，SQL Server引擎中的優(yōu)化器根據下列因素自動生成不同的查詢計劃：

　　1)數據量

　　2)統計數據

　　3)索引變化

　　4)TSQL中的參數值

　　5)服務器負載

　　這就意味著，對于特定的SQL，即使表和索引結構是一樣的，但在生產服務器和在測試服務器上產生的執(zhí)行計劃可能會不一樣，這也意味著在測試服務器上創(chuàng)建的索引可以提高應用程序的性能，但在生產服務器上創(chuàng)建同樣的索引卻未必會提高應用程序的性能。因為測試環(huán)境中的執(zhí)行計劃利用了新創(chuàng)建的索引，但在生產環(huán)境中執(zhí)行計劃可能不會利用新創(chuàng)建的索引(例如，一個非聚集索引列在生產環(huán)境中不是一個高選中性列，但在測試環(huán)境中可能就不一樣)。

　　因此我們在創(chuàng)建索引時，要知道執(zhí)行計劃是否會真正利用它，但我們怎么才能知道呢?答案就是在測試服務器上模擬生產環(huán)境負載，然后創(chuàng)建合適的索引并進行測試，如果這樣測試發(fā)現索引可以提高性能，那么它在生產環(huán)境也就更可能提高應用程序的性能了。

　　雖然要模擬一個真實的負載比較困難，但目前已經有很多工具可以幫助我們。

　　使用SQL profiler跟蹤生產服務器，盡管不建議在生產環(huán)境中使用SQL profiler，但有時沒有辦法，要診斷性能問題關鍵所在，必須得用，在http://msdn.microsoft.com/en-us/library/ms181091.aspx有SQL profiler的使用方法。

　　使用SQL profiler創(chuàng)建的跟蹤文件，在測試服務器上利用數據庫調整顧問創(chuàng)建一個類似的負載，大多數時候，調整顧問會給出一些可以立即使用的索引建議，在http://msdn.microsoft.com/en-us/library/ms166575.aspx有調整顧問的詳細介紹。

　　第三步：整理索引碎片

　　你可能已經創(chuàng)建好了索引，并且所有索引都在工作，但性能卻仍然不好，那很可能是產生了索引碎片，你需要進行索引碎片整理。

　　什么是索引碎片?

　　由于表上有過度地插入、修改和刪除操作，索引頁被分成多塊就形成了索引碎片，如果索引碎片嚴重，那掃描索引的時間就會變長，甚至導致索引不可用，因此數據檢索操作就慢下來了。

　　有兩種類型的索引碎片：內部碎片和外部碎片。

　　內部碎片：為了有效的利用內存，使內存產生更少的碎片，要對內存分頁，內存以頁為單位來使用，最后一頁往往裝不滿，于是形成了內部碎片。

　　外部碎片：為了共享要分段，在段的換入換出時形成外部碎片，比如5K的段換出后，有一個4k的段進來放到原來5k的地方，于是形成1k的外部碎片。

　　如何知道是否發(fā)生了索引碎片?

　　執(zhí)行下面的SQL語句就知道了(下面的語句可以在SQL Server 2005及后續(xù)版本中運行，用你的數據庫名替換掉這里的AdventureWorks)：

　SELECTobject_name(dt.object_id) Tablename,si.name

　　IndexName,dt.avg_fragmentation_in_percentAS

　　ExternalFragmentation,dt.avg_page_space_used_in_percentAS

　　InternalFragmentation

　　FROM

　　(

　　SELECTobject_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent

　　FROMsys.dm_db_index_physical_stats (db_id('AdventureWorks'),null,null,null,'DETAILED'

　　)

　　WHEREindex_id<>0)ASdtINNERJOINsys.indexes siONsi.object_id=dt.object_id

　　ANDsi.index_id=dt.index_idANDdt.avg_fragmentation_in_percent>10

　　ANDdt.avg_page_space_used_in_percent<75ORDERBYavg_fragmentation_in_percentDESC

　　執(zhí)行后顯示AdventureWorks數據庫的索引碎片信息。

　　圖 3 索引碎片信息

　　使用下面的規(guī)則分析結果，你就可以找出哪里發(fā)生了索引碎片：

　　1)ExternalFragmentation的值>10表示對應的索引發(fā)生了外部碎片;

　　2)InternalFragmentation的值<75表示對應的索引發(fā)生了內部碎片。

　　如何整理索引碎片?

　　有兩種整理索引碎片的方法：

　　1)重組有碎片的索引：執(zhí)行下面的命令

　　ALTER INDEX ALL ON TableName REORGANIZE

　　2)重建索引：執(zhí)行下面的命令

　　ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR=90,ONLINE=ON)

　　也可以使用索引名代替這里的“ALL”關鍵字重組或重建單個索引，也可以使用SQL Server管理工作臺進行索引碎片的整理。

　　圖 4 使用SQL Server管理工作臺整理索引碎片

　　什么時候用重組，什么時候用重建呢?

　　當對應索引的外部碎片值介于10-15之間，內部碎片值介于60-75之間時使用重組，其它情況就應該使用重建。

　　值得注意的是重建索引時，索引對應的表會被鎖定，但重組不會鎖表，因此在生產系統中，對大表重建索引要慎重，因為在大表上創(chuàng)建索引可能會花幾個小時，幸運的是，從SQL Server 2005開始，微軟提出了一個解決辦法，在重建索引時，將ONLINE選項設置為ON，這樣可以保證重建索引時表仍然可以正常使用。

　　雖然索引可以提高查詢速度，但如果你的數據庫是一個事務型數據庫，大多數時候都是更新操作，更新數據也就意味著要更新索引，這個時候就要兼顧查詢和更新操作了，因為在OLTP數據庫表上創(chuàng)建過多的索引會降低整體數據庫性能。

　　我給大家一個建議：如果你的數據庫是事務型的，平均每個表上不能超過5個索引，如果你的數據庫是數據倉庫型，平均每個表可以創(chuàng)建10個索引都沒問題。

　　在前面我們介紹了如何正確使用索引，調整索引是見效最快的性能調優(yōu)方法，但一般而言，調整索引只會提高查詢性能。除此之外，我們還可以調整數據訪問代碼和TSQL，本文就介紹如何以最優(yōu)的方法重構數據訪問代碼和TSQL。

　　第四步：將TSQL代碼從應用程序遷移到數據庫中

　　也許你不喜歡我的這個建議，你或你的團隊可能已經有一個默認的潛規(guī)則，那就是使用ORM(Object Relational Mapping，即對象關系映射)生成所有SQL，并將SQL放在應用程序中，但如果你要優(yōu)化數據訪問性能，或需要調試應用程序性能問題，我建議你將SQL代碼移植到數據庫上(使用存儲過程，視圖，函數和觸發(fā)器)，原因如下：

　　1、使用存儲過程，視圖，函數和觸發(fā)器實現應用程序中SQL代碼的功能有助于減少應用程序中SQL復制的弊端，因為現在只在一個地方集中處理SQL，為以后的代碼復用打下了良好的基礎。

　　2、使用數據庫對象實現所有的TSQL有助于分析TSQL的性能問題，同時有助于你集中管理TSQL代碼。

　　3、將TS QL移植到數據庫上去后，可以更好地重構TSQL代碼，以利用數據庫的高級索引特性。此外，應用程序中沒了SQL代碼也將更加簡潔。

　　雖然這一步可能不會象前三步那樣立竿見影，但做這一步的主要目的是為后面的優(yōu)化步驟打下基礎。如果在你的應用程序中使用ORM(如NHibernate)實現了數據訪問例行程序，在測試或開發(fā)環(huán)境中你可能發(fā)現它們工作得很好，但在生產數據庫上卻可能遇到問題，這時你可能需要反思基于ORM的數據訪問邏輯，利用TSQL對象實現數據訪問例行程序是一種好辦法，這樣做有更多的機會從數據庫角度來優(yōu)化性能。

　　我向你保證，如果你花1-2人月來完成遷移，那以后肯定不止節(jié)約1-2人年的的成本。

　　OK!假設你已經照我的做的了，完全將TSQL遷移到數據庫上去了，下面就進入正題吧!

　　第五步：識別低效TSQL，采用最佳實踐重構和應用TSQL

　　由于每個程序員的能力和習慣都不一樣，他們編寫的TSQL可能風格各異，部分代碼可能不是最佳實現，對于水平一般的程序員可能首先想到的是編寫TSQL實現需求，至于性能問題日后再說，因此在開發(fā)和測試時可能發(fā)現不了問題。

　　也有一些人知道最佳實踐，但在編寫代碼時由于種種原因沒有采用最佳實踐，等到用戶發(fā)飆的那天才乖乖地重新埋頭思考最佳實踐。

　　我覺得還是有必要介紹一下具有都有哪些最佳實踐。

　　1、在查詢中不要使用“select *”

　　(1)檢索不必要的列會帶來額外的系統開銷，有句話叫做“該省的則省”;

　　(2)數據庫不能利用“覆蓋索引”的優(yōu)點，因此查詢緩慢。

　　2、在select清單中避免不必要的列，在連接條件中避免不必要的表

　　(1)在select查詢中如有不必要的列，會帶來額外的系統開銷，特別是LOB類型的列;

　　(2)在連接條件中包含不必要的表會強制數據庫引擎檢索和匹配不需要的數據，增加了查詢執(zhí)行時間。

　　3、不要在子查詢中使用count()求和執(zhí)行存在性檢查

　　(1)不要使用

SELECTcolumn_listFROMtableWHERE0<(SELECTcount(*)FROMtable2WHERE..)

　　使用

SELECTcolumn_listFROMtableWHEREEXISTS(SELECT*FROMtable2WHERE...)

　　代替;

　　(2)當你使用count()時，SQL Server不知道你要做的是存在性檢查，它會計算所有匹配的值，要么會執(zhí)行全表掃描，要么會掃描最小的非聚集索引;

　　(3)當你使用EXISTS時，SQL Server知道你要執(zhí)行存在性檢查，當它發(fā)現第一個匹配的值時，就會返回TRUE，并停止查詢。類似的應用還有使用IN或ANY代替count()。

　　4、避免使用兩個不同類型的列進行表的連接

　　(1)當連接兩個不同類型的列時，其中一個列必須轉換成另一個列的類型，級別低的會被轉換成高級別的類型，轉換操作會消耗一定的系統資源;

　　(2)如果你使用兩個不同類型的列來連接表，其中一個列原本可以使用索引，但經過轉換后，優(yōu)化器就不會使用它的索引了。例如：　

SELECTcolumn_listFROMsmall_table, large_tableWHERE

　　smalltable.float_column=large_table.int_column

　　在這個例子中，SQL Server會將int列轉換為float類型，因為int比float類型的級別低，large_table.int_column上的索引就不會被使用，但smalltable.float_column上的索引可以正常使用。

　　5、避免死鎖

　　(1)在你的存儲過程和觸發(fā)器中訪問同一個表時總是以相同的順序;

　　(2)事務應經可能地縮短，在一個事務中應盡可能減少涉及到的數據量;

　　(3)永遠不要在事務中等待用戶輸入。

　　6、使用“基于規(guī)則的方法”而不是使用“程序化方法”編寫TSQL

　　(1)數據庫引擎專門為基于規(guī)則的SQL進行了優(yōu)化，因此處理大型結果集時應盡量避免使用程序化的方法(使用游標或UDF[User Defined Functions]處理返回的結果集) ;

　　(2)如何擺脫程序化的SQL呢?有以下方法：

　　- 使用內聯子查詢替換用戶定義函數;

　　- 使用相關聯的子查詢替換基于游標的代碼;

　　- 如果確實需要程序化代碼，至少應該使用表變量代替游標導航和處理結果集。

　　7、避免使用count(*)獲得表的記錄數

　　(1)為了獲得表中的記錄數，我們通常使用下面的SQL語句：

　SELECTCOUNT(*)FROMdbo.orders

　　這條語句會執(zhí)行全表掃描才能獲得行數。

　　(2)但下面的SQL語句不會執(zhí)行全表掃描一樣可以獲得行數：

SELECTrowsFROMsysindexes

　　WHEREid=OBJECT_ID('dbo.Orders')ANDindid<2

　　8、避免使用動態(tài)SQL

　　除非迫不得已，應盡量避免使用動態(tài)SQL，因為：

　　(1)動態(tài)SQL難以調試和故障診斷;

　　(2)如果用戶向動態(tài)SQL提供了輸入，那么可能存在SQL注入風險。

　　9、避免使用臨時表

　　(1)除非卻有需要，否則應盡量避免使用臨時表，相反，可以使用表變量代替;

　　(2)大多數時候(99%)，表變量駐扎在內存中，因此速度比臨時表更快，臨時表駐扎在TempDb數據庫中，因此臨時表上的操作需要跨數據庫通信，速度自然慢。

　　10、使用全文搜索搜索文本數據，取代like搜索

　　全文搜索始終優(yōu)于like搜索：

　　(1)全文搜索讓你可以實現like不能完成的復雜搜索，如搜索一個單詞或一個短語，搜索一個與另一個單詞或短語相近的單詞或短語，或者是搜索同義詞;

　　(2)實現全文搜索比實現like搜索更容易(特別是復雜的搜索);

　　11、使用union實現or操作

　　(1)在查詢中盡量不要使用or，使用union合并兩個不同的查詢結果集，這樣查詢性能會更好;

　　(2)如果不是必須要不同的結果集，使用union all效果會更好，因為它不會對結果集排序。

　　12、為大對象使用延遲加載策略

　　(1)在不同的表中存儲大對象(如VARCHAR(MAX)，Image，Text等)，然后在主表中存儲這些大對象的引用;

　　(2)在查詢中檢索所有主表數據，如果需要載入大對象，按需從大對象表中檢索大對象。

　　13、使用VARCHAR(MAX)，VARBINARY(MAX) 和 NVARCHAR(MAX)

　　(1)在SQL Server 2000中，一行的大小不能超過800字節(jié)，這是受SQL Server內部頁面大小8KB的限制造成的，為了在單列中存儲更多的數據，你需要使用TEXT，NTEXT或IMAGE數據類型(BLOB);

　　(2)這些和存儲在相同表中的其它數據不一樣，這些頁面以B-Tree結構排列，這些數據不能作為存儲過程或函數中的變量，也不能用于字符串函數，如REPLACE，CHARINDEX或SUBSTRING，大多數時候你必須使用READTEXT，WRITETEXT和UPDATETEXT;

　　(3)為了解決這個問題，在SQL Server 2005中增加了VARCHAR(MAX)，VARBINARY(MAX) 和 NVARCHAR(MAX)，這些數據類型可以容納和BLOB相同數量的數據(2GB)，和其它數據類型使用相同的數據頁;

　　(4)當MAX數據類型中的數據超過8KB時，使用溢出頁(在ROW_OVERFLOW分配單元中)指向源數據頁，源數據頁仍然在IN_ROW分配單元中。

　　14、在用戶定義函數中使用下列最佳實踐

　　不要在你的存儲過程，觸發(fā)器，函數和批處理中重復調用函數，例如，在許多時候，你需要獲得字符串變量的長度，無論如何都不要重復調用LEN函數，只調用一次即可，將結果存儲在一個變量中，以后就可以直接使用了。

　　15、在存儲過程中使用下列最佳實踐

　　(1)不要使用SP_xxx作為命名約定，它會導致額外的搜索，增加I/O(因為系統存儲過程的名字就是以SP_開頭的)，同時這么做還會增加與系統存儲過程名稱沖突的幾率;

　　(2)將Nocount設置為On避免額外的網絡開銷;

　　(3)當索引結構發(fā)生變化時，在EXECUTE語句中(第一次)使用WITH RECOMPILE子句，以便存儲過程可以利用最新創(chuàng)建的索引;

　　(4)使用默認的參數值更易于調試。

　　16、在觸發(fā)器中使用下列最佳實踐

　　(1)最好不要使用觸發(fā)器，觸發(fā)一個觸發(fā)器，執(zhí)行一個觸發(fā)器事件本身就是一個耗費資源的過程;

　　(2)如果能夠使用約束實現的，盡量不要使用觸發(fā)器;

　　(3)不要為不同的觸發(fā)事件(Insert，Update和Delete)使用相同的觸發(fā)器;

　　(4)不要在觸發(fā)器中使用事務型代碼。

　　17、在視圖中使用下列最佳實踐

　　(1)為重新使用復雜的TSQL塊使用視圖，并開啟索引視圖;

　　(2)如果你不想讓用戶意外修改表結構，使用視圖時加上SCHEMABINDING選項;

　　(3)如果只從單個表中檢索數據，就不需要使用視圖了，如果在這種情況下使用視圖反倒會增加系統開銷，一般視圖會涉及多個表時才有用。

　　18、在事務中使用下列最佳實踐

　　(1)SQL Server 2005之前，在BEGIN TRANSACTION之后，每個子查詢修改語句時，必須檢查@@ERROR的值，如果值不等于0，那么最后的語句可能會導致一個錯誤，如果發(fā)生任何錯誤，事務必須回滾。從SQL Server 2005開始，Try..Catch..代碼塊可以處理TSQL中的事務，因此在事務型代碼中最好加上Try…Catch…;

　　(2)避免使用嵌套事務，使用@@TRANCOUNT變量檢查事務是否需要啟動(為了避免嵌套事務);

　　(3)盡可能晚啟動事務，提交和回滾事務要盡可能快，以減少資源鎖定時間。

　　要完全列舉最佳實踐不是本文的初衷，當你了解了這些技巧后就應該拿來使用，否則了解了也沒有價值。此外，你還需要評審和監(jiān)視數據訪問代碼是否遵循下列標準和最佳實踐。

　　如何分析和識別你的TSQL中改進的范圍?

　　理想情況下，大家都想預防疾病，而不是等病發(fā)了去治療。但實際上這個愿望根本無法實現，即使你的團隊成員全都是專家級人物，我也知道你有進行評審，但代碼仍然一團糟，因此需要知道如何治療疾病一樣重要。

　　首先需要知道如何診斷性能問題，診斷就得分析TSQL，找出瓶頸，然后重構，要找出瓶頸就得先學會分析執(zhí)行計劃。

　　理解查詢執(zhí)行計劃

　　當你將SQL語句發(fā)給SQL Server引擎后，SQL Server首先要確定最合理的執(zhí)行方法，查詢優(yōu)化器會使用很多信息，如數據分布統計，索引結構，元數據和其它信息，分析多種可能的執(zhí)行計劃，最后選擇一個最佳的執(zhí)行計劃。

　　可以使用SQL Server Management Studio預覽和分析執(zhí)行計劃，寫好SQL語句后，點擊SQL Server Management Studio上的評估執(zhí)行計劃按鈕查看執(zhí)行計劃，如圖1所示。

　　圖 1 在Management Studio中評估執(zhí)行計劃

　　在執(zhí)行計劃圖中的每個圖標代表計劃中的一個行為(操作)，應從右到左閱讀執(zhí)行計劃，每個行為都一個相對于總體執(zhí)行成本(100%)的成本百分比。

　　在上面的執(zhí)行計劃圖中，右邊的那個圖標表示在HumanResources表上的一個“聚集索引掃描”操作(閱讀表中所有主鍵索引值)，需要100%的總體查詢執(zhí)行成本，圖中左邊那個圖標表示一個select操作，它只需要0%的總體查詢執(zhí)行成本。

　　下面是一些比較重要的圖標及其對應的操作：

　　圖 2 常見的重要圖標及對應的操作

　　注意執(zhí)行計劃中的查詢成本，如果說成本等于100%，那很可能在批處理中就只有這個查詢，如果在一個查詢窗口中有多個查詢同時執(zhí)行，那它們肯定有各自的成本百分比(小于100%)。

　　如果想知道執(zhí)行計劃中每個操作詳細情況，將鼠標指針移到對應的圖標上即可，你會看到類似于下面的這樣一個窗口。

　　圖 3 查看執(zhí)行計劃中行為(操作)的詳細信息

　　這個窗口提供了詳細的評估信息，上圖顯示了聚集索引掃描的詳細信息，它要查找AdventureWorks數據庫HumanResources方案下Employee表中 Gender = ‘M’的行，它也顯示了評估的I/O，CPU成本。

　　查看執(zhí)行計劃時，我們應該獲得什么信息

　　當你的查詢很慢時，你就應該看看預估的執(zhí)行計劃(當然也可以查看真實的執(zhí)行計劃)，找出耗時最多的操作，注意觀察以下成本通常較高的操作：

　　1、表掃描(Table Scan)

　　當表沒有聚集索引時就會發(fā)生，這時只要創(chuàng)建聚集索引或重整索引一般都可以解決問題。

　　2、聚集索引掃描(Clustered Index Scan)

　　有時可以認為等同于表掃描，當某列上的非聚集索引無效時會發(fā)生，這時只要創(chuàng)建一個非聚集索引就ok了。

　　3、哈希連接(Hash Join)

　　當連接兩個表的列沒有被索引時會發(fā)生，只需在這些列上創(chuàng)建索引即可。

　　4、嵌套循環(huán)(Nested Loops)

　　當非聚集索引不包括select查詢清單的列時會發(fā)生，只需要創(chuàng)建覆蓋索引問題即可解決。

　　5、RID查找(RID Lookup)

　　當你有一個非聚集索引，但相同的表上卻沒有聚集索引時會發(fā)生，此時數據庫引擎會使用行ID查找真實的行，這時一個代價高的操作，這時只要在該表上創(chuàng)建聚集索引即可。

　　TSQL重構真實的故事

<p style="margin:10px auto; paddi