SQL Q&A: Temp Tables, 64-bit Processing, Mirroring, and More

發行項
08/17/2016

SQL 問題與解答暫存資料表、64 位元處理、鏡像及其他資訊

編輯：Nancy Michell

暫存資料表

問：我聽說為了避免爭用問題，最好不要老是使用暫存資料庫 tempdb。這是真的嗎？我會使用這個資料庫是因為我需要存取分散在多個資料表內的資料，甚至隨時加以修改。使用檢視表無法輕易辦到這一點。

答：的確如此，過度使用 tempdb 可能導致爭用問題。但是，正如知識庫文章 support.microsoft.com/kb/328551 所述，爭用通常是在罕見的情況下才會發生的問題，例如同時有數百個 SPID (伺服器處理序識別碼) 爭相建立和刪除大量暫存資料表的情況。

實際上，使用暫存資料表 (順便一提，不見得位於 tempdb 中) 是可接受的一種解決方案。關鍵在於與第一時間建立資料表的負擔相比，您從暫存資料表提取資料所獲得的效能提升是否較佔上風。

您必須先考量暫存資料表的大小、使用壽命、使用頻率，特別是作用中執行個體的數目，再決定是否摒棄這種方案。伺服器規模同樣重要，因為 tempdb 爭用可能與硬體有關。

如果您需要大量的小型臨時資料表，可以試著使用資料表變數，在某些方面會比暫存資料表更具優勢 (請參閱下一個問題的解答，以獲取資料表變數和暫存資料表之間差異的詳細介紹，並另請參閱「常見問題集 - SQL Server 2000 - 資料表變數」取得詳細資訊)。

以您的情況為例，如果所有對資料庫的查詢都必須經歷暫存資料表建立程序，則對原始資料模型進行查詢而非單僅試著使用資料表或許也是明智之舉。

問：何謂資料表變數，對效能有何幫助？我真的能用它們來代替暫存資料表嗎？

暫存資料表和資料表變數提供相同的基本功能；事實上，兩者均具體化為 tempdb 資料庫中的暫存資料表。不過，如果只插入少許資料列，資料表變數可提供較優越的效能。這是因為資料表變數不會保存任何統計資料或索引，所以負擔較輕。如果資料表內容不超過資料庫引擎將存留在資料快取中的 8KB 資料庫分頁大小，則使用資料表變數較好。

根據《SQL Server™ 線上叢書》：

資料表變數就像是本機變數。其範圍定義明確，即以對其進行宣告的函數、預存程序或批次指令為範圍。在此範圍內，資料表變數可視同一般資料表使用。
每當定義資料表變數的函數、預存程序或批次指令結束時，即自動清除資料表變數。
與使用暫存資料表相比，在預存程序中使用資料表變數可減少重新編譯預存程序的次數。
使用資料表變數時，需要鎖定與記錄的資源較少。
在某些情況下可改善效能，儘管不會保存統計資料。

鏡像

問：我打算使用 SQL Server 2005 鏡像處理來支援高可用性。系統將在高可用性模式下執行，以支援自動容錯移轉。但我還想執行一些位於鏡像上的其他應用程式，以便充分利用資源。鏡像伺服器將支援兩個資料庫：Production DB 做為主要資料庫的鏡像，及 Staging DB 做為獨立資料庫。另外還要支援兩種用戶端類型：其一設定為鏡像容錯移轉後存取 Production DB，其一則直接連接 Staging DB。

此外，某些 SQL Server Integration Services (SSIS) 封裝將在鏡像伺服器上執行。這些封裝會存取以上兩個資料庫，將本機上的 Staging DB 傳來的資料轉送到在主要伺服器上執行的 Production DB。在容錯移轉期間，SSIS 封裝會將本機 Staging DB 傳來的資料轉送到本機 Production DB (進行容錯移轉)。這種組態是否還有哪些問題需要注意？

答：從一般看法來講，不建議使用這種組態，因為容錯移轉後的新主體可能不堪負荷。

就鏡像的觀點而言，整個組態的功能性似乎沒有問題，但若您要繼續實施此方案，便應徹底進行測試。測試應該在尖峰負荷時段，於容錯移轉後期使用標準組態進行。尖峰負荷應為估算的未來尖峰負荷而非目前的負荷值；您必須考慮到日後的商務需求，否則一旦負荷超過過去的容量，方案將無法運作。

測試後很有可能發現網路、磁碟甚至 CPU 的瓶頸，這些瓶頸具體表現時會是應用程式回應時間遲緩、輸送量不如預期、逾時錯誤，或鏡像伺服器不斷重排佇列 (視實際的重排率而定，可能導致容錯移轉超時太久出乎意料之外)。

此外，在容錯移轉期間執行封裝將造成與故障的主體中斷連線。一旦容錯移轉完成且新主體上的資料庫已可使用，封裝應能再與新主體重新連線才對。當然，其前提為：封裝內建連線重試機制並能正常處理連線錯誤，比方能夠從「已中斷」的連線狀態復原。

SQL Server 與 64 位元作業系統

問：如果我執行 64 位元版本的 Windows Server® 作業系統，就必須使用 SQL Server 2000 Enterprise Edition (64 位元) 而不能使用 32 位元 SQL Server 2000，是嗎？

答：從 SQL Server 2000 的觀點來看，唯一支援的原生 64 位元架構是 Intel 的 ia64。因此，SQL Server 2000 所謂的 64 位元支援是指 ia64。原生 64 位元 ia64 唯一支援的 SQL Server 2000 版本是 Enterprise Edition 版。既沒有像原生 64 位元應用程式一樣在 ia64 上執行的 SQL Server 2000 Standard Edition，也沒有在 ia64 Windows® on Windows (WOW，可執行 32 位元應用程式的作業系統子系統) 下執行的 SQL Server 2000 Standard Edition。

AMD x64 電腦要使用 SQL Server 2000 的話有兩種選擇。您可以執行 32 位元作業系統，在此情況下可執行任何 SQL Server 2000 版本及任何 Service Pack (SP)。或者，您也可以在 WOW 下執行 64 位元作業系統和 SQL Server 2000 (任一版本) SP4。執行於第二種環境的 SQL Server 2000 並非原生 64 位元應用程式，而是以 32 位元模式執行於 WOW 下，因此甚至不知道外界是 64 位元環境。

更確切地說，ia64 WOW 和 x64 WOW 是兩種截然不同的子系統。在 ia64 架構下，WOW 必須模擬受限制的虛擬位址空間，並進行實際的電腦指令模擬。ia64 電腦指令與 x86 完全不一樣，所以是截然不同的電腦。SQL Server 所有的元件 (SQL Server 2000 或 SQL Server 2005) 都不支援在 ia64 WOW 下執行。

而在 x64 WOW 下，情況則完全不同。x86 和 x64 電腦的架構非常相近。兩者擁有相同的指令集 (或幾乎相同)，所以只需要模擬 32 位元虛擬位址空間，而硬體更可在這方面提供協助。因此，SQL Server 多數元件都支援 x64 WOW，比方 SQL Server 2000 SP4 及 SQL Server 2005 任一版本。

升級到 SQL Server 2005

問：我想從 SQL Server 2000 升級到 SQL Server 2005。除了 SQL 程式碼的更新外，應用程式還有哪些方面需要更動？例如，若我要使用 Microsoft® .NET Framework 用戶端連接 SQL Server 2005，桌面用戶端是否有必要升級到 Windows XP SP2？

我目前使用 Windows XP SP1 和 Visual Basic® 6.0，以及一些採用 .NET 技術的應用程式。這種情況下是否需要 SQL Server Native Client、.NET Framework 2.0 和 Windows XP SP2？換言之，用戶端升級的整體最低需求為何？此外若我決定採用 SQL Server 2005 鏡像處理，用戶端需求是否有所不同？

如果想要充分發揮鏡像的功能，您必須使用 SQL Native Client (用於 OleDb 或 ODBC) 或是 ADO.NET 2.0 SqlClient。而且連接字串必須變更為同時參照主體和鏡像。

然而，只要有 SQL Native Client 或 .NET Framework 2.0，用戶端就能識別鏡像。當主體停機時，舊版用戶端雖能連接卻不會自動嘗試鏡像。

您不需要碰觸用戶端，即可在用戶端和伺服器之間附加 BIG-IP 切換，以便於容錯移轉期間手動搬移用戶端。或者，您可以修改應用程式的程式碼，而在發生主體伺服器連線故障時嘗試連接鏡像伺服器。

系統需求如下：Windows Installer 3.0、Microsoft Windows XP SP1 或更新版本、Microsoft Windows 2000 SP4 或更新版本，或是 Microsoft Windows Server 2003。有關各項需求的詳細資訊，請參閱 Using ADO with SQL Native Client (英文)、Updating an Application to SQL Native Client from MDAC (英文) 及 System Requirements for SQL Native Client (英文)。

SQL Server 2000 記憶體不足

問：當我用來實際營運的 SQL Server 2000 Enterprise Edition SP4 在 Windows Server 2003 SP1 上執行時，發生了異常的「記憶體不足」例外狀況。事件日誌中記錄著如 [圖 1] 的錯誤。

Figure 1 「記憶體不足」錯誤

2006-06-23 14:41:40.72 spid77    WARNING:  Failed to reserve contiguous memory of Size= 24641536.
2006-06-23 14:41:40.85 spid77    Buffer Distribution:  Stolen=4800 Free=1744 Procedures=39391
                                Inram=0 Dirty=90621 Kept=0
                                I/O=0, Latched=99, Other=3063345
2006-06-23 14:41:40.85 spid77    Buffer Counts:  Commited=3200000 Target=3200000 Hashed=3154065
                                InternalReservation=587 ExternalReservation=0 Min Free=1024 Visible= 173320
2006-06-23 14:41:40.85 spid77    Procedure Cache:  TotalProcs=9555 TotalPages=39391 InUsePages=1031
2006-06-23 14:41:40.85 spid77    Dynamic Memory Manager:  Stolen=44191 OS Reserved=7648 
                                OS Committed=7619
                                OS In Use=7603
                                Query Plan=39088 Optimizer=0
                                General=4183
                                Utilities=140 Connection=7651
2006-06-23 14:41:40.85 spid77    Global Memory Objects:  Resource=1617 Locks=313 
                                SQLCache=1836 Replication=204
                                LockBytes=2 ServerGlobal=42
                                Xact=63
2006-06-23 14:41:40.85 spid77    Query Memory Manager:  Grants=0 Waiting=0 Maximum=94533 Available=94533
2006-06-23 14:41:40.88 spid77    Error: 17803, Severity: 20, State: 12
2006-06-23 14:41:40.88 spid77    Insufficient memory available..

我的伺服器裝有很多 RAM (32GB)。奇怪的是，這個問題似乎不定時發生；導致此錯誤的預存程序大概每執行 20 次就會發生一兩次異常。

伺服器的效能計數器指出可用的記憶體還有很多。延遲寫入器對磁碟進行分頁的速度不夠快嗎？處理序為何會在執行預存程序時要求那麼多的記憶體？是 Select 陳述式的數目有問題，還是因為使用了暫存資料表？

這個問題與電腦的實體記憶體數量絲毫無關。依您的 boot.ini 設定情形，應用程式大概會配置到 2 至 3GB 的虛擬位址空間。虛擬位址空間是 32 位元系統的寶貴資源。根據預設，緩衝集區將消耗約 384MB 的虛擬位址空間。這 384MB 係配置供執行緒堆疊使用，及供給下列元件配置使用：太大以致緩衝集區無法處理，或不知如何透過緩衝集區進行配置的元件 (協力廠商延伸預存程序、連結的伺服器、COM 元件)。

警告訊息表示約有 23MB 配置失敗。這種數量上的配置成功與否取決於 384MB 區塊內其他配置的數量與位置。

您或可考慮移除一部分載入系統的協力廠商延伸預存程序，以判斷是否透過 sp_oacreate 或連結的伺服器使用了任何 COM 元件。

如果 SQL 預存程序 (sproc) 中使用了 FOR XML 子句，則一旦您加以執行，預存程序很有可能要求如此大量的記憶體 (此處並未刊載說明)。然而，如需詳細資訊，您可以從 Process\sqlservr\Virtual Bytes 查看還有多少虛擬位址空間可用。您也可以使用 VMStat 工具 (附在 Jeffrey Richter 所著 Programming Applications for Microsoft Windows 一書附贈的光碟中) 來判斷最大的虛擬位址區塊大小。延遲寫入器並不涉及此等配置作業；供配置使用的記憶體區域不是位於緩衝集區 (bpool)，對吧？

**衷心感謝以下 Microsoft IT 專業人員不吝於分享技術專業知識：**Ramon Arjona、Stephen Borg、Sandu Chirica、Robert Djabarov、Guillaume Fourrat、Osamu Hirayama、Alejandro Mihanovich、Maxwell Myrick、Uttam Parui、Shashi Ramaka、Gavin Sharpe、Vijay Sirohi、Jimmie Thompson、Madhusudhanan Vadlamaani、Jian Wang 與 Dave Wickert。

共用方式為

其他資源