آموزش Natively Compiled Stored Procedure در SQL Server

تعریف و جایگاه موضوع

Natively Compiled Stored Procedure بخشی از مسیر Native Compilation در In-Memory OLTP است و هزینه تفسیر T-SQL را در سناریوهای پرتکرار کاهش می‌دهد.

جایگاه Natively Compiled Stored Procedure در معماری این است که میان نیاز کسب‌وکار و رفتار واقعی موتور SQL Server یک نقطه کنترل یا مشاهده ایجاد کند. مفاهیم محوری آن شامل Natively Compiled Stored Procedure، NATIVE_COMPILATION، SCHEMABINDING، EXECUTE AS، BEGIN ATOMIC، Memory-Optimized Table است. هرکدام باید در سطح درست اندازه‌گیری شوند؛ زیرا شباهت نام‌ها به معنای یکسان بودن Scope نیست.

این تصویر جایگاه Natively Compiled Stored Procedure را در کنار Natively Compiled Stored Procedure، NATIVE_COMPILATION، SCHEMABINDING، EXECUTE AS نشان می‌دهد و کمک می‌کند پیش از اجرای دستور، وابستگی‌های اصلی و مسیر تصمیم مشخص شوند.

نحو، سطح دسترسی و رفتار اجرایی

Syntax یا Query پایه

CREATE PROCEDURE dbo.usp_InsertFastOrder
    @OrderID bigint,
    @CustomerID int
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'Persian')
    INSERT dbo.FastOrders(OrderID, CustomerID, CreatedAt)
    VALUES (@OrderID, @CustomerID, SYSUTCDATETIME());
END;

پارامترها و اجزای اثرگذار

• Natively Compiled Stored Procedure: باید با Scope واقعی Natively Compiled Stored Procedure و نسخه SQL Server هماهنگ شود.
• NATIVE_COMPILATION: باید با Scope واقعی Natively Compiled Stored Procedure و نسخه SQL Server هماهنگ شود.
• SCHEMABINDING: باید با Scope واقعی Natively Compiled Stored Procedure و نسخه SQL Server هماهنگ شود.
• EXECUTE AS: باید با Scope واقعی Natively Compiled Stored Procedure و نسخه SQL Server هماهنگ شود.
• BEGIN ATOMIC: باید با Scope واقعی Natively Compiled Stored Procedure و نسخه SQL Server هماهنگ شود.

نوع خروجی و رفتارهای ویژه

خروجی Natively Compiled Stored Procedure ممکن است یک تغییر DDL، وضعیت کاتالوگی، Snapshot از DMV یا اثر روی کامپایل‌های بعدی باشد. رفتار با NULL، Restart، Reconfigure و Reset به نوع موضوع وابسته است؛ بنابراین نتیجه ظاهری Query را با اثر Runtime اشتباه نگیرید.

منطق اجرا و نقاط کنترل

جریان بالا نشان می‌دهد Natively Compiled Stored Procedure از ورودی و پیکربندی اولیه به مشاهده خروجی، سنجش هزینه و تصمیم نهایی می‌رسد؛ بخش Chart-like برای مقایسه مراحل پرهزینه‌تر طراحی شده است.

مثال‌های عملی از ساده تا پیشرفته

مثال 1: سناریوی Natively Compiled Stored Procedure در مرحله 1

در این سناریو هدف آن است که بُعد شماره 1 از Natively Compiled Stored Procedure به‌صورت کنترل‌شده بررسی شود. Query را ابتدا در محیط Test اجرا کنید و نتیجه را با Baseline همان بازه مقایسه کنید.

CREATE PROCEDURE dbo.usp_InsertFastOrder
    @OrderID bigint,
    @CustomerID int
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'Persian')
    INSERT dbo.FastOrders(OrderID, CustomerID, CreatedAt)
    VALUES (@OrderID, @CustomerID, SYSUTCDATETIME());
END;

نکته کاربردی: مثال 1 زمانی معتبر است که خروجی آن با نسخه، Permission و بار همزمان سرور شما سازگار باشد؛ صرف موفق بودن Syntax به معنای مناسب بودن تصمیم نیست.

مثال 2: سناریوی Natively Compiled Stored Procedure در مرحله 2

در این سناریو هدف آن است که بُعد شماره 2 از Natively Compiled Stored Procedure به‌صورت کنترل‌شده بررسی شود. Query را ابتدا در محیط Test اجرا کنید و نتیجه را با Baseline همان بازه مقایسه کنید.

SELECT SERVERPROPERTY('IsXTPSupported') AS IsXTPSupported;

نکته کاربردی: مثال 2 زمانی معتبر است که خروجی آن با نسخه، Permission و بار همزمان سرور شما سازگار باشد؛ صرف موفق بودن Syntax به معنای مناسب بودن تصمیم نیست.

