SQL Anti Patterns, SQL Performance Tuning, Query Store Optimization, SQL Execution Plan, Parameter Sniffing Fix, Index Tuning, SQL Server Performance, تحلیل پلن اجرا, آنتی پترن SQL, رفع کندی SQL, بهینه سازی کوئری SQL, SQL Server Tuning

در دنیای SQL Server، همه ما یک واقعیت را می‌دانیم: مشکلِ کندی سیستم معمولاً از خود Query نیست، از اجرای آن است. یعنی Query Execution Plan.

در بسیاری از سازمان‌ها کدها ثابت‌اند، سیستم‌ها حساس‌اند و امکان دست‌زدن به Query وجود ندارد. اما خوشبختانه SQL Server مثل یک «دوربین حرارتی» رفتار می‌کند: کافی است Query Plan را بخوانید، آن‌وقت تمام الگوهای پنهان، اشتباهات طراحی، نقاط گلوگاهی و Anti-Patternها را می‌بینید—بدون اینکه حتی یک کاما در کد تغییر کند.

این مقاله یک راهنمای کامل است برای اینکه چطور فقط با تحلیل Query Plans، مشکلات پنهان را استخراج کنید، Bottleneckها را تشخیص دهید و کارایی را بدون هیچ تغییری در Application Code افزایش دهید. آن‌ هم با ساختار استاندارد لاندا، کامل، عمیق، کاربردی و مناسب DBAها، مهندسان عملکرد، DevOps و تیم‌های BI.

چرا تحلیل Query Plan مهم‌ترین مهارت Performance است؟

سه دلیل اصلی وجود دارد:

  • ۹۰٪ کندی‌های SQL “کُدی” نیستند بلکه «اجرایی» هستند.
  • Query Plan تنها جایی است که رفتار واقعی Optimizer را نشان می‌دهد.
  • با تحلیل درست، می‌توان مشکل را رفع کرد بدون آنکه حتی Query را لمس کنیم.

تقریباً همه Anti-Patternهای مهم، از Parameter Sniffing تا Missing Index و Spoolهای خطرناک—در Plan قابل‌مشاهده‌اند، ولی فقط اگر بلد باشید آنها را بخوانید.

اصلی‌ترین علائم هشدار در Query Plan

(نشانه‌هایی که اگر دیدید، باید بدانید یک Anti-Pattern پشتش پنهان است)

Key Lookupهای پرتکرار

اگر در Execution Plan دیدید:

Clustered Index Seek → Key Lookup → Nested Loop

این یعنی: هر رکوردی که از Index خوانده می‌شود باید دوباره از جدول اصلی Fetch شود.

نشانه‌های خطر:

  • Lookup با Row Count بالا
  • هزینه تجمعی بالای Nested Loop
  • تکرار Lookup در چند Operator متوالی

چرا بد است؟ چون باعث I/O انفجاری، Page Split و افزایش Latency می‌شود.

بدون تغییر Query چطور حل شود؟

  • اضافه کردن Covering Index
  • اضافه کردن Included Columns
  • جابه‌جایی Index روی یک Column با Selectivity بهتر
  • تغییر Fill Factor روی Indexهای پرترافیک

Missing Index + High Cost Scan

وقتی SQL Server در Plan نوشته باشد:

Missing Index (Impact: 87%)

و کنار آن:

Index Scan / Table Scan

این یعنی Optimizer مجبور شده کل جدول را اسکن کند چون Index مناسب وجود ندارد.

بدون تغییر Query:

  • ایجاد Index بر اساس پیشنهاد SQL پس از بررسی و صحت سنجی
  • Merge کردن Indexهای تکراری
  • اضافه کردن Filtered Index برای Workload واقعی

Parameter Sniffing

مشهورترین Anti-Pattern دنیا.

