بهینه سازی Query در SQL Server ؛ راهنمای جامع Query Performance Tuning

مقدمه :

یکی از مهم ترین موضوعات در مدیریت و نگهداری SQL Server ، بهینه سازی عملکرد Query ها ( Query Performance Tuning ) است . زمانی که یک Query با سرعت مناسبی اجرا نمی شود ، معمولاٌ نمی توان با یک تغییر ساده مشکل را برطرف کرد . در SQL Server دکمه ای با عنوان « اجرای سریع تر Query » وجود ندارد و برای رسیدن به Performance مناسب باید ابتدا علت اصلی مشکل شناسایی و سپس راهکار مناسب اعمال و نتیجه آن اندازه گیری شود .

بهینه سازی Performance فقط به تغییر Query یا ایجاد Index محدود نمی شود . عمواملی مانند CPU ، حافظه ، Disk I/O ، Network ، تنظیمات SQL Server ، طراحی Database ، برنامه کاربردی و حتی زیرساخت Cloud یا Virtual Machine نیز می توانند روی عملکرد سیستم تاثیر داشته باشند . بنابر این Query Performance Tuning یک فرآیند مرحله ای و تکرار شونده است که باید بر اساس اندازه گیری واقعی انجام شود .

Query Performance Tuning چیست ؟

Query Performance Tuning به مجموعه اقداماتی گفته می شود که با هدف بهبود زمان اجرا و کاهش مصرف منابع Query ها انجام می شود .

در این فرآیند ابتدا باید مشخص کنیم :

• کدام Query مشکل دارد ؟
• مشکل Query دقیقاٌ چیست ؟
• کدام Resource باعث ایجاد Bottleneck شده است ؟
• آیا مشکل از Query است یا از زیرساخت ؟
• آیا Execution Plan مناسب است ؟
• آیا Statistics به روز هستتند ؟
• آیا Index مناسبی وجود دارد ؟
• آیا Blocking یا Deadlock باعث کندی شده است ؟
• آیا Query بیش از حد Resource مصرف می کند ؟

بعد از شناسایی مشکل ، یک تغییر مشخص اعمال می شود و نتیجه آن با وضعیت قبلی مقایسه می شود .

چرا بهینه سازی Query همیشه ساده نیست ؟

گاهی تصور می شود اگر یک Query کند است ، کافی است یک Index ایجاد کنیم یا Query را تغییر دهیم . اما در عمل Performance یک سیستم Database می تواند تحت تأثیر عوامل مختلفی قرار داشته باشد .

برا مثال مشکل ممکن است مربوط به یکی از موارد زیر باشد :

• CPU
• Memory
• Disk I/O
• Network
• SQL Server Configuration
• Database Design
• Application
• ORM
• Transaction
• Index
• Statistics
• Execution Plan
• Blocking
• Deadlock

بنابر این قبل از اعمال تغییر باید مشخص کنیم Bottleneck واقعی سیستم کجاست .

فرآیند صحیح Query Performance Tuning :

یک روش مناسب برای Performance Tuning را می توان به شکل زیر خلاصه کرد :

Identify ----> Diagnose ----> Fix ----> Verify ----> Measure ----> Repeat

یعنی :

1. شناسایی Query مشکل دار
2. تشخیص علت مشکل
3. اعمال راهکار
4. بررسی نتیجه
5. اندازه گیری Performance 
6. تکرار فرآیند برای مشکل بعدی

نکته : مهم این است که نباید صرفاٌ بر اساس حدس و آزمایش های تصادفی تغییرات مختلفی روی Database اعمال کنیم . یک روش ساختار یافته باعث می شود تأثیر هر تغییر قابل اندازه گیری باشد .

مرحله اول : تعیین Performance Target 

قبل از شروع Tuning باید مشخص کنیم Performance قابل قبول برای سیستم چیست .

برای مثال ممکن است در یک Application مشخص شود که Query ها باید معمولاٌ کمتر از 3 ثانیه اجرا شوند .

این مقدار برای همه سیستم ها یکسان نیست . یک Query ممکن است در یک سیستم کاملاٌ قابل قبول باشد اما در سیستم دیگر مشکل Performance محسوب شود .

همچنین باید به تعداد دفعات اجرای Query توجه کرد .

فرض کنید :

• Query A در 10 میلی ثانیه اجرا می شود اما هزاران بار در رقیقه اجرا می شود .
• Query B در 30 میلی ثانیه اجرا می شود اما فقط چندبار در ساعت اجرا می شود .

در این شرایط ممکن است Query اول اهمیت بیشتری برای Optimization داشته باشد.

بنابراین هدف Performance Tuning فقط کاهش زمان یک Query نیست ؛ بلکه باید تأثیر واقعی Query روی کل سیستم را در نظر گرفت .

