از SQL Server 2016 به بعد Trace Flagهای T1117 و T1118 اثری ندارند و با Database Scoped Configuration جایگزین شده‌اند. مزیت اصلی این است که Setting در سطح Database است و یک Consolidated Instance می‌تواند Databaseهای با Workload متفاوت را مستقل پیکربندی کند. همچنین این گزینه‌ها رسمی، مستند و پشتیبانی‌شده‌اند.

Listing 6-14 — فعال‌کردن Autogrow برای همه Fileها

ALTER DATABASE Chapter6 MODIFY FILEGROUP [Primary] AUTOGROW_ALL_FILES

Listing 6-15 — غیرفعال‌کردن Mixed Page Allocation

ALTER DATABASE Chapter6 SET MIXED_PAGE_ALLOCATION OFF

نگهداری Transaction Log

Transaction Log برای Recovery و Featureهایی مانند AlwaysOn Availability Groups، Transactional Replication و Change Data Capture حیاتی است. Log File درون خود به VLF یا Virtual Log File تقسیم می‌شود. وقتی آخرین VLF پر شود SQL Server به ابتدای Log برمی‌گردد؛ اگر VLF اول قابل Reuse نباشد، Log Grow می‌کند. اگر به دلیل کمبود Disk یا Max Size نتواند Grow کند Error 9002 رخ می‌دهد و Transaction Rollback می‌شود.

تعداد VLFها به Initial Size و Growth Increment وابسته است: Growth کمتر از 64MB چهار VLF، بین 64MB و 1GB هشت VLF و بیشتر از 1GB شانزده VLF اضافه می‌کند. Figure 6-11 ساختار Circular Log را نشان می‌دهد.

Recovery Model

Recovery Model ویژگی Database-level است که شیوه Logging Transaction و در نتیجه Maintenance Log را تعیین می‌کند. سه Model اصلی در جدول 6-1 هستند.

Figure 6-11 — ساختار Log File.

تعداد Log Fileها

داشتن چند Transaction Log File Performance را بهتر نمی‌کند. Log Sequential است و SQL Server File دوم را فقط بعد از پرشدن File اول استفاده می‌کند؛ بنابراین IO بین Driveها Strip نمی‌شود. تنها دلیل عملی برای Log File دوم زمانی است که Volume فعلی پر شده و امکان Expand یا Move وجود ندارد.

Shrink کردن Log

Shrink Log نباید Routine Maintenance باشد. گاهی پس از فعالیت غیرعادی مانند Initial Population یا One-time ETL که Log بیش از حد Grow کرده، Shrink می‌تواند مناسب باشد؛ ولی ابتدا باید مشخص شود Event واقعاً استثنایی بوده و دوباره تکرار نمی‌شود.

در GUI از Shrink File و File Type برابر Log استفاده می‌شود. Release Unused Space عملاً TRUNCATEONLY را اعمال می‌کند. Shrink ممکن است هیچ Spaceای پس نگیرد اگر آخرین VLF قابل Reuse نباشد.

Listing 6-16 — Shrink Log با TRUNCATEONLY

USE [Chapter6]
GO
DBCC SHRINKFILE (N'Chapter6_log', 0, TRUNCATEONLY);
GO

Log Fragmentation

Truncate شدن Log در FULL Recovery Model هنگام Backup و در SIMPLE هنگام Checkpoint، VLFهای قابل Reuse را آزاد می‌کند. VLF حاوی Active Transaction یا Transactionی که هنوز به Replication/AlwaysOn مقصد نرسیده قابل Reuse نیست. Shrink نیز VLFها را از انتها تا اولین VLF فعال حذف می‌کند.

قاعده مطلق برای تعداد مناسب VLF وجود ندارد، اما نویسنده برای Logهای بزرگ در حد ده‌ها GB حدود دو VLF به ازای هر GB را هدف قرار می‌دهد. VLF خیلی زیاد Performance عملیات Log را کاهش می‌دهد و VLF خیلی بزرگ نیز Truncation طولانی ایجاد می‌کند. برای Logهای بزرگ Growth در Chunkهای حدود 8GB توصیه شده است.

برای نمایش Fragmentation، Database نمونه Chapter6LogFragmentation با یک میلیون Row ساخته می‌شود تا Growthهای کوچک تعداد زیادی VLF ایجاد کنند.

Listing 6-17 — ساخت Chapter6LogFragmentation، بخش اول

CREATE DATABASE [Chapter6LogFragmentation]
 CONTAINMENT = NONE
 ON PRIMARY
( NAME = N'Chapter6LogFragmentation',
  FILENAME = N'F:\MSSQL\MSSQL15.PROSQLADMIN\MSSQL\DATA\Chapter6LogFragmentation.mdf',
  SIZE = 5120KB, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'Chapter6LogFragmentation_log',
  FILENAME = N'E:\MSSQL\MSSQL15.PROSQLADMIN\MSSQL\DATA\Chapter6LogFragmentation_log.ldf',
  SIZE = 1024KB, FILEGROWTH = 10%);
