由於承接的某網站系統的資料庫主機磁碟陣列卡毀損，造成硬碟資料整個毀損，客戶重灌主機作業系統Windows Server 2003 Standard後，自行決定將原本使用的SQLServer 2000 Standard版本換成SQLServer 2005 Standard版本，在還原備份資料後，系統運行期間，前端使用者反應系統效能變得比以前還慢，由於系統是使用asp開發的老舊系統，應用程式與資料庫運行在不相同的主機上，應用程式運行在Windows Server 2003 Standard上的IIS上，在程式未修改的狀態下，應用程式主機也沒有更動的狀態下，那問題就出在資料庫主機上，由於資料庫主機只換了張磁碟陣列卡，記憶體未增加且硬碟也未更換，那麼我暫且把效能低落的問題歸責於資料庫的升級。
在網路上查了一下資料，便找到了類似的的問題，由德瑞克前輩於2008-10-05所撰寫的【升級SQL Server，效能變差】一文找到了解答，文中載明當升級資料庫到SQL Server 2005、2008版本後，須執行下列兩項作業：
1.更新統計資料。
2.更新使用方式的記數器。
3.停用【超執行緒，Ht（Hyper-threading】。
4.設定【max degree of parallelism】選項平行處理原則的最大程度。
備註：
3、4、項為德瑞克前輩經過評估分析後所建議執行，請依實際狀況決定是否需要執行，1、2為必須執行項目。
【max degree of parallelism】選項平行處理原則的最大程度用來限制用於執行平行計劃的處理器數目 (最大值為 64)，預設值為 0 會使用所有可以使用的處理器。將 max degree of parallelism 設成 1 可抑制產生平行計劃。將此值設成大於 1 的數字會限制單一平行查詢執行所用的最大處理器數目。如果指定的數值大於可用的處理器數目，就會使用可用處理器的實際數目。如果電腦只有一個處理器，則會忽略 max degree of parallelism 值。
【max degree of parallelism】修改說明如下：
max degree of parallelism 屬於進階選項。若使用 sp_configure 系統預存程序來變更該設定，只有當 show advanced options 設為 1 時，才可以變更 max degree of parallelism。這個設定會立即生效 (不需重新啟動 MSSQLSERVER 服務)。
以下範例會將 max degree of parallelism 選項設為 8。
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'max degree of parallelism', 8;
GO
RECONFIGURE WITH OVERRIDE;
GO
由於沒實際測試過，先取用由客戶端帶回的備份資料庫於家中測試，在VMware中Windows Server 2003 Standard Edition Service Pack2上安裝SQL Server 2005 Standard並安裝Service Pack 2，再將資料庫還原並命名為TestDB，以下是TestDB的資料庫屬性選項項目：
TestDB資料庫屬性選項項目1
TestDB資料庫屬性選項項目2
接著我執行下列四步驟：
1.我先把TestDB的相容性層級由SQL Server 2000（80）改為SQL Server 2005（90），如下圖示：
2.接著再執行德瑞克前輩的T-SQL語法
/*
sp_updatestats
更新統計資料 - 若要協助最佳化查詢效能，我們建議您在升級之後，更新所有資料庫的統計資料。
請使用 sp_updatestats 預存程序來更新 SQL Server 資料庫中使用者定義資料表的統計資料。
針對目前資料庫中的所有使用者自訂資料表和內部資料表來執行 UPDATE STATISTICS。
*/
--01 更新 TestDB 資料庫中之資料表的統計資料。
USE TestDB;
GO
EXEC sp_updatestats
--02 對每一個資料庫執行 sp_updatestats 作業
USE master
GO
EXEC sp_MSforeachdb @command1="print '?' EXEC [?].dbo.sp_updatestats"
/*
DBCC UPDATEUSAGE
更新使用方式計數器 - 在舊版 SQL Server 中，資料表和索引資料列計數與頁面計數的值可能會變成不正確。
若要更正任何無效的資料列或頁面計數，我們建議您在升級後，針對所有資料庫執行 DBCC UPDATEUSAGE。
報告和更正目錄檢視中不準確的頁面和資料列計數。這些不準確可能會使 sp_spaceused 系統預存程序傳回不正確的空間使用方式報表。
在 SQL Server 2005 和更新版本中，永遠會正確維護這些值。從 SQL Server 2000 升級的資料庫可能會包含無效的計數。
我們建議您在升級之後執行 DBCC UPDATEUSAGE，以便更正任何無效的計數。
*/
--01 更新目前資料庫中之所有物件的頁面及 (或) 資料列計數
USE TestDB
GO
DBCC UPDATEUSAGE (0);
GO
--02 對每一個資料庫執行 DBCC UPDATEUSAGE 作業
USE master
GO
EXEC sp_MSforeachdb @command1="print '?' DBCC UPDATEUSAGE (?)"
執行sp_updatestats更新統計資訊的過程圖示
執行sp_updatestats結束訊息圖示
更新使用方式計數器結果圖示
3.接著再參考Will保哥 讓 SQL Server 告訴你有哪些索引應該被重建或重組 一文，執行下列指令，找出索引的碎裂狀態，並將結果儲存保留起來，用來當作比對重建或重組索引後的基準線：
SELECT OBJECT_NAME(dt.object_id) ,
si.name ,
dt.avg_fragmentation_in_percent,
dt.avg_page_space_used_in_percent
FROM
(SELECT object_id ,
index_id ,
avg_fragmentation_in_percent,
avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE index_id <> 0
) AS dt --does not return information about heaps
INNER JOIN sys.indexes si
ON si.object_id = dt.object_id
AND si.index_id = dt.index_id
索引的碎裂狀態
索引重組的時機
• 檢查 External fragmentation 部分
o 當 avg_fragmentation_in_percent 的值介於 10 到 15 之間
• 檢查 Internal fragmentation 部分
o 當 avg_page_space_used_in_percent 的值介於 60 到 75 之間
索引重建的時機
• 檢查 External fragmentation 部分
o 當 avg_fragmentation_in_percent 的值大於 15
• 檢查 Internal fragmentation 部分
o 當 avg_page_space_used_in_percent 的值小於 60
4.再依據Will保哥提供的指令產生重建或是重組索引的指令碼
SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
CASE
WHEN ps.avg_fragmentation_in_percent > 15
THEN 'REBUILD'
ELSE 'REORGANIZE'
END +
CASE
WHEN pc.partition_count > 1
THEN ' PARTITION = ' + CAST(ps.partition_number AS nvarchar(MAX))
ELSE ''
END,
avg_fragmentation_in_percent
FROM sys.indexes AS ix
INNER JOIN sys.tables t
ON t.object_id = ix.object_id
INNER JOIN sys.schemas s
ON t.schema_id = s.schema_id
INNER JOIN
(SELECT object_id ,
index_id ,
avg_fragmentation_in_percent,
partition_number
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)
) ps
ON t.object_id = ps.object_id
AND ix.index_id = ps.index_id
INNER JOIN
(SELECT object_id,
index_id ,
COUNT(DISTINCT partition_number) AS partition_count
FROM sys.partitions
GROUP BY object_id,
index_id
) pc
ON t.object_id = pc.object_id
AND ix.index_id = pc.index_id
WHERE ps.avg_fragmentation_in_percent > 10
AND ix.name IS NOT NULL
重建或是重組索引的指令碼
5.執行步驟4.第一行的指令。
6.重新執行步驟3.，與第一次執行的結果作比較，看有沒有改善，若執行後仍有需要重建或重組的索引，依據保哥的說法解釋如下：
【因為 SQL Server 資料庫的儲存單位是「分頁」(page)，每一個分頁大小為 8060 位元組(bytes)，每一個分頁能儲存的資料列數會依照你的資料大小而定，所以的確有可能會發生 External fragmentation 永遠大於 15% 的情況。】
參考資料：
德瑞克 【升級SQL Server，效能變差】
http://sharedderrick.blogspot.com/2008/10/sqlserver.html
微軟MSDN
如何：升級到 SQL Server 2008 R2 (安裝程式)
http://msdn.microsoft.com/zh-tw/library/ms144267.aspx
Will保哥 讓 SQL Server 告訴你有哪些索引應該被重建或重組
留言列表
軟體開發 (1)
{{ article.title }}