مرحله دوم : ایجاد Baseline 

قبل از ایجاد تغییر ، باید وضعیت فعلی سیستم را اندازه گیری کنیم . به این وضعیت Baseline گفته می شود .

برای مثال می توان موارد زیر را ثبت کرد :

• Query Duration
• CPU Usage
• Disk I/O
• Memory Consumption
• تعداد دفعات اجرای Query

بعد از اعمال تغییر ، همین معیارها دوباره اندازه گیری می شوند .

به این ترتیب می توانیم مشخص کنیم که تغییر انجام شده واقعأ باعث بهبود شده یا خیر .

فقط به مدت زمان اجرای Query توجه نکنید :

یکی از اشتباهات رایج این است که Performance را فقط با Execution Plan بررسی کنیم . در حالی که ممکن است یک Query از نظر زمان اجرا سریع باشد اما مقدار زیادی CPU یا Disk I/O مصرف کند .

بنابراین هنگام بررسی Performance بهتر است معیارهایی مانند موارد زیر نیز بررسی شوند :

• Execution Time
• CPU
• Disk I/O
• Memory
• تعداد اجرای Query

گاهی کاهش مصرف منابع از کاهش چند میلی ثانیه ای زمان اجرا مهم تر است .

مهم ترین مشکلات Performance در SQL Server :

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

1. Index های نامناسب یا ناکافی
2. Statistics ناقص یا قدیمی
3. T-SQL نامناسب
4. Execution Plan نامناسب
5. Blocking
6. Deadlock
7. عملیات غیر Set-Based
8. طراحی نامناسب Database
9. استفاده نامناسب از Plan Cache
10. Recompilation بیش از حد

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

1. Index نامناسب یا ناکافی :

Index یکی از مهمترین ابزارهای SQL Server برای بهبود Performance است . نبودن Index مناسب می تواند باعث شود SQL Server برای پیدا کردن اطلاعات مورد نظر مجبور به بررسی تعداد زیادی از صفحات و رکوردها شود . این موضوع می تواند باعث افزایش :

• Disk I/O
• Memory Consumption
• CPU Usage
• Contention

شود .

اما ایجاد Index بیشتر همیشه به معنی Performance بهتر نیست .

Index ها هنگام عملیات هایی مانند :

• INSERT
• UPDATE
• DELETE

نیاز به Maintenance دارند . بنابراین تعداد زیاد Index می تواند هزینه عملیات DML را افزایش دهد و حتی روی Query های دیگر نیز تأثیر منفی بگذارد .

نتیجه : Index باید بر اساس Workload واقعی و پس از تست ایجاد شود .

2. Statistics قدیمی یا نامناسب :

SQL Server برای انتخاب Execution Plan مناسب به Statistics وابسته است .

Statistics اطلاعاتی درباره توزیع داده ها در اختیار Query Optimizer قرار می دهد و Optimizer با استفاده از این اطلاعات تعداد تقریبی Row های مورد انتظار را تخمین می زند .

اگر Statistics وجود نداشته باشد یا اطلاعات آن قدیمی باشد ، Optimizer ممکن است تعداد Row ها را اشتباه تخمین بزند .

در نتیجه ممکن است Execution Plan نامناسبی انتخاب شود ؛ برای مثال SQL Server ممکن است به جای یک روش مناسب برای دسترسی به داده ها ، Scan را انتخاب کند . بنابراین در Performance Tuning باید وضعیت Statistics نیز بررسی شود .

3. T-SQL نامناسب :

گاهی مشکل اصلی نه در Hardware و نه در Index ، بلکه در نحوه نوشتن Query است .

برخی مشکلات رایج عبارتند از :

• انتقال حجم زیادی از داده 
• Filter کردن داده ها به شکل نامناسب
• جلوگیری از استفاده مؤثر از Index
• پیچیدگی بیش از حد Query
• استفاده نامناسب از SQL Server Objects

حتی اگر SQL Server روی Hardware قدرتمندی اجرا شود ، T-SQL نامناسب می تواند Performance را به شدت کاهش دهد .

4. Execution Plan نامناسب :

SQL Server برای اجرای Query یک Execution Plan ایجاد می کند . Query Optimizer تلاش می کند بهترین Plan را بر اساس اطلاعات موجود انتخاب کند ؛ اما همیشه Plan انتخاب شده بهترین حالت ممکن نیست. execution Plan می تواند تحت تأثیر مواردی مانند :

• Query
• Index
• Statistics
• Data Distribution

قرار گیرد . بنابراین بررسی Execution Plan یکی از مراحل مهم Query Performance Tuning است .

یکی از مشکلات مهم در این بخش Parameter Sniffing است که ممکن است باعث شود Plan برای یک نوع مقدار Parameter مناسب باشد اما برای مقدار دیگری Performance نامناسبی داشته باشد .

5. Bloking :

در SQL Server برای حفظ سازگاری و یکپارچگی داده ها از Lock استفاده می شود . در نتیجه ممکن است یک Query برای دسترسی به Resource مورد نظر مجبور شسود منتظر Query دیگری بماند . این وضعیت را Blocking می نماند .

Blocking می تواند باعث افزایش زمان اجرای Query و ایجاد صف انتظار برای Session های دیگر شود . همچنین کمبود منابع می تواند شرایط Blocking را شدیدتر کند . بنابر این هنگام مشاهده Query های کند باید بررسی کنیم که آیا Query واقعاً در حال اجرای عملیات است یا در انتظار Lock قرار دارد .

6. Deadlock :

در Deadlock معمولاً دو یا چند Process منابعی را در اختیار دارند و هر کدام منتظر Resource ای هستند که Process دیگر در اختیار دارد .

برای مثال :

• Transaction اول Resource A را Lock کرده و منتظر Resource B است .
• Transaction دوم Resource B را Lock کرده و منتظر Resource A است .

در این شرایط SQL Server برای شکستن چرخه ، یکی از Transaction ها را به عنوان Deadlock Victim انتخاب کرده و Rollback می کند . Rollback نیز خود می تواند مصرف منابع و Workload سیستم را افزایش دهد .

7. عملیات Row-by-Row و غیر Set-Based :

SQL Server برای کار با مجموعه ای از داده ها طراحی شده است . بنابراین در بسیاری از موارد بهتر است عملیات روی مجموعه داده ها به شکل Set-Based انجام شود .

استفاده بیش از حد از :

• Cursor
• Loop
• پردازش Row-by-Row

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

8. طراحی Database :

گاهی مشکل Performance از Query نیست ، بلکه ریشه مشکل در طراحی Database قرار دارد .

موارد مانند :

• طراحی Relational
• Normalization
• Star Schema
• Clustered Index
• Columnstore Index
• Data typ مناسب

می توانند روی Performance تأثیر داشته باشند .

بنابراین در Performance Tuning نباید فقط Query را بررسی کرد ؛ بلکه Data Model و Database Design نیز باید مورد توجه قرار گیرد .

9. Plan Reuse و Plan Cache :

Compile کردن Query می تواند هزینه بر باشد . SQL Server برای کاهش این هزینه از Plan Cache استفاده می کند تا در شرایط مناسب بتواند Execution Plan های قبلی را دوباره استفاده کند . Parameterization و Prepared Statement می توانند به Reuse شدن Plan کمک کنند . در مقابل Dynamic نامناسب یا Parameterization ضعیف در Application و ORM ممکن است باعث کاهش Plan Reuse شود .

10. Recompilation بیش از حد :

Recompilation در برخی شرایط مفید است ؛ مخصوصاً زمانی که تغییرات قابل توجهی در داده ها یا Statistics رخ داده باشد . اما Recompilation مکرر می تواند هزینه اضافی برای SQL Server ایجاد کند . بنابراین باید بین مزایای Recompilation و هزینه Compile مجدد تعادل برقرار شود .

آیا همیشه مشکل از Query است ؟

خیر .

یکی از مهم ترین نکات در Performance Tuning این سات که نباید تمام مشکلات Performance را به Query یا Index نسبت داد . مشکل ممکت است از موارد زیر باشد :

CPU : پردازش های سنگین می توانند CPU را تحت فشار قرار دهند .

Memoey : کمبود Memory می تواند باعث افزایش فشار روی سایر منابع شود .

Disk I/O : عملیات خواندن و نوشتن سنگین روی storage می تواند Performance را کاهش دهد .

Network : گاهی Query در SQL Server سریع اجرا می شود اما انتقال نتیجه به Application زمان زیادی می برد .

Application : نحوه اجرای Query ، Transaction ها و ORM می توانند روی Performance تأثیر داشته باشند .

Virtual Machine و Container : در محیط های مجازی و Container نیز باید Resource های اختصاصی داده شده و Bottleneck های موجود بررسی شوند .

تأثیر Cloud و زیرساخت بر Performance :

امروزه Database ها فقط روی Server های سنتی On-Premises اجرا نمی شوند . ممکن است SQL Server یا Database در محیط هایی مانند :

• Server های داخلی سازمان
• Google Cloud SQL
• Amazon RDS
• Azure SQL Database
• Virtual Machine
• Kubernetes

اجرا شود .

در محیط Cloud ، انتخاب Service Tier و میزان Resource اختصاص یافته نیز می تواند مستقیماً روی Performance تأثیر بگذارد . بنابر این در زمان Performance Tuning باید کل Environment را در نظر گرفت ، نه فقط Query را .

