SQLServer存儲過程調(diào)試指南

1、lecKlargetTT中*"©Search DttbSSSSQL Server 存儲過程調(diào)試指南SQL Server存儲過程調(diào)試指南存儲過程（Stored Procedure ）是一組為了完成特定功能的SQL語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中。用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來 執(zhí)行它。有時人們將存儲過程稱為“數(shù)據(jù)庫中埋頭苦干的老黃?！保菙?shù)據(jù)庫中的一個 重要對象，任何一個設(shè)計良好的數(shù)據(jù)庫應(yīng)用程序都應(yīng)該用到存儲過程。但是無論編寫還是 調(diào)試存儲過程都是一項復(fù)雜的工作，因此在本次技術(shù)手冊中，我們將對SQL Server存儲過程的調(diào)試進行詳細的介紹，

2、包括了基礎(chǔ)的調(diào)試方法和在調(diào)試過程中出現(xiàn)的T-SQL性能問題和解決方法。SQL Server存儲過程調(diào)試基礎(chǔ)本部分介紹了在SQL Server中使用異常處理調(diào)試存儲過程的方法，并對怎樣調(diào)試T-SQL存儲過程進行了詳細介紹，通過對基礎(chǔ)的學習，相信您會對存儲過程的基礎(chǔ)有個更加深刻的了解。SQL Server中使用異常處理調(diào)試存儲過程（一） SQL Server中使用異常處理調(diào)試存儲過程（二） 怎樣調(diào)試T-SQL存儲過程（一）怎樣調(diào)試T-SQL存儲過程（二）怎樣調(diào)試T-SQL存儲過程（三）T-SQL性能問題和解決方法當應(yīng)用程序用戶開始遇到性能問題時，一般他們會聯(lián)系數(shù)據(jù)庫管理員并詢問是否數(shù)據(jù) 庫存在問題

3、。導(dǎo)致嚴重性能問題的往往是編寫不當?shù)腡ransact-SQL（T-SQL）代碼。因此,你必須找出確定性能糟糕的查詢并對它們進行優(yōu)化。TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 3 of 31TTttWIV"Jseardi DatabasexomxnSQL Server中使用游標進行行處理存儲過程與嵌套查詢優(yōu)化SQL Server中的視圖與UDF性能問題SQL Server不必要的記錄鎖解決SQL Server觸發(fā)器濫用問題如何診斷和修復(fù)T-SQL問題SQL Server存儲過程的修改與變更在對SQL Server存儲過程進行修改和變更時，往往會遇到各種各樣

4、的錯誤和問題，這可能是由于與現(xiàn)有數(shù)據(jù)庫函數(shù)沖突造成的。本部分介紹了如何對存儲過程進行升級與批 量修改，而不造成錯誤的方法。批量編輯SQL Server存儲過程在SQL Server 2005中升級存儲過程TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page # of 31I Data bsEexomx nI； . TargetTT中國SQL Server中使用異常處理調(diào)試存儲過程（上）異常處理被普遍認為是 T-SQL腳本編程中的最弱的方面。幸運的是，這一點在SQLServer 2005中得到了改變，因為 SQL Server 2005支持結(jié)構(gòu)化異常處理。本文首先關(guān)注 新特性“

5、 TRY,CATCH的基本構(gòu)成，然后在SQL Server 2000和SQL Server 2005 中對照著看一些T-SQL的例子，這些例子中使用事務(wù)代碼故意制造了一些違反約束限制的情況。 將來的文章會繼續(xù)探討這一主題。在SQL Server之前的版本中，你需要在執(zhí)行 INSERT UPDATE DELETE后立即檢查 全局變量“ erro”來處理異常，如果“ erro”變量不為零的話（表示有錯誤），就 接著執(zhí)行一些糾正動作。開發(fā)人員常常重復(fù)這種與業(yè)務(wù)邏輯無關(guān)的代碼，這會導(dǎo)致重復(fù)代 碼塊，而且需要與GOT（語句和RETURN!句結(jié)合使用。結(jié)構(gòu)化異常處理為控制具有許多動態(tài)運行時特性的復(fù)雜程序提

6、供了一種強有力的處理 機制。目前，這種機制經(jīng)實踐證明是良好的，許多流行的編程語言（比如：微軟的VisualBasic.Net和Visual C# ）都支持這種異常處理機制。接下來你會在例子中看到，采用了 這種健壯的方法以后，會使你的代碼可讀性和可維護性更好。TRY塊包含了可能潛在失敗的事務(wù)性代碼，而 CATCHfc包含了 TRY塊中出現(xiàn)錯誤時執(zhí)行的代碼。如果TRY塊中出現(xiàn)了任何錯誤，執(zhí)行流程被調(diào)轉(zhuǎn)到CATCH塊，錯誤可以被處理，而出錯函數(shù)可以被用來提供詳細的錯誤信息。TRY,CATCH 基本語法如下：BEGIN TRYRAISERROR('Houst on, we have a pro

7、blem', 16,1 END TRYBEGIN CATCHSELECT ERROR_NUM(ER as ERROR_NUMBER,ERROR_SEVERITY as ERROR_SEVERITY, ERROR_STATE as ERROR_STATE, ERROR_MESSAG) as ERROR_MESSAGE END CATCHniiH iihi hi iiiiih iiiii lima m 11 iiiii lima iiiiih ii im iiiiih iihi im iiiiih iihimniiai iiiiai m iiiihi iiiiTTttW*lecKlarge