اگر این علائم را دیدید:

  • Query گاهی سریع، گاهی بسیار کند
  • یک Plan Cache شده برای همه کاربرها استفاده می‌شود
  • در Plan مقدار پارامتر (Parameter List) خیلی کوچک‌تر یا بزرگ‌تر از داده واقعی است
  • در Actual Rows vs Estimated Rows تفاوت فاحش باشد

بدون تغییر Query چگونه حل می‌شود؟

  • Recompile دستی روی SP
  • ایجاد Optimize for Unknown از طریق Plan Guide
  • استفاده از Forced Parameterization
  • جداسازی Workload با Resource Governor
  • ایزوله‌سازی دسترسی‌ها و افزایش Refresh آمارها

Estimate اشتباه = نشانه مستقیم Anti-Pattern

اگر تخمین (Estimated Rows) با مقدار واقعی (Actual Rows) اختلاف زیادی دارد، معنایش این است:

  • آمارها قدیمی‌اند
  • توزیع داده واقعی با آنچه Optimizer فکر می‌کند متفاوت است
  • Query Plan بر اساس اطلاعات کاملاً اشتباه ساخته شده

بدون تغییر Query:

  • Update Statistics با FULLSCAN
  • فعال‌سازی Auto Update Statistics Async
  • ایجاد Statistics دستی روی ستون‌های پرتکرار
  • بازطراحی Partitioning برای بهبود Selectivity

Spoolهای سنگین: Lazy Spool / Eager Spool

Spool یعنی SQL Server یک نسخه موقتی از داده تولید می‌کند چون نمی‌تواند Plan بهتری پیدا کند.

اگر دیدید:

  • Eager Spool با هزینه سنگین
  • Spool داخل Loop
  • چند Spool پشت سر هم

بدانید یک Anti-Pattern مهم رخ داده.

بدون تغییر Query:

  • Index مناسب روی ستون‌های Join
  • تبدیل Spool به Seek با اصلاح Statistics
  • جلوگیری از تکرار اسکن‌ها با ایجاد Index Covering
  • بهبود Cardinality Estimation

Hash Match سنگین روی Workload OLTP

اگر در Plan از Hash Match استفاده شده و اندازه داده زیاد است، به معنی این است که:

  • Join بر اساس Index مناسب انجام نشده
  • ساخت Hash Table باعث افزایش CPU و TempDB می‌شود
  • Query به صورت Batch اجرا می‌شود و Latency بالا می‌رود

مخصوصاً اگر Memory Grant بالا باشد.

بدون تغییر Query:

  • طراحی Index مخصوص Join
  • فعال کردن Adaptive Join در نسخه‌های جدید
  • کاهش اندازه Memory Grant با تنظیمات Resource Governor
  • استفاده از Join Hints از طریق Plan Guide

Memory Grantهای غیرعادی

اگر حباب زرد روی Operator دیدید که می‌گوید:

Warning: Operator used tempdb to spill data

این یعنی Memory Grant درست محاسبه نشده.

علائم:

  • Spill به TempDB
  • Sort Warning
  • Hash Warning

بهترین راهکار بدون تغییر Query:

  • افزایش Max Memory SQL با روش اصولی
  • تنظیم Min Grant Percent برای Workload
  • بازطراحی Index برای کاهش Sort
  • آپدیت Statistics برای تخمین صحیح

Nested Loopهای غیرعادی

اگر Nested Loop دارید که:

  • طرف داخلش Scan دارد
  • تعداد Iteration بالا است
  • Estimated vs Actual تفاوت شدید دارد

این یک Anti-Pattern کلاسیک است.

راه‌حل بدون تغییر Query:

  • تبدیل Nested Loop به Merge Join با Index مناسب
  • بهبود Selectivity با طراحی Index
  • کاهش اندازه Dataset با Partitioning

Anti-Patternهای پنهان که معمولاً کشف نمی‌شوند.

مواردی که بیشتر DBAها نمی‌بینند.

۱. Bookmark Lookupهای تو در تو

