SQLServer2012中的资源管理员.docx

/sundae_mengSQL Server 2012 中的資源管理員SQL Server 技術文件作者：Guy Bowerman技術審稿者：Jim van de Erve、Lindsey Allen (ZHU)、Madhan Arumugam Ramakrishnan、XinJin發行日期：2012 年 7 月適用於：SQL Server 2012摘要：SQL Server 2012 提供資源管理員的增強功能，因應集中管理資料庫服務不斷成長的需求，針對需要隔離工作負載的客戶提供多組織用戶共享。本文件將說明這些增強功能，以及新增這些功能的原因。其中包括獨立且詳盡的逐步解說，方便您熟悉新功能。目錄簡介4SQL Server 2012 的資源管理員新增功能5資源管理員為什麼變更了？5新功能摘要6工作負載隔離使用案例範例6第 1 部分 初始設定 建立使用者和工作負載7第 2 部分 設定資源管理員7第 3 部分 執行工作負載8第 4 部分 監視9第 5 部分 競爭的工作負載10最佳作法17疑難排解18結論19參考19簡介我們在 Microsoft SQL Server 2008 Enterprise 中首次採用 SQL Server 資源管理員，用意是為服務多重用戶端工作負載的單一 SQL Server 執行個體，提供多組織用戶共享和資源隔離功能。有了資源管理員這項功能後，您即可針對內送要求可使用的記憶體和 CPU 資源數量設定限制。同時為隔離及限制失控查詢，資源管理員更提供了詳細資源追蹤資訊供您做為收費的依據，並確保更精準的預測效能。如需這項功能的詳細資訊，請參閱SQL Server 線上叢書中的資源管理員(/library/bb933866.aspx)。透過 SQL Server 2012，您可以為工作負載提供更完整的 CPU 資源隔離，設定 CPU 使用率的上限以提供更高的可預測性，並且可以掌控更高比例的 SQL Server 記憶體配置。資源集區、工作負載群組和分類SQL Server 資源管理員導入了資源集區的概念，這正是 SQL Server 執行個體中資源隔離的基礎實作。透過 Transact-SQL 和其他管理介面 (例如 SMO)，您可以針對資源集區進行控制，並且可以透過 CREATE RESOURCE POOL 和 ALTER RESOURCE POOL 陳述式來指派最小及最大的 CPU 和記憶體資源。SQL Server 2012 支援高達 62 個使用者可定義的集區，較 SQL Server 2008 中的 18 個大幅增加。另外還有兩個內建資源集區：一個名為 internal，這是為系統工作保留的集區，不可設定。而另一個是使用者可設定的集區，名為 default。依預設，工作負載會在這個集區中執行。每一個使用者資源集區都可與一個或多個工作負載群組建立關聯。所謂的工作負載群組就是代表一個或多個用戶端工作負載的邏輯實體。您可利用由使用者定義的分類函數，將內送工作階段與這些工作負載群組建立關聯。分類函數是在使用者登入後執行，並且可以呼叫系統函數來評估各種登入屬性，例如使用者名稱、工作站名稱、資料庫名稱。下圖顯示這些元件如何搭配運作，合力將內送連接分類到資源集區中。圖 1：資源管理員工作階段分類如需詳細資訊，包括對於 SQL Server 2008 和 SQL Server 2008 R2 中資源管理員功能明確且深入的介紹，請參閱由 Aaron Bertrand 和 Boris Baryshnikov 撰寫的技術白皮書使用資源管理員(/zh-tw/library/ee151608.aspx)。本文將審視在 SQL Server 2012 中導入的新功能，討論新增這些功能的原因，並說明如何使用這些功能。SQL Server 2012 的資源管理員新增功能資源管理員為什麼變更了？科技日新月異，隨著每個插槽的 CPU 核心數目不斷增加，以及大型電腦成本持續降低的趨勢，我們從使用資源管理員來為客戶隔離 SQL 資源的 SQL Server 主控合作夥伴那裡，聽見了類似下列的意見：較新的市售伺服器中，每個 CPU 都有 10 到 12 個核心，由於我們受限於最大集區數，伺服器上的 CPU 資源也跟著受到影響。想要以 SQL Server 為基礎提供具成本效益的資料庫服務給客戶，主機服務提供者需要更大的彈性，以便將大型電腦的可用資源分配給工作負載，並盡量減少工作負載之間的問題。工作負載彼此競爭資源時會產生問題；某個工作負載對另一個工作負載的可用資源有負面影響時也會產生問題，這就是所謂的吵鬧的鄰居。隨著電腦和虛擬軟體的功能愈來愈強大，私人雲端中要虛擬化及管理的 SQL Server 執行個體數目和大小也與日俱增。因此，為共用資源的使用者提供更有效的隔離措施，變得愈發重要。工作負載需要在 SQL Server 執行個體的分割資源上執行，不能受到吵鬧的鄰居問題影響。我們也發現了另一個趨勢，那就是針對資源使用量收費的使用率呈現增加的狀態。針對企業內部及公用主控提供者的內部資源使用進行追蹤和收費的情形變得愈來愈普遍，連帶也促進了為資源取用者提供預測費用的需求。新功能摘要為滿足這些需求，SQL Server 2012 新增了下列功能：- 將資源集區的數目上限從 20 增加到 64，以便分割較大的電腦供更多工作負載使用。- 新增 CAP_CPU_PERCENT 選項至資源集區，以設定 CPU 資源使用量的硬體上限，提供更高的可預測性。- 新增 AFFINITY 選項至資源集區，可讓個別資源集區與一個或多個排程器和 NUMA 節點同質化，促進 CPU 資源的隔離。- 除了單一頁面配置，還提供 SQL Server 記憶體管理員的多頁面配置管理功能。這是源自於 SQL Server 2012 大幅重新設計的成果，記憶體管理員現在變成了頁面配置的中央作業系統介面。如需有關記憶體管理員變更的詳細資訊，請參閱 SQLOS 與雲端基礎結構團隊部落格 (/b/sqlosteam/)。為顯示新資源管理功能的運作方式及其用法，這裡提供一個簡單的示範，顯示運作中的工作負載隔離。工作負載隔離使用案例範例在此案例中，您主控著私人雲端，並負責維護支援銷售和行銷這兩個部門的集中式資料庫服務。這兩個部門在兩個需要大量 CPU 的工作負載之間共用相同的資料庫。為了妥善記錄成本，您追蹤了每個部門使用的資源。然後針對各部門使用的 CPU 資源，向各部門個別收費。對於查詢，這兩個部門都要求獲得可預測且穩定的效能。銷售部門希望其查詢永遠擁有所需的資源，最好是隨時都有 100% 專屬的 CPU 核心可供其使用。但同時，其使用者又必須與其他部門共用資料庫執行個體。此外，行銷部門的查詢優先順序較低。他們仍需可預測的效能，但是同時須兼顧固定的資源預算。您的工作就是在此環境中提供工作負載隔離、可預測的效能，以及可預測的費用。注意：為清楚顯示新功能，此逐步解說會將焦點放在 CPU 資源上，其中不包括管理記憶體 (您也可以透過 SQL Server 資源管理員加以控制)。下列逐步解說可以當做教學課程逐步進行。若要自行嘗試，請利用已安裝 SQL Server 2012 Enterprise、Evaluation 或 Developer，並已設定兩個 CPU 核心的電腦或虛擬機器來進行。第 1 部分 初始設定 建立使用者和工作負載首先，為銷售和行銷工作負載建立使用者登入。(這裡假設您已為此執行個體啟用 SQL Server 混合模式驗證，以做為執行個體屬性。)CREATE LOGIN Sales WITH PASSWORD = UserPwd, CHECK_POLICY = OFFCREATE LOGIN Marketing WITH PASSWORD = UserPwd, CHECK_POLICY = OFFCREATE USER Sales FOR LOGIN Sales;CREATE USER Marketing FOR LOGIN Marketing;GO此示範的第一個部分顯示工作負載彼此間正在爭用 CPU 時間，因此在您開始使用資源管理員之前，請將 SQL Server 與單一 CPU 同質化，這樣所有的工作負載就必須共用單一 CPU。sp_configure show advanced, 1GORECONFIGUREGOsp_configure affinity mask, 1GORECONFIGUREGO第 2 部分 設定資源管理員我們會使用 SQL Server 資源管理員，將銷售和行銷工作負載與不同的資源集區建立關聯性。這樣您就可以利用效能監視器分別追蹤其資源使用量。不過，一開始還沒有進一步的資源管理員設定；也就是說，CPU 資源還不會配置到資源集區，因此使用者無法看出採用或不採用資源管理員有何差別。1. 建立資源集區。CREATE RESOURCE POOL SalesPoolCREATE RESOURCE POOL MarketingPool2. 建立工作負載群組。CREATE WORKLOAD GROUP SalesGroupUSING SalesPoolCREATE WORKLOAD GROUP MarketingGroupUSING MarketingPoolGO3. 建立會在使用者連接時觸發的分類函數。此函數會依據連入的使用者登入 (也就是來自銷售或行銷部門的登入)，在連接與特定的工作負載群組之間建立關聯性。CREATE FUNCTION CLASSIFIER_V1()RETURNS SYSNAME WITH SCHEMABINDINGBEGINDECLARE val varchar(32)SET val = default;IF Sales = SUSER_SNAME()SET val = SalesGroup;ELSE IF Marketing = SUSER_SNAME()SET val = MarketingGroup;RETURN val;ENDGOALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.CLASSIFIER_V1)GO4. 現在啟動資源管理員。ALTER RESOURCE GOVERNOR RECONFIGUREGO第 3 部分 執行工作負載下面的簡單迴圈示範了需要大量 CPU 的工作負載，以清楚說明使用資源管理員的效果。也就是說，這個範例的唯一目的就是讓 CPU 工作。SET NOCOUNT ONDECLARE i INT DECLARE s VARCHAR(100) SET i = 100000000WHILE i 0 BEGIN SELECT s = version; SET i = i - 1; END如果您將此迴圈儲存成名為 workload.sql 的檔案，即可在命令提示字元中啟動銷售工作負載，如下所示。start sqlcmd -S localhost -U Sales -P UserPwd -i workload.sql在此工作負載執行時，SQL Server 的 CPU 使用率會直接達到與 SQL Server 同質化之單一核心的 100%。第 4 部分 監視透過效能監視器，您可以依照集區追蹤資源使用量。相關效能計數器為：SQLServer:Resource Pool Stats-CPU usage您可以使用 加入計數器 對話方塊，為每個集區各新增一個計數器以利追蹤。請注意，在具有兩個 CPU 的電腦上，如果 SQL Server 與單一 CPU 同質化，銷售集區的 CPU 使用量將會達到 50% 的上限 (也就是一個 CPU 的 100%)，如下圖所示。因此，每當您在此逐步解說中看到圖表計數器時，將其加倍計算後才是單一核心的 CPU 使用量百分比。第 5 部分 競爭的工作負載新增其他工作負載來代表行銷部門。start sqlcmd -S localhost -U Marketing -P UserPwd -i workload.sql現在這兩個工作負載呈現競爭狀態，而 CPU 資源在銷售 (綠色) 與行銷 (紅色) 資源集區之間均衡分配。由於銷售查詢具有優先權，所以您可以調整銷售資源集區，並設定 CPU 的最小值，以確保銷售查詢分配到較大比例的 CPU 資源。將銷售集區的值設為 70%。ALTER RESOURCE POOL SalesPoolWITH (MIN_CPU_PERCENT = 70)GOALTER RESOURCE GOVERNOR RECONFIGUREGO現在銷售工作負載顯然獲得較多的 CPU 資源，接近單一 CPU 的 70%，而行銷工作負載則接近 30%。當兩個工作負載像這樣達到單一 CPU 的最大值時，SQL Server 排程器還是很難將工作分成等比例的配量，給予銷售工作負載剛好 70% 或更多的 CPU。本文稍後將討論如何使用排程器相似性的新功能，將工作負載隔離至個別的排程器或排程器群組。不過，為了說明如何使用資源管理員將單一或共用 CPU 上執行的工作負載分開，請假設 CPU 的數目受限，而且這些工作負載必須共用在單一 CPU 上執行的單一排程器。您可以將行銷集區的最大值設為 30%，讓銷售工作負載更接近 70%。當工作負載之間有競爭的情況時，MAX_CPU_PERCENT 值會告訴排程器該如何配置資源。ALTER RESOURCE POOL MarketingPoolWITH (MAX_CPU_PERCENT = 30)GOALTER RESOURCE GOVERNOR RECONFIGUREGO現在 CPU 資源分割更加接近所需的 70/30 比例。但是如果銷售工作負載下降或全部一起停止了，行銷工作負載會怎麼樣？它是否會停留在 30% 的最大值？下圖顯示這類變動的結果。否。MAX_CPU_PERCENT 設定是機會取向的最大值。如果有可用的 CPU 容量，工作負載就會使用 100%。唯有在爭用 CPU 的情況下，最大值才適用。因此如果銷售工作負載關閉，行銷工作負載就會開始使用 100% 的 CPU。在許多情況下，這是需要的，因為我們通常不會希望 CPU 閒置。但是因為在此案例中，行銷部門需要支付資源使用費，而且他們想要掌控精準預測的費用，所以不想使用 30% 以上的核心資源。假設您想要為其他工作負載保留資源，而且一律要將行銷工作負載保持在 30% 或以下。SQLServer 2012 提供了一個新的資源管理員功能，名為 CAP_CPU_PERCENT 資源集區設定。您可以使用這個功能設定 CPU 使用率的硬性上限。在行銷集區上設定 30% 的硬性上限。ALTER RESOURCE POOL MarketingPoolWITH (CAP_CPU_PERCENT=30)GOALTER RESOURCE GOVERNOR RECONFIGUREGO現在行銷集區的 CPU 圖表趨於平緩，不會再超過 30%。行銷部門如願獲得可預測的費用帳單。假設銷售部門需要更強的隔離體驗，並且想要隨時都能使用 100% 的 CPU 資源。他們可以使用 SQL Server 2012 中的另一個新資源管理員功能 - 排程器相似性。若要顯示此功能，請移除您套用於 SQL Server 的相似性遮罩，讓 SQL Server 引擎使用兩部電腦上的 CPU。sp_configure affinity mask, 0GORECONFIGUREGO現在您可以使用新的資源集區 AFFINITY 選項，將每個集區與其本身的排程器同質化。在此案例中，該選項會有效地讓單一 CPU 核心供每個工作負載使用。ALTER RESOURCE POOL SalesPoolWITH (AFFINITY SCHEDULER = (0)GOALTER RESOURCE POOL MarketingPoolWITH (AFFINITY SCHEDULER = (1)GOALTER RESOURCE GOVERNOR RECONFIGUREGO執行這個程式碼之後，工作負載就會排程在不同的 CPU 上。現在，在 CAP 仍存在的情況下，行銷工作負載會繼續使用一個核心的 30%，而銷售工作負載會使用另一個核心的 100%。在排程器相似性存在的情況下，每個資源集區的 CPU 使用率線狀圖現在是平順的狀態，表示這兩個工作負載之間的 CPU 資源隔離完整且一致。現在，就銷售和行銷工作負載而言，它們是在兩部隔離的電腦上執行，如願取得各自所需的資源。此逐步解說顯示透過一個簡單的範例，說明如何使用一些新的 SQL Server 2012 資源管理員功能來提供更有效的資源隔離。最佳作法考量整體應用程式資源耗用量。本文中的示範代表理想化的工作負載，並不需要依賴其他的資源 (例如記憶體和 I/O)。針對記憶體，資源管理員可以設定硬性上限並分割電腦上的資源。MAX_MEMORY_PERCENT 資源集區設定是有效的記憶體使用量硬性上限，而不是機會取向的設定。但緩衝集區對於記憶體管理來說是一項例外，因為緩衝集區是讓各方共用，進而促使整體查詢效能最佳化。資源管理員目前並不管理 I/O，所以您需要使用其他方法將 I/O 的吵鬧鄰居問題減到最低，例如將記憶體大幅擴充、等量配置、隔離 I/O，以及利用有效率的 SAN 來最佳化隨機 I/O。避免在相同的 CPU 上共用混合的工作負載。當工作負載彼此競爭時，排程器比較容易公平地分配資源，並設定類似工作負載的上限。如果您要為非常多變的工作負載管理資源 (例如，高 CPU 與高 I/O 的混合)，請考慮使用排程器相似性來分割這些工作負載，而不要設定 CPU 最大值和上限。在您變更 CPU 組態之後，一律重新設定集區相似性。將資源集區與一組 SQL Server 排程器同質化時，您所預期的是這些排程器會隔離至各自專屬的 CPU。如果電腦關閉且 CPU 遭到移除時，將會發生什麼情況？例如，您的 SQL Server 執行個體在虛擬機器中執行，實際 CPU 使用量很低，因此您重新設定，減少虛擬機器擁有的 CPU。最好的情況是排程器相似性設定失準，因為與資源集區同質化的排程器現在可能指向不同的 CPU。最糟的情況則是排程器指向不存在的 CPU，導致資源管理員失敗，或是相似性設定還原為預設值 自動。因此，當您對 CPU 組態進行變更之後，請一律重新設定排程器相似性設定，並重新啟動資源管理員。遵循其他最佳作法指導方針。如需涵蓋 SQL Server 2008 資源管理員功能的指導方針詳細資訊，請參閱技術白皮書使用資源管理員(/library/ee151608.aspx) 中的最佳作法一節。疑難排解有新的工具可以協助我排解疑難雜症嗎？為了排解資源集區相似性及監視器相似性設定的問題，SQL Server 2012 包含了新的 DMV：sys.dm_resource_governor_resource_pool_affinity資料行名稱資料類型說明Pool_idint資源集區的識別碼。Processor_groupsmallintWindows 邏輯處理器群組的 ID。Scheduler_maskbigint二進位遮罩，表示與集區相關聯的排程器。我收到排程器相似性錯誤。假設您在設定集區的排程器相似性，決定使用 12 到 14 的排程器時，您會看到下列的錯誤：指定排程器 12 到 14 的排程器範圍，至少包含一個不能用於目前執行個體的排程器。可用於此執行個體的排程器最大數為 12。這表示您嘗試將資源集區與不存在的排程器同質化 (例如，您已將組態移至有較少 CPU 的電腦)。這時有一個很好的解決方法，您可以查詢 sys.dm_os_schedulers DMV，藉此查看確切的排程器/CPU 指派情形。我沒有看到 SMO 物件模型中定義的新資源管理員功能。如果您使用 Windows PowerShell 或 C# 來設定使用 SQL Server 管理物件 (SMO) 的資源管理員，則無法使用 SQL Server 2012 中的新功能。新的 SMO 類別預計會在 SQL Server 2012 Service Pack 1 (SP1) 中提供。當我