بهینه‌سازی SQL Server برای APIهای پرترافیک

Q: اولین قدم برای پیدا کردن کوئری کند در SQL Server چیست؟

Query Store را روشن کنید و کوئریها را بر اساس کل CPU یا کل زمان اجرا مرتب کنید؛ از SQL Server 2022 برای پایگاهدادههای تازه بهطور پیشفرض روشن است. اگر در دسترس نیست، sys.dm_exec_query_stats آمار مشابهی را برای پلنهای داخل کش میدهد.

Q: آیا روشن کردن READ_COMMITTED_SNAPSHOT امن است؟

در بیشتر برنامههای OLTP انتخاب رایجی است و در Azure SQL Database پیشفرض است، اما معنای خواندن را عوض میکند و برای نسخههای ردیف فضا لازم دارد. پیش از روشن کردن آن، کدی را که به منتظر ماندن خواندن پشت قفل تکیه دارد بازبینی کنید.

Q: آیا AsNoTracking همیشه لازم است؟

فقط وقتی موجودیت کامل را برای خواندن برمیگردانید. اگر با Select به یک DTO پروجکشن کنید و هیچ موجودیتی در نتیجه نباشد، EF Core اصلاً چیزی را ردیابی نمیکند.

Q: کی از SqlBulkCopy بهجای SaveChanges استفاده کنم؟

وقتی هزاران ردیف را یکجا مینویسید، مثل دستههای رویداد. الگوی رایج این است که دسته را با SqlBulkCopy در یک جدول موقت بریزید و بعد با یک upsert مجموعهمحور در جدول اصلی ادغام کنید.

مهدی مولوی ۱۴ مهر ۱۴۰۵ ۱۳ دقیقه مطالعه

وقتی یک API در ساعت اوج کند می‌شود،‏ مقصر معمولاً چند کوئری مشخص است،‏ نه «کل پایگاه‌داده». این راهنما مسیر عملی از گزارش کندی تا اصلاح را مرور می‌کند: پیدا کردن کوئری‌های گران با Query Store،‏ خواندن پلن اجرایی،‏ ایندکس‌گذاری با حساب هزینهٔ نوشتن،‏ مهار parameter sniffing و قفل‌ها،‏ اصلاح الگوهای پرهزینهٔ EF Core و نوشتن دسته‌ای با SqlBulkCopy.

از «کند است» تا یک کوئری مشخص

«در اوج کند است» برای شروع کار کافی نیست؛‏ باید معلوم شود کدام اندپوینت،‏ زیر چه باری و با کدام کوئری. در «گزینه دو» پیش از اوج آزمون‌ها با Locust از APIهای پرتکرار تست بار می‌گرفتم تا گلوگاه‌ها زودتر پیدا شوند و بعد کوئری‌های SQL Server،‏ مسیرهای کش و همان APIها را بهینه می‌کردم. شرح آن کار در نمونه‌کار اوج آزمون‌ها آمده و طراحی سناریوی بار را در راهنمای تست بار با Locust نوشته‌ام. تست بار علامت را بازتولید می‌کند و ابزارهای خود SQL Server نشان می‌دهند زمان کجا خرج می‌شود.

ابزار اول Query Store است. از SQL Server 2016 وجود دارد و از SQL Server 2022 برای پایگاه‌داده‌های تازه به‌طور پیش‌فرض روشن است؛‏ در پایگاه‌داده‌ای که پیش‌تر ساخته شده ممکن است هنوز خاموش باشد. تاریخچهٔ کوئری‌ها،‏ پلن‌ها و آمار اجرا را داخل خود پایگاه‌داده نگه می‌دارد و با ری‌استارت پاک نمی‌شود. در گزارش Top Resource Consuming Queries در SSMS آمارهٔ Total را انتخاب کنید: کوئری چندمیلی‌ثانیه‌ای که ثانیه‌ای صدها بار اجرا می‌شود،‏ در جمع از یک کوئری چندثانیه‌ای ساعتی گران‌تر است.