8、t TT 中 SI注意上面腳本中函數(shù)的用法，我們可以用它們代替局部變量和（或者）全局變量。這 些函數(shù)只應(yīng)該被用在 CATCH塊中，函數(shù)功能說明如下：ERROR_NUMBER 返回錯誤數(shù)量。ERROR_SEVERITY 返回錯誤嚴重等級。ERROR_STATE 返回錯誤狀態(tài)號。ERROR_PROCEDURE返回出錯位置存儲過程或者觸發(fā)器的名稱。ERROR_LINE） 返回程序中引起錯誤的行號。ERROR_MESSAGGE返回錯誤信息的完整文本。錯誤內(nèi)容包括可替換參數(shù)的值，比如： 長度，對象名稱或者時間。我會先用SQL Server 2000演示一個簡單例子，然后演示一個SQL Server 20

9、05異常處理的例子。下面是一個簡單的存儲過程示例，先用SQL Server 2000編寫，然后改用 SQL Server2005實現(xiàn)。兩者都從簡單的表開始，我們在對這些表執(zhí)行插入操作時會違反約束限制。下面是表結(jié)構(gòu)：create table dbo.Titles(TitleID int Primary Key iden tity,TitleName nvarchar ( 128) NOT NULL,Price mo ney NULL con strai nt CHK_Price check( Price > 0 )create table dbo.Authors(Authors_ID in

10、t primary key ide ntity,auname nvarchar ( 32) NULL,aun ame nvarchar ( 64) NULL,TitleID int constraint FK_TitleID foreign keyrefere nces Titles( TitleID ),Commissi on Rati ng int con stra int CHK_ValidateCommissio nRati ngCheck ( Commissi on Rati ng betwee n 0 and 100)create table dbo.Applicati on _E

11、rror_Log(table name sys name,1 Search DatabaElecKJarget TT中國TTttM*userName sys name,errorNumber int,errorSeverity int,errorState int, errorMessage varchar (4000)（作者：Serdar Yegulalp 譯者：馮旳暉 來源：TT中國）TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 7 of 31SQL Server中使用異常處理調(diào)試存儲過程（下）點擊這里獲取存儲過程 P_l nsert_New_BookTitle_

12、2K 的源代碼。你可以看到，這個存儲過程包含了非結(jié)構(gòu)化的錯誤處理代碼，這是我們在SQL Server 2005之前使用的方式我們已經(jīng)先看到了存儲過程P_I nsert_New_BookTitle_2K 中使用的代碼。你頂多能說:“至少我們有異常處理。”下面的語句執(zhí)行這個SQL Server 2000下的存儲過程。exec P_I nsert_New_BookTitle_2K 'Red Storm Ris in g',16.99, 'Tom','Cla ncy', 200在用指定的參數(shù)執(zhí)行存儲過程時，對Authors表的插入失敗了，因為傭金費率值無

13、效我們的約束檢查發(fā)現(xiàn)了該無效值，我們可以看到如下錯誤信息：Msg 547, Level 16, State 0, Procedure P_ln sert_New_BookTitle, Li ne 23 The INSERT stateme nt c on flicted with the CHECK con strai nt"CHK_ValidateCommissio nRati ng". The con flict occurred in database"Adve ntureworks2005", table "dbo.Authors&qu

14、ot;, colum n 'Commissi on Rat in g'. The stateme nt has bee n termi nated.這里的問題是我們不能阻止這些消息被送到客戶端。所以判斷哪里出錯的重擔就放到 了客戶端的頭上。令人遺憾的是，在有些情況下，這樣的結(jié)果對于一些不使用約束限制的 應(yīng)用程序可能足夠了。我們再來試一次，這次我們使用TRY,CATCH代碼塊。點擊這里獲取存儲過程 P_l nsert_New_BookTitle_2K5 的源代碼。在這段新改進的存 儲過程中，我們看到使用了TRY,CATCH代碼塊的結(jié)構(gòu)化錯誤處理：要注意SQL Server 200

15、5異常處理代碼是經(jīng)過簡化的，因此具有更好的可讀性和可維 護性。不需要剪切和粘貼異常處理代碼，也不需要使用GOT（語句。執(zhí)行該存儲過程時，你可以看到如下結(jié)果：lecKlarget TT 中 BOTT0t»*；exec P_ln sert_New_BookTitle_2K5 'Red Storm Risi ng',16.99,'Tom','Cla ncy', 200我們用指定的參數(shù)執(zhí)行存儲過程，同樣因為傭金費率值無效，對Authors表的插入失敗了。錯誤發(fā)生時，程序執(zhí)行流程跳轉(zhuǎn)到了CATCH代碼塊，在CATCH代碼塊中我們回滾了事務(wù)，然后用

16、SQL Server 2005自帶的函數(shù)給Application_Error_Log 表插入一行日志。新的TRY,CATCH代碼塊無疑使編寫處理錯誤代碼更容易，它還可以在任何時候阻 止錯誤信息發(fā)送到客戶端。當然這可能需要T-SQL程序員的編程思維有一個轉(zhuǎn)變，這是一個絕對有必要使用的特性。要記住遷移SQL Server 2000代碼到SQL Server 2005時，如果程序的錯誤處理機制已經(jīng)設(shè)計為舊的發(fā)送錯誤到客戶端的方式，那你可能不得不修改應(yīng) 用程序了。從長遠來看，我相信為這種潛在的問題付出努力重新設(shè)計是值得的。（作者：Serdar Yegulalp 譯者：馮旳暉 來源：TT中國）TT數(shù)據(jù)庫

17、技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 9 of 31怎樣調(diào)試T-SQL存儲過程（一）我會執(zhí)行（或者單步執(zhí)行）一個T-SQL存儲過程示例程序，并在調(diào)試過程中執(zhí)行以下操作：給輸入?yún)①x值，監(jiān)視變量內(nèi)容，在運行過程中跟蹤存儲過程的邏輯流程，估算T-SQL表達式的值，查看儲存過程輸出內(nèi)容，設(shè)置斷點，大體檢查環(huán)境狀態(tài)。（后面的技巧 文章會繼續(xù)探討這些主題）。我們演示示例存儲過程時，不是在Management Studio中調(diào)試，而是在 Visual Studio 2005開發(fā)環(huán)境中調(diào)試。我提到這一點是因為在SQL Server2000下，我們可以使用 Query Analyzer調(diào)

18、試存儲過程?？赡軐?Management Studio也 會增加對調(diào)試功能的支持。T-SQL 示例存儲過程：P_DisplayProductDetails我們使用的存儲過程示例使用函數(shù)來根據(jù)單價給每一個產(chǎn)品子類排序，從AdventureWorks數(shù)據(jù)庫中查詢產(chǎn)品明細信息。該存儲過程接收產(chǎn)品類別名作為一個可選入 參。幾個輸出參數(shù)會給調(diào)用它的分支返回有用的信息。最后，正如前面的文章講到的，該 存儲過程使用了“結(jié)構(gòu)化異常處理”。Use Adve ntureWorksGOIF EXISTS （ SELECT * FROM sysobjects WHERE type = 'P' AND

19、 n ame = 'P_DisplayProductDetails'）DROP Procedure P_DisplayProductDetailsGOCREATE Procedure P_DisplayProductDetails（Categoryname varchar （ 50） = NULL,Matchi ngRows int = NULL OUTPUT,ErrorStri ng varchar（ 128）= NULL OUTPUT,ErrorNumber int = NULL OUTPUT）asBEGIN TRY-添加一個 % （模糊查詢），這樣調(diào)用者不需要知道子類的完

20、整名稱if CategoryName is n ullselect CategoryName = '%'elseselect CategoryName = CategoryName + '%'-使用rank函數(shù)，根據(jù)子類名稱按 ListPrice 字段排名-DENSE_RA NK函數(shù)分配相鄰值SELECT Productio n. Product.ProductID,Productio n.P roduct.Name AS ProductName,Product ion .ProductCategory.Name AS CategoryName,Product

21、ion .ProductSubcategory.Name AS SubcategoryName,Productio n.Product. ListPrice,DENSE_RANK) over(Partiti on byProductio n. ProductSubcategory.Name ORDER BYProduction.Product.ListPrice DESC) as PriceRankFROM Productio n.P roductINNER JOIN Productio n. ProductSubcategoryON Productio n. Product.ProductS

22、ubcategoryID =Productio n.ProductSubcategory .P roductSubcategoryIDINNER JOIN Productio n.P roductCategoryON Productio n. ProductSubcategory.ProductCategoryID =Product ion .ProductCategory.ProductCategorylDWHERE Productio n.P roductCategory.Name like CategoryNameORDER BY Productio n.P roductCategory

23、.Nameselect Matchi ngRows = ROWCOUNT return 0END TRYBEGIN CATCH-把錯誤信息記錄日志”在調(diào)試時，我們可能會跳過這一步！insert dbo.Applicatio n_Error_Log( UserName, errorNumber,errorSeverity, errorState, errorMessage)values (suser_sname(), ERROR_NUMBER ,ERROR_SEVERITY) ERROR_STATE , ERROR_MESSAGE)SELECT ErrorNumber = ERROR_NUMBE