GO
USE Chapter6LogFragmentation
GO
CREATE TABLE dbo.Inserts(ID INT IDENTITY, DummyText NVARCHAR(50));
DECLARE @Numbers TABLE(Number INT)
;WITH CTE(Number) AS (
  SELECT 1 UNION ALL SELECT Number+1 FROM CTE WHERE Number <= 99
)

ادامه Listing 6-17

INSERT INTO @Numbers SELECT * FROM CTE;
INSERT INTO dbo.Inserts
SELECT 'DummyText'
FROM @Numbers a CROSS JOIN @Numbers b CROSS JOIN @Numbers c;

برای مشاهده Size Log و تعداد VLF، Listing 6-18 از Result مربوط به DBCC LOGINFO استفاده می‌کند.

Listing 6-18 — اندازه Log و تعداد VLF، بخش اول

DECLARE @DBCCLogInfo TABLE
(
 RecoveryUnitID TINYINT,
 FieldID TINYINT,
 FileSize BIGINT,
 StartOffset BIGINT,
 FseqNo INT,
 Status TINYINT,
 Parity TINYINT,
 CreateLSN NUMERIC
);

ادامه Listing 6-18

INSERT INTO @DBCCLogInfo EXEC('DBCC LOGINFO');
SELECT name,[Size in MBs],[Number of VLFs],
       [Number of VLFs] / ([Size in MBs] / 1024) 'VLFs per GB'
FROM (
 SELECT name,size * 1.0 / 128 'Size in MBs',
        (SELECT COUNT(*) FROM @DBCCLogInfo) 'Number of VLFs'
 FROM sys.database_files WHERE type = 1
) a;

در Result نمونه، Log با Size حدود 345MB دارای 61 VLF است که بیش از حد محسوب می‌شود.

Figure 6-12 — تعداد VLF به ازای GB.

CreateLSN صفر نشان می‌دهد VLF در Initial Log Creation ساخته شده، Parity صفر یعنی هنوز استفاده نشده و بیشترین FSeqNo محل Write جاری را مشخص می‌کند. در مثال، 51 VLF Active هستند و قابل Reuse نیستند.

اگر Log را Shrink کنیم فقط VLFهای انتهایی غیرActive قابل حذف هستند. علت Growth نمونه این بود که Space در یک Transaction بزرگ مصرف شده و Log Backup نیز انجام نشده است.

برای تشخیص علت عدم Reuse از ستون log_reuse_wait_desc در sys.databases استفاده کنید.

Listing 6-19 — بررسی Log Reuse Wait

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'Chapter6LogFragmentation';

این Wait دلیل آخرین تلاش برای Cycle را نشان می‌دهد و ممکن است هنگام Query دیگر Current نباشد.

در سناریوی نمونه برای Reusable شدن VLFها ابتدا Full Backup و سپس Log Backup لازم است. Switch به SIMPLE نیز ممکن بود اما Log Chain را می‌شکست. پس از Backup فقط VLF جاری Active می‌ماند.

برای Defragment Log باید تا حد ممکن Shrink و سپس با Increment بزرگ‌تر دوباره Grow شود.

Listing 6-20 — Defragment کردن Transaction Log

USE Chapter6LogFragmentation
GO
DBCC SHRINKFILE ('Chapter6LogFragmentation_log', 0, TRUNCATEONLY);
GO
ALTER DATABASE Chapter6LogFragmentation
MODIFY FILE ( NAME = 'Chapter6LogFragmentation_log', SIZE = 512000KB );
GO

پس از Grow مجدد، تعداد VLFها کمتر و Size منطقی‌تر می‌شود.

Figure 6-13 — Log Fragmentation بعد از Shrink و Expand.

جمع‌بندی

Filegroup Container منطقی Data File است؛ FILESTREAM/FileTable و Memory-optimized Data Filegroupهای ویژه خود را دارند. Object روی Filegroup ساخته می‌شود و Data بین Fileهای آن توزیع می‌شود. Filegroup Strategy می‌تواند Performance، Backup/Restore و Storage Tiering را بهبود دهد.

برای VLDB با Maintenance Window محدود می‌توان Filegroupها را شب‌های مختلف Backup کرد و Critical Data را در Filegroup جدا قرار داد تا Piecemeal Restore سریع‌تر شود. Storage Tiering نیز با Partition و Filegroupهای روی Storage Tierهای متفاوت قابل پیاده‌سازی است.

FILESTREAM و Memory-optimized Filegroup به Folderهای OS یا Container اشاره می‌کنند؛ برای Memory-optimized بهتر است برای هر Disk Array دو Container داشته باشید تا IO متوازن شود.

Expand و Shrink Data File ممکن است، اما Shrink و مخصوصاً Auto Shrink Bad Practice است و Fragmentation ایجاد می‌کند. Growth باید با Increment مناسب باشد. Log نیز فقط در شرایط خاص Shrink شود؛ Growthهای کوچک VLF زیاد و Log Fragmentation ایجاد می‌کنند. راه اصلاح، Shrink کنترل‌شده و Grow مجدد با Increment بزرگ‌تر است.