נהל את סביבתך עם Resource Governor ב – SQL SERVER 2014

ה-Resource Governor נועד לעזור לנו לנהל ולפקח על צריכת המשאבים הפיזיים של ה-SQL Server בין בקשות האפליקציות השונות המתקבלות. הגישה בשימוש ב – Resource Governor היא לזהות את אותן אפליקציות שעלולות לחנוק את שרת ה – SQL Server ולשייך אותם לסביבת עבודה משלהם בעלת משאבים מוגבלים, בצורה הזאת נוכל למנוע עומס יתר על ה -SQL Server.

ה-Resource Governor הינו פיצ'ר שקיים מאז גרסת – SQL Server 2008 בגרסאות Enterprise ,Developer ו-Evaluation בלבד.

איך ה-Resource Governor עובד?

כדי להבין את המנגנון, נגדיר כמה מונחים בסיסים ב -Resource Governor :

Resource Pool

ה – Resource Pool הינו אוסף של משאבים פיזיים בו מגדירים ספי מינימום ומקסימום לכל משאב – לדוגמה CPU, זיכרון ו –IO  (הגבלת IO קיימת רק מגרסת SQL Server 2014 ועליו יורחב בהמשך).

ב – SQL Server ניתן להגדיר עד כ – 18 Pools וקיימים עודPools  2 פנימיים:

• Internal– משמש את מנוע SQL Server בלבד ואינו ניתן לקינפוג.
• Default– משמש את כל שלא הוגדר להם Resource Pool ספציפי.

נציין שה – DBA יכול ליצור בעצמו Resource Pools נוספים ולקנפג ספים לכל משאב.

Workload Group

על מנת שלא נצטרך לשייך כל Resource Pool למשתמש ספציפי, ה – Workload נועד להקל עלינו בניהול סביבות העבודה השונות כאשר כל  Resource Pool יכול להכיל Workload Group אחד ומעלה.

בדומה ל – Resource Pool, גם כאן קיימים שני Workload Group פנימיים:

• Internal– מוקצה ל – Resource Pool פנימי המשמש את מנוע ה – SQL Server.
• Default– מטפל בכל מי שלא סווג ל – Workload Group ספציפי.

Classifier Function

ה -Classifier Function  הוא החוט המקשר בין המשמש הפונה ל – Workload Group. מדובר בפונקציה רגילה UDF (User Defined Function) אשר מסווגת כל פניה לשרת על פי שם אפליקציה, שם משתמש, IP וכדומה.

יש להגדיר ל -Resource Governor  באיזו Classifier Function להשתמש.

איך הכול מתחבר?

ה -Resource Governor  מזהה כל פנייה המגיע מאפליקציה ומשייך אותה ל -Workload Group  המתאים ע"פ מה שהוגדר ב – Classifier Function ( לפי שם משתמש, שם אפליקציה, IP וכדומה(.

מה חדש בגרסת SQL Server 2014

כאשר השתמשנו ב -Resource Governor  בגרסת SQL Server  2008 הייתה לנו רק את היכולת לנהל את משאבי CPU וזיכרון בלבד. בגרסת SQL Server  2014 הורחבה היכולת לשליטה  גם בכמות IOPS (IO Per Seconds) שיוקצו לסביבת עבודה, בכך נוכל לנהל ולפקח על צריכת שימוש ב – Storage מצד חלק מהצרכנים.

בנוסף, הגבלת IOPS יכולה להיות שימושית עבור פעולות אדמיניסטרציה ותחזוקה המתבצעות ע"י DBA וצורכות המון IO (כמו למשל סריקת טבלה, גיבויים, תחזוקת בסיס נתונים וכן הלאה .(ניקח לדוגמא סביבת ייצור שבה נדרשת זמינות גבוהה 24/7, במקרה כזה נוכל להתאים לפעילות כמו בניית אינדקס סביבת עבודה בעלת משאבים מוגבלים על מנת לא להשפיע על התפקוד השוטף של המערכת.

כיצד להשתמש ב -Resource Governor

להלן סקריפט ליצירה וקינפוג Resource Governor:

-- The code below establishes a Resource Pool that restricts I/O, 
-- We use a very high value for MAX_IOPS_PER_VOLUME so that we are:
USE master; 
GO
Create RESOURCE POOL IOPool WITH
(
       MAX_IOPS_PER_VOLUME = 100000,
       MIN_IOPS_PER_VOLUME = 1
);


-- To associate a Resource Group to the Resource Pool I created above I will run this code:

USE master;
GO
CREATE WORKLOAD GROUP IOPool
USING IOPool;

-- In the next step I’m creating the Classifier Function that is needed by Resource Governor.
USE master;
GO
CREATE FUNCTION dbo.LimitIO()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
       DECLARE @GroupName SYSNAME

       IF SUSER_NAME() = 'MyIOUser'
       BEGIN
              SET @GroupName = 'IOPool'
       END
       ELSE
       BEGIN
              SET @GroupName = 'default'
       END
       RETURN @GroupName;
END

-- associate the Classifier Function within Resource Governor
USE master; 
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.LimitIO);

-- Reconfigure Resource Governor
ALTER RESOURCE GOVERNOR RECONFIGURE

GO

בוצע יצירה של אינדקס: ,Resource Governor לאחר קינפוג ה –

-- create NONCLUSTERED index on OrderStatus column
use [MyIOTestDB]
go
CREATE NONCLUSTERED INDEX idx_status ON Orders(OrderStatus)
GO

מדידת ביצועי IO באמצעות Performance Monitor :

בוצע הרצה של Rebuild Index על טבלה המכילה 10 מיליון רשומות (המשתמש (MyIOUser ביצע את ההרצה ):

-- rebuild index to measure iops
use [MyIOTestDB]
go
ALTER INDEX idx_status ON Orders REBUILD
GO

הופעל Performance Monitor במהלך הריצה שמדד את ה – counter Disk Read/write IO/sec ולהלן התוצאות:

לפי הגרף, ניתן לזהות כמות IOPS גדולה מאוד, וכי על ה –Storage  הופעל עומס בזמן זה.

לאחר מכן , בוצע קינפוג ספים מחדש של ה – Resource Pool:

USE master; 
GO
alter RESOURCE POOL IOPool WITH
(
       MAX_IOPS_PER_VOLUME = 10,
       MIN_IOPS_PER_VOLUME = 1
);

ALTER RESOURCE GOVERNOR RECONFIGURE
GO

בוצע ניטור נוסף ב – Performance Monitor עבור אותה פעולת Index Rebuild ולהלן התוצאות:

Resource Governor הינו פיצ'ר שקיים מ – SQL Server 2008

כפי שניתן לראות, ה – Resource Governor אכן הגביל את כמות ה – IOPS ל – 10 בלבד, וה – Storage  לא עמד בפני שום עומס.

מניסיוני האישי ממליץ מאוד להשתמש ב -Resource Governor , המטרה היא לזהות מערכות העלולות להעמיס על ה -SQL Server  ולשייך אותן לסביבת עבודה בעלת משאבים מוגבלים כך שלא יושפעו מערכות נוספות. בארגוני הפיצ'ר נמצא חיוני מאוד בניהול ב -SQL Server  מרכזי המכיל מאות בסיסי נתונים ומשרת מערכות רבות.

The following two tabs change content below.

• Bio
• Latest Posts

אביעד כהן

DBA אפליקטיבי ותשתיתי של SQL Server, Oracle, MySQL בעל ניסיון של למעלה מ – 5 שנים. מתמחה בשיפור ביצועים וכוונון שאילתות, שרידות, יתירות, רפלקציה, Mirroring, Spatial, T - SQL, PowerShell ו – Business Intelligence (SSIS, SSRS, SSAS).

Latest posts by אביעד כהן (see all)

• איך לבצע Sizing DB ב-Datacenter בארגונך בקלות (חלק א') - 15/07/2015
• נהל את סביבתך עם Resource Governor ב – SQL SERVER 2014 - 09/11/2014