24、R ,ErrorStri ng = ERROR_MESSAGE()RAISERROR( ErrorStri ng, 16,1END CATCH（作者：Serdar Yegulalp 譯者：馮旳暉 來源：TT中國）TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 11 of 31TT*B怎樣調(diào)試T-SQL存儲過程（二）從哪里開始調(diào)試存儲過程在進行任何調(diào)試動作之前，你必須先創(chuàng)建該存儲過程。我們可以用Ma nageme ntStudio中的“新建查詢”或者通過使用Visual Studio 2005圖形化方式創(chuàng)建該存儲過程。在Visual Studio中，可以打開一個數(shù)據(jù)庫項目

25、，然后用Visual Studio 已安裝的模板來創(chuàng)建存儲過程。存儲過程創(chuàng)建好了以后（不管是用哪種方式創(chuàng)建的），你就可以開始在VisualStudio 2005 中調(diào)試了。在為調(diào)試目的啟動Visual Studio 時，你不需要創(chuàng)建一個新項目。取而代之的是，你可以在Server Explorer中創(chuàng)建一個到 AdvertureWorks 數(shù)據(jù)庫的連接,就像我在下圖中做的一樣。如圖1。（你必須提供服務(wù)器名以及登錄驗證，然后選擇AdventureWorks數(shù)據(jù)庫。）然后你可以展開樹形結(jié)構(gòu)，打開存儲過程文件夾。接下來，右 擊你想調(diào)試的存儲過程，然后在右鍵菜單中選擇“單步調(diào)試存儲過程”。然后你就可以啟

26、 動調(diào)試了 ！TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page # of 31圖1 :在VS 2005中怎樣啟動調(diào)試怎樣運行存儲過程在執(zhí)行單步調(diào)試存儲過程時，你實際上是在告訴Visual Studio 一行一行地運行存儲過程。既然我們的存儲過程示例不接受入?yún)ⅲ銜吹揭粋€Local Window （局部變量窗口），在這個窗口中你可以滾動瀏覽存儲過程中的局部變量和參數(shù)。如圖2,在表頭Direction列下面，你可以發(fā)現(xiàn) Visual Studio給你識別出了輸出參數(shù)，Value列是該表中唯一你可以修改的列。在這個例子中，我輸入Bike作為CategoryName的一個值。T

27、T數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 13 of 31TecKlargettt 申 anDatj baEexomx nTT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page # of 31TecKlargettt 申 anTT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page # of 31TecKlargettt 申 an圖2:輸出參數(shù)Visual Studio提供了許多窗口來查看運行環(huán)境的狀態(tài)。代碼中即將運行的行被標注了一個黃色的箭頭。如下圖 3所示。eWorka-AlTEt ffreMivrt靜.弧砂1丫耗0磴"甲

28、鼻匚匸聒匸許“皿匸匚肚匸傭G二迅總二 NatelogSou* l-t u.Jtt CtJTWTirIlSl i 艱=OTtE 豔書口點：4±<f 二RE 河二 0CTF7IIB3IUU9.口0右曲電Wik胃I fh&K 'vtlwtfTT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page # of 31TecKlargettt 申 anTT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page # of 31TecKlargettt 申 ani-f I -：*, t r*ijf ±-!SL.±? L ,Er -Fr

29、：liac .Nnae U Ei-$-l>a&ZK.,和.斗 HJlhF-K關(guān)辰, ；Ufir*5 z*?iit'4!Ai3*TV. >4ii* X :?:：-廣政并亡丁丁 JfiW.trsi'-eiieiiP_eqi1;j±覽-IIjzEilm：,圖3:待執(zhí)行代碼大部分時候，你會在存儲過程中使用單步執(zhí)行或者跳過執(zhí)行命令。下面的命令都可以應(yīng)用到T-SQL單行：單步執(zhí)行Step Into （ F11）:用來單步執(zhí)行代碼。（黃色箭頭會一行一行向下移動。）跳過執(zhí)行Step Over (F10):如果有的代碼行可能修改數(shù)據(jù)或者調(diào)用其他存儲過程， 但是你在調(diào)

30、試過程中不關(guān)心該部分內(nèi)容，那么就可以使用跳過執(zhí)行。例如：你可能想跳過 執(zhí)行審計的代碼。跳出執(zhí)行Step Out (SHIFT-F11):執(zhí)行存儲過程的剩余部分，中間不停頓。運行到光標位置 Run to Cursor ( CTRL-F10):把光標定位到代碼中的一點，然后摁 CTRL-F10執(zhí)行當前光標位置前的全部代碼。繼續(xù)執(zhí)行Continue ( F5)：重新開始執(zhí)行，運行到完成或者運行到下一個斷點(可以 很快跳過多個斷點)。(作者：Serdar Yegulalp 譯者：馮旳暉 來源：TT中國)TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 15 of 31Dlechl

31、argetTT*B怎樣調(diào)試T-SQL存儲過程（三）Visual Studio調(diào)試窗口Visual Studio給我們提供了許多信息豐富的調(diào)試窗口。我會把我們使用的示例代碼過一下，演示幾個這樣的窗口。我們從 Autos窗口開始。Autos窗口顯示當前語句中使用的變量。當黃色箭頭指向“ select CategoryName = CategoryName + '%'"這一行代碼時，注意看“ CategoryNam”的值實際上是該語句執(zhí) 行前的值。在本例中，我們給這個參數(shù)值后面追加了一個百分號，如圖4.圖 4: Autos 窗口Locals窗口顯示當前局部變量和參數(shù)，你可以

32、在代碼執(zhí)行過程中在這個窗口里交互式地修改這些變量的值。該窗口會把改變的值著色顯示。如圖5,你可以看到我把“ Bikes改成了“ Clothing "，為了便于在 Visual Studio中查看，我把它標為紅色。'lechlaret TT中國圖5:本地變量窗口監(jiān)視窗口支持你輸入或者從代碼中拖拽 T-SQL表達式進來，在該窗口中估算該表達式 代碼的實際值。如果你想檢查條件表達式（像 IF，WHILE或者CASE所包含表達式的值, 這一點可能很有用。實際上，你可以同時使用多達四個監(jiān)視窗口。35個匹配“ Clothing "的行數(shù)據(jù)（見圖6）輸出窗口顯示查詢語句或者輸出語

33、句返回的結(jié)果集。在我們的例子中，我們返回了圖6:輸出窗口懸浮值窗口不是一個真正的窗口，但是它是一個特別有用的特性，非常值得一提。如 果你在調(diào)試模式下，把光標懸浮在代碼行上，你會看見與該行有關(guān)變量的值。這一點看起 來與Windows應(yīng)用程序的功能類似：在 Windows應(yīng)用程序中，你把光標懸浮到工具欄圖標 時，不需要點擊就可以看到幫助信息。Search Dat 自 b asexom .c nTTttWVlecKlargetTT中國斷點窗口顯示了當前所有斷點，而且允許你添加新斷點。斷點是用戶定義的代碼位置 和（或者）暫停執(zhí)行的條件，允許調(diào)試人員停下來思考，查看等等。你可以通過點擊代碼 窗口左側(cè)邊緣

34、部分添加斷點。要注意在圖7中顯示的斷點窗口實際上被分成了兩個窗口?？肯碌拇翱谔峁┝藬帱c信息，告訴我們在第20行有一個斷點。圖7中靠上的窗口包含一個代碼片段。在上面的窗口中我們可以看到在第20行有一個紅色的泡泡，它就是VisualStudio的斷點標識。圖7:斷點窗口結(jié)論Visual Studio 2005 為你的T-SQL存儲過程提供了非常容易使用的圖形化調(diào)試工具。 你可以利用它對你的代碼進行單元測試。因為開發(fā)人員典型的單元測試代碼缺少所有支持 的存儲過程或者函數(shù)時，Step Over選項真的遲早會有用的。而且既然你可以很容易改變 入?yún)⒒蛘呔植孔兞康闹?，你就可以強行改變程序的?zhí)行路徑分支，這樣