اگر Query Store خاموش است،‏ sys.dm_exec_query_stats آمار تجمعی پلن‌های داخل کش را می‌دهد؛‏ با بیرون رفتن پلن از کش،‏ آمارش هم پاک می‌شود. خواندن آن از SQL Server 2022 مجوز VIEW SERVER PERFORMANCE STATE می‌خواهد (پیش‌تر VIEW SERVER STATE).

SELECT TOP (20)
    qs.execution_count,
    qs.total_worker_time / 1000                        AS total_cpu_ms,
    qs.total_worker_time / qs.execution_count / 1000.0 AS avg_cpu_ms,
    qs.total_logical_reads / qs.execution_count        AS avg_logical_reads,
    SUBSTRING(st.text, qs.statement_start_offset / 2 + 1,
        (CASE qs.statement_end_offset
             WHEN -1 THEN DATALENGTH(st.text)
             ELSE qs.statement_end_offset
         END - qs.statement_start_offset) / 2 + 1)     AS statement_text,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

زمان‌ها در این DMV به میکروثانیه‌اند و برای همین بر ۱۰۰۰ تقسیم شده‌اند. ستون avg_logical_reads برای مقایسهٔ قبل و بعد از اصلاح بهتر از زمان است،‏ چون کمتر به بار لحظه‌ای سرور وابسته است. پلنی که این کوئری برمی‌گرداند پلن داخل کش است و تعداد واقعی ردیف‌ها را ندارد.

خواندن پلن اجرایی

پلن واقعی را در SSMS با Include Actual Execution Plan یا SET STATISTICS XML ON بگیرید؛‏ SET STATISTICS IO ON هم خواندن منطقی هر جدول را نشان می‌دهد. کوئری ساختهٔ EF Core را از لاگ یا با ToQueryString() بردارید و با همان پارامترها از طریق sp_executesql اجرا کنید؛‏ متغیر محلی (مثل DECLAREهایی که ToQueryString() می‌نویسد) یا مقدار ثابت ممکن است پلن دیگری بسازد. ARITHABORT را هم یکی کنید: در SSMS پیش‌فرض روشن است و در اتصال برنامه خاموش،‏ و چون جزو کلید کش پلن است،‏ همان کوئری ممکن است در SSMS سریع و در برنامه کند باشد. در پلن سراغ این نشانه‌ها می‌روم:

Scan در برابر Seek. Seek مستقیم به بازهٔ لازم می‌رود و Scan کل ایندکس یا بخش بزرگی از آن را می‌خواند. Scan روی جدول بزرگ در مسیر داغ زنگ خطر است. دو علت رایجش با وجود ایندکس،‏ تابع روی ستون (YEAR(CreatedAt) = 2025) و تبدیل ضمنی نوع است. مثلاً مقایسهٔ ستون varchar با پارامتر nvarchar با collationهای SQL (مثل SQL_Latin1_General_CP1_CI_AS) جلوی Seek را می‌گیرد و با collationهای Windows معمولاً نه. EF Core نوع پارامتر را از نگاشت ستون می‌گیرد،‏ پس برای ستون varchar در مدل IsUnicode(false) بگذارید.
Key Lookup. ایندکس غیرخوشه‌ای ردیف را پیدا کرده،‏ اما بقیهٔ ستون‌ها را برای هر ردیف جداگانه از ایندکس خوشه‌ای می‌خواند؛‏ روی heap اسم همین عملگر RID Lookup است. برای چند ردیف ارزان است و برای هزاران ردیف نه.
ردیف تخمینی در برابر واقعی. فاصلهٔ زیاد یعنی پلن بر پایهٔ فرض غلط انتخاب شده است: آمار کهنه،‏ parameter sniffing یا شرطی که تخمینش سخت است. در شاخهٔ داخلی Nested Loops عدد تخمینی برای یک اجراست و عدد واقعی جمع همهٔ اجراها؛‏ پیش از مقایسه،‏ تخمین را در تعداد اجرا ضرب کنید (SSMS 18.5 به بعد این عدد را با نام Estimated Number of Rows for All Executions نشان می‌دهد).
هشدارها. مثلث زرد معمولاً یعنی ریختن Sort یا Hash به tempdb یا تبدیل ضمنی. پیشنهاد سبز Missing Index فقط سرنخ است: از تخمین یک کوئری آمده،‏ ترتیب ستون‌های کلید را مشخص نمی‌کند،‏ ایندکس فیلترشده پیشنهاد نمی‌دهد و ممکن است با ایندکس‌های موجود هم‌پوشانی داشته باشد.

