作者： TechRepublic.com
Friday, March 5 2004 9:51 AM
|
特別說明：
在微軟的SQL服務器系統中，對數據庫查詢功能進行適當的優化需要懂得一些基本的查詢索引和性能統計方面的知識。熟悉該系統的優化工作是如何實現的將有助于提高決策的正確性。
隨著你對微軟的SQL服務器數據庫實現的逐漸熟悉，性能優化的需求也將進一步增加。建立一個真正實現最優查詢功能的數據庫環境的第一步是要懂得SQL服務器系統的優化器是如何工作的。
索引
雖然對于特定的查詢來說，進行查詢規劃和性能優化可能只需要少量的成本估算與比較，也可以沒有成本估算與比較，但是大多數的查詢將從實現完全優化的工作中受益。提高查詢性能的最有效的方法之一就是創建一個高效率的索引。一個構架良好的索引在執行查詢工作的時候可以避免出現掃描整個數據表的情況。
在創建索引的時候，SQL服務器系統將自動度量和存儲那些與索引列相關的分布狀態值相對應的統計信息。這些統計信息常常被優化器用來評估查詢的優化策略是否合理。
有兩種類型的索引：clustered索引和non-clustered索引，根據數據集合的不同，每種類型的索引都有各自獨特的優點。
clustered索引要求數據表中數據按照順序存儲。因為數據已經排序，所以對于查找一定范圍的索引值時clustered 索引是非常有效的。對于查找具有唯一索引值的行信息來說，這種類型的索引性能也優于其他類型的索引。
non-clustered索引和教科書中的索引非常相似，索引在一個位置而其數據值卻在另外一個位置。對于一個數據值的查詢搜索來說，首先搜索non-clustered的索引，找到數據值在數據表中的位置，然后直接從這個位置得到數據。non-clustered 索引對于精確匹配查詢是非常有用的。
統計學
作為一種常用的規則，和大多數商業使用需求一樣，索引的數量應該盡可能少，以減少與每個查詢相關的處理過程。如果要分析和優化查詢的性能，首先應該度量和收集數據的統計特性。
SQL服務器系統能夠維護索引值的數據統計特性。如果對其進行適當的配置，對于非索引值也能夠進行統計度量。
對于性能優化，數據庫管理員應該懂得幾個基本的統計概念，這些概念的定義如下：
|
基數：度量在數據集中可以存在多少個唯一值。
密度：度量在數據集中唯一值的個數。密度通過如下方法得到：給定鍵值的行數除以數據表的總行數。優化器將忽略高密度的索引。
選擇率：度量對于一個特定的查詢將返回查詢結果的行數。選擇率通過如下方法得到：查詢關鍵字的個數除以查詢得到的行數。要計算查詢規劃的相對成本，優化器需要一個有效的選擇率來度量。
隨著列中數據的變化，索引和列統計信息就變得沒有用處了，這樣將導致優化器在決定如何處理查詢時達不到最優性能。因此，根據數據表中數據的變化，SQL服務器系統周期的自動更新這些統計信息。通過對這些數據的采樣，這種統計信息的自動更新將使得成本降到最低，而且不需要對全部數據進行分析。
最佳性能
在一個復雜的數據庫表中設計并指定索引是一件非常棘手的任務。幸運的是，SQL服務器系統有一個內置的調節向導來幫助你建立最優的統計和索引集合。要提高數據庫的查詢性能，可以通過運行向導來提供一個基于腳本的建議列表。
對于SQL服務器查詢優化器如何工作這一部分懂得越多，你就會知道對于特定的情形為什么只能用向導的建議來實現。但是，對于動態系統來說，最佳的數據庫性能分析部分將需要進行周期性地更新。理解查詢索引性能中的每個統計度量的真正含義將有助于你在管理決策方面有一個良好的知識基礎。
更新索引統計
分布頁面并不是每次一個記錄更新時都要進行更新.在大型數據庫中,這會導致巨大的性能損失.因此,當用戶初始創建一個空表時,分布頁面仍是空的.它僅在發生如下情況時才被更新:
1.用戶在一個已存在數據表上創建一個索引.
2.用戶進行了update?satatic語句
從系統管理員角度來看,用戶應該創建一個工具來自動地更新分布頁面.自動更新應該至少每周一次,如果數據量每天增加10%以上則應每天一次.
因為不可能每天都添加索引,用戶需要使用update?statistics語句更新分布頁面,用以優化SQLserver.
UPDATE?STATISTICS
在指定的表或索引視圖中，對一個或多個統計組（集合）有關鍵值分發的信息進行更新。若要基于列生成統計，請參見?CREATE?STATISTICS。?
語法
UPDATE?STATISTICS?table?|?view
????[?
????????index
????????|?(?statistics_name?[?,...n?]?)
????]?
????[????WITH
????????[?
????????????[?FULLSCAN?]
????????????|?SAMPLE?number?{?PERCENT?|?ROWS?}?]
????????????|?RESAMPLE
????????]?
????????[?[?,?]?[?ALL?|?COLUMNS?|?INDEX?]
????????[?[?,?]?NORECOMPUTE?]?
????]?
參數
table?|?view
要更新統計的表或索引視圖的名稱。表名和視圖名必須符合標識符的規則。有關更多信息，請參見使用標識符。由于索引名在每個數據庫中不唯一，所以必須指定?table?或?view。可選擇指定數據庫、表或視圖所有者。只有在?Microsoft??SQL?Server??2000?企業版中才支持索引視圖。
index
要更新統計的索引。索引名必須符合標識符的規則。如果未指定?index，則更新指定表或索引視圖中的所有索引的分發統計。若要查看索引名和描述的列表，請帶表名或視圖名執行?sp_helpindex。
statistics_name
要更新的統計組（集合）的名稱。統計名稱必須符合標識符規則。有關生成統計組的更多信息，請參見?CREATE?STATISTICS。
n
是表示可以指定多個?statistic_name?組的占位符。
FULLSCAN
指定應讀取?table?或?view?中的所有行以收集統計。FULLSCAN?提供與?SAMPLE?100?PERCENT?相同的行為。FULLSCAN?不能與?SAMPLE?選項一起使用。
SAMPLE?number?{?PERCENT?|?ROWS?}
當為較大的表或視圖收集統計時，指定要采樣的表或索引視圖的百分比或行數。number?只允許使用整數，無論它是?PERCENT?還是?ROWS。若要對較大的表或視圖使用默認采樣行為，請將?SAMPLE?number?和?PERCENT?或?ROWS?一起使用。Microsoft?SQL?Server?將確保值的采樣數不低于某一數目，以保證統計有用。如果?PERCENT、ROWS?或?number?選項導致要采樣的行數過小，SQL?Server?則自動根據表或視圖中的現有行數改正采樣。
說明??默認行為是在目標表或索引視圖上進行采樣掃描。SQL?Server?自動計算所需的樣本大小。
RESAMPLE
指定使用從所有現有統計（包括索引）繼承的采樣速率來收集統計。如果采樣速率導致要采樣的行過少，SQL?Server?則自動根據表或視圖中的現有行數改正采樣。
ALL?|?COLUMNS?|?INDEX
指定?UPDATE?STATISTICS?語句是否影響列統計、索引統計或所有現有統計。如果未指定選項，則?UPDATE?STATISTICS?語句影響所有的統計。每個?UPDATE?STATISTICS?語句只能指定一種類型（ALL、COLUMNS?或?INDEX）。?
NORECOMPUTE
指定過期統計不自動重新計算。統計過期與否取決于在索引列上進行的?INSERT、UPDATE?和?DELETE?操作的數量。指定該選項時，將導致?SQL?Server?禁用自動統計重建功能。若要還原自動統計重新計算，請重新執行?UPDATE?STATISTICS（不要?NORECOMPUTE?選項），或者執行?sp_autostats。
重要??禁用自動統計重新計算會導致?SQL?Server?查詢優化器對于涉及指定表的查詢選擇非最佳的策略。
注釋
SQL?Server?保留每個索引中關于鍵值分發的統計，并且使用這些統計來決定查詢處理中使用哪個（或哪些）索引。用戶可以通過使用?CREATE?STATISTICS?語句生成基于非索引列的統計。查詢優化依賴于分發步驟的準確性：?
如果索引中的鍵值有顯著變化，請對此索引重新運行?UPDATE?STATISTICS。
如果索引列中添加、更改或刪除大量數據（即如果鍵值分發更改），或者用?TRUNCATE?TABLE?語句將表截斷然后重新填充，請使用?UPDATE?STATISTICS。?
若要查看統計最近一次更新的時間，請使用?STATS_DATE?函數。
只有當能夠在計算列上創建索引時，才可以在包含這些計算列的表上創建或更新統計。有關在計算列上創建索引的要求和限制的更多信息，請參見?CREATE?INDEX。
權限
UPDATE?STATISTICS?權限默認授予表或視圖的所有者，并且該權限不可轉讓。
示例
A.?更新單個表的所有統計
本示例更新表?authors?上的所有索引分發統計。
UPDATE?STATISTICS?authors
B.?僅更新單一索引的統計
本示例僅更新表?authors?的索引?au_id_ind?的分發信息。?
UPDATE?STATISTICS?authors?au_id_ind
C.?使用?50%?采樣更新特定統計組（集合）的統計
本示例首先創建表?authors?中?au_lname?列和?au_fname?列的統計組，然后對其進行更新。
CREATE?STATISTICS?anames?
???ON?authors?(au_lname,?au_fname)
???WITH?SAMPLE?50?PERCENT
GO
--?Time?passes.?The?UPDATE?STATISTICS?statement?is?then?executed.
UPDATE?STATISTICS?authors(anames)?
???WITH?SAMPLE?50?PERCENT
GO
D.?使用?FULLSCAN?和?NORECOMPUTE?更新特定統計組（集合）的統計
本示例更新表?authors?中的?anames?統計組（集合），強制對表?authors?中的所有行進行完全掃描，并且關閉該統計組（集合）的自動統計更新。
UPDATE?STATISTICS?authors(anames)
???WITH?FULLSCAN,?NORECOMPUTE
sp_updatestats對當前數據庫中所有用戶定義的表運行?UPDATE?STATISTICS。
語法
sp_updatestats?[[@resample?=]?''resample'']
返回代碼值
0（成功）或?1（失敗）
參數
[@resample?=]?''resample''
指定?sp_updatestats?將使用?UPDATE?STATISTICS?命令的?RESAMPLE?選項。新統計表將繼承舊統計表的采樣比率。如果未指定?''resample''，則?sp_updatestats?使用默認采樣更新統計表。該參數的數據類型為?varchar(8)，默認值為?''NO''。
注釋
sp_updatestats?會顯示表示其進度的消息。完成更新之后，該存儲過程將報告已為所有的表更新了統計信息。?
權限
只有?DBO?和?sysadmin?固定服務器角色的成員才能執行該過程。
示例
下例為數據庫?pubs?中的表更新統計信息。
USE?pubs
EXEC?sp_updatestats?
Sqlserver7?編程技術內幕提供的方法.
drop?proc?pr_updateindex
create?proc?pr_updateindex
as
?set?nocount?on?
?declare?get_index_curs?cursor?
??for?select?name--tablename
??from?sysobjects?--systemtable
?where?type=''u''?-usertable
?declare?@holdtable?varchar(30)
?declare?@message?varchar(40)
?declare?@dynamic?varchar(51)
?open?getindex_curs
?fetch?next?from?getindex_curs?into?@holdtable
?while?@@fetch_status=0
?begin
???select?@dynamic=''update?statistics?''+@holdtable
???select?@message=''updating''+@holdtable
???exec(@dynamic)
??print?@message
??fetch?next?from?getindex_curs?into?@holdtable
end
??close?getindex_curs
Copyright?(C)?2003?Cameron?Michelis?copying?and?redistribution?of?this?file?is?permitted?provided?
this?notice?and?the?above?comments?are?preserved.
*/
Set?quoted_identifier?off
use?master
DECLARE?@fillfactor?varchar(2)
DECLARE?@tablename?varchar(30)
DECLARE?@tablename_header?varchar(75)
DECLARE?@dataname?varchar(30)
DECLARE?@dataname_header?varchar(75)
DECLARE?datanames_cursor?CURSOR?FOR?SELECT?name?FROM?sysdatabases
????????WHERE?name?not?in?('master',?'pubs',?'tempdb',?'model',?'northwind')
/*?Variable?Initialization?*/
?select?@fillfactor?=?"0"?--?Set?Fill?factor?here
?????--?Note?"0"?will?use?original?fillfactor.
/*?End?Variable?Initialization?*/
OPEN?datanames_cursor
??FETCH?NEXT?FROM?datanames_cursor?INTO?@dataname
??WHILE?(@@fetch_status?<>?-1)
????BEGIN
??????IF?(@@fetch_status?=?-2)
????????BEGIN
??FETCH?NEXT?FROM?datanames_cursor?INTO?@dataname
???????????CONTINUE
????????END
?SELECT?@dataname_header?=?"Database?"?+?RTRIM(UPPER(@dataname))
???????PRINT?"?"
?PRINT?@dataname_header
???????PRINT?"?"
?EXEC?("USE?"?+?@dataname?+?"?DECLARE?tnames_cursor?CURSOR?FOR?SELECT?name?from?sysobjects?where?type?=?'U'")
?Select?@dataname_header?=?RTRIM(UPPER(@dataname))
?Exec?("Use?"?+?@dataname)?
?OPEN?tnames_cursor
??FETCH?NEXT?FROM?tnames_cursor?INTO?@tablename
??WHILE?(@@fetch_status?<>?-1)
??????????BEGIN
?????????????IF?(@@fetch_status?=?-2)????????????
????BEGIN
??????????????????FETCH?NEXT?FROM?tnames_cursor?INTO?@tablename
??????????????????CONTINUE
???????????????END
????????SELECT?@tablename_header?=?"??Updating?"?+?RTRIM(UPPER(@tablename))
????PRINT?""
?????????????PRINT?@tablename_header
????EXEC?("USE?"?+?@dataname?+?"?DBCC?DBREINDEX?("?+?@tablename?+?","?+?"''"?+?","?+?@fillfactor?+?")")
????EXEC?("USE?"?+?@dataname?+?"?UPDATE?STATISTICS?"?+?@tablename)
????FETCH?NEXT?FROM?tnames_cursor?INTO?@tablename
??????????END
?DEALLOCATE?tnames_cursor
???????FETCH?NEXT?FROM?datanames_cursor?INTO?@dataname
??????END
DEALLOCATE?datanames_cursor
PRINT?""
PRINT?"?"
PRINT?"Indexing?complete?for?All?User?Databases"
SET QUOTED_IDENTIFIER OFF
/* Start with master DB */
USE master
/* Create Variables */
DECLARE @DBName CHAR(64)
DECLARE @TableName CHAR(64)
DECLARE @FQTableName CHAR(64)
DECLARE @TempVar CHAR(256)
/* Create DB List */
DECLARE DBCursor CURSOR FOR
SELECT name
FROM master..sysdatabases
OPEN DBCursor
FETCH NEXT
FROM DBCursor
INTO @DBName
/* Create Database Loop */
WHILE @@FETCH_STATUS = 0
BEGIN
/* Retrieve Table List */
PRINT 'Retrieving Table List for DB ' + @DBName
EXEC ('SELECT name AS TableName INTO ##TableNames FROM [' + @DBName + ']..sysobjects WHERE type = ''U''')
/* Open Table List */
DECLARE TableCursor CURSOR FOR
SELECT TableName
FROM ##TableNames
OPEN TableCursor
FETCH NEXT
FROM TableCursor
INTO @TableName
/* Create Table Loop */
WHILE @@FETCH_STATUS = 0
BEGIN
/* Add DB Name to Table Name */
SELECT @FQTableName = QUOTENAME(RTRIM(@DBName)) + '..' + QUOTENAME(RTRIM(@TableName))
SELECT @TableName = RTRIM(@DBName) + '..' + RTRIM(@TableName)
/* ReIndex Table */
PRINT 'ReIndexing Table ' + @TableName
DBCC DBREINDEX(@TableName)
/* Update Statics on Table */
PRINT 'Updating Statistics on Table ' + @TableName
EXEC ('UPDATE STATISTICS ' + @FQTableName)
/* Get Next Table Name */
FETCH NEXT
FROM TableCursor
INTO @TableName
END
/* Close Table Cursor */
CLOSE TableCursor
DEALLOCATE TableCursor
/* Remove Tempory Table */
DROP TABLE ##TableNames
/* Preform DB Checks */
PRINT 'Preforming DB Checks on ' + @DBName
DBCC CHECKDB (@DBName)
/* Get Next Table Name */
FETCH NEXT
FROM DBCursor
INTO @DBName
END
/* Close DB Curosor */
CLOSE DBCursor
DEALLOCATE DBCursor
/* Finished */