35、就可以執(zhí)行代碼的 特定部分。（作者：Serdar Yegulalp 譯者：馮旳暉 來源：TT中國）'lechlarget TT申國SQL Server中使用游標進行行處理當應(yīng)用程序用戶開始遇到性能問題時，一般他們會聯(lián)系數(shù)據(jù)庫管理員并詢問是否數(shù)據(jù)庫存在問題。有趣的是，大多數(shù)用戶都會查看內(nèi)存、CPU和磁盤使用率，而不是造成嚴重性能問題的特定的代碼模塊;很不幸，導(dǎo)致嚴重性能問題的往往是編寫不當?shù)腡ransact-SQL（T-SQL）代碼；而且，通過在服務(wù)器上增加更多的內(nèi)存來解決這些問題，而不對問題 的根源進行修復(fù)，將直接導(dǎo)致更大的問題。因此，你必須找出確定性能糟糕的查詢并對它 們進行優(yōu)化。T

36、-SQL是一個強大的程序語言，它可以讀取和修改數(shù)據(jù)、改變SQL Server設(shè)置、創(chuàng)建和修改數(shù)據(jù)庫對象、修改 Registry設(shè)置等等。然而，必須謹記的是，沒有任何一個T-SQL功能可以適應(yīng)所有環(huán)境和所有應(yīng)用的。比如，如果你的應(yīng)用需要在不同網(wǎng)絡(luò)共享中復(fù)制大 量的文件，那么T-SQL就不是你的最佳選擇了。類似的，使用 SQL Server的內(nèi)置電子郵 件功能來發(fā)送多兆字節(jié)附件的電子郵件并不是一個好主意。相反，如果你需要在數(shù)據(jù)庫中 檢索數(shù)據(jù)行或者修改所有滿足特定條件的行，那么T-SQL則是你的最佳選擇。由于 T-SQL支持大量的功能，因此我無法全面的闡述可能造成性能問題的所有具體情況。相反，我將

