و سپس «افزودن به صفحه اصلی» ضربه بزنید
و سپس «افزودن به صفحه اصلی» ضربه بزنید
اگر با SQL Server سر و کار دارید، احتمالاً بارها با کوئریهایی مواجه شدهاید که «بیدلیل» کندند، CPU میخورند یا TempDB را منفجر میکنند. اما واقعیت این است: بیشتر مشکلات Performance در SQL Server نتیجه یک Anti-Pattern ساده در Query Plan هستند.
وقتی Execution Plan را درست بخوانید، رفتار موتور را دقیقاً جلوی چشم میبینید:
- کجا Scan زده؟
- کجا Nested Loop بیخود ایجاد کرده؟
- کجا Sort بیمورد انجام شده؟
- کجا Hash Spill خورده؟
در این مقاله یک رویکرد کاملاً عملی، انسانی و منطبق بر تجربیات پروژهها ارائه میکنیم برای اینکه فقط با نگاه به Execution Plan، Anti-Patternهای مهم را تشخیص دهید و بهینهترین راهحل را انتخاب کنید.
چرا Anti-Patternها اتفاق میافتند؟
تجربه ما در لاندا نشان میدهد که بیش از ۷۰٪ مشکلات عملکردی نتیجه این سه عامل است:
- طراحی نادرست ایندکسها
- Join/Predicate غیربهینه
- استفاده اشتباه از توابع یا فیلترهای Non-SARGable
بنابراین بهجای تغییر سرور یا افزایش منابع، کافی است Plan را درست بخوانید و آن Anti-Pattern مخفی را شکار کنید.
۱. Missing Index + Scanهای بزرگ
نشانههای Plan
- Scan روی جدولهای بزرگ
- Estimated Number of Rows بسیار بالا
- رنگ زرد “Missing Index Suggestion”
چطور تشخیص دهیم؟
اگر Logical Reads زیاد است یا Scan در مسیر Query وجود دارد، Anti-Pattern روشن است.
راهحلها
- ایجاد Index بر اساس ستونهای Filter و Join
- کاهش تعداد ستونهای برگشتی (Narrow Index)
- جدا کردن ستونهای Non-SARGable
۲. Key Lookup زیاد (RID Lookup)
یکی از رایجترین Anti-Patternها در پروژههای سازمانی.
نشانهها
- تعداد «Lookup» بسیار زیاد
- Estimated Cost بالا
- Bookmark Lookup روی هر ردیف
چرا خطرناک است؟
چون باعث I/O تصاعدی میشود.
راهحلها
- تبدیل ایندکس به Covering Index
- کاهش ستونهای انتخابی
- دوباره طراحی ایندکس بر اساس Query Patterns واقعی
۳. Sort پرهزینه و Spoolهای ناخواسته
نشانهها
- Sort با درصد هزینه بالا
- Spool در مسیر Nested Loop
- Memory Grant بیش از حد
چرا خرابکار است؟
Sort وقتی حافظه کم بیاورد، Spill به TempDB ایجاد میکند.
راهحل
- ایندکسهایی که مستقیماً Sorted باشند (Order Supported)
- حذف ORDER BYهای غیرضروری
- افزایش Max Memory Grant (در برخی سناریوهای خاص)
۴. Join نامناسب: Nested Loop vs Hash vs Merge
سه نشانه کلیدی Anti-Pattern:
Nested Loop اشتباهی
- یک جدول کوچک در برابر یک جدول بسیار بزرگ
- Cardinality Estimate اشتباه
- میلیونها Iteration
راهحل
تغییر Join یا افزودن ایندکس مناسب کلید Join.
Hash Join ناخواسته
- Hash Build گران روی جدول بزرگ
- Memory Spill
راهحل
ایندکس مناسب روی کلید Join برای تبدیل Hash به Merge/Nested Loop بهینه.
۵. Non-SARGable Predicates، قاتل پنهان
به موارد زیر دقت کنید، همه Anti-Pattern هستند:
WHERE CONVERT(VARCHAR,DateCol) = '2023-10-01'
WHERE YEAR(DateCol) = 2023
WHERE ISNULL(Col,0) = 10
WHERE Col + 1 = 5
چرا بد است؟
چون باعث میشود Index قابل استفاده نباشد → Scan.
راهحل
- Rewrite شرطها
- اصلاح نوع داده
- جایگزینی توابع با محاسبات SARGable
۶. Parameter Sniffing در Query Plan
نشانهها
- سرعت بسیار متفاوت بر اساس پارامتر
- Plan Cache که Plan بد را برای همه Queryها «اجبار» میکند.
راهحل
- OPTION (RECOMPILE) برای Queryهای خاص
- استفاده از OPTIMIZE FOR
- طراحی مجدد ایندکسها برای الگوهای مختلف
۷. Cardinality Estimate اشتباه
بدترین Anti-Pattern زمانی اتفاق میافتد که موتور تصور غلطی از تعداد رکوردها دارد.
دلایل
- آمار قدیمی (Outdated Statistics)
- تغییرات زیاد بدون آپدیت Statistics
- فیلترهای غیردقیق، UDFها یا پارامترهای پویا
راهحلها
- UPDATE STATISTICS با FULLSCAN
- استفاده از Trace Flag 9481 در برخی سناریوها
- بازنویسی Query برای شفافسازی فیلترها
الگوریتم سریع تشخیص Anti-Pattern (۴۵ ثانیهای لاندا)
برای تیم لاندا یک الگوریتم عملی وجود دارد که همیشه جواب میدهد:
۱. آیا Scan غیرضروری وجود دارد؟
نقص ایندکس یا Non-SARGable
۲. آیا Lookup زیاد است؟
ایندکس Covering
۳. آیا Sort / Hash با Spill وجود دارد؟
Memory Pressure یا Join اشتباه
۴. آیا Cardinality Estimate صفر یا خیلی کم/زیاد است؟
Statistics مشکل دارد
۵. آیا Nested Loop میلیونها بار تکرار شده؟
Join غلط یا Estimate بیدقت
به همین سادگی.
کاهش اجرای Query از ۱۸ ثانیه → ۹۰۰ میلیثانیه
مشکل: Key Lookup + Scan بزرگ + Estimate اشتباه
راهحل: ایجاد Covering Index + Update Statistics
نتیجه:
- ۹۰٪ کاهش I/O
- افزایش سرعت ۲۰ برابر
- حذف کامل TempDB Spill
نتیجهگیری
تشخیص Anti-Pattern در Execution Plan یک مهارت حیاتی در SQL Performance است.
اگر Plan را «درست» بخوانید، ۸۰٪ مشکلات عملاً بدون تغییر سختافزار و بدون Downtime حل میشوند.
کافی است بتوانید:
- ✔ Scan را از Seek تشخیص دهید.
- Lookup را حذف کنید.
- Join درست انتخاب کنید.
- Predicateها را SARGable نگه دارید.
اگر میخواهید تحلیل کامل Plan سرور شما انجام شود، تیم لاندا برای این کار آماده است.
سوالات متداول (FAQ)
۱. آیا همیشه باید پیشنهادهای Missing Index را اعمال کنیم؟
خیر، اغلب موارد پیشنهاد ناقص است. باید با الگوی Query و حجم داده تطبیق داده شود.
۲. بهترین ابزار برای تحلیل Anti-Pattern چیست؟
Execution Plan Viewer، Actual Plan، Query Store و DMVs مثل sys.dm_exec_query_stats.
۳. Parameter Sniffing چطور حل میشود؟
با OPTION(RECOMPILE)، با OPTIMIZE FOR یا با طراحی صحیح ایندکسها.
۴. آیا Statistics واقعاً روی عملکرد تأثیر دارد؟
بله، بهبود Statistics در بسیاری از پروژهها منجر به کاهش ۵۰٪–۹۵٪ هزینه CPU شده است.
تماس و مشاوره، درخواست تحلیل Query Plan توسط لاندا
اگر Queryهای شما کند هستند، TempDB زیر فشار است یا درصد CPU سرور بالا میرود:
برای درخواست تحلیل کامل Query Plan و ایندکسها توسط لاندا با کارشناسان ما تماس ✆ بگیرید.
در لاندا الگوهای واقعی سرویس شما را بررسی و Anti-Patternهای مخفی را استخراج و اصلاح میکنیم.
نظری داده نشده