© Peter A. Carter 2019 — P. A. Carter, Pro SQL Server 2019 Administration — doi.org/10.1007/978-1-4842-5089-1_5

فصل 5 — پیکربندی Instance

نصب و پیکربندی SQL Server با پایان موفقیت‌آمیز Setup تمام نمی‌شود. ملاحظات فراوان دیگری، هم درون Database Engine و هم خارج از آن و با استفاده از ابزارهایی مانند SQL Server Configuration Manager، باید بررسی شوند. در این فصل بسیاری از مهم‌ترین گزینه‌های پیکربندی در سطح Instance، از جمله فناوری Buffer Pool Extension و انتخاب‌های مهم مربوط به System Databaseها بررسی می‌شود. همچنین نحوه پیکربندی Instance برای کار با Firewall را خواهیم دید.

پیکربندی Instance

در سطح Instance تعداد زیادی Setting و Flag قابل تنظیم است. در بخش‌های بعدی مشاهده و تغییر این تنظیمات با ابزارهایی مانند sp_configure، sys.configurations، DBCC TRACEON و ALTER SERVER بررسی می‌شود.

استفاده از sp_configure

بسیاری از تنظیمات سطح Instance را می‌توان با Stored Procedure سیستمی sp_configure مشاهده و تغییر داد. این Procedure در مثال‌های متعدد کتاب استفاده می‌شود. اگر Procedure اولین Statement در Batch باشد، می‌توان آن را بدون EXEC اجرا کرد؛ ولی اگر Statement دیگری قبل از آن وجود داشته باشد، استفاده از EXEC الزامی است. اجرای sp_configure بدون Parameter یک Result Set پنج‌ستونی برمی‌گرداند که معنای ستون‌های آن در جدول 5-1 آمده است.

برای تغییر مقدار یک Setting با sp_configure باید دو Parameter ارسال شود. Parameter اول configname با نوع VARCHAR(35) است و نام Setting را مشخص می‌کند. Parameter دوم configvalue از نوع Integer است و مقدار جدید Setting را تعیین می‌کند. تغییر Setting بلافاصله اعمال نمی‌شود؛ برای فعال‌شدن آن باید Database Engine Service Restart شود یا Instance دوباره Reconfigure شود.

برای Reconfigure دو گزینه وجود دارد: RECONFIGURE و RECONFIGURE WITH OVERRIDE. فرمان RECONFIGURE مقدار در حال اجرا را فقط زمانی تغییر می‌دهد که مقدار جدید از نظر SQL Server «معقول» باشد. مثلاً وقتی Contained Database روی Instance وجود دارد، اجازه غیرفعال‌کردن آن را نمی‌دهد. RECONFIGURE WITH OVERRIDE محدودیت‌های منطقی بیشتری را نادیده می‌گیرد، هرچند همچنان Min/Max مجاز Setting را کنترل می‌کند و عملیاتی که خطای جدی ایجاد کنند، مانند قرار دادن Min Server Memory بالاتر از Max Server Memory، را نمی‌پذیرد.

در SQL Server 2019، نخستین بار که sp_configure را بدون Parameter اجرا می‌کنید 18 ردیف از گزینه‌های پایه Instance نمایش داده می‌شود. یکی از گزینه‌ها Show Advanced Options است. اگر آن را فعال و Instance را Reconfigure کنید، مطابق Listing 5-1 هنگام اجرای Procedure، 52 Setting پیشرفته دیگر نیز نمایش داده می‌شوند. تلاش برای تغییر گزینه پیشرفته قبل از فعال‌کردن Show Advanced Options با خطا مواجه می‌شود.

Listing 5-1 — نمایش Advanced Options

EXEC sp_configure 'show advanced options', 1
RECONFIGURE

همین اطلاعات را می‌توان از View سیستمی sys.configurations نیز دریافت کرد. این View دو ستون اضافی دارد: is_dynamic مشخص می‌کند Setting با RECONFIGURE قابل اعمال است (1) یا Restart لازم دارد (0)؛ و is_Advanced نشان می‌دهد Setting بدون فعال‌کردن Show Advanced Options قابل تنظیم است یا خیر.

پیکربندی Processor و Memory

یکی از اولین ملاحظات پیکربندی Instance، منابع Processor و Memory است. برای Processor دو موضوع اصلی مطرح است: Processor Affinity و MAXDOP یا Maximum Degree of Parallelism.

