سطح Nontransactional Access برای FileTable در Database می‌تواند یکی از این مقادیر باشد:

• NONE: مقدار پیش‌فرض؛ فقط Transactional Access مجاز است.
• READ_ONLY: Object در File System قابل مشاهده ولی غیرقابل تغییر است.
• FULL: Object قابل مشاهده و تغییر است.
• IN_TRANSITION_TO_READ_ONLY: در حال Transition به READ_ONLY.
• IN_TRANSITION_TO_OFF: در حال Transition به NONE.

همچنین Root Directory مربوط به FileTable Container باید تعیین شود. هر دو کار با یک ALTER DATABASE انجام می‌شود.

Listing 6-4 — تنظیم Nontransactional Access

ALTER DATABASE Chapter6
SET FILESTREAM ( NON_TRANSACTED_ACCESS = FULL,
                 DIRECTORY_NAME = N'Chapter6_FileTable' );

SQL Server Shareای با نام Instance می‌سازد. Folderی با نام تعیین‌شده داخل Share ایجاد می‌شود و هنگام ایجاد FileTable می‌توان Directory دیگری نیز برای Subfolder مشخص کرد. FileTable Relational Schema آزاد ندارد و Metadata File ثابت است؛ بنابراین Syntax ایجاد آن فقط Table Name، Directory و Collation را مشخص می‌کند.

Listing 6-5 — ساخت FileTable

USE Chapter6
GO
CREATE TABLE dbo.ch06_test AS FILETABLE
WITH
(
    FILETABLE_DIRECTORY = 'Chapter6_FileTable',

ادامه Listing 6-5

    FILETABLE_COLLATE_FILENAME = database_default
);
GO

برای Load کردن Fileها کافی است آن‌ها را در Folder مربوط Copy/Move کنید یا Developer از Namespace برابر System.IO استفاده کند؛ SQL Server Metadata Columnهای FileTable را خودکار Update می‌کند. مسیر نمونه Container شامل Loopback Address، Share مبتنی بر Instance Name و Directoryهای FileTable است.

Memory-Optimized Filegroup

SQL Server 2014 Memory-optimized Table را معرفی کرد. Data این Tableها در Memory قرار دارد، اما برای Durability نسخه‌ای نیز روی Disk نوشته می‌شود و Transactionها همان ویژگی‌های ACID Tableهای Disk-based را دارند.

برای ذخیره پایدار In-memory Data از Memory-optimized Filegroup استفاده می‌شود. این Filegroup شبیه FILESTREAM است، ولی هر Database فقط یک Memory-optimized Filegroup می‌تواند داشته باشد و اگر FILESTREAM جداگانه لازم نباشد، فعال‌سازی صریح FILESTREAM ضروری نیست.

In-memory Data روی Disk با دو نوع File پایدار می‌شود: Data File برای Insert و Delta File برای Delete. این Fileها به‌صورت Pair و برای Range مشخصی از Transactionها کار می‌کنند و تعدادشان باید برابر باشد. Update ترکیبی از Delete و Insert محسوب می‌شود. Fileها Sequential نوشته می‌شوند و Table-agnostic هستند؛ هر File می‌تواند Data چند Table را داشته باشد.

در GUI می‌توان از بخش Memory Optimized Data در تب Filegroups، Filegroup را ایجاد کرد.

سپس از تب Files با Add File، Logical Name را تعیین، File Type را FILESTREAM انتخاب و Container را به Memory-optimized Filegroup متصل می‌کنیم.

Figure 6-6 — افزودن In-memory Filegroup.

Listing 6-6 همان عملیات را با T-SQL انجام می‌دهد. File Location باید با Directory Structure محیط شما هماهنگ شود.

Listing 6-6 — افزودن In-Memory Filegroup و Container

ALTER DATABASE [Chapter6] ADD FILEGROUP [Chapter6_InMemory]
CONTAINS MEMORY_OPTIMIZED_DATA;
GO
ALTER DATABASE [Chapter6] ADD FILE
( NAME = N'InMemory',
  FILENAME = N'F:\MSSQL\MSSQL15.PROSQLADMIN\MSSQL\DATA\InMemory' )
TO FILEGROUP [Chapter6_InMemory];
GO

Figure 6-7 — افزودن In-memory Container.

Strategy برای Structured Filegroup

DBA می‌تواند Filegroup Strategyهای متفاوتی برای Performance، Backup Time، Recovery Time Objective و Tiered Storage انتخاب کند.

Strategyهای Performance