ایندکس: پوشاننده،‏ فیلترشده و هزینه‌ای که روی نوشتن می‌افتد

ایندکس پوشاننده همهٔ ستون‌هایی را که کوئری لازم دارد در خودش دارد: ستون‌های جست‌وجو،‏ اتصال و مرتب‌سازی در کلید و بقیه در INCLUDE. ستون‌های INCLUDE فقط در سطح برگ ذخیره می‌شوند،‏ در سقف ۳۲ ستون و ۱٬۷۰۰ بایتِ کلید حساب نمی‌شوند و ترتیبشان روی کارایی اثری ندارد. نتیجه یک Seek بدون Key Lookup است.

-- Hot query (example):
-- SELECT QuestionId, ChoiceId, UpdatedAt FROM dbo.Answers WHERE AttemptId = @attemptId;
CREATE NONCLUSTERED INDEX IX_Answers_AttemptId
    ON dbo.Answers (AttemptId)
    INCLUDE (QuestionId, ChoiceId, UpdatedAt);

-- Only open attempts are searched often (Status 0 = InProgress)
CREATE NONCLUSTERED INDEX IX_Attempts_Open
    ON dbo.Attempts (ExamId, Deadline)
    WHERE Status = 0;

ایندکس اول کوئری نمونهٔ بالا را کامل می‌پوشاند. ایندکس دوم فیلترشده است و فقط جلسه‌های باز آزمون را نگه می‌دارد؛‏ کوچک‌تر است،‏ آمار دقیق‌تری دارد و فقط با تغییر همان زیرمجموعه به‌روز می‌شود. محدودیت‌هایش: فقط عملگرهای مقایسهٔ ساده را می‌پذیرد و LIKE را نه،‏ شرط کوئری باید زیرمجموعهٔ شرط ایندکس باشد و اگر مقدار فیلتر از پارامتر بیاید (Status = @status)،‏ بهینه‌ساز معمولاً از آن استفاده نمی‌کند،‏ چون پلن کش‌شده باید برای هر مقداری درست باشد (OPTION (RECOMPILE) این را حل می‌کند). در EF Core ثابتی که در خود عبارت LINQ نوشته شده،‏ مثل عضو یک enum،‏ به literal تبدیل می‌شود و متغیر به پارامتر. اتصالی هم که با QUOTED_IDENTIFIER یا ANSI_NULLS خاموش دادهٔ چنین ایندکسی را تغییر دهد،‏ خطا می‌گیرد.

اما ایندکس هزینه هم دارد. هر ایندکس غیرخوشه‌ای در هر INSERT و DELETE و در UPDATE ستون‌هایش به‌روز می‌شود،‏ با لاگ و قفل بیشتر. به گفتهٔ راهنمای طراحی ایندکس SQL Server،‏ ساختن ایندکس‌های زیاد «برای این‌که بهینه‌ساز گزینه داشته باشد» تغییر داده را کند می‌کند و می‌تواند مشکل هم‌زمانی بسازد. روی جدولی که در اوج پرنوشتن است،‏ مثل جدول پاسخ‌ها در یک آزمون،‏ این هزینه درست در بدترین لحظه پرداخت می‌شود. در sys.dm_db_index_usage_stats جمع user_seeks،‏ user_scans و user_lookups را با user_updates مقایسه کنید. این شمارنده‌ها با ری‌استارت صفر می‌شوند،‏ پس بازه‌ای را ببینید که دست‌کم یک اوج را در بر بگیرد. افزودن ستون INCLUDE به ایندکس موجود هم معمولاً از ساختن ایندکسی تقریباً تکراری بهتر است.

Parameter sniffing: وقتی یک پلن برای همه مناسب نیست