مثال 3: سناریوی Natively Compiled Stored Procedure در مرحله 3

در این سناریو هدف آن است که بُعد شماره 3 از Natively Compiled Stored Procedure به‌صورت کنترل‌شده بررسی شود. Query را ابتدا در محیط Test اجرا کنید و نتیجه را با Baseline همان بازه مقایسه کنید.

SELECT name, is_memory_optimized_elevate_to_snapshot_on
FROM sys.databases
WHERE database_id = DB_ID();

نکته کاربردی: مثال 3 زمانی معتبر است که خروجی آن با نسخه، Permission و بار همزمان سرور شما سازگار باشد؛ صرف موفق بودن Syntax به معنای مناسب بودن تصمیم نیست.

مثال 4: سناریوی Natively Compiled Stored Procedure در مرحله 4

در این سناریو هدف آن است که بُعد شماره 4 از Natively Compiled Stored Procedure به‌صورت کنترل‌شده بررسی شود. Query را ابتدا در محیط Test اجرا کنید و نتیجه را با Baseline همان بازه مقایسه کنید.

SELECT name, type_desc
FROM sys.filegroups
WHERE type = 'FX';

نکته کاربردی: مثال 4 زمانی معتبر است که خروجی آن با نسخه، Permission و بار همزمان سرور شما سازگار باشد؛ صرف موفق بودن Syntax به معنای مناسب بودن تصمیم نیست.

مثال 5: سناریوی Natively Compiled Stored Procedure در مرحله 5

در این سناریو هدف آن است که بُعد شماره 5 از Natively Compiled Stored Procedure به‌صورت کنترل‌شده بررسی شود. Query را ابتدا در محیط Test اجرا کنید و نتیجه را با Baseline همان بازه مقایسه کنید.

SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, name, durability_desc
FROM sys.tables
WHERE is_memory_optimized = 1;

نکته کاربردی: مثال 5 زمانی معتبر است که خروجی آن با نسخه، Permission و بار همزمان سرور شما سازگار باشد؛ صرف موفق بودن Syntax به معنای مناسب بودن تصمیم نیست.

مثال 6: سناریوی Natively Compiled Stored Procedure در مرحله 6

در این سناریو هدف آن است که بُعد شماره 6 از Natively Compiled Stored Procedure به‌صورت کنترل‌شده بررسی شود. Query را ابتدا در محیط Test اجرا کنید و نتیجه را با Baseline همان بازه مقایسه کنید.

SELECT OBJECT_NAME(object_id) AS table_name, *
FROM sys.dm_db_xtp_table_memory_stats
ORDER BY memory_used_by_table_kb DESC;

نکته کاربردی: مثال 6 زمانی معتبر است که خروجی آن با نسخه، Permission و بار همزمان سرور شما سازگار باشد؛ صرف موفق بودن Syntax به معنای مناسب بودن تصمیم نیست.

مثال 7: سناریوی Natively Compiled Stored Procedure در مرحله 7

در این سناریو هدف آن است که بُعد شماره 7 از Natively Compiled Stored Procedure به‌صورت کنترل‌شده بررسی شود. Query را ابتدا در محیط Test اجرا کنید و نتیجه را با Baseline همان بازه مقایسه کنید.

SELECT OBJECT_NAME(object_id) AS object_name, index_id, total_bucket_count, empty_bucket_count, avg_chain_length
FROM sys.dm_db_xtp_hash_index_stats;

نکته کاربردی: مثال 7 زمانی معتبر است که خروجی آن با نسخه، Permission و بار همزمان سرور شما سازگار باشد؛ صرف موفق بودن Syntax به معنای مناسب بودن تصمیم نیست.

مثال 8: سناریوی Natively Compiled Stored Procedure در مرحله 8

در این سناریو هدف آن است که بُعد شماره 8 از Natively Compiled Stored Procedure به‌صورت کنترل‌شده بررسی شود. Query را ابتدا در محیط Test اجرا کنید و نتیجه را با Baseline همان بازه مقایسه کنید.

SELECT name, is_memory_optimized, durability_desc
FROM sys.tables
WHERE is_memory_optimized = 1;

نکته کاربردی: مثال 8 زمانی معتبر است که خروجی آن با نسخه، Permission و بار همزمان سرور شما سازگار باشد؛ صرف موفق بودن Syntax به معنای مناسب بودن تصمیم نیست.

مثال 9: سناریوی Natively Compiled Stored Procedure در مرحله 9

در این سناریو هدف آن است که بُعد شماره 9 از Natively Compiled Stored Procedure به‌صورت کنترل‌شده بررسی شود. Query را ابتدا در محیط Test اجرا کنید و نتیجه را با Baseline همان بازه مقایسه کنید.