اهمیت محیط Test و Development :

یکی از اصول مهم در Performance Tuning این است که تغییرات مهم ابتدا در محیط مناسب مانند Test یا Development بررسی شوند . همچنین بهتر است در هر مرحله فقط یک تغییر اصلی اعمال شود .

برای مثال اگر هم زمان :

• Index تغییر کند .
• Query Rewrite شود .
• Statistics Update شود .
• Configuration تغییر کند .

تشخیص اینکه دقیقاً کدام تغییر باعث بهبود یا افت Performance شده دشوار خواهد بود . همچنین یک Index ممکن است یک Query را سریع تر کند اما روی Query های دیگر یا عملیات DML تأثیر منفی داشته باشد .

استفاده از AI در Query Performance Tuning :

AI می توند در تحلیل Query و شناسایی مشکلات احتمالی کمک کننده باشد . با این حال نباید پیشنهاد AI را بدون بررسی و تست اجرا کرد.

دو نکته مهم وجود دارد :

    1. اطلاعات خصوصی و محرمانه Query ها و سیستم را نباید بدون توجه به سیاست های امنیتی در اختیار سرویس های عمومی AI قرار         داد .

    2. پاسخ AI ممکن است اشتباه باشد یا بر اساس اطلاعات ناقص ارائه شود .

بنابراین AI می تواند یک ابزار کمک برای Performance Tuning باشد ، اما نتیجه آن باید با Execution Plan و Measurement و Test واقعی بررسی شود .

چرا Performance Tuning یک فرآیند دائمی است ؟

Performance یک سیستم ثابت نیست و با گذشت زمان :

• حجم داده تغییر می کند .
• تعداد کاربران افزایش یا کاهش پیدا می کند .
• الگوی اجرای Query ها تغییر می کند .
• Application تغییر می کند .
• Statistics تغییر می کند .
• Workload تغییر می کند .

بنابراین Query ای که امروز Performance مناسبی دارد ، ممکن است چند ماه بعد به Bottleneck تبئیل شود . به همین دلیل Performance Tuning باید به صورت Iterative انجام شود .

یک چک لیست عملی برای SQL Server Performance Tuning :

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

مرحله 1 : هدف Performance را مشخص کنید 

            مشخص کنید Performance قابل قبول برای Application چیست .

مرحله 2 : Baseline ایجاد کنید 

            Execution Time ، CPU ، I/O و سایر معیارهای مهم را ثبت کنید .

مرحله 3 : Query های مهم را شناسایی کنید 

            Query هایی را پیدا کنید که بیشترین تأثیر را روی سیستم دارند .

مرحله 4 : Bottleneck را پیدا کنید 

            مشخص کنید مشکل از CPU ، Memory ، I/O ، Network ، Query ، Index ، Statistics یا موارد دیگر است .

مرحله 5 : Execution Plan را بررسی کنید 

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

مرحله 6 : Index و Statistics را بررسی کنید 

            بررسی کنید Index وجود دارد و Statistics وضعیت مناسبی دارند .

مرحله 7 : Blocking و Deadlock را بررسی کنید 

            مشخص کنید ایا Query به دلیل Lock منتظر مانده است یا خیر .

مرحله 8 : Query و Database Design را بررسی کنید 

            T-SQL و Data Model را بررسی کنید .

مرحله 9 : فقط یک تغییر اصلی اعمال کنید 

            تغییر را در محیط مناسب تست کنید .

مرحله 10 : نتیجه را اندازیه گیری کنید 

            Performance جدید را با Baseline مقایسه کنید .

مرحله 11 : فرآیند را تکرار کنید 

            پس از حل Bottleneck اول ، سراغ Bottleneck بعدی بروید .

 

جمع بندی :

Query Performance Tuning در SQL server فقط به سریع کردن یک Query محدود نمی شود . برای بهبود واقعی Performance باید یک نگاه جامع به کل سیستم داشته باشیم ؛ از Query و Execution Plan گرفته تا Index ، Statistics ، Blocking ، Deadlock ، Database Design ، Application ، Network , cdvshoj .

روش صحیح این است :

Measure ----> Identify ----> Diagnose ----> Fix ----> Verify ----> Measure Again

 

همیشه ابتدا وضعیت فعلی را اندازه گیری کنید ، سپس Bottleneck واقعی را پیدا کنید و بعد یک تغییر مشخص انجام دهید . در نهایت نیز باید نتیجه را با Baseline مقایسه کنید .

مهمتر از همه ، Performance Tuning یک فعالیت یکباره نیست . با تغییر داده ها ، کاربران و Workload ، مشکلات جدیدی ایجاد می شوند و بنابراین فرآیند بهینه سازی باید به صورت مستمر تکرار شود .