如何提高SQL SERVER的性能
2024-08-31 00:48:27
供稿：網(wǎng)友
如何提高sql server的性能
第一篇：通過維護表的索引來提高數(shù)據(jù)的訪問速度
大多數(shù)sql server表需要索引來提高數(shù)據(jù)的訪問速度，如果沒有索引，sql server要進行表格掃描讀取表中的每一個記錄才能找到索要的數(shù)據(jù)。索引可以分為簇索引和非簇索引，簇索引通過重排表中的數(shù)據(jù)來提高數(shù)據(jù)的訪問速度，而非簇索引則通過維護表中的數(shù)據(jù)指針來提高數(shù)據(jù)的索引。
索引的體系結構：
為什么要不斷的維護表的索引？首先，簡單介紹一下索引的體系結構。sql server在硬盤中用8kb頁面在數(shù)據(jù)庫文件內存放數(shù)據(jù)。缺省情況下這些頁面及其包含的數(shù)據(jù)是無組織的。為了使混亂變?yōu)橛行颍鸵伤饕Ｉ伤饕螅陀辛怂饕摵蛿?shù)據(jù)頁，數(shù)據(jù)頁保存用戶寫入的數(shù)據(jù)信息。索引頁存放用于檢索列的數(shù)據(jù)值清單（關鍵字）和索引表中該值所在紀錄的地址指針。索引分為簇索引和非簇索引，簇索引實質上是將表中的數(shù)據(jù)排序，就好像是字典的索引目錄。非簇索引不對數(shù)據(jù)排序，它只保存了數(shù)據(jù)的指針地址。向一個帶簇索引的表中插入數(shù)據(jù)，當數(shù)據(jù)頁達到100%時，由于頁面沒有空間插入新的的紀錄，這時就會發(fā)生分頁，sql server 將大約一半的數(shù)據(jù)從滿頁中移到空頁中，從而生成兩個半的滿頁。這樣就有大量的數(shù)據(jù)空間。簇索引是雙向鏈表，在每一頁的頭部保存了前一頁、后一頁地址以及分頁后數(shù)據(jù)移動的地址，由于新頁可能在數(shù)據(jù)庫文件中的任何地方，因此頁面的鏈接不一定指向磁盤的下一個物理頁，鏈接可能指向了另一個區(qū)域，這就形成了分塊，從而減慢了系統(tǒng)的速度。對于帶簇索引和非簇索引的表來說，非簇索引的關鍵字是指向簇索引的，而不是指向數(shù)據(jù)頁的本身。
為了克服數(shù)據(jù)分塊帶來的負面影響，需要重構表的索引，這是非常費時的，因此只能在需要時進行。可以通過dbcc showcontig來確定是否需要重構表的索引。下面舉例來說明dbcc showcontig和dbcc redbindex的使用方法。以sql server自帶的northwind數(shù)據(jù)作為例子
帶開sql server的query analyzer輸入命令：
use northwind
declare @table_id int
set @table_id=object_id('orders')
dbcc showcontig(@table_id)
這個命令顯示northwind數(shù)據(jù)庫中的orders表的分塊情況，結果如下：
dbcc showcontig scanning 'orders' table...
table: 'orders' (21575115); index id: 1, database id: 6
table level scan performed.
- pages scanned................................: 20
- extents scanned..............................: 5
- extent switches..............................: 4
- avg. pages per extent........................: 4.0
- scan density [best count:actual count].......: 60.00% [3:5]
- logical scan fragmentation ..................: 0.00%
- extent scan fragmentation ...................: 40.00%
- avg. bytes free per page.....................: 146.5
- avg. page density (full).....................: 98.19%
dbcc execution completed. if dbcc printed error messages, contact your system administrator.
通過分析這些結果可以知道該表的索引是否需要重構。表1.1描述了每一行的意義
信息 描述
pages scanned 表或索引中的長頁數(shù)
extents scanned 表或索引中的長區(qū)頁數(shù)
extent switches dbcc遍歷頁時從一個區(qū)域到另
一個區(qū)域的次數(shù)
avg. pages per extent 相關區(qū)域中的頁數(shù)
scan density best count是連續(xù)鏈接時的理想?yún)^(qū)
[best count:actual count] 域改變數(shù)，actual count是實際區(qū)
域改變數(shù)，scan density為100%
表示沒有分塊。
logical scan fragmentation 掃描索引頁中失序頁的百分比
extent scan fragmentation 不實際相鄰和包含鏈路中所有鏈
接頁的區(qū)域數(shù)
avg. bytes free per page 掃描頁面中平均自由字節(jié)數(shù)
avg. page density (full) 平均頁密度，表示頁有多滿
從上面命令的執(zhí)行結果可以看的出來，best count為3 而actual count為5這表明orders表有分塊需要重構表索引。下面通過dbcc dbreindex來重構表的簇索引。
同樣在query analyzer中輸入命令：
use northwind
dbcc dbreindex('northwind.dbo.orders',pk_orders,90)
執(zhí)行結果：
dbcc execution completed. if dbcc printed error messages, contact your system administrator.
dbcc dbreindex參數(shù)說明：第一個參數(shù)為要重構的表明。第二個參數(shù)為需要重構的索引表識，‘’表示所有的索引。第三個參數(shù)為頁的填充因子，填充因子越大，頁越滿。
然后再用dbcc showcontig查看重構簇索引后的結果：
use northwind
declare @table_id int
set @table_id=object_id('orders')
dbcc showcontig(@table_id)
返回結果如下：
dbcc showcontig scanning 'orders' table...
table: 'orders' (21575115); index id: 1, database id: 6
table level scan performed.
- pages scanned................................: 22
- extents scanned..............................: 3
- extent switches..............................: 2
- avg. pages per extent........................: 7.3
- scan density [best count:actual count].......: 100.00% [3:3]
- logical scan fragmentation ..................: 0.00%
- extent scan fragmentation ...................: 33.33%
- avg. bytes free per page.....................: 869.2
- avg. page density (full).....................: 89.26%
dbcc execution completed. if dbcc printed error messages, contact your system administrator.
通過結果我們可以看到scan denity為100%表沒有分塊不需要重構表索引了。如果重構表的簇索引scan denity還小于100%的話可以重構表的全部索引。命令如下：
--use northwind
--dbcc dbreindex('northwind.dbo.orders',’’,90)
使用作業(yè)定時重構索引：
如果你的數(shù)據(jù)庫訪問非常頻繁的話，非常容易出現(xiàn)數(shù)據(jù)分塊的現(xiàn)象，因此你可以利用作業(yè)來幫你在系統(tǒng)相對空閑的時候重構你的索引。
