.Net+SQL Server公司專屬應用程式效能最佳化筆記1——調研

在進行了一段時間的調研後，本周開始著手進行效能最佳化工作。現在在最佳化工作工作之前，我總結一下調研了的一些資訊。

1.背景

客戶這是一個03年的時候開發的系統了，所以使用的是.NET 1.1+SQL Server 2000，作業系統用的是Windows2003，使用了這麼幾年，只是對作業系統進行了升級（從當時的Windows2000升級到2003的）以及對系統進行維護，.Net環境和資料庫並沒有改變。由於系統中記錄了幾年的資料，有些表有幾百萬行的資料，當初沒有建立索引和系統程式上考慮的不足造成目前系統運行十分緩慢，需要進行效能最佳化。我現在都開始用VS2008開發和使用SQL Server 2008資料庫了，突然接收這樣一個老項目，還真有點不適應，SQL2000我好久沒有碰過了。

2.硬體

在硬體環境上，客戶的伺服器還是很不錯的，Web伺服器和資料庫伺服器各一台，都是4個雙核CPU、8G記憶體、1G網路、300G的RAID5硬碟，總體來說感覺很好了。在得知客戶是8G的記憶體時我第一反應就是客戶肯定浪費記憶體了！結果實際一看，果然如此，系統是8G的記憶體，但是所有程式加起來用的記憶體才2G左右，而且一直上不去，為什麼呢？因為客戶使用的是32位的作業系統，所以預設只能支援4G的記憶體，有2G用於作業系統，另外2G用於應用程式，在工作管理員中可以看到SQL Server只佔用了1.7G左右的記憶體，而不能再佔用更多就是這樣一個原因。SQL Server是一個做大量資料處理的程式，記憶體的速度比硬碟快很多，若要處理的資料如果都是在記憶體中將會比讀取硬碟進行處理快的多，所以SQL Server佔用的記憶體越多越好。要突破32位作業系統對應用程式2G的記憶體限制，可以開啟3GB開關，將作業系統的記憶體使用量改為1G，應用程式使用的記憶體改為3G。當然這裡是8G的記憶體，所以開啟3GB開關是不夠的，這裡就需要開啟系統的APE開關，使用SQL Server的AWE功能。另外一種解決辦法就是換成64位的作業系統和SQL Server。

3.資料訪問

我簡單的Review了一下程式碼，該系統是BS程式，三層架構，資料庫訪問主要是採用SqlHelper調用預存程序和SQL語句，然後使用DataReader最終返回一個對象或對象集合。由於系統是在不斷的需求變化中完成了，所以也犯了很多項目的一個通病，為了趕時間，大家就不顧代碼的規範和架構，有的是使用預存程序，有的是使用SQL語句，有的是直接在UI層寫SQL語句，然後將SQL語句傳到業務層，最後再傳到資料層去執行。我已經不是第一次看到這樣的程式了，我大學實習時開始做的一個項目中也出現過這樣的代碼，所以感覺可以理解。

在Review資料訪問層的代碼時，我看到了大量的讀取一個DataReader用於填充一個對象的代碼如下：

private void fillRegionLevel(RegionLevelMod level, SqlDataReader reader)
        {
            // 行政層級代碼
            if (!reader.IsDBNull(0))
                level.LevelCode =  reader.GetString(0);
            // 行政層級名稱
            if (!reader.IsDBNull(1))
                level.LevelName = reader.GetString(1);
            // 行政層級描述
            if (!reader.IsDBNull(2))
                level.LevelDescribe = reader.GetString(2);
        }

這樣寫程式也不會有什麼錯誤，但是整個程式太依賴資料庫返回對象的順序了，如果修改了資料庫，在最前面多提供了一列，那麼reader.GetString(0)就會錯位讀取到其他的列，以後的讀取也全部錯位，所以擴充相對比較麻煩。我個人比較建議的寫法是通過列名來讀取DataReader中的內容，比如：

if (Convert.IsDBNull(reader["Code"]))
            {
                level.LevelCode = reader["Code"].ToString();
            }

當然其實並不需要這麼麻煩，在項目中如果通過DataReader填充一個對象的話，我一般採用反射的方式，在實體類定義的時候就為每個欄位添加Attribute，然後統一使用一個方法通過反射方式就可以將類中的欄位與DataReader返回的列進行映射了。具體參見範例程式碼： /Files/studyzy/LoadDbToObjectDemo.rar

還有一個問題就是對於字典表（比如地市地區、類型等），應該使用緩衝，將資料儲存在Web伺服器的記憶體中，不需要每次都讀取資料庫。

4.圖表

該系統中要使用大量的Chart，為此開發人員自己寫了一個伺服器控制項，通過設定各個屬性，然後傳入資料，最後執行DataBind()即可。這兒抽象出一個控制項是相當不錯的，實現了代碼功能的複用。整個Chart的展現思路是這樣的：