SQL Server کوئری پارامتردار را بار اول با مقدار همان اجرا کامپایل می‌کند و پلن را برای مقدارهای بعدی هم به کار می‌برد. با دادهٔ نامتوازن این کار دردسر می‌شود. برای نمونه،‏ اگر یک آزمون سراسری ده‌ها هزار شرکت‌کننده داشته باشد و هر آزمون کلاسی چند ده نفر،‏ پلنی که برای آزمون کوچک ساخته شده (Nested Loops و Key Lookup) روی آزمون بزرگ بسیار کند است. علامتش این است که کوئری گاهی سریع و گاهی کند است و رفتارش بعد از ری‌استارت یا به‌روزرسانی آمار ناگهان عوض می‌شود. Query Store چنین کوئری‌ای را با چند پلنِ بسیار متفاوت در گزارش Queries With High Variation یا Regressed Queries نشان می‌دهد.

مستندات Microsoft استفاده از hint را آخرین راه می‌داند؛‏ پس اول آمار و ایندکس‌ها را بررسی کنید. بعد این گزینه‌ها هست:

OPTION (RECOMPILE): برای هر اجرا پلن تازه‌ای می‌سازد و بعد دورش می‌ریزد. دقیق است،‏ اما هزینهٔ کامپایل را هر بار می‌پردازید؛‏ مناسب کوئری سنگین و کم‌تکرار است.
OPTIMIZE FOR: پلن را برای یک مقدار نماینده (OPTIMIZE FOR (@examId = 42)) یا بر اساس گزینش‌پذیری میانگین (OPTIMIZE FOR UNKNOWN) می‌سازد. پایدار است،‏ ولی برای مقدارهای دور از میانگین بهترین نیست.
اجبار پلن: sp_query_store_force_plan یکی از پلن‌هایی را که Query Store برای همان کوئری ثبت کرده ثابت می‌کند. با تغییر حجم و توزیع داده باید دوباره بررسی‌اش کرد.
Query Store hints (SQL Server 2022 به بعد): sys.sp_query_store_set_hints یک hint مثل OPTION(RECOMPILE) را بدون تغییر متن کوئری اعمال می‌کند؛‏ برای SQL ساختهٔ EF Core راه تمیزی است.
بهینه‌سازی PSP (SQL Server 2022 به بعد): Parameter Sensitive Plan در سطح سازگاری 160 پیش‌فرض روشن است. حداکثر سه شرط تساویِ پارامتردار روی دادهٔ نامتوازن را انتخاب می‌کند و بسته به مقدار پارامتر،‏ چند نسخهٔ کوئری با پلن جداگانه نگه می‌دارد. SQL Server 2025 در سطح 170 آن را به دستورهای DML هم گسترش داده است. پایگاه‌داده‌ای که restore یا attach شده سطح سازگاری قبلی‌اش را نگه می‌دارد،‏ پس ارتقای سرور به‌تنهایی کافی نیست.

قفل و انتظار: blocking و READ_COMMITTED_SNAPSHOT

گاهی CPU پایین است و درخواست‌ها باز هم کندند؛‏ یعنی کوئری‌ها منتظر چیزی‌اند. ستون‌های blocking_session_id و wait_type در sys.dm_exec_requests نشان می‌دهند کدام نشست منتظر کدام است و انتظارهای LCK_M_* یعنی قفل. Query Store هم از SQL Server 2017 آمار انتظار را به تفکیک کوئری نگه می‌دارد.

در SQL Server گزینهٔ READ_COMMITTED_SNAPSHOT به‌طور پیش‌فرض خاموش است و READ COMMITTED با قفل اشتراکی کار می‌کند؛‏ خواننده پشت نویسنده منتظر می‌ماند. با روشن کردن آن،‏ هر دستور آخرین دادهٔ commit شده تا شروع خودش را از نسخه‌های ردیف می‌خواند و خواننده و نویسنده یکدیگر را مسدود نمی‌کنند،‏ اما نویسنده‌ها همچنان یکدیگر را مسدود می‌کنند. در Azure SQL Database این گزینه پیش‌فرض روشن است. هنگام اجرای ALTER DATABASE … SET READ_COMMITTED_SNAPSHOT ON نباید اتصال فعال دیگری به پایگاه‌داده باشد،‏ هرچند حالت single-user لازم نیست.