برای Performance باید Object Placement را نسبت به Joinهایی که Application اجرا می‌کند در نظر گرفت. فرض کنید Data Warehouse بزرگی Fact Table عریض و دو Dimension Table میلیونی دارد. اگر هر سه روی یک Filegroup باشند، چند File روی Spindleهای جدا IO را توزیع می‌کند، اما کنترل دقیقی بر اینکه هر Table روی کدام LUN قرار گیرد نداریم؛ Round-robin و Proportional Fill Objectها را روی Fileها پخش می‌کنند. تفکیک سه Table به سه Filegroup روی LUNهای جدا می‌تواند Parallel Table Scan را بهتر کند.

در Data Warehouse چند ده Terabyte با Server بسیار بزرگ و Balanced Throughput مانند Fast Track Data Warehouse Reference Architecture، ممکن است بهترین Performance با Filegroupهایی باشد که از همه Diskهای موجود استفاده می‌کنند تا بیشترین IO Throughput و کمترین Bottleneck حاصل شود.

برای Tableهای Horizontal Partitioning نیز Placement مهم است. اگر Data ماهانه Partition شده و چند Month هم‌زمان خوانده می‌شود، قرار دادن Monthهای مختلف در Filegroupهای جدا روی Spindleهای مجزا می‌تواند Performance را بهتر کند.

Strategyهای Backup و Restore

SQL Server علاوه بر Database Level، Backup در File و Filegroup Level را نیز پشتیبانی می‌کند. با این قابلیت می‌توان Piecemeal Restore انجام داد و Database را مرحله‌به‌مرحله Online کرد؛ برای Database بزرگ با RTO پایین بسیار مفید است.

مثلاً Database بزرگی را در نظر بگیرید که بخش کوچکی از Data آن Critical است و حداکثر ظرف دو ساعت باید برگردد، اما Historical Data بزرگ فقط برای Reporting روزانه لازم است و Restore آن می‌تواند دیرتر انجام شود. با دو Secondary Filegroup، Critical Data را در اولی و Historical Data را در دومی قرار می‌دهید. پس از Disaster، Primary و Critical Filegroup Restore و Database Online می‌شود؛ Historical Filegroup بعداً Online خواهد شد.

Filegroup می‌تواند Backup Window را نیز حل کند. اگر Full Backup دو ساعت طول می‌کشد ولی Nightly Window فقط یک ساعت است، Data را بین دو Filegroup تقسیم کرده و آن‌ها را در روزهای متفاوت Backup کنید.

Storage Tiering

برای Databaseهای بزرگ، Tiering معمولاً همراه Partitioning اجرا می‌شود. فرض کنید Table شش سال Data دارد: Current Year روزانه زیاد Read/Write می‌شود، سه سال قبلی فقط در Monthly Report و Data قدیمی‌تر صرفاً برای Regulatory Requirement باید فوراً قابل دسترسی باشد.

می‌توان Partitionهای Yearly ساخت: Current Year روی Local RAID 10 برای Performance، سال‌های 2 و 3 روی Premium Tier مربوط به SAN و Data قدیمی‌تر روی Near-line Storage کم‌هزینه قرار گیرد.

برخی Organizationها Automated Storage Tiering مانند Adaptive Optimization استفاده می‌کنند. این فناوری دو Phase دارد: Analysis برای تعیین Tier مناسب هر Block/File و Move Data به Tier انتخاب‌شده. مشکل آن است که Window مربوط به Move Data Performance SAN را کاهش می‌دهد. اگر Analysis/Move خیلی مکرر باشد Performance دائماً افت می‌کند؛ اگر خیلی دیر انجام شود ممکن است Usage Pattern SQL Server را اشتباه پیش‌بینی کند. مثال Reporting Weekly نشان می‌دهد Analysis روز قبل از Peak می‌تواند Data را روی Tier کند قرار دهد و بعد از Peak، Data بی‌استفاده را روی Premium Tier ببرد. بنابراین Automated Tiering بیشتر در Environmentهایی خوب کار می‌کند که ساعات Operation و Usage Profile پایدار دارند.

Strategy برای Memory-Optimized Filegroup

Memory-optimized Filegroup نیز Allocation را Round-robin بین Containerها انجام می‌دهد. معمول است Containerهای متعدد روی Spindleهای جدا قرار گیرند تا IO Throughput افزایش یابد؛ اما اگر فقط یک Container روی Volume A و یکی روی Volume B باشد، ممکن است همه Data Fileها روی یک Volume و همه Delta Fileها روی دیگری بیفتند.