Processor Affinity

به‌طور پیش‌فرض Instance می‌تواند از همه Coreهای Processor موجود استفاده کند. Processor فیزیکی یا Socket/CPU شامل چند Core است. با Processor Affinity می‌توان Coreهای مشخصی را به Instance اختصاص داد تا فقط همان Coreها قابل استفاده باشند. یکی از دلایل این کار زمانی است که چند Instance روی یک Server اجرا می‌شوند و برای Processor Resource با یکدیگر رقابت می‌کنند. Processor Affinity با Settingی به نام affinity mask کنترل می‌شود.

فرض کنید Server چهار Processor فیزیکی دارد و هرکدام دو Core دارند و Hyper-Threading نیز خاموش است؛ بنابراین هشت Core در اختیار SQL Server است. اگر چهار Instance داشته باشید، می‌توانید Coreهای 0 و 1 را به Instance 1، Coreهای 2 و 3 را به Instance 2، Coreهای 4 و 5 را به Instance 3 و Coreهای 6 و 7 را به Instance 4 اختصاص دهید تا Instanceها برای Resource رقابت نکنند. عیب این روش آن است که اگر یک Instance Idle باشد، Core اختصاص‌داده‌شده به آن بلااستفاده می‌ماند.

اگر Serviceهای دیگری مانند SQL Server Integration Services یا SSIS روی Server اجرا می‌شوند، ممکن است بخواهید یک Core را برای Windows و Applicationهای دیگر آزاد بگذارید. مثلاً اگر Instance 4 مخصوص ETL و SSIS Catalog است و Processor کمتری مصرف می‌کند، می‌توان فقط Core 6 را به آن داد و Core 7 را آزاد گذاشت. این طراحی در Figure 5-1 نشان داده شده است.

Figure 5-1 — نمودار Processor Affinity.

در استفاده از Processor Affinity برای Performance مهم است Coreهای یک Instance روی یک NUMA Node واحد قرار بگیرند. دسترسی Processor به Memory یک NUMA Node دیگر از طریق Interconnect انجام می‌شود و بسیار کندتر از دسترسی به Memory محلی همان Node است. در مثال Figure 5-1 اگر Instance 1 به Coreهای 0 و 7 اختصاص می‌یافت، مرز NUMA نقض و Performance کاهش پیدا می‌کرد.

کاهش Contention با Affinity Mask در Cluster نیز صادق است. در Cluster دو Node به‌صورت Active/Active که هر Node یک Instance دارد، اگر هر Node هشت Core داشته باشد، می‌توان Instance 1 را روی Node 1 به Coreهای 0 تا 3 و Instance 2 را روی Node 2 به Coreهای 4 تا 7 محدود کرد. در Failover نیز Instanceها همان Coreها را مصرف می‌کنند و برای Resource با هم رقابت نخواهند کرد.

دلیل دوم استفاده از Processor Affinity کاهش سربار جابه‌جایی Threadها بین Processorها در سطح Operating System است. در Load سنگین، ثابت‌کردن SQL Server Threadها روی Coreهای مشخص می‌تواند Performance را بهبود دهد و حتی Taskهای عادی SQL Server را از Taskهای IO جدا کند.

برای مثال در Server دارای یک Processor دو Core، می‌توان Taskهای IO مانند Lazy Writer را روی Core 0 و سایر SQL Server Threadها را روی Core 1 قرار داد. برای IO از Setting دیگری به نام Affinity I/O Mask استفاده می‌شود. با فعال‌شدن آن یک Scheduler مخفی فقط برای Lazy Writer ساخته می‌شود. Affinity Mask و Affinity I/O Mask نباید روی یک Core تنظیم شوند، زیرا همان Contentionی ایجاد می‌شود که قصد حذف آن را داریم.

Affinity Mask و Affinity I/O Mask هر دو از GUI در SQL Server Management Studio و از تب Processors در Instance Properties قابل تنظیم هستند.

Figure 5-2 — تب Processors.

Processor Affinity بر اساس Bitmap کار می‌کند. بنابراین برای تنظیم آن با sp_configure باید نمایش Integer مربوط به Bitmap محاسبه شود. به دلیل Signed بودن نوع 32-bit INT بعضی نمایش‌ها منفی هستند. جدول 5-2 مقدار هر Processor را نشان می‌دهد.