اینجا SQL Server چند Lookup را پشت هم انجام می‌دهد.
خطرناک است زیرا CPU خالی می‌شود اما I/O می‌ترکد.

۲. Scalar UDF در Select

اگر دیدید:

Compute Scalar (Contains: T-SQL UDF)

یعنی Performance سقوط کرده.

بدون تغییر Query:

  • اجرای UDF Inlining در نسخه‌های جدید
  • ایجاد Computed Column تا SQL خودش استفاده کند
  • ساخت Index روی Computed Column

۳. Table Spool داخل Cursor

یکی از بدترین Anti-Patternها که تقریباً همیشه دیده می‌شود.

بدون تغییر Query:

  • تبدیل Cursor به Set-Based با Plan Guide
  • افزایش Concurrent Workers
  • ایجاد Index مناسب روی داده‌های درون Cursor

۴. Expensive Remote Query

اگر Query به Linked Server زده شده و در Plan دیدید:

Remote Query
Remote Scan

بدانید Bottleneck بیرون از SQL Server است.

بدون تغییر Query:

  • فعال‌سازی Delegation
  • ایجاد Index مناسب در سرور مقصد
  • انتقال بار Query به مبدأ با OPENQUERY بهینه‌شده

۵. Compute Scalarهای غیرعادی

Compute Scalar فقط ظاهرش بی‌ضرر است.

اما اگر:

  • Persistent ناشده باشد.
  • قرار باشد روی کل جدول اعمال شود.
  • ستون تبدیل نوع (CAST/CONVERT) داشته باشد.

به شدت CPU مصرف می‌کند.

بدون تغییر Query:

  • ایجاد Computed Column
  • ایجاد Index روی آن ستون
  • استفاده از Persisted برای جلوگیری از محاسبه دوباره

روش‌های تشخیص سریع Anti-Patternها—even Under Load

لاندا همیشه تأکید می‌کند: تحلیل Query Plan باید عملیاتی باشد، نه تئوری.

برای همین ۳ تکنیک حرفه‌ای را معرفی می‌کنیم:

۱. Actual Plan تحت فشار

در این حالت:

  • Memory
  • Spill
  • Concurrency
  • Waiting Tasks

واقعی و قابل اعتماد هستند.

۲. Live Query Statistics

بهترین ابزار لحظه‌ای برای مشاهده:

  • جریان داده
  • مسیر Join
  • توقف‌های ناگهانی
  • Locking و Latch

بدون کاهش بار سیستم.

۳. Query Store

برای تصویب اینکه آیا یک Anti-Pattern موقتی یا دائمی است، Query Store بهترین ابزار است.

به‌خصوص برای:

  • Plan Regression
  • تغییر رفتار Optimizer
  • Parameter Sniffing
  • تغییر Cardinality Estimator

روش اصلاح Anti-Patternها بدون تغییر Query

این بخش مهم‌ترین قسمت مقاله است.

شما می‌توانید مشکلات Performance را بدون دست‌زدن به کد حل کنید:

Index Tuning

  • ایجاد Indexهای Covering
  • حذف Indexهای بلااستفاده
  • Merge Indexها
  • اضافه کردن Included Columns
  • ایجاد Filtered Index

Statistics Engineering

  • Fullscan روی جداول کلیدی
  • ایجاد Statistics روی ترکیب ستون‌ها
  • فعال‌سازی Async Update Statistics
  • جداکردن آمار برای Partitionهای مختلف

Plan Guide

قدرتمندترین ابزار برای اصلاح رفتار Query بدون تغییر کد.

کاربردها:

  • Force Join Type
  • Optimize for Unknown
  • Fixed Parameter Value
  • Force Recompile
  • تنظیم Hintهای خاص

Query Store + Plan Forcing

بدون دست‌زدن به Query می‌توان بهترین Plan را Force کرد.

Memory Grant Tuning

  • کاهش Spill
  • تنظیم Resource Governor
  • کاهش Sort Cost
  • کنترل مصرف TempDB

طراحی Index برای Join