37、介紹某些情況下一種編寫代碼的方法將可以改善其它方面的性能。使用游標進行行處理游標可能是最常見的引起性能問題的原因，雖然它們對于某些任務(wù)而言是一個強大且 方便的方法。比如，假設(shè)你有一個復(fù)雜的存儲過程，其中你必須反復(fù)調(diào)用存儲在臨時表中 的每一行數(shù)據(jù)。你可以使用光標從臨時表中檢索參數(shù)值，同時對每一個檢索的值都調(diào)用一 次存儲過程。基本上，游標允許你處理數(shù)據(jù)集中的每一行數(shù)據(jù)，同時對數(shù)據(jù)記錄進行相同 的邏輯處理，每次處理一行數(shù)據(jù)。然而，T-SQL是一個基于集合的語言；這種語言是針對記錄集進行數(shù)據(jù)讀寫操作優(yōu)化的，而不是針對記錄行。從前端特定語言（比如VBScript、ASP或者ColdFusion ）開發(fā)轉(zhuǎn)

38、變過來的T-SQL初學者，他們往往在并不需要循環(huán)時在T-SQL中錯誤地使用循環(huán)。比如，下面的代碼將酒店數(shù)據(jù)庫的所有California 作者的“姓”標記為“ changed":DECLARE last_ name VARCHAR50)TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 19 of 31SQL Server中使用游標進行行處理當應(yīng)用程序用戶開始遇到性能問題時，一般他們會聯(lián)系數(shù)據(jù)庫管理員并詢問是否數(shù)據(jù)庫存在問題。有趣的是，大多數(shù)用戶者E會查看內(nèi)存、CPlffi磁盤使用率，而不是造成嚴重性能問題的特定的代碼模塊；很不幸，導(dǎo)致嚴重性能問題的往往是編寫不當?shù)?/p>

39、Transact-SQL (T-SQL)代碼；而且，通過在服務(wù)器上增加更多的內(nèi)存來解決這些問題，而不對問題 的根源進行修復(fù)，將直接導(dǎo)致更大的問題。因此，你必須找出確定性能糟糕的查詢并對它 們進行優(yōu)化。T-SQL是一個強大的程序語言，它可以讀取和修改數(shù)據(jù)、改變 SQL Server設(shè)置、創(chuàng)建 和修改數(shù)據(jù)庫對象、修改 Registry設(shè)置等等。然而，必須謹記的是，沒有任何一個T-SQL功能可以適應(yīng)所有環(huán)境和所有應(yīng)用的。比如，如果你的應(yīng)用需要在不同網(wǎng)絡(luò)共享中復(fù)制大 量的文件，那么T-SQL就不是你的最佳選擇了。類似的，使用 SQL Server的內(nèi)置電子郵 件功能來發(fā)送多兆字節(jié)附件的電子郵件并不是一

40、個好主意。相反，如果你需要在數(shù)據(jù)庫中 檢索數(shù)據(jù)行或者修改所有滿足特定條件的行，那么T-SQL則是你的最佳選擇。由于 T-SQL支持大量的功能，因此我無法全面的闡述可能造成性能問題的所有具體情況。相反，我將 介紹某些情況下一種編寫代碼的方法將可以改善其它方面的性能。使用游標進行行處理游標可能是最常見的引起性能問題的原因，雖然它們對于某些任務(wù)而言是一個強大且 方便的方法。比如，假設(shè)你有一個復(fù)雜的存儲過程，其中你必須反復(fù)調(diào)用存儲在臨時表中 的每一行數(shù)據(jù)。你可以使用光標從臨時表中檢索參數(shù)值，同時對每一個檢索的值都調(diào)用一 次存儲過程?；旧希螛嗽试S你處理數(shù)據(jù)集中的每一行數(shù)據(jù)，同時對數(shù)據(jù)記錄進行相同 的

41、邏輯處理，每次處理一行數(shù)據(jù)。然而，T-SQL是一個基于集合的語言；這種語言是針對記錄集進行數(shù)據(jù)讀寫操作優(yōu)化的，而不是針對記錄行。從前端特定語言(比如VBScript、ASP或者ColdFusion )開發(fā)轉(zhuǎn)變過來的T-SQL初學者，他們往往在并不需要循環(huán)時在T-SQL中錯誤地使用循環(huán)。比如，下面的代碼將酒店數(shù)據(jù)庫的所有California 作者的"姓”標記為 “ changed":DECLARE last_name VARCHAR50)TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 18 of 31SearchD'lechlhret TT*國

42、TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 20 of 31TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 20 of 31存儲過程與嵌套查詢優(yōu)化使用嵌套查詢是初學者所犯的典型錯誤。有些語言提供直接從前端應(yīng)用發(fā)送到數(shù)據(jù)庫 的合并查詢。如果你的應(yīng)用用戶數(shù)較少，那么你可以使用這個方法。但是，一旦你的用戶 是成十成百的增長，那么你一定會遇到性能問題通知（以及甚至是用戶的抱怨）。該怎么處理呢？ SQL Server具備一個非常智能的嵌套查詢優(yōu)化器，它可以在運行時創(chuàng) 建查詢執(zhí)行規(guī)劃。優(yōu)化器可以緩存最近使用執(zhí)行規(guī)劃并重用它們。這樣做將導(dǎo)致減少服務(wù) 器的負

43、載從而獲得更好的性能。然而，優(yōu)化器重用存儲過程的查詢規(guī)劃比嵌套查詢更容易。 你的存儲過程至少可以保證與嵌套查詢一樣的執(zhí)行速度99%勺情況下存儲過程比嵌套查詢性能好。另外的1%是極其罕見的個例，即當參數(shù)值頻繁變化時，重用查詢規(guī)劃實際上 是個糟糕的方法。那么你該如何處理呢？通常的做法是，總是使用存儲過程替代嵌套查詢。（作者：Serdar Yegulalp 譯者：曾少寧 來源：TT中國）TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 20 of 31TT*BSQL Server中的視圖與UDF性能問題SQL Server視圖為T-SQL程序提供多種好處：它們可以用于掩蓋實際的

44、表結(jié)構(gòu)，限制 來自某些用戶的敏感數(shù)據(jù)等等。然而，視圖并不總是最好的某些從Oracle轉(zhuǎn)到SQL Server的程序員很喜歡使用視圖；較老版本的Oracle并不允 許從存儲過程返回數(shù)據(jù)集，因此必須使用視圖。這并不是SQL Serve不存在此r的問題，但是，憑心而論，并不是只有 Oracle程序員才犯這樣的錯誤?；蛟S，最常見的誤解是使用大量JOIN操作的視圖比使用相同 JOIN操作的存儲過程快。事實上并不是這樣的。視圖對于擁有相同數(shù)目JOIN操作的查詢并沒有任何優(yōu)勢。事實上，視圖有一個最大限制一一它們不能接收參數(shù)。因此，如果你有一個連接10個表的視圖并且這些表中的其中一個有上百萬的行，那么你的視圖