هزینه‌اش این است: نسخه‌های ردیف در tempdb نگه داشته می‌شوند،‏ یا اگر Accelerated Database Recovery (از SQL Server 2019) روشن باشد در خود پایگاه‌داده،‏ و تراکنش طولانی آن‌ها را زنده نگه می‌دارد. معنای خواندن هم عوض می‌شود. کدی که مقداری مثل ظرفیت باقی‌مانده را می‌خواند و بر اساسش می‌نوشت،‏ دیگر پشت تغییرِ در جریان منتظر نمی‌ماند و نسخهٔ قبلی را می‌بیند. این الگو بدون RCSI هم امن نبود؛‏ آن را اتمی کنید (UPDATE … SET Remaining = Remaining - 1 WHERE Id = @id AND Remaining > 0 و بررسی تعداد ردیف‌های تغییرکرده) یا در خواندن UPDLOCK بگذارید. در هر حال تراکنش را کوتاه نگه دارید،‏ وسطش سراغ سرویس بیرونی نروید و ستون‌های شرط UPDATE و DELETE را ایندکس کنید.

EF Core روی مسیرهای داغ

سربار خود EF Core در بیشتر برنامه‌ها کم است؛‏ هزینهٔ اصلی از الگوهایی می‌آید که نوشتنشان راحت است. لاگ SQL را در محیط تست روشن کنید و تعداد کوئری‌های هر درخواست را بشمارید. بعد این‌ها را بررسی کنید:

N+1: lazy loading یا حلقه‌ای که برای هر آیتم کوئری می‌زند،‏ یک درخواست را به ده‌ها رفت‌وبرگشت تبدیل می‌کند. با Include یا پروجکشن،‏ دادهٔ مرتبط را در همان کوئری بیاورید.
پروجکشن با Select: فقط ستون‌های لازم را بخوانید تا داده کمتر شود و ایندکس پوشاننده ممکن شود. نتیجه‌ای که هیچ موجودیتی در آن نیست اصلاً ردیابی نمی‌شود.
AsNoTracking: برای موجودیت‌های کاملِ فقط‌خواندنی،‏ هزینهٔ change tracker را حذف می‌کند؛‏ اما چون identity resolution ندارد،‏ ردیفی که چند بار در نتیجه آمده چند نمونهٔ جدا می‌شود.
AsSplitQuery(): وقتی دو مجموعهٔ هم‌سطح را Include می‌کنید،‏ جلوی حاصل‌ضرب دکارتی را می‌گیرد،‏ اما رفت‌وبرگشت بیشتری دارد و بدون تراکنش snapshot یا serializable،‏ سازگاری داده بین کوئری‌ها تضمین نمی‌شود.
Compiled query و pooling: EF.CompileAsyncQuery و AddDbContextPool سربار EF را کم می‌کنند،‏ اما در برابر زمان شبکه و پایگاه‌داده بهبودشان کوچک است؛‏ فقط بعد از اندازه‌گیری سراغشان بروید.
پارامتر به‌جای ثابت: ساختن پویای عبارت با Expression.Constant برای هر مقدار SQL و پلن تازه می‌سازد. در EF Core 10 هم Contains روی یک لیست به‌طور پیش‌فرض به چند پارامتر جدا با padding ترجمه می‌شود تا تعداد پلن‌ها محدود بماند.

// Read path: only the needed columns; no entity in the result, so nothing is tracked
var answers = await db.Answers
    .TagWith("GET /attempts/{id}/answers")
    .Where(a => a.AttemptId == attemptId)
    .Select(a => new AnswerDto(a.QuestionId, a.ChoiceId, a.UpdatedAt))
    .ToListAsync(ct);

// One UPDATE statement (EF Core 7+), no entities loaded
var now = DateTime.UtcNow;
int expired = await db.Attempts
    .Where(a => a.ExamId == examId
             && a.Status == AttemptStatus.InProgress
             && a.Deadline < now)
    .ExecuteUpdateAsync(s => s
        .SetProperty(a => a.Status, AttemptStatus.Expired)
        .SetProperty(a => a.ClosedAt, now), ct);

