提高SQL Server資料庫效率常用方法

在現在互連網應用程式開發過程中，常常會發現查詢或者操作資料速度慢。其原因很多，常見如下幾種：
1、沒有索引或者沒有用到索引(這是資料庫設計的缺陷)
2、I/O輸送量小，形成了瓶頸效應。
3、記憶體不足
4、網路速度慢
5、查詢出的資料量過大（可以採用多次查詢）
6、鎖或者死結(這也是程式設計的缺陷)
7、返回了不必要的行和列
8、查詢語句不好，沒有最佳化
      資料庫要高效運行，首先要保證資料庫設計的正確性，非特殊需要不要違反三大範式原則；然後再來考慮資料庫效能及效率的最佳化工作。日常工作中，我們經常用到的一些最佳化方法如下：
1、把資料、日誌、索引放到不同的I/O裝置上，增加讀取速度。資料量（尺寸）越大，提高I/O越重要。
2、升級硬體。
3、根據查詢條件，建立索引，最佳化索引、最佳化訪問方式，限制結果集的資料量。索引應該盡量小，使用位元組數小的列建索引好，不要對有

  
     限的幾個值的欄位建單一索引。
4、查詢耗時和欄位值總長度成正比，所以資料庫設計的時候可變長欄位不能用CHAR類型，而是VARCHAR。有相當一部份開發人員喜歡可變長字串也用CHAR，然後補空格。
5、重建索引DBCC REINDEX，DBCC INDEXDEFRAG，收縮資料和日誌 DBCC SHRINKDB，DBCC SHRINKFILE。設定自動收縮日誌。對於大的資料庫不要設定資料庫自動成長，它會降低伺服器的效能。
6、在查詢Select語句中用Where字句限制返回的行數，避免表掃描。如果返回不必要的資料，浪費了伺服器的I/O資源，加重了網路的負擔降低效能。如果表很大，在表掃描的期間將表鎖住，禁止其他的聯結訪問表，後果嚴重。
7、儘可能不使用游標，它佔用大量的資源。如果需要row-by-row地執行，盡量採用非游標技術，如：在用戶端迴圈，用暫存資料表，Table變數，用子查詢，用Case語句等等。
8、用Profiler來跟蹤查詢，得到查詢所需的時間，找出SQL的問題所在；用索引最佳化器最佳化索引。
9、注意UNion和UNion all 的區別。盡量使用UNION all。
10、注意使用DISTINCT，在沒有必要時不要用，它同UNION一樣會使查詢變慢。
11、查詢時不要返回不需要的行、列
12、用select top 100 / 10 Percent 來限制使用者返回的行數或者SET ROWCOUNT來限制操作的行。
13、使用查詢分析器，查看SQL語句的查詢計劃和評估分析是否是最佳化的SQL。一般的20%的代碼佔據了80%的資源，我們最佳化的重點是這些慢的地方。
14、如果使用了IN或者OR等時發現查詢沒有走索引，使用顯示申明指定索引： Select * FROM tablename (INDEX = IX_Title) Where sex IN ('男'，'女')
15、資料庫有一個原則是代碼離資料越近越好，所以優先選擇Default，依次為規則、觸發器、約束Constraint（約束如外健主健CheckUNIQUE……，資料類型的最大長度等等都是約束）、預存程序。這樣不僅維護工作小，編寫程式品質高，並且執行的速度快。
16、如果要插入大的二進位值到Image列，使用預存程序，千萬不要用內嵌Insert來插入。因為應用程式首先將二進位值轉換成字串（尺寸是它的兩倍），伺服器受到字元後又將他轉換成二進位值。儲存過

  
     程就沒有這些動作。方法：Create procedure p_insert as insert into table(Fimage) values (@image)， 在前台調用這個預存程序傳入二進位參數，這樣處理速度明顯改善。
17、Between在某些時候比IN 速度更快，Between能夠更快地根據索引找到範圍。用查詢最佳化工具可見到差別。 select * from chineseresume where title in ('男'，'女')和Select * from chineseresume where between '男' and '女' 是一樣的功能。由於in會在比較多次，所以有時會慢些。
18、不要在程式中使用沒有作用的交易處理。
19、用OR的字句可以分解成多個查詢，並且通過UNION 串連多個查詢。他們的速度只同是否使用索引有關，如果查詢需要用到聯合索引，用UNION all執行的效率更高。多個OR的字句沒有用到索引，改寫成UNION的形式再試圖與索引匹配。一個關鍵的問題是否用到索引。
20、盡量少用視圖，它的效率低。對視圖操作比直接對錶操作慢，可以用stored procedure來代替它。特別的是不要用視圖嵌套，嵌套視圖增加了尋找原始資料的難度。
21、沒有必要時不要用DISTINCT和ORDER BY，這些動作可以改在客戶
端執行。它們增加了額外的開銷。這同UNION 和UNION ALL一樣的道理。
22、在IN後面值的列表中，將出現最頻繁的值放在最前面，出現得最少的放在最後面，減少判斷的次數。
23、一次更新多條記錄比分多次更新每次一條快，就是說批處理好。
24、盡量將資料的處理工作放在伺服器上，減少網路的開銷，如使用預存程序。預存程序是編譯好、最佳化過、並且被組織到一個執行規劃裡、且儲存在資料庫中的SQL語句，是流程控制語言的集合，速度當然快。
25、通過SQL Server Performance Monitor監視相應硬體的負載 Memory: Page Faults / sec計數器如果該值偶爾走高，表明當時有線程競爭記憶體。如果持續很高，則記憶體可能是瓶頸。

#資料庫技術