روی Server دارای 32 Core تعداد ترکیب‌های ممکن برای Processor Affinity بسیار زیاد است. ادامه جدول 5-2 مقدار Processorهای 16 تا 31 را نشان می‌دهد.

از آنجا که Affinity Mask و نوع Integer حداکثر محدوده 2^32 دارند، برای Serverهای 33 تا 64 Processor باید Affinity64 Mask و Affinity64 I/O Mask نیز تنظیم شوند تا Processorهای اضافه پوشش داده شوند.

همه Settingهای این بخش با sp_configure قابل تنظیم‌اند. Listing 5-2 Instance را به Coreهای 0 تا 3 اختصاص می‌دهد.

Listing 5-2 — تنظیم Processor Affinity

EXEC sp_configure 'affinity mask', 15
RECONFIGURE

حتی با Maskهای 64-bit این روش فقط 64 Core اول را پوشش می‌دهد، در حالی که SQL Server تا 256 Logical Processor را پشتیبانی می‌کند. بنابراین نسخه‌های جدید روش بهتری با ALTER SERVER CONFIGURATION ارائه کرده‌اند. این فرمان می‌تواند Instance را به CPUهای مشخص یا کل Processorهای NUMA Nodeهای مشخص متصل کند و تغییرات آن نیز در sys.configurations منعکس می‌شود.

Listing 5-3 — ALTER SERVER CONFIGURATION

ALTER SERVER CONFIGURATION
    SET PROCESS AFFINITY CPU=0 TO 3
ALTER SERVER CONFIGURATION
    SET PROCESS AFFINITY NUMANODE=0, 4

MAXDOP

MAXDOP حداکثر تعداد Coreهایی را تعیین می‌کند که هر اجرای مستقل Query می‌تواند استفاده کند. شاید در نگاه اول تصور شود Parallelism بیشتر همیشه بهتر است، اما چنین نیست. برخی Queryهای Data Warehouse از Parallelism زیاد سود می‌برند، ولی بسیاری از Workloadهای OLTP با Parallelism کمتر بهتر اجرا می‌شوند. اگر Query روی Threadهای زیاد تقسیم شود و یک Thread بسیار دیرتر تمام شود، سایر Threadها برای Synchronize شدن منتظر می‌مانند و Wait Typeهایی مانند CXPACKET زیاد دیده می‌شود.

در بسیاری از OLTP Systemها انتخاب Parallelism بالا توسط Query Optimizer می‌تواند نشانه Missing/Fragmented Index یا Statistics قدیمی باشد. رفع این مشکلات معمولاً بیش از بالا بردن Degree of Parallelism به Performance کمک می‌کند.

برای Data Warehouse باید MAXDOPهای مختلف Test شود. MAXDOP در Query Level نیز با Query Hint قابل تنظیم است. در اکثر موارد مقدار Instance-level باید کمترین مقدار از این سه باشد:

• 8
• تعداد Coreهای قابل مشاهده برای Instance
• تعداد Coreهای موجود در یک NUMA Node

مقدار پیش‌فرض MAXDOP برابر 0 است؛ یعنی Query فقط به تعداد Coreهای قابل مشاهده توسط Instance محدود می‌شود. در GUI می‌توان گزینه Max Degree of Parallelism را در تب Advanced از Server Properties تنظیم کرد؛ Figure 5-3 مقدار 8 را نشان می‌دهد.

Figure 5-3 — تب Advanced.

MAXDOP از طریق sp_configure نیز قابل تنظیم است.

Listing 5-4 — پیکربندی MAXDOP با sp_configure

EXEC sys.sp_configure 'max degree of parallelism', 8
RECONFIGURE

در کنار کاهش MAXDOP می‌توان Thresholdی را که Query Optimizer در آن Parallel Plan را به Serial Plan ترجیح می‌دهد افزایش داد. مقدار پیش‌فرض Cost Threshold for Parallelism معادل Cost تخمینی 5 است و می‌توان آن را بین 0 و 32767 تنظیم کرد. در بسیاری از محیط‌ها حدود 30 مقدار عملی محسوب می‌شود، اما کاملاً Workload-specific است و باید Test شود. اگر MAXDOP برابر 1 باشد یا فقط یک Core در اختیار Instance باشد، این گزینه نادیده گرفته می‌شود. Listing 5-5 مقدار Threshold را به 10 افزایش می‌دهد.