کوئری اول با TagWith توضیحی به ابتدای SQL می‌افزاید که در لاگ و در متن کامل batch در sys.dm_exec_sql_text دیده می‌شود. نتیجه DTO است،‏ پس چیزی ردیابی نمی‌شود و ایندکس IX_Answers_AttemptId سه ستونش را می‌پوشاند. دستور دوم با ExecuteUpdateAsync (از EF Core 7) یک UPDATE واحد می‌فرستد و موجودیتی بار نمی‌کند. شرط وضعیت ثابت است و literal می‌شود،‏ پس اگر مقدار InProgress برابر 0 باشد،‏ بهینه‌ساز می‌تواند از ایندکس فیلترشدهٔ بالا استفاده کند. این متد change tracker را دور می‌زند: موجودیت‌های بارشده در همان context به‌روز نمی‌شوند و خودش هم تراکنشی باز نمی‌کند؛‏ پس آن را با SaveChanges روی همان ردیف‌ها مخلوط نکنید.

نوشتن مجموعه‌محور و SqlBulkCopy

INSERT تک‌تک در حلقه یعنی یک رفت‌وبرگشت و یک تراکنش autocommit برای هر ردیف،‏ و با durability کامل که پیش‌فرض است،‏ هر commit منتظر نوشته شدن لاگ می‌ماند. SaveChanges دستورها را دسته‌ای می‌فرستد،‏ اما برای هزاران ردیف SqlBulkCopy مناسب‌تر است که داده را از DataTable یا IDataReader با پروتکل bulk copy در جدول می‌ریزد. الگوی من این است: دسته را در جدول موقت می‌ریزم و با یک دستور مجموعه‌محور در جدول اصلی ادغام می‌کنم.

در خط پردازش زمان تماشای یک پلتفرم بزرگ آموزش آنلاین فارسی‌زبان،‏ مسیر درخواستِ سرویس دریافت (یک Minimal API روی .NET 10) فقط رویداد را در کانالی حافظه‌ای با ظرفیت محدود می‌گذارد و 202 برمی‌گرداند،‏ یا اگر کانال پر باشد 503. یک سرویس پس‌زمینه دسته‌ها را در جدول‌های موقت می‌ریزد و upsert مجموعه‌محور را در یک تراکنش اجرا می‌کند؛‏ نتیجه‌های تحلیل هم با SqlBulkCopy روی اتصالی با MARS خاموش نوشته می‌شوند. در بنچمارک Locust روی یک ایستگاه کاری ۱۲ هسته‌ای،‏ با ۱۰٬۰۰۰ رویداد و ۵۰ کاربر هم‌زمان،‏ سرویس حدود ۲٬۴۵۰ درخواست در ثانیه را با میانهٔ ۱۳ و صدک ۹۵ برابر ۲۱ میلی‌ثانیه پذیرفت و حافظه‌اش در اوج حدود ۱۰۸ مگابایت بود. این عددها مسیر دریافت را می‌سنجند و نکتهٔ طراحی همین است که درخواست منتظر SQL Server نمی‌ماند؛‏ جزئیات در یادداشت زمان تماشای واقعی آمده است. نمونهٔ کلی این الگو:

const string UpsertSql = """
    UPDATE t SET t.Seconds = t.Seconds + b.Seconds
    FROM dbo.WatchTotals AS t WITH (UPDLOCK, HOLDLOCK)
    JOIN #Batch AS b ON b.UserId = t.UserId AND b.LessonId = t.LessonId;

    INSERT dbo.WatchTotals (UserId, LessonId, Seconds)
    SELECT b.UserId, b.LessonId, b.Seconds FROM #Batch AS b
    WHERE NOT EXISTS (SELECT 1 FROM dbo.WatchTotals AS t WITH (UPDLOCK, HOLDLOCK)
                      WHERE t.UserId = b.UserId AND t.LessonId = b.LessonId);
    """;

await using var conn = new SqlConnection(connectionString); // MARS is off by default
await conn.OpenAsync(ct);
await using var tx = (SqlTransaction)await conn.BeginTransactionAsync(ct);

await using (var create = new SqlCommand(
    "CREATE TABLE #Batch (UserId int NOT NULL, LessonId int NOT NULL, Seconds int NOT NULL);",
    conn, tx))
    await create.ExecuteNonQueryAsync(ct);