SELECT OBJECT_NAME(object_id) AS object_name, name, type_desc
FROM sys.indexes
WHERE object_id IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1);

نکته کاربردی: مثال 9 زمانی معتبر است که خروجی آن با نسخه، Permission و بار همزمان سرور شما سازگار باشد؛ صرف موفق بودن Syntax به معنای مناسب بودن تصمیم نیست.

مثال 10: سناریوی Natively Compiled Stored Procedure در مرحله 10

در این سناریو هدف آن است که بُعد شماره 10 از Natively Compiled Stored Procedure به‌صورت کنترل‌شده بررسی شود. Query را ابتدا در محیط Test اجرا کنید و نتیجه را با Baseline همان بازه مقایسه کنید.

SELECT * FROM sys.dm_xtp_gc_stats;

نکته کاربردی: مثال 10 زمانی معتبر است که خروجی آن با نسخه، Permission و بار همزمان سرور شما سازگار باشد؛ صرف موفق بودن Syntax به معنای مناسب بودن تصمیم نیست.

کاربردهای واقعی در پروژه

Natively Compiled Stored Procedure در پروژه‌های واقعی برای کنترل یک نقطه مشخص از چرخه عمر داده یا Query به‌کار می‌رود. تیم عملیات می‌تواند از آن برای ساخت Runbook، تیم توسعه برای طراحی رفتار قابل پیش‌بینی و تیم معماری برای مقایسه هزینه گزینه‌ها استفاده کند.

در محیط سازمانی بهتر است اجرای Natively Compiled Stored Procedure از مسیر Change Management عبور کند. Ticket باید شامل دلیل، دامنه، زمان، مالک، Query قبل و بعد و شاخص پذیرش باشد. این رویکرد اختلاف میان نتیجه آزمایش و رفتار Production را کاهش می‌دهد.

اشتباهات رایج و روش اصلاح

• اشتباه: نتیجه یک اجرای کوتاه را معیار قطعی Natively Compiled Stored Procedure دانستن. اصلاح: چند Snapshot در بار مشابه ثبت کنید.
• اشتباه: کپی Syntax از نسخه دیگر. اصلاح: مستندات نسخه مقصد و Metadata همان Instance را کنترل کنید.
• اشتباه: حذف وابستگی‌ها از تحلیل. اصلاح: Object، Group، Pool، Plan یا Indexهای مرتبط را پیش از تغییر فهرست کنید.
• اشتباه: نداشتن فرمان بازگشت. اصلاح: قبل از Apply، دستور معکوس و شرط توقف را آزمایش کنید.

Performance Considerations

اثر کارایی Natively Compiled Stored Procedure باید در سطح درست سنجیده شود. زمان اجرای یک Query ممکن است کاهش یابد اما مصرف حافظه، تعداد Compile، فشار CPU یا هزینه نگهداری افزایش پیدا کند. بنابراین حداقل دو شاخص مستقیم و یک شاخص جانبی ثبت کنید.

در تحلیل Plan و Cardinality، تفاوت میان همبستگی و علت را رعایت کنید. اگر همزمان با Natively Compiled Stored Procedure تغییر دیگری منتشر شده باشد، آزمایش A/B یا بازه کنترل لازم است. Queryهای DMV نیز خودشان هزینه دارند و نباید با نرخ بسیار بالا Poll شوند.

Best Practices

• Natively Compiled Stored Procedure را ابتدا روی نمونه نماینده Workload و نه داده ساختگی بسیار کوچک آزمایش کنید.
• نام‌گذاری اشیا و تغییرات را استاندارد کنید تا ممیزی و حذف بعدی ساده باشد.
• Baseline، مقدار هدف، بازه اندازه‌گیری و آستانه شکست را پیش از اجرا بنویسید.
• پس از تغییر، هم تعریف کاتالوگی و هم رفتار Runtime را بررسی کنید.
• برای تغییرات پرریسک، Maintenance Window و مالک Rollback تعیین کنید.

این نمودار خطاهای رایج Natively Compiled Stored Procedure را در برابر مسیر پیشنهادی قرار می‌دهد؛ فلش‌ها نشان می‌دهند هر خطا با کدام کنترل فنی و معیار Performance اصلاح می‌شود.

مزایا، محدودیت‌ها و زمان نامناسب استفاده

سؤالات متداول

برای شروع کار با Natively Compiled Stored Procedure چه پیش‌نیازی لازم است؟

پیش از اجرا، نسخه و Edition، سطح دسترسی، وضعیت پایگاه داده و یک محیط آزمایشی همسان با Production را بررسی کنید. برای Natively Compiled Stored Procedure ثبت Baseline اولیه باعث می‌شود تغییر واقعی از نوسان عادی جدا شود.

چگونه صحت پیکربندی Natively Compiled Stored Procedure را بررسی کنیم؟

تعریف کاتالوگی را با نمای Runtime مقایسه کنید، سپس یک سناریوی کنترل‌شده اجرا و نتیجه را در بازه زمانی مشخص ثبت کنید. اتکا به یک Snapshot برای قضاوت درباره Natively Compiled Stored Procedure کافی نیست.

Natively Compiled Stored Procedure در چه پروژه‌هایی ارزش تجاری بیشتری ایجاد می‌کند؟

در سامانه‌هایی که تأخیر، پایداری و تفکیک بارکاری مستقیماً بر درآمد یا SLA اثر دارد، Natively Compiled Stored Procedure می‌تواند ارزش بیشتری ایجاد کند؛ البته نتیجه باید با شاخص قابل اندازه‌گیری تأیید شود.

چه زمانی هزینه نگهداری Natively Compiled Stored Procedure از منفعت آن بیشتر می‌شود؟

وقتی حجم عملیات پایین، تیم فاقد مهارت نگهداری یا مسیر بازگشت نامشخص است، پیچیدگی Natively Compiled Stored Procedure ممکن است توجیه نداشته باشد. تصمیم باید بر هزینه کل مالکیت استوار باشد.

Natively Compiled Stored Procedure با روش جایگزین در SQL Server چه تفاوتی دارد؟

روش جایگزین معمولاً Scope، هزینه اجرا و میزان کنترل متفاوتی دارد. مقایسه درست باید روی Workload واقعی، Plan یا مصرف منابع و نه صرفاً زمان یک Query انجام شود.

برای پیاده‌سازی حرفه‌ای Natively Compiled Stored Procedure چه خدماتی لازم است؟

تحلیل Workload، طراحی آزمایش، پیاده‌سازی مرحله‌ای، مستندسازی، آموزش تیم و پایش پس از انتشار اجزای اصلی خدمت حرفه‌ای هستند؛ اجرای مستقیم در Production بدون این زنجیره پرریسک است.

رایج‌ترین خطای عملیاتی در Natively Compiled Stored Procedure چیست؟

خطای رایج، اعمال تنظیم بدون اندازه‌گیری وضعیت پایه و بدون کنترل وابستگی‌هاست. در Natively Compiled Stored Procedure ابتدا Scope را محدود کنید و هر تغییر را با یک معیار موفقیت و یک شرط توقف همراه سازید.

اثر Natively Compiled Stored Procedure بر Performance چگونه اندازه‌گیری می‌شود؟

شاخص مناسب به موضوع بستگی دارد، اما زمان پاسخ، CPU، Memory، تعداد اجرای موفق، صف انتظار و تغییر Plan از معیارهای رایج‌اند. مقایسه باید در پنجره بار مشابه انجام شود.

بهترین روش مستندسازی و Rollback برای Natively Compiled Stored Procedure چیست؟

نام اشیا، دلیل تغییر، مقدار قبل و بعد، مالک تصمیم، زمان اعمال، Query اعتبارسنجی و فرمان بازگشت را در Runbook ثبت کنید تا Natively Compiled Stored Procedure به تنظیمی ناشناخته تبدیل نشود.

Natively Compiled Stored Procedure با کدام نسخه‌های SQL Server سازگار است؟

قابلیت دقیق می‌تواند بین نسخه‌ها و محیط‌های Azure تفاوت داشته باشد. مستندات همان نسخه را بررسی و Syntax را روی محیط Test اجرا کنید؛ از تعمیم رفتار نسخه جدید به سرور قدیمی پرهیز شود.

سؤالات مصاحبه

• چگونه تشخیص می‌دهید Natively Compiled Stored Procedure واقعاً روی Workload اثر گذاشته است؟
• اگر پس از اعمال Natively Compiled Stored Procedure وضعیت بدتر شد، ترتیب عیب‌یابی شما چیست؟
• چه تفاوتی میان تعریف کاتالوگی و وضعیت Runtime در Natively Compiled Stored Procedure وجود دارد؟
• برای جلوگیری از تغییرات ناخواسته در Natively Compiled Stored Procedure چه کنترل‌هایی می‌گذارید؟
• چه زمانی تصمیم می‌گیرید Natively Compiled Stored Procedure را حذف یا به روش دیگری مهاجرت دهید؟

چک‌لیست نهایی

1. Scope دقیق Natively Compiled Stored Procedure را ثبت کنید.
2. Permission و سازگاری نسخه را کنترل کنید.
3. Query پایه و Snapshot قبل از تغییر را ذخیره کنید.
4. ده مثال این مقاله را متناسب با نام اشیای واقعی بازنویسی کنید.
5. شاخص موفقیت و شرط Rollback را تعیین کنید.
6. پس از Apply، Metadata و Runtime را دوباره بررسی کنید.