45、將返回至少1百萬的行。在存儲過程中，將這樣的視圖連接到很少的幾個表上就將成為災(zāi)難。你該怎么做呢？在這樣的情況下，一個較好的選擇是用戶自定義方法（UDF，它可接收參數(shù)并允許你限制返回行的數(shù)目。另外一個選擇是在一個單元存儲過程中連接所有表并 使用參數(shù)限制輸出。自從UDF出現(xiàn)在SQL Server 2000中時，它就受到了 T-SQL程序員的歡迎。UDF允許 你創(chuàng)建非常類似于內(nèi)置方法執(zhí)行的常規(guī)程序，但是它們按照你的具體業(yè)務(wù)需要來執(zhí)行數(shù)據(jù) 處理。比如，你可以寫一個方法來計算兩個時期之間的營業(yè)天數(shù)。有三種類型的UDF標量：返回唯一值嵌套表：返回行集（或者表）多語句：返回行集（或者表）TT數(shù)據(jù)庫技術(shù)專題之

46、“ SQL Server存儲過程調(diào)試指南”Page 21 of 31SQL Server中的視圖與UDF性能問題SQL Server視圖為T-SQL程序提供多種好處：它們可以用于掩蓋實際的表結(jié)構(gòu)，限制 來自某些用戶的敏感數(shù)據(jù)等等。然而，視圖并不總是最好的某些從Oracle轉(zhuǎn)到SQL Server的程序員很喜歡使用視圖;較老版本的Oracle并不允 許從存儲過程返回數(shù)據(jù)集，因此必須使用視圖。這并不是SQL Serve不存在此r的問題，但是，憑心而論，并不是只有 Oracle程序員才犯這樣的錯誤?；蛟S，最常見的誤解是使用大量JOIN操作的視圖比使用相同 JOIN操作的存儲過程快。事實上并不是這樣的

47、。視圖對于擁有相同數(shù)目JOIN操作的查詢并沒有任何優(yōu)勢。事實上，視圖有一個最大限制一一它們不能接收參數(shù)。因此，如果你有一個連接10個表的視圖并且這些表中的其中一個有上百萬的行，那么你的視圖將返回至少1百萬的行。在存儲過程中，將這樣的視圖連接到很少的幾個表上就將成為災(zāi)難。你該怎么做呢？在這樣的情況下，一個較好的選擇是用戶自定義方法（ UDB ,它可接 收參數(shù)并允許你限制返回行的數(shù)目。另外一個選擇是在一個單元存儲過程中連接所有表并 使用參數(shù)限制輸出。自從UDF出現(xiàn)在SQL Server 2000中時，它就受到了 T-SQL程序員的歡迎。UDF允許 你創(chuàng)建非常類似于內(nèi)置方法執(zhí)行的常規(guī)程序，但是它們按

48、照你的具體業(yè)務(wù)需要來執(zhí)行數(shù)據(jù) 處理。比如，你可以寫一個方法來計算兩個時期之間的營業(yè)天數(shù)。有三種類型的UDE標量：返回唯一值嵌套表：返回行集（或者表）多語句：返回行集（或者表）TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 21 of 31lecKlargetTT*BTTttW*SQL Server不必要的記錄鎖如果你的應(yīng)用執(zhí)行緩慢，那么你可能在鎖住一些不必要的記錄。這對于在同一服務(wù)器 上同時整合報表和交易活動的應(yīng)用往往會出現(xiàn)。默認情況下，SQL Server會在執(zhí)行SELECT語句期間鎖住記錄。其他的用戶必須一直 等到你的SELECT完成才可以修改數(shù)據(jù)。報表很少需要最新

49、的數(shù)據(jù)。例如，一個作業(yè)記錄應(yīng)用一般只顯示之前幾周或者數(shù)月的 數(shù)據(jù)。這樣，你可以使用鎖定提示（NOLOQK或者一個較少限制的事務(wù)孤立級別而不影響報表質(zhì)量。你應(yīng)該怎樣做呢？在你的報表查詢引用的表中添加NOLOC提示。例如，下面的查詢在不鎖定任何記錄的情況下查詢一個包含所有標題和作者的列表：SELECT aun ame + ', ' + au_f name, title, priceFROM authors a(NOLOCKINNER JOIN titleauthor b(NOLOCK ON a.aud = b.au_idINNER JOIN titles c(NOLOCK ON b

50、.title_id = c.title_id（作者：Serdar Yegulalp 譯者：曾少寧 來源：TT中國）TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 23 of 31解決SQL Server觸發(fā)器濫用問題觸發(fā)器濫用觸發(fā)器是在添加、修改數(shù)據(jù)或者將數(shù)據(jù)遷移到一個指定表時執(zhí)行某些動作的方法。 SQL Server 2005也同樣提供數(shù)據(jù)庫級的觸發(fā)器，但是我在此只說明表級觸發(fā)器。由于觸 發(fā)器會啟動一個隱藏事務(wù)，因此觸發(fā)器會在服務(wù)器上強加額外的開支。一旦執(zhí)行觸發(fā)器， 一個新的隱藏事務(wù)就會開始，同時在事務(wù)中的任意數(shù)據(jù)檢索將鎖定受影響表。你應(yīng)該怎么處理呢？要盡量少用觸發(fā)器

51、并且盡可能使觸發(fā)器邏輯簡單。你可以簡單地 以完整性引用約束來替代某些觸發(fā)器；其它觸發(fā)器功能可以在存儲過程中實現(xiàn)。返回受影響行數(shù)默認情況下，SQL Server返回一個友好的消息來報告每個查詢所影響的行總數(shù)。這對 于調(diào)試應(yīng)用或者直接在 Query An alyzer中修改數(shù)據(jù)是一個非常好的設(shè)置。然而，你的前 端應(yīng)用并不需要知道受影響的行數(shù)目一一它只需要數(shù)據(jù)。發(fā)送這個消息可能會引入不必要 的網(wǎng)絡(luò)負荷。你應(yīng)該如何處理呢？在你的所有存儲過程中使用“ SET NOCOUNT Ol來減少網(wǎng)絡(luò)傳輸條件語句執(zhí)行往往，你需要根據(jù)傳遞到存儲過程的參數(shù)的不同值應(yīng)用不同的代碼邏輯。比如，如果 我的參數(shù)值是0,那么我可

52、以從一組表中檢索值，如果參數(shù)值為 1,則可以從另一組表中 檢索。IF parameter = 0BEGINSELECT colu mn1, colum n2FROMSome_tables,ENDlecKJarget TT中國ELSEBEGINSELECT colu mn1, colum n2FROMbther_tables,END這個代碼迫使我的過程在每次執(zhí)行時都必須重新編譯，因為SQL Server只有在運行時才能識別兩個規(guī)劃中的哪一個是有用的。你應(yīng)該如何處理呢？將上面的過程分成兩個獨立的過程，每個對應(yīng)查詢不同的表集。 在你的中間層代碼中確定參數(shù)值，然后再調(diào)用恰當?shù)拇鎯^程。作者：Serda

53、r Yegulalp 譯者：曾少寧 來源：TT中國）TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 23 of 31TT*B如何診斷和修復(fù)T-SQL問題有幾種方法可以診斷代碼相關(guān)的性能問題，但由于篇幅局限，在本章中我將不全面的 涉及。但是，你可以使用 SQL Profiler來去掉性能糟糕的一系列常規(guī)程序。同時，你必 須檢查查詢執(zhí)行規(guī)劃以便確保SQL Server使用最佳的規(guī)劃來執(zhí)行你的程序。一旦你意識到存儲過程出現(xiàn)了問題，那么可以采取下面的步驟來解決：1. 創(chuàng)建名稱稍微不同的存儲過程（例如以“ rout in el "替代“ routi ne ”）2. 在新

54、過程中修改代碼同時確保它比現(xiàn)有代碼效率更高3. 將舊的過程重新命名為“ rout in e2 ”4. 將新的過程重新命名為“ routine ”如何解決嵌套查詢的問題呢？你可以嘗試重寫現(xiàn)有的查詢，但是，很可能你將需要重 新編譯和部署代碼。這樣做是為了同時避免嵌套查詢重復(fù)和將所有T-SQL附加到存儲過程上。如何防范未然制定編碼規(guī)范和準則。Microsoft提供了對這方面有所幫助的資源。在部署之前進行代碼檢查。代碼檢查必須：確保代碼完成應(yīng)該完成的任務(wù)。確保代碼符合編碼規(guī)范和準則。培訓新程序員關(guān)于編碼技巧和以前沒遇到過的技術(shù)。在將程序部署到生產(chǎn)環(huán)境之前，先全面地進行程序壓力測試。TTttA*TecK

55、largettt 申 an（作者：Serdar Yegulalp 譯者：曾少寧 來源：TT中國）TT數(shù)據(jù)庫技術(shù)專題之“ SQL Server存儲過程調(diào)試指南”Page 23 of 31TT*B批量編輯SQL Server存儲過程我維護的數(shù)據(jù)庫系統(tǒng)廣泛使用存儲過程。在數(shù)據(jù)庫中執(zhí)行的大多數(shù)的普通任務(wù)是專門由SQL Server 2000和SQL Server 2005通過存儲過程來處理的。經(jīng)?？梢酝ㄟ^簡單編輯SQL Server中的存儲過程來使得數(shù)據(jù)庫系統(tǒng)發(fā)生全局性更改。然而，有時你需要一次性修 改全部的存儲過程，這就像做外科手術(shù)一樣，需要盡可能地完美。當然，這里假定你不使 用Visual Stu

56、dio或其它的一些IDE來完成這種工作,這些IDE自然會節(jié)省很大量的苦差。這是我修改SQL Server中一組存儲過程的最基本的方法：1生成一個SQL腳本，在數(shù)據(jù)庫中創(chuàng)建所有相關(guān)的存儲過程。確保此腳本也執(zhí)行了任 何所需的DROP FUNCTIO命令,以使得現(xiàn)存的全部存儲過程復(fù)本都被刪除。實現(xiàn)這個的最 簡便的方法是僅需使用SQL Server 2000 數(shù)據(jù)庫企業(yè)管理器中的“ Gen erate SQL Script ”上下文菜單選項。在 SQL Server 2005數(shù)據(jù)庫中，則是“ Tasks”上下文菜單選項中的“ Gen erate Scripts "選項。注意，SQL Server 2005腳本向?qū)Э雌饋砼c老版本有很大差別，且如果你正運行著 SQL Server 2005 SP1的話，會使得你在這種問題上限入一個可能引發(fā)重復(fù)操作的麻煩。在“