using (var bulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tx))
{
    bulk.DestinationTableName = "#Batch";
    await bulk.WriteToServerAsync(batch, ct); // DataTable or IDataReader, one row per key
}

await using (var upsert = new SqlCommand(UpsertSql, conn, tx))
    await upsert.ExecuteNonQueryAsync(ct);

await tx.CommitAsync(ct);

همه‌چیز روی یک اتصال است،‏ چون #Batch فقط در همان session دیده می‌شود،‏ و در یک تراکنش که به SqlBulkCopy هم داده شده (بدون آن خطا می‌دهد)،‏ تا دسته یا کامل اعمال شود یا اصلاً. فرض این است که دسته برای هر کلید یک ردیف دارد و (UserId, LessonId) کلید یکتاست. UPDLOCK, HOLDLOCK برای نویسنده‌های هم‌زمان است: روی کلیدهای ناموجود هم قفل بازه‌ای می‌گیرد تا دو تراکنش یک کلید تازه را با هم درج نکنند و افزایشِ یکی گم نشود؛‏ در این حالت deadlock هم ممکن است و تراکنش قربانی را باید تکرار کرد. با یک نویسندهٔ تک‌نمونه رقابتی در کار نیست. MARS در SqlClient پیش‌فرض خاموش است و این کد به آن نیازی ندارد.

ترتیب کار در یک جمله: بازتولید با تست بار،‏ پیدا کردن کوئری گران،‏ خواندن پلن واقعی،‏ اصلاح ایندکس یا کوئری،‏ بررسی parameter sniffing و انتظارها و در آخر اجرای دوبارهٔ همان سناریوی بار. اگر API شما در اوج کند می‌شود و یک نگاه دوم لازم دارید،‏ در صفحهٔ همکاری توضیح داده‌ام چه کمکی از من برمی‌آید.

پرسش‌های رایج

اولین قدم برای پیدا کردن کوئری کند در SQL Server چیست؟

Query Store را روشن کنید و کوئری‌ها را بر اساس کل CPU یا کل زمان اجرا مرتب کنید؛‏ از SQL Server 2022 برای پایگاه‌داده‌های تازه به‌طور پیش‌فرض روشن است. اگر در دسترس نیست،‏ sys.dm_exec_query_stats آمار مشابهی را برای پلن‌های داخل کش می‌دهد.

parameter sniffing چیست؟

SQL Server پلن کوئری پارامتردار را با مقدار پارامترِ اولین اجرا می‌سازد و برای مقدارهای بعدی هم به کار می‌برد. اگر توزیع داده نامتوازن باشد،‏ همین پلن برای بعضی مقدارها بسیار کند است. راه‌حل‌ها از OPTION (RECOMPILE) و OPTIMIZE FOR تا اجبار پلن در Query Store و بهینه‌سازی PSP در SQL Server 2022 به بعد را در بر می‌گیرد.

آیا روشن کردن READ_COMMITTED_SNAPSHOT امن است؟

در بیشتر برنامه‌های OLTP انتخاب رایجی است و در Azure SQL Database پیش‌فرض است،‏ اما معنای خواندن را عوض می‌کند و برای نسخه‌های ردیف فضا لازم دارد. پیش از روشن کردن آن،‏ کدی را که به منتظر ماندن خواندن پشت قفل تکیه دارد بازبینی کنید.

آیا AsNoTracking همیشه لازم است؟

فقط وقتی موجودیت کامل را برای خواندن برمی‌گردانید. اگر با Select به یک DTO پروجکشن کنید و هیچ موجودیتی در نتیجه نباشد،‏ EF Core اصلاً چیزی را ردیابی نمی‌کند.

کی از SqlBulkCopy به‌جای SaveChanges استفاده کنم؟

وقتی هزاران ردیف را یک‌جا می‌نویسید،‏ مثل دسته‌های رویداد. الگوی رایج این است که دسته را با SqlBulkCopy در یک جدول موقت بریزید و بعد با یک upsert مجموعه‌محور در جدول اصلی ادغام کنید.

در همین بخش

راهنمای بک‌اند