ساخت Index روی:

  • ستون Join
  • ستون Filter
  • ستون مرتب‌سازی

باعث حذف Spool، Hash Match و Nested Loop سنگین می‌شود.

مراقبت از TempDB

اگر در Plan Spill می‌بینید:

  • TempDB را روی SSD ببرید
  • Trace Flag 1117/1118 برای نسخه قدیمی
  • چند فایل TempDB
  • اندازه مناسب و Autogrowth اصولی

Anti-Patternهایی که ۹۹٪ DBAها نمی‌بینند.

لاندا همیشه سراغ مواردی می‌رود که دیگران نمی‌گویند.

موارد زیر معمولاً در گزارش‌های معمولی دیده نمی‌شوند اما در Plan کاملاً قابل تشخیص‌اند:

  • Hash Match Rehash
  • Nested Loop غیر Selective
  • Merge Join با Sortهای سنگین
  • Non-SARGable Expressions
  • فیلتر شدن روی ستون بدون Index
  • داینامیک بودن Plan و استفاده از Sniffing
  • Memory Grant بسیار کوچک‌تر از نیاز واقعی
  • استفاده از UDFهایی که قابل Inline نیستند
  • تبدیل نوع مخفی در Compute Scalar
  • انباشته شدن Deadlocks به دلیل سوءاستفاده Nested Loop

نتیجه‌گیری

تحلیل Query Plan تنها ابزار SQL Server نیست، نقشه کامل رفتار واقعی Optimizer است.
اگر آن را درست بخوانی، تمام Anti-Patternهای پنهان را می‌بینی؛ بدون اینکه نیاز باشد Query را تغییر بدهی.

این مقاله یک Playbook عملی LANDA بود برای:

  • تشخیص مشکلات واقعی Performance
  • رفع Bottleneck بدون دست‌زدن به کد
  • بهبود Query Plans
  • کاهش هزینه CPU / I/O / TempDB
  • افزایش سرعت سرویس‌های سازمانی

سوالات متداول (FAQ)

  1. آیا همیشه می‌توان بدون تغییر Query مشکلات را رفع کرد؟
    بله، در ۷۰٪ موارد. بقیه موارد نیاز به Refactor دارند.
  2.  برای Queryهای حیاتی بهترین نقطه شروع چیست؟
    Actual Plan + Query Store.
  3. آیا Index Tuning می‌تواند Parameter Sniffing را حل کند؟
    در بسیاری موارد بله، چون Selectivity را پیش‌بینی‌پذیر می‌کند.
  4. آیا Plan Guide خطرناک است؟
    اگر اصولی انجام شود نه؛ اما باید Version Control داشته باشد.
پیشنهاد مطالعه:
تماس و مشاوره با لاندا 

اگر سازمان شما هم Queryهای کند، Bottleneckهای ناشناخته، Reportهای سنگین یا رفتار غیرقابل پیش‌بینی SQL Server دارد، لاندا می‌تواند کل Query Planهای شما را مهندسی، تحلیل و اصلاح کند، بدون نیاز به تغییر کدهای اپلیکیشن.

خدمات تخصصی Performance Engineering برای SQL Server، شامل:

  • تحلیل کامل Query Store
  • کشف Anti-Patternهای پنهان
  • طراحی Index سازمانی
  • رفع مشکلات Parameter Sniffing
  • اصلاح Plan بدون تغییر Query
  • بهینه‌سازی سرویس‌های گزارش‌گیری، ERP و OLTP

برای شروع همکاری، با لاندا تماس ✆ بگیرید و ارزیابی فنی اولیه را رایگان دریافت کنید.

روزبه امیرعصامی

مدیریت پایگاه‌داده (DBA)

SQL Serverاسکیوال سرورایندکسباز طراحی دیتابیسبهینه سازی کوئریپایگاه داده

Xلینکدینواتساپتلگرامایمیل

نوشته های مرتبط

نظری داده نشده

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *