SQL 問題與解答CPU 瓶頸、還原和移動資料庫...等等

由 Nancy Michell 編輯

問：我的 SQL Server™ CPU 使用量突然暴增，但是我沒有做任何變更，未增加新使用者、沒有硬體發生故障、未建立新資料表。因此到底發生了什麼事？

提示：在建立索引期間的存取

有的時候，當您想要對大型資料表建立索引時 (此舉可能要花費大量的時間)，但是卻希望讓資料在此程序的期間仍可使用。您該如何同時達到這兩個目標呢？

叢集索引在建立、刪除或是重建時，SQL Server 都會在資料表上附加結構描述修改 (SCH-M) 鎖定，此舉可以防止所有使用者在此作業期間，存取基礎的資料。這就是當您對資料表建立叢集索引時的情況。相反的，當您對某資料行建立非叢集索引時，SQL Server 會在資料表上附加共用 (S) 鎖定，此舉雖然也會防止對基礎資料表進行資料更新，但是至少讓您能夠執行 SELECT 陳述式，因此您仍可讀取資料。

在建立叢集索引期間，如果資料表的讀取性是重要的，那麼您可以建立資料表的索引，並使其成為線上作業。以下就是這個命令：

CREATE UNIQUE CLUSTERED INDEX CLUST_IDX_SQLTIPS 
ON SQLTips (tip) with (ONLINE=ON) Go;

對資料表使用線上索引作業時，SQL Server 仍會在基礎資料表上，針對叢集索引放置 SCH-M 鎖定，或是針對非叢集索引放置共用 (S) 鎖定，但是只會進行一段非常短的時間，亦即在索引作業開始和結束的階段。所以在索引建立期間，此選項可以繼續讓使用者查詢和更新基礎資料表。但是請注意，線上索引建立/作業僅適用於 SQL Server 2005 Enterprise Edition。

答：會突然發生出乎預期的 CPU 瓶頸，伺服器上又毫無變更或額外的負荷時，有幾項可能的核心原因，但是其中一些常見的原因包括：未最佳化的查詢規劃、差勁的 SQL Server 設定、不適當的應用程式/資料庫設計，以及硬體資源不足。

在像這樣的情況下，第一件要做的事情就是辨識您的伺服器是否受 CPU 限制，如果是的話，請找出本機 SQL Server 系統上，成為 CPU 最大消耗者的陳述式。您可以使用效能監視器並查看 PROCESSOR:% PROCESSOR TIME 計數器，來判斷伺服器是否受 CPU 限制。如果您發現每個 CPU 所使用的時間值等於或大於百分之 75，就表示發生了 CPU 瓶頸。

您還應該查詢系統上稱為 SYS.DM_OS_SCHEDULERS 的動態管理檢視 (DMV)，以查看可執行工作的數目，來監視 SQL Server 的排程器。非零的值表示工作必須等候其時間片段才能執行；此計數器的值如果太大，也是發生 CPU 瓶頸的徵兆。

您可以使用以下查詢來列出所有排程器，並且查看可執行工作的數目：

SELECT
 Scheduler_ID,
 Current_Tasks_Count,
 Runnable_Tasks_Count
FROM
 SYS.DM_OS_SCHEDULERS
WHERE
 Scheduler_ID < 255

若要取得前 50 項耗用 CPU 資源的 SQL 陳述式，請使用 [圖 1] 中的查詢。

Figure 1 前 50 大 CPU 資源耗用者

SELECT TOP 50 (a.total_worker_time/a.execution_count) AS [Avg_CPU_Time],
 Convert(Varchar,Last_Execution_Time) AS 'Last_execution_Time',
 Total_Physical_Reads,
 SUBSTRING(b.text,a.statement_start_offset/2,
 (case when a.statement_end_offset = -1 then len(convert(nvarchar(max), b.text)) * 2
 else
 a.statement_end_offset end - a.statement_start_offset)/2) AS Query_Text,
 dbname=Upper(db_name(b.dbid)),
 b.objectid AS 'Object_ID'
 FROM sys.dm_exec_query_stats a
 CROSS APPLY
 sys.dm_exec_sql_text(a.sql_handle) AS b
 ORDER BY
 [Avg_CPU_Time] DESC

問：我必須從磁帶復原 SQL Server 2005 的資料庫。在復原之後，使用者對資料庫所擁有的大部分權限都遺失了。可否告訴我在還原的期間，我做錯了什麼？資料都完好，但是權限都亂成一團。

答：最可能的情況是您未及時將主資料庫還原到同一時間點，並且使用者資料庫中的登入識別碼與目前的主資料庫不符。您必須將其同步。「移動資料庫時可從何處獲得協助」資訊看板列出了最有用的資源，以供您解決在還原或移動資料庫時所遭遇到的問題。

問：我開發了一個應用程式，其中的預存程序使用 SQL Server 2005 SP1 中的 OpenXML，將 XML 資料切換成關聯式資料表。XML 文件最大可為 5KB (平均為 2.5KB)。預存程序可供多次平行呼叫 (至多 50 次)。

我遇到非常嚴重的鎖定爭奪問題，我想它們可能是由 OpenXML 造成的。您認為如何？

答：對於在單一執行緒上切斷或轉換資料，雖然 OpenXML 可能比節點方法快速，但是節點方法通常會有更好的擴充性，特別是在平行使用的情況下。然而，如果您一定要使用 OpenXML，就應該遵循以下指引，來改善整體 OpenXML 的效能。

不要用相同的資料列模式呼叫 OpenXML 五次 (正如您在解決方案中所做的一樣)，您應該將具相同資料列模式的所有資料，都擷取到暫存資料表中，然後再從暫存資料表中執行您的選取作業。請嘗試用 sp_xml_removedocument 盡早釋放記憶體。此外，最好還是避免使用萬用字元 (例如 * 和 //)；而且盡可能提供明確的路徑，因為如此可提升查詢的效率。

問：DBCC SHRINKFILE 在我的伺服器上執行得實在很緩慢。請問在多處裡器的電腦上，是否能獲得較佳的效能？我該怎麼做才能改善這種情況？

答：DBCC SHRINKFILE 是單一執行緒的作業，這表示它無法利用多個 CPU。它會把頁面從檔案的後端移動到檔案的前端，一次移動一個頁面。而且壓縮 (正如其名) 並不會執行重組；事實上在許多情況下，壓縮還會造成邏輯的分散。

改善壓縮效能的一些方式，包括在叢集索引中移動頁面。如果您擁有堆積，並且這些堆積具有許多非叢集的索引，速度就可能相當緩慢 (相較於叢集索引的情況)。

另請注意，移動大型物件區塊 (LOB) 資料的頁面會很緩慢，因為必須讀取整個資料列的資料，才能找到 LOB 資料的根節點。

如果索引/資料表的大部分內容位於檔案的結尾，您就可以重建索引，使其移動到檔案的前端。重建索引會利用多個 CPU，並且在 bulk_logged 模式下可能使用比較少的記錄空間。然後您在執行壓縮時，它就會執行得更快。

如需壓縮作業的詳細資訊，請參考位於 blogs.msdn.com/sqlserverstorageengine/archive/2007/03/29/how-does-your-schema-affect-your-shrink-run-time.aspx 和 blogs.msdn.com/sql-serverstorageengine/archive/2007/04/15/how-to-avoid-using-shrink-in-sql-server-2005.aspx 的內容。

移動資料庫時可從何處獲得協助

• 如何在執行 SQL Server 的電腦之間移動資料庫
• 如何使用 SQL Server 中的卸離和附加功能將 SQL Server 資料庫移到新位置
• INF:使用 WITH MOVE 選項搭配 RESTORE 陳述式
• 如何解決在 SQL Server 之間移動資料庫的權限的問題
• INF:如何在 SQL Server 的執行個體之間傳送登入和密碼
• INF:認識並對 SQL Server 2000 中的複製資料庫精靈進行疑難排解
• 還原傾印之後發生使用者登入及 (或) 權限錯誤
• 您無法將系統資料庫備份還原至不同的 SQL Server 組建
• 如何從資料庫備份或交易記錄檔備份在 SQL Server 擷取特定資料表或資料列

提示：變更通訊埠

任何熟悉 SQL Server 的人都知道，SQL Server 接聽的預設通訊埠編號是 1433。雖然 SQL Server 的命名執行個體係設定為使用動態通訊埠 (這表示在 SQL Server 執行個體啟動的時候，都可以選取任何可以使用的通訊埠)，但是預設的 SQL Server 執行個體卻一律接聽通訊埠 1433。因此，如果您的伺服器也是接聽預設的通訊埠，而您未嚴密地保護它的安全，則伺服器有可能暴露在安全性風險中。但是您可以變更預設的通訊埠來對抗攻擊。以下是變更的方式。

開啟 [SQL Server 組態管理員]，並且展開 [SQL Server 2005 網路組態]，然後選取 [通訊協定]。接下來，按兩下 [TCP/IP]。TCP/IP 屬性的清單及其功能顯示於下列圖表中，請按照規定加以設定。

請注意，SQL Server 資料庫引擎可以在同一 IP 位址上接聽多個通訊埠，因此請以半形逗號，如 1433,1500,1501 的格式分隔，來分別列出您要使用的通訊埠。如果您想要設定單一 IP 位址接聽多個通訊埠，還必須在 [TCP/IP 內容] 對話方塊的 [通訊協定] 索引標籤上，將 [全部接聽] 參數設定為 [否]。

接下來用滑鼠右鍵按一下每個位址，再按一下 [內容] 指定您想要設定的 IP 位址。如果 [TCP 動態通訊埠] 對話方塊包含 0，這表示資料庫引擎正在接聽動態通訊埠，請刪除 0。在 [IP 內容] 區域內的 [TCP 通訊埠] 方塊中，輸入您希望此 IP 位址接聽的通訊埠編號，然後按一下 [確定]。在 [主控台] 窗格中，按一下 [SQL Server 2005 服務]，並且在 [詳細資訊] 窗格中，用滑鼠右鍵按一下 [SQL Server (<執行個體名稱>)]，然後按一下 [重新啟動]，以停止並重新啟動 SQL Server。

在將 SQL Server 設定為接聽特定通訊埠之後，共有三種方式可供用戶端連接到此通訊埠。您可以在伺服器上執行 SQL Server 瀏覽器服務，用名稱來連接到資料庫引擎執行個體；您可以在用戶端建立一個別名，指定其通訊埠編號；或是您可以用程式設計的方式，將用戶端設計為利用自訂連接字串來連接。

感謝以下 Microsoft IT 專家回答本月的問題： Chad Boyd、Cindy Gross、John Hadden、Saleem Hakani、Stephen Jiang、Mahesh Nayak、Paul Randal 及 Wayne Yu。

© 2008 Microsoft Corporation and CMP Media, LLC. 保留所有權利；未經允許，嚴禁部分或全部複製.