برای جلوگیری از این عدم تعادل، روی هر Volume دو Container قرار دهید تا Data و Delta IO متوازن شود؛ Figure 6-8 این Best Practice را نشان می‌دهد.

Maintenance مربوط به File و Filegroup

در طول عمر Application ممکن است برای Performance یا Capacity Management نیاز به Add، Expand یا Shrink کردن Fileها داشته باشید.

افزودن File

File جدید هم برای Capacity و هم Performance اضافه می‌شود. اگر Database از Estimate عبور کرده و Volume فعلی قابل Resize نیست، File جدید روی LUN دیگری به Filegroup اضافه کنید. اگر Storage Subsystem Bottleneck است نیز File اضافی می‌تواند IO Throughput را افزایش دهد. در GUI این کار از تب Files در Database Properties با تعیین Logical Name، Filegroup، Initial Size، Autogrowth، Max Size و Physical Path انجام می‌شود.

Figure 6-8 — توزیع متوازن IO برای Memory-optimized Filegroup.

Listing 6-7 یک File جدید با T-SQL اضافه می‌کند.

Listing 6-7 — افزودن File جدید

ALTER DATABASE [Chapter6] ADD FILE
( NAME = N'Chapter6_File4',
  FILENAME = N'G:\DATA\Chapter6_File4.ndf',
  SIZE = 5120KB,
  FILEGROWTH = 1024KB )
TO FILEGROUP [PRIMARY];
GO

در این سناریو Proportional Fill مهم است. File تازه و خالی ابتدا بیشتر Target می‌شود تا مقدار Free Space آن با Fileهای قدیمی برابر شود. بنابراین اگر File جدید را هم‌اندازه Fileهای موجود بسازید ممکن است Distribution دلخواه فوراً حاصل نشود. Listing 6-8 با افزودن 10,000 Row این Behavior را نشان می‌دهد.

Listing 6-8 — افزودن Rowهای بیشتر به RoundRobinTable، بخش اول

DECLARE @Numbers TABLE(Number INT)
;WITH CTE(Number) AS
(
    SELECT 1 Number
    UNION ALL
    SELECT Number +1

ادامه Listing 6-8

    FROM CTE
    WHERE Number <= 99
)
INSERT INTO @Numbers SELECT * FROM CTE;
INSERT INTO dbo.RoundRobinTable
SELECT 'DummyText' FROM @Numbers a CROSS JOIN @Numbers b;
SELECT b.file_id, COUNT(*)
FROM
(
    SELECT ID, DummyTxt, a.file_id
    FROM dbo.RoundRobinTable
    CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%) a
) b
GROUP BY b.file_id;

Figure 6-9 نشان می‌دهد Proportional Fill ابتدا File جدید را تقریباً انحصاری استفاده کرده تا Free Spaceها هم‌سطح شوند و سپس Round-robin را از سر گرفته است. در ادامه Autogrowth روی File اول اتفاق افتاده و چون Free Space آن بیشتر شده، بیشتر Rowهای باقی‌مانده به آن File رفته‌اند.

برای جلوگیری از پرشدن اولیه File جدید، یا File کوچک‌تری بسازید یا Size Fileهای موجود را افزایش دهید تا Free Spaceها نزدیک شوند. راه دیگر Database Scoped Configuration است تا با هر Autogrowth همه Fileهای Filegroup Grow کنند.

Expand کردن File

مقدار Initial Size در GUI عملاً Current Size است. اگر Autogrowth فعال باشد، Fileها با پرشدن خودکار Grow می‌کنند و Size فعلی از Initial Value بیشتر می‌شود.

Figure 6-9 — Allocation Row به File جدید.

Autogrowth یک Fail-safe مفید است، اما بهتر است به آن تکیه نشود. Grow کردن File Resource مصرف می‌کند و Lock می‌گیرد و Processهای دیگر را Block می‌کند. Database Fileها باید بر اساس Capacity Estimate از قبل Presize شوند.

به همان دلیل Filegrowth برابر 1MB معمولاً بد است؛ Growهای ریز و متعدد Performance را کاهش می‌دهند، حتی با Instant File Initialization. مقدار مناسب به Free Space Volume و تعداد Databaseهای Shareکننده Volume بستگی دارد. sys.dm_db_file_space_usage ستون unallocated_extent_page_count را می‌دهد تا Free Space محاسبه شود.

Listing 6-9 — محاسبه Free Space هر File

SELECT
    file_id,
    unallocated_extent_page_count * 1.0 / 128 'Free Space (MB)'
FROM sys.dm_db_file_space_usage;

برای Expand لازم نیست منتظر Autogrowth باشید. از GUI یا ALTER DATABASE می‌توان Size را دستی افزایش داد.

Listing 6-10 — Expand کردن File

ALTER DATABASE [Chapter6]
MODIFY FILE ( NAME = N'Chapter6_File4', SIZE = 20480KB );

Shrink کردن File

همان‌طور که File Expand می‌شود، امکان Shrink نیز وجود دارد: Shrink یک File، همه Fileهای Database همراه Log، یا Auto Shrink در سطح Database.

برای Shrink یک File از DBCC SHRINKFILE استفاده می‌شود. می‌توان Target Size یا گزینه EMPTYFILE را تعیین کرد. EMPTYFILE همه Data را به Fileهای دیگر همان Filegroup منتقل می‌کند تا File قابل حذف شود.

با Target Size می‌توان TRUNCATEONLY یا NOTRUNCATE انتخاب کرد. TRUNCATEONLY از انتهای File Space آزاد را تا آخرین Allocated Extent پس می‌گیرد. NOTRUNCATE از انتهای File، Allocated Extentها را به Free Space ابتدای File جابه‌جا می‌کند.

Listing 6-11 — Shrink File با TRUNCATEONLY

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

برای آزادکردن Space انتهای همه Fileها می‌توان از Tasks → Shrink → Database استفاده کرد و Reorganize Files Before Releasing Unused Space را غیرفعال گذاشت؛ معادل T-SQL آن Listing 6-12 است.

Listing 6-12 — Shrink Database

USE [Chapter6]
GO
DBCC SHRINKDATABASE(N'Chapter6');

به‌ندرت Shrink کردن Database یا File قابل توجیه است. تصور اینکه File بزرگِ خالی Backup را کند می‌کند غلط است. Shrink معمولاً فقط در شرایط استثنایی مانند حذف صدها GB Archive و نزدیک‌شدن به Limit Storage قابل قبول است. به‌طور کلی Database File را Shrink نکنید و هرگز Auto Shrink را برای Database فعال نکنید.

اگر ناچار به Shrink هستید، انتظار Operation کند و Single-threaded داشته باشید. NOTRUNCATE نیز نباید استفاده شود، چون Extentها را داخل File جابه‌جا کرده و Fragmentation شدید ایجاد می‌کند. Listing 6-13 ابتدا Clustered Index می‌سازد، Fragmentation را با sys.dm_db_index_physical_stats می‌سنجد، Database را با NOTRUNCATE Shrink و دوباره Fragmentation را اندازه می‌گیرد.

Listing 6-13 — Fragmentation ناشی از Shrink، بخش اول

USE Chapter6
GO
CREATE UNIQUE CLUSTERED INDEX CIX_RoundRobinTable
ON dbo.RoundRobinTable(ID);
GO
SELECT * FROM sys.dm_db_index_physical_stats(
 DB_ID('Chapter6'),OBJECT_ID('dbo.RoundRobinTable'),1,NULL,'DETAILED')
WHERE index_level = 0;
DBCC SHRINKDATABASE(N'Chapter6', NOTRUNCATE);
GO

ادامه Listing 6-13

SELECT * FROM sys.dm_db_index_physical_stats(
 DB_ID('Chapter6'),OBJECT_ID('dbo.RoundRobinTable'),1,NULL,'DETAILED')
WHERE index_level = 0;
GO

Figure 6-10 نشان می‌دهد Fragmentation سطح Leaf از حدود 0.08% به 71.64% افزایش یافته و Queryهای استفاده‌کننده از Index را شدیداً تحت تأثیر قرار می‌دهد. مقدار دقیق Fragmentation به Layout Extentها بستگی دارد.

Database Scoped Configuration

پیش از SQL Server 2016، Trace Flagهایی مانند T1117 و T1118 Behavior مربوط به Storage و Autogrowth را تغییر می‌دادند. T1117 باعث می‌شد همه Fileهای یک Filegroup با هم Grow کنند و T1118 Mixed Extent را عملاً غیرفعال و Uniform Extent را اجباری می‌کرد؛ برای TempDB و Data Warehouse کاربرد داشتند. در مقاله بعدی جایگزین‌های Database-level این Flagها بررسی می‌شوند.

Figure 6-10 — نتیجه Fragmentation.