Listing 5-5 — Cost Threshold for Parallelism

EXEC sp_configure 'cost threshold for parallelism', 10
RECONFIGURE

Min و Max Server Memory

Settingهای Min Server Memory (MB) و Max Server Memory (MB) مقدار Memory در دسترس SQL Server Memory Pool را کنترل می‌کنند. این Pool اجزای مختلفی دارد و برخی از بزرگ‌ترین اجزا در جدول 5-4 معرفی شده‌اند.

در بسیاری از محیط‌ها ممکن است Min و Max Server Memory با یک مقدار تنظیم شوند تا سربار Dynamic Memory Management کاهش یابد. اما در Server دارای چند Instance، Dynamic Management می‌تواند مفید باشد تا Instance با Workload سنگین‌تر در هر لحظه Resource بیشتری مصرف کند. در Active/Active Cluster باید احتیاط بیشتری کرد؛ اگر برای هر Node Memory را مانند Stand-alone تنظیم کنید و Lock Pages in Memory نیز فعال باشد، Failover ممکن است باعث شود Node باقی‌مانده RAM کافی برای همه Instanceها نداشته باشد و Crash کند.

صرف‌نظر از طراحی محیط، همیشه باید Memory کافی برای Operating System باقی بماند.

اگر تنها یک Instance دارید و Application دیگری مانند SSIS روی Server اجرا نمی‌شود، معمولاً Min و Max Memory را روی کمترین مقدار حاصل از دو فرمول زیر قرار می‌دهند:

• RAM - 2GB
• (RAM / 8) * 7

برای چند Instance این مقدار باید بر اساس نیاز بین آن‌ها تقسیم شود. اگر Applicationهای دیگری روی Server هستند، Memory موردنیاز آن‌ها نیز باید به نیاز Operating System اضافه شود. هر دو Setting Min و Max Server Memory از تب Memory در Server Properties قابل تنظیم هستند.

Figure 5-4 — تب Memory.

Min و Max Memory را می‌توان با T-SQL و sp_configure نیز تنظیم کرد. Listing 5-6 دو فرمول قبلی را محاسبه کرده و مقدار مناسب را روی هر دو Setting اعمال می‌کند.

Listing 5-6 — پیکربندی Min و Max Server Memory

DECLARE @MemOption1 INT = (SELECT physical_memory_kb/1024 - 2048 FROM sys.dm_os_sys_info)
DECLARE @MemOption2 INT = (SELECT ((physical_Memory_kb/1024)/8) * 7 FROM sys.dm_os_sys_info)
IF @MemOption1 <= 0
BEGIN
    EXEC sys.sp_configure 'min server memory (MB)', @MemOption2
    EXEC sys.sp_configure 'max server memory (MB)', @MemOption2
    RECONFIGURE
END
ELSE IF @MemOption2 < @MemOption1
BEGIN
    EXEC sys.sp_configure 'min server memory (MB)', @MemOption2
    EXEC sys.sp_configure 'max server memory (MB)', @MemOption2
    RECONFIGURE
END
ELSE
BEGIN
    EXEC sys.sp_configure 'min server memory (MB)', @MemOption1
    EXEC sys.sp_configure 'max server memory (MB)', @MemOption1
    RECONFIGURE
END

Trace Flagها

Trace Flagها Switchهایی در SQL Server هستند که Behavior یا Functionality را روشن و خاموش می‌کنند. می‌توان آن‌ها را در Session Level یا به‌صورت Global با DBCC TRACEON اعمال کرد. همه Trace Flagها Session-level نیستند؛ مثلاً Trace Flag 634 Thread پس‌زمینه مربوط به فشرده‌سازی دوره‌ای Rowgroupهای Columnstore را خاموش می‌کند و ذاتاً Setting مربوط به یک Session خاص نیست.

Listing 5-7 Trace Flag 634 را Global و Trace Flag 1211 را فقط برای Session جاری فعال می‌کند. 1211 Lock Escalation را بر اساس Memory Pressure یا تعداد Lockها غیرفعال می‌کند. سپس DBCC TRACESTATUS وضعیت Flagها را نشان می‌دهد و DBCC TRACEOFF آن‌ها را به حالت Default برمی‌گرداند. Parameter برابر -1 Scope را Global می‌کند.

