بهینه سازی منابع SQL Server Resource Governor

مدیریت منابع SQL Server با Resource Governor: بهینه‌سازی عملکرد پایگاه داده

Resource Governor (RG) قابلیتی در SQL Server است که امکان مدیریت بار کاری و مصرف منابع سیستمی SQL Server را فراهم می‌کند. این ابزار به تضمین عملکرد ثابت برای بارهای کاری مختلف کمک می‌کند؛ با محدود کردن میزان CPU، ورودی/خروجی فیزیکی (I/O) و حافظه‌ای که سشن‌ها می‌توانند استفاده کنند.

استفاده از Resource Governor برای جلوگیری از مصرف بی‌رویه منابع توسط یک بار کاری خاص و اطمینان از دسترسی بارهای کاری حیاتی به منابع کافی، بسیار مفید است. این قابلیت به پیش‌بینی‌پذیری عملکرد کمک کرده و امکان نظارت بر مصرف منابع برای هر بار کاری را فراهم می‌آورد.

اجزای اصلی Resource Governor

Resource Governor از سه جزء اصلی برای سازماندهی و مدیریت منابع استفاده می‌کند:

1. Resource Pools (گروه‌های منابع):
این‌ها نمایانگر زیرمجموعه‌ای از منابع فیزیکی سرور مانند CPU، حافظه و I/O دیسک هستند.
گروه داخلی (internal pool): برای فرآیندهای سیستمی SQL Server رزرو شده است.
گروه پیش‌فرض (default pool): برای تمام درخواست‌هایی که به طور صریح به گروه دیگری اختصاص داده نشده‌اند.
گروه‌های تعریف‌شده توسط کاربر: توسط مدیر سیستم برای مدیریت بارهای کاری خاص ایجاد می‌شوند.

2. Workload Groups (گروه‌های بار کاری):
این‌ها مجموعه‌ای از سشن‌ها را نشان می‌دهند که دارای معیارهای طبقه‌بندی مشابهی هستند.
گروه داخلی (internal group): برای فرآیندهای سیستمی SQL Server.
گروه پیش‌فرض (default group): برای تمام درخواست‌هایی که به طور صریح به گروه دیگری اختصاص داده نشده‌اند.
گروه‌های تعریف‌شده توسط کاربر: این گروه‌ها توسط مدیر ایجاد شده و به یک Resource Pool اختصاص داده می‌شوند.

3. Classifier Function (تابع طبقه‌بندی‌کننده):
یک تابع تعریف‌شده توسط کاربر (UDF) است که سشن‌های ورودی را ارزیابی کرده و آن‌ها را به یک گروه بار کاری خاص اختصاص می‌دهد. این تابع برای هر سشن یا اتصال جدید اجرا می‌شود و بر اساس منطق پیاده‌سازی شده در آن (مانند نام کاربری، نام برنامه، آدرس IP)، سشن را به گروه مناسب هدایت می‌کند.

نحوه عملکرد Resource Governor

هنگامی که یک سشن جدید متصل می‌شود، تابع طبقه‌بندی‌کننده فراخوانی می‌شود. بر اساس منطق تعریف‌شده در تابع (مثلاً نام کاربری، نام برنامه، یا آدرس IP)، سشن به یک Workload Group خاص اختصاص داده می‌شود. این Workload Group سپس به یک Resource Pool متصل است که محدودیت‌های منابع را برای آن سشن تعیین می‌کند. این فرآیند تضمین می‌کند که هر سشن بر اساس قواعد تعیین‌شده، از منابع استفاده کند.

پیکربندی Resource Governor

برای راه‌اندازی Resource Governor در SQL Server، مراحل زیر را دنبال کنید:

مرحله 1: فعال‌سازی Resource Governor
ابتدا باید Resource Governor را فعال کنید تا تغییرات پیکربندی اعمال شوند. این کار با دستور `ALTER RESOURCE GOVERNOR` و گزینه `RECONFIGURE` انجام می‌شود.


ALTER RESOURCE GOVERNOR RECONFIGURE;


مرحله 2: ایجاد Resource Pool (گروه منابع)
این دستور یک Resource Pool جدید ایجاد می‌کند و پارامترهای مربوط به حداقل و حداکثر مصرف CPU و حافظه را تنظیم می‌کند. پارامترهایی مانند `CAP_CPU_PERCENT` و `AFFINITY CPU` از SQL Server 2012 به بعد و `MIN_IOPS_PER_VOLUME` و `MAX_IOPS_PER_VOLUME` از SQL Server 2014 به بعد در دسترس هستند.


CREATE RESOURCE POOL Pool1
WITH (
MIN_CPU_PERCENT = 0,
MAX_CPU_PERCENT = 50,
CAP_CPU_PERCENT = 75,
MIN_MEMORY_PERCENT = 10,
MAX_MEMORY_PERCENT = 20,
AFFINITY CPU = (0 TO 7),
MIN_IOPS_PER_VOLUME = 0,
MAX_IOPS_PER_VOLUME = 100
);


`MIN_CPU_PERCENT`: حداقل تضمین‌شده CPU برای درخواست‌ها در این گروه منابع.
`MAX_CPU_PERCENT`: حداکثر CPU که درخواست‌ها در این گروه می‌توانند استفاده کنند.
`CAP_CPU_PERCENT`: (از SQL Server 2012 به بعد) حداکثر میزان CPU که این گروه می‌تواند به صورت سخت مصرف کند.
`MIN_MEMORY_PERCENT`: حداقل حافظه تضمین‌شده برای درخواست‌ها.
`MAX_MEMORY_PERCENT`: حداکثر حافظه که درخواست‌ها می‌توانند استفاده کنند.
`AFFINITY CPU`: (از SQL Server 2012 به بعد) این گروه منابع را به پردازنده‌های خاصی متصل می‌کند.
`MIN_IOPS_PER_VOLUME`: (از SQL Server 2014 به بعد) حداقل تعداد عملیات ورودی/خروجی در ثانیه که برای این گروه تضمین شده است.
`MAX_IOPS_PER_VOLUME`: (از SQL Server 2014 به بعد) حداکثر تعداد عملیات ورودی/خروجی در ثانیه که این گروه می‌تواند انجام دهد.

مرحله 3: ایجاد Workload Group (گروه بار کاری)
این دستور یک Workload Group جدید ایجاد کرده و آن را به یک Resource Pool (در اینجا `Pool1`) اختصاص می‌دهد. شما می‌توانید پارامترهایی مانند حداکثر درخواست‌های همزمان، اهمیت بار کاری و حداکثر حافظه تخصیص‌یافته به درخواست‌ها را تنظیم کنید.


CREATE WORKLOAD GROUP Group1
WITH (
GROUP_MAX_REQUESTS = 100,
IMPORTANCE = HIGH,
REQUEST_MAX_MEMORY_GRANT_PERCENT = 25,
REQUEST_MAX_CPU_TIME_SEC = 0,
REQUEST_MEMORY_GRANT_TIMEOUT_SEC = 0,
MAX_DOP = 0
)
USING Pool1;


`GROUP_MAX_REQUESTS`: حداکثر تعداد درخواست‌های همزمانی که در این گروه می‌توانند اجرا شوند.
`IMPORTANCE`: اهمیت نسبی یک درخواست در این گروه (LOW, MEDIUM, HIGH – پیش‌فرض MEDIUM).
`REQUEST_MAX_MEMORY_GRANT_PERCENT`: حداکثر درصدی از حافظه گروه منابع که یک درخواست می‌تواند از آن استفاده کند.
`REQUEST_MAX_CPU_TIME_SEC`: حداکثر زمان CPU که یک درخواست می‌تواند استفاده کند (0 به معنی نامحدود).
`REQUEST_MEMORY_GRANT_TIMEOUT_SEC`: حداکثر زمانی که یک درخواست منتظر تخصیص حافظه می‌ماند (0 به معنی نامحدود).
`MAX_DOP`: (از SQL Server 2014 به بعد) حداکثر درجه موازی‌سازی برای درخواست‌های درون این گروه.

مرحله 4: ایجاد یک Classifier Function (تابع طبقه‌بندی‌کننده)
این تابع T-SQL scalar UDF مسئول ارزیابی سشن‌های ورودی و تخصیص آن‌ها به Workload Group مناسب است. در این مثال، سشن‌ها بر اساس نام برنامه (`APP_NAME()`) یا نام کاربری (`SUSER_SNAME()`) به گروه‌های مختلف اختصاص داده می‌شوند.


CREATE FUNCTION dbo.rgClassifier()
RETURNS SYSNAME
WITH SCHEMABINDING
AS
BEGIN
DECLARE @grpName SYSNAME;
SET @grpName = 'default'; -- گروه پیش‌فرض در صورت عدم تطابق

IF (APP_NAME() = ‘Reporting Application’)
BEGIN
SET @grpName = ‘ReportingGroup’; — تخصیص به گروه گزارش‌گیری
END
ELSE IF (SUSER_SNAME() = ‘ETL_Login’)
BEGIN
SET @grpName = ‘ETLGroup’; — تخصیص به گروه ETL
END
— در اینجا می‌توانید منطق پیچیده‌تری را اضافه کنید
RETURN @grpName;
END;

مرحله 5: اعمال Classifier Function
پس از ایجاد تابع طبقه‌بندی‌کننده، باید آن را به Resource Governor متصل کرده و تغییرات را دوباره پیکربندی کنید تا فعال شود.


ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.rgClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;


نظارت بر Resource Governor

برای نظارت بر عملکرد Resource Governor و بررسی مصرف منابع، می‌توانید از DMV (Dynamic Management Views)های زیر استفاده کنید:

`sys.dm_resource_governor_resource_pools`: اطلاعات مربوط به Resource Poolها را نمایش می‌دهد.
`sys.dm_resource_governor_workload_groups`: اطلاعات مربوط به Workload Groupها را نمایش می‌دهد.
`sys.dm_resource_governor_configuration`: پیکربندی فعلی Resource Governor را نشان می‌دهد.

همچنین، Performance Monitor (perfmon) و سایر ابزارهای نظارتی SQL Server نیز می‌توانند داده‌های مفیدی در مورد مصرف منابع تحت Resource Governor ارائه دهند.

حذف اشیاء Resource Governor

اگر نیاز به حذف Resource Poolها یا Workload Groupهای تعریف‌شده دارید، باید آن‌ها را به ترتیب صحیح حذف کنید:

1. حذف تابع طبقه‌بندی‌کننده: ابتدا تابع طبقه‌بندی‌کننده را غیرفعال کنید.


ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);


2. حذف Workload Groupها: سپس Workload Groupهایی که ایجاد کرده‌اید را حذف کنید.


DROP WORKLOAD GROUP Group1;


3. حذف Resource Poolها: در نهایت، Resource Poolها را حذف کنید.


DROP RESOURCE POOL Pool1;


4. اعمال تغییرات: برای اعمال تغییرات، Resource Governor را دوباره پیکربندی کنید.


ALTER RESOURCE GOVERNOR RECONFIGURE;


با استفاده از Resource Governor، شما می‌توانید به طور موثر منابع سرور SQL Server خود را مدیریت کرده، عملکرد بارهای کاری مهم را تضمین کرده و پایداری کلی سیستم را افزایش دهید. این ابزار بهینه‌سازی عملکرد پایگاه داده را به یک واقعیت قابل کنترل تبدیل می‌کند.