（1）系統將資料傳入Chart控制項，執行其DataBind方法，該控制項將使用GDI+進行繪圖。

（2）將繪出的圖根據當前的時間等屬性儲存到伺服器硬碟的某個檔案夾中。

（3）將繪出的圖的路徑與<img>程式碼群組合，將這段HTML代碼Render出來。

這樣功能是實現了，但是也存在幾個問題：

• Chart的繪製是在伺服器上進行了，有大量的Chart需要展示時將增加伺服器的負擔。
• Chart儲存到了硬碟中，然後在img標籤中指向該硬碟地址，為了這個圖片就做了2次硬碟的IO操作。
• 整個Chart資料讀取和繪製與Page_Load是同一個線程，如果一個Chart在資料讀取或繪製過程需要很長時間，則會導致整個頁面響應被阻塞。

經過我實際跟蹤測試，發現確實如此，如果繪製一個Chart要5秒，一個頁面開啟時間就必然超過5秒，只有等Chart繪製完成了，整個系統才會Response出來。對於以上提出的問題，我們可以採用以下的解決辦法：

• 將Chart的繪製從伺服器轉移到用戶端，具體做法就是採用Flash或者SilverLight（其實使用JavaScript也可以繪製Chart）。用戶端只需要下載了Flash後，系統將要繪製Chart的資料集通過XML、AMF等方式傳送給Flash，然後由Flash負責將整個Chart繪製出來。使用Flash後一方面可以減小伺服器壓力，另一方面可以提供訪問頁面的速度，另外也可以做出很好的效果提高使用者體驗。
• 不進行硬碟的IO操作，在伺服器繪製了Chart後，就直接使用一個頁面Response出來即可。
• 將Chart的資料讀取和繪製放在另外一個頁面中進行，展現Chart的頁面只需要輸出<img src='Chart.aspx?type=1&data=1,3,5,7'/>這樣的HTML即可。所有的資料讀取和繪製操作將在Chart.aspx頁面進行。這樣如果繪製一個Chart要5秒鐘，由於頁面的線程中並沒有執行繪圖，所以可以很快返回，瀏覽器在載入了頁面後才會去請求Chart.aspx頁面，這個時候才進行繪圖。

這裡個人更推薦第一種方法，在新版系統中可以考慮採用。不過對於現有系統的最佳化，當然不能進行這麼大的改動，採用第三點即可。

5.HTML

該系統的首頁存在效能問題，每次開啟都需要很長時間，如果網路情況不好的話就等的更久了。使用FireFox中的FireBug可以跟蹤首頁開啟時請求的所有資源的大小、回應時間等，也可以使用網路抓包工具進行分析。經過我抓包分析發現，首頁的HTML有180K左右，其實也不算大，引用的圖片、CSS、JS也都不多，但是整個響應很慢除了系統進行大量資料庫操作之外與HTML也存在很大關係。查看其HTML發現以下問題：

• HTML中存在很大的ViewState，但是首頁主要是唯讀資料繫結，所以很多控制項如DataGrid都可以關閉ViewState。
• HTML中使用的是儲存的Table拼接方式，這種方式將導致系統必須把整個大Table載入完成後才會呈現，可以改用div+css的方式，這樣每獲得了一塊DIV就可以呈現一塊內容。
• 沒有使用AJAX技術，頁面上的很多內容特別耗時間，完全可以通過AJAX進行非同步綁定的。

6.資料庫

資料庫是我本次最佳化的重點，由於是SQL Server 2000的資料庫，所以沒有DMV、沒有SSMS用的效能監控器、沒有包含索引……太多好用的功能沒有啊，十分的不方便。對資料庫的效能最佳化一般來說我覺得主要是3個方向：

• 最佳化索引，建立該建的索引去掉沒有用的索引。
• 改寫查詢，使之符合SARG。
• 減少阻塞和資源等待，避免死結。

我接下來的工作就是圍繞這3個方向展開。在SQL Server效能最佳化上必不可少的工具就是SQL Server Profiler，也就是SQL2000中的事件探測器。一種是使用Profiler抓取生產環境在業務高峰時的資料，一種是在測試環境中沒有其他使用者和程式幹擾的情況下抓取開啟某個頁面或者執行某個操作時的SQL跟蹤。跟蹤的結果儲存到資料庫中，然後使用查詢語句找到Reads和Duration很大的SQL語句，針對這些語句進行效能調優。

對於系統中的死結，使用如下命令可以開啟死結追蹤記錄，一旦資料庫中出現了死結，則將會把死結資訊記錄到資料庫的日誌中。

dbcc traceon (1204, 3605, -1)

dbcc tracestatus(-1)

DBCC TRACEON

資料庫最佳化是一門很大的學問，接下來的日子我將主要進行資料庫最佳化的工作，具體最佳化過程我也將盡量詳細的記錄下來，希望對大家也有所協助。