Listing 5-7 — تنظیم Trace Flag با DBCC TRACEON

DBCC TRACEON(634, -1)
DBCC TRACEON(1211)
DBCC TRACESTATUS
DBCC TRACEOFF(634, -1)
DBCC TRACEOFF(1211)

Figure 5-5 Result مربوط به اجرای Script را، در حالتی که Trace Flag دیگری تغییر نکرده، نشان می‌دهد. DBCC TRACEON و DBCC TRACEOFF Result Set ندارند و فقط Message مربوط به موفقیت یا Error نمایش می‌دهند.

Figure 5-5 — نتایج DBCC TRACESTATUS.

محدودیت DBCC TRACEON حتی در Scope سراسری این است که Settingها موقت‌اند و بعد از Restart باقی نمی‌مانند. برای تغییر دائمی باید Startup Parameter برابر -T را روی SQL Server Service تنظیم کرد.

Startup Parameterها در SQL Server Configuration Manager قابل تنظیم‌اند. با انتخاب Database Engine Service و تب Startup Parameters می‌توان Parameter اضافه یا حذف کرد. Figure 5-6 نمونه تنظیم Trace Flag 809 را نشان می‌دهد؛ اثر این Flag در بخش Hybrid Buffer Pool توضیح داده می‌شود.

Figure 5-6 — Startup Parameters.

اگر Instance روی Windows Server Core باشد یا بخواهید Configuration را Script کنید، PowerShell Listing 5-8 همان نتیجه را می‌دهد. Instance Name و Trace Flag در دو Variable اول مشخص می‌شوند و در صورت نیاز می‌توان آن‌ها را Parameterize کرد. Script ابتدا Registry Path مربوط به Startup Parameterها را پیدا می‌کند، تعداد Argumentهای موجود را می‌شمارد تا شماره بعدی تعیین شود و سپس Argument جدید را با Trace Flag لازم اضافه می‌کند.

Listing 5-8 — پیکربندی Trace Flag در Windows Server Core

# Define initial variables
$InstanceName = "PROSQLADMINCORE"
$TraceFlag = "809"
# Configure full service name to be inserted into Registry path
$Instance = "MSSQL12.$InstanceName"
#Create full registry path
$RegistryPath = "HKLM:\SOFTWARE\Microsoft\Microsoft SQL ServerMSSQL12.$InstanceName\MSSQLServer\Parameters"
# Gather all properties from the Registry path
$Properties = Get-ItemProperty $RegistryPath
# Count the number of SQLArg properties that already exist so that the next 
# number in sequence can be determined
$Arguments = $Properties.psobject.properties | ?{$_.Name -like 'SQLArg*'} | 
select Name, Value
# Create the name of the new argument based on the next argument number in sequence
$NewArgument = "SQLArg"+($Arguments.Count)

ادامه Listing 5-8

# Construct the complete value of the argument
$FullTraceFlag = "-T$TraceFlag"
# Set the trace flag
Set-ItemProperty -Path $RegistryPath -Name $NewArgument -Value $FullTraceFlag

Trace Flagهای زیادی را می‌توان Startup Parameter کرد و بیشتر آن‌ها فقط در شرایط بسیار خاص مفیدند. چند مورد کاربرد عمومی‌تری دارند.

Trace Flag 3042

در Backup Compression، SQL Server با الگوریتم Preallocation درصدی از اندازه Database را از ابتدا برای Backup File رزرو می‌کند که Performance را بهتر می‌کند. اگر حفظ فضای Disk روی Backup Volume مهم‌تر باشد، Trace Flag 3042 این رفتار را غیرفعال می‌کند تا File فقط در صورت نیاز Grow شود.

Trace Flag 3226

به‌طور پیش‌فرض هر Backup موفق Message‌ای در SQL Server Error Log ثبت می‌کند. در Log Backupهای بسیار مکرر این پیام‌ها Noise زیادی ایجاد می‌کنند و Troubleshooting را دشوار می‌سازند. Trace Flag 3226 پیام Backup موفق را Suppress می‌کند و Log کوچک‌تر و قابل مدیریت‌تری به‌دست می‌دهد. روش دیگر این است که با sys.xp_readerrorlog Log را خوانده، Result را در Table ذخیره و فقط Eventهای مهم را Filter کنید.