搜尋本站文章

2008-10-05

升級 SQL Server,效能變差



最近開始陸續有朋友開始進行升級作業,將 SQL Server 7.0、2000版本,升級到 SQL Server 2005、2008 版本上。但卻發覺執行並沒有提昇,甚至變得更差。

一般來講,可能是少作兩項作業所造成的原因。在此提醒各位,將資料庫升級到 SQL Server 2005、2008 版本後,請務必執行以下兩項作業:

(1)更新統計資料 - 若要協助最佳化查詢效能,我們建議您在升級之後,更新所有資料庫的統計資料。請使用 sp_updatestats 預存程序來更新 SQL Server 資料庫中使用者定義資料表的統計資料。
(2)更新使用方式計數器 - 在舊版 SQL Server 中,資料表和索引資料列計數與頁面計數的值可能會變成不正確。若要更正任何無效的資料列或頁面計數,我們建議您在升級後,針對所有資料庫執行 DBCC UPDATEUSAGE。


範例程式如下:

/*
sp_updatestats
更新統計資料 - 若要協助最佳化查詢效能,我們建議您在升級之後,更新所有資料庫的統計資料。
請使用 sp_updatestats 預存程序來更新 SQL Server 資料庫中使用者定義資料表的統計資料。

針對目前資料庫中的所有使用者自訂資料表和內部資料表來執行 UPDATE STATISTICS。
*/
--01 更新 AdventureWorks 資料庫中之資料表的統計資料。
USE Northwind;
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 Northwind
DBCC UPDATEUSAGE (0);
GO

--02 對每一個資料庫執行 DBCC UPDATEUSAGE 作業
USE master
GO
EXEC sp_MSforeachdb @command1="print '?' DBCC UPDATEUSAGE (?)"


站在使用者的角度來看,相信大家都期待 SQL Server 開發團隊能夠更貼心的設計出更好用的升級功能、工具。例如:
1. 自動提醒 IT 人員,必須要更新更新統計資料、更新使用方式計數器等兩項系統資料。
2. 在升級後,提供選項讓 IT 人員,可以選擇立刻更新這兩項系統資料,或是排程更新之。




參考文件:

如何:升級到 SQL Server 2008 (安裝程式)
http://msdn.microsoft.com/zh-tw/library/ms144267.aspx




若是升級 SQL Server後,效能變差,
依據不同的情境整理,請參考以下的建議:


(一)若是為同一台伺服器執行升級作業

建議的作法是:
(1) 執行 sp_updatestats
(2) 執行 DBCC UPDATEUSAGE

(3) 重建索引(Rebuild Index)


(二)若是移轉到新的伺服器上的移轉升級作業

建議的作法是:
(1) 執行 sp_updatestats
(2) 執行 DBCC UPDATEUSAGE

(3) 重建索引(Rebuild Index)

經過評估分析後:
(4) 停用「超執行緒(HT,Hyper-Threading)」。
(5) 設定 max degree of parallelism 選項;「平行處理原則的最大程度」。


參考資料:

認識平行(parallelism)處理,以MAXDOP、cost threshold for parallelism與max degree of parallelism選項為例
http://sharedderrick.blogspot.com/2009/12/parallelismmaxdopcost-threshold-for.html

在 SQL Server 上,是否該停用「超執行緒(HT,Hyper-Threading」?
http://sharedderrick.blogspot.com/2010/11/sql-server-hthyper-threading.html

11 則留言:

  1. Derrick 你好
    我有些關於 SQL 2005 跟SQL 2008 的問題想問..是關於 UPGRADE 的
    公司需要UPGRADE SQL SERVER... 現在有2個選擇 SQL 2005 和SQL2008
    有2個同事都說 SQL 2008 不太STABLE, 但是我覺得SQL 2008 應該會是好一點
    所以希望你給我一些意見
    你認為在公司應該 UPGRADE 2005 還是2008 好一點
    你知不知道那裡可以找到SQL 2008 BUGS 的INFORMATION 和 SP 會何時RELEASE

    希望你可以給我一些意見
    謝謝你

    回覆刪除
  2. 升級到哪一個的考量,會有很多項目的考量,例如:功能面、效能面等等。
    很難一言以蔽之。
    關於您談及的 Stable(是指「穩定度」嗎?)

    關於升級作業造成系統不穩定原因也是很多,分享過往的經驗給您參考,其中有部分來自於人員熟不熟悉升級到新版本上所需要要具備的技能。這不是說要先熟悉SQL Server 2008整個功能後才能升級,而是要知道升級的流程、步驟,升級到新的版本後,有那些功能可以沿用,那些需要改寫,人員是否熟悉所需要使用的新功能等等。否則貿然升級,無論到哪一個版本,都可能釀成災難。

    對於企業的核心系統而言,都請務必在先在測試環境上,執行「完整」的升級測試作業。
    至於您說要 SQL Server 2008 功能修補等等資訊,或許您可以參考
    Microsoft 技術支援服務的網頁
    http://support.microsoft.com/search/

    回覆刪除
  3. Derrick你好,之前上過您的課,有點問題想請教,之前SQL2000查詢sysprocesses 可以用條件撈出是SQL Agent執行的job,不知SQL2005是否有類似的撈法呢,因為我看SQL2005的job顯示在sysprocesses內的是SSMS...不像sql2000是SQLAgent - JobStep...
    主要目的是查出目前的job,吃資源的情形,希望能請教您,謝謝

    回覆刪除
  4. 老師您好
    之前我在恆逸有上過關於HA您所教學的課程,我在實做上有遇到個問題想請教您,就是在做High Availability時,使用資料庫鏡像監視器的時候所出現的問題,
    目前的狀況是已經可以同步備援了,但在做自動容錯轉移時無法使用,我有稍微找過原因,
    發現在資料庫鏡像監視器裡看到鏡像資料庫與見證連接是顯示中斷連接的狀態,
    會是因為這個原因才會導致無法做High Availability嗎??如果是,想請教解決的方法該怎麼執行,謝謝。

    SQL版本為SQL Server 2005 Enterprise X32跟SQL Server 2005 Enterprise ia64,版本相容性已問過微軟,是可相容的。

    回覆刪除
  5. 請您參考看看以下的動態管理函數:

    SELECT * FROM sys.dm_exec_sessions
    WHERE host_name IS NOT NULL

    回覆刪除
  6. 您好,
    筆者這邊僅對於「資料庫鏡像」之「自動容錯移轉」部份作討論:

    自動容錯移轉必須符合下列條件:
    1. 資料庫鏡像工作階段必須在高安全性模式下執行而且必須擁有見證。
    2. 鏡像資料庫必須已經完成同步處理。如此可確保所有傳送到鏡像伺服器的記錄都已寫入磁碟中。

    3. 主體伺服器已經中斷與資料庫鏡像組態其他元件的通訊,但鏡像與見證仍保有仲裁。不過,如果所有的伺服器執行個體都失去通訊,而見證與鏡像伺服器稍後重新取得通訊,則不會發生自動容錯移轉。
    4. 鏡像伺服器已偵測到主體伺服器的遺失。

    所以需要符合上述的要件,將可執行「資料庫鏡像」之「自動容錯移轉」。

    回覆刪除
  7. 老師您好
    關於SQL 2005 實在讓我很百思不得其解
    為何一下 count 指令,會使 CPU 飆高
    造成前端網頁無法顯示

    希望您能給我一些方向及意見,非常謝謝您

    回覆刪除
  8. Derrick你好:
    請問一下如果只是資料從SQL2000還原至SQL2005,SQL2005的環境是新安裝的,那也需要執行sp_updatestats嗎。Thanks.

    回覆刪除
  9. Hi: Jerry,

    是的,一樣建議您執行 sp_updatestats 與 DBCC UPDATEUSAGE。
    若沒有改善效能,建議您 Rebuild Index,謝謝。

    回覆刪除
  10. Derrick您好:
    我們公司的資料庫移轉升級後,在檢查資料庫完整性的時候會出錯,我按照您的文章,做了USE Ptcl
    DBCC UPDATEUSAGE (0);
    GO
    的敘述,晚上的排程,就沒有產生任何報表(連失敗或成功的都沒有),不知道是不是要再加上
    USE Ptcl
    GO
    EXEC sp_MSforeachdb @command1="print '?' DBCC UPDATEUSAGE (?)"
    才會正確執行排程的工作,並產生成功的報告呢?,希望能給予一些意見,謝謝。

    回覆刪除
  11. derrick您好~
    可否請教一問題~
    從2000升級至2008後是否可再倒回2000??
    因為某些原因我需要再倒回2000使用~

    回覆刪除