پایش پایگاه‌داده SQL Server با ابزار Query Store

برخلاف سر و صدای NoSQLای ها، دیتابیس‌های رابطه‌ای هنوز هم در جایگاه خاص خودشان در صنعت یکه‌تاز اند و سهم قابل توجهی از بازار فنی را در دست دارند؛ به عنوان نمونه در سیستم‌های مالی مثلا در بسترهای ETL مالی و ابزارهای Core banking بخش زیادی از منطق تجاری (business logic) با بهره‌گیری از دیتابیس‌های رابطه‌ای مثل SQL Server، Oracle، MySQL و... در معیّت داده و دیتابیس (DB-centric) مدیریت می‌شود [ حتی به غلط:) ]. یکی از علل مهم گرایش به این دیتابیس‌ها احتمالا «تضمین سازگاری داده‌ای» و به طور کلی فراهم کردن سیستم‌ای مبتنی بر تراکنش (Transaction) هست که سر بزنگاه‌های عملیاتی [به‌ویژه عملیات مالی که ماهیت تراکنشی دارند]، بشه بهش اتکا کرد. این سیستم‌ها (یا دیتابیس‌ها) نوعا وفاداری و تعهد ویژه‌ای به مفاهیم بنیادی جبر رابطه‌ای، جامعیت داده‌ای، اجرای اتمیک و چندین معیار دیگر، ضمن بهره‌گیری [گاهاً] پایین از منابع را دارند.

با این مقدمه میخوام بگم دیتابیس‌های این شکلی در کاربردهای خاصی که اهمیت معیارهایی مثل «قابلیت اطمینان - reliability» و «سازگاری - consistency» به نسبت دیگر معیارهای سنجش کارایی [مثل تعداد OPS بالا در عملیات Write در کاربردهای Streaming] بالاست، به طور جدی و البته با چالش‌های نگهداری و Tuning خاص خودشان حضور دارند. به طور کلی این‌ها هم مثل هر سیستمی نیاز به پایش (monitoring) دارند که بفهمیم چه موقع چه «رخدادی» اتفاقی افتاده و در مرتبه بالاتر بتواند به observability سیستم کمک کند تا «علت» اون رخدادها هم برای ما مشخص بشود.

قبل از اینکه به جزئیات و ساختار این ابزار بپردازیم، بهتره قصه «اهمیت مساله» را کمی جزئی‌تر تعریف کنیم. موارد زیر عموماً باعث می‌شوند «پایش عملکرد دیتابیس» خودش به صورت مجزا یک «نیازمندی» باشد که برای آن دنبال رهیافت باشیم.

• تشخیص روندها در رشد داده و عملیات (trendy events)
• پایش میزان مصرف توان پردازشی در بازه‌های زمانی مشخص با workload های مختلف
• پایش روند دسترسی به حافظه اصلی و استفاده از آن در workload های مختلف
• تشخیص رخدادهای CPU-burst و IO-burst کوتاه و گذارا (عموما بدون الگوی ثابت)
• پایش عملکرد شاخص‌ها (indices)
• پایش مدت زمان اجرای تراکنش‌ها
• پیدا کردن گلوگاه‌‌های (bottlenecks) عملیاتی و داده‌ای ساده و چند سطحی

پیش‌تر، در این پست (+لینک) در خصوص چند مورد از گلوگاه‌ها (یا تنگناها) نوشته‌ام.

• پایش میزان Lockهای اتفاق افتاده بر روی رکوردها، صفحات و شاخص‌ها
• پایش نموداری، به منظور تشخیص Spikeها و استخراج الگوهای معنادار
• پایش پلن‌های اجرایی (Execution plans) ایجاد شده برای پرس و جوها
• استخراج الگوی دسترسی به منابع مختلف توسط یک دیتابیس مشخص
• تشخیص Deadlockها، keylockها و به طور کلی lockها اتفاق افتاده
• استخراج آمار اجرای پرس و جوها
• تشخیص پرس و جوها و پلن‌هایی با مساله پارامترسازی (Parameterization problem)
• و طبیعیتا موارد جزئی دیگر

این موارد نه فقط در پایش دیتابیس بلکه در نظارت بر سیستم‌های دیگر نیز نوعا یک «مساله» است. در دیتابیس SQL Server چندین ابزار از جمله SQL Profiler، Activity Monitor و Standard Reportها وجود دارند که اطلاعات خوبی را می‌شود از آن‌ها استخراج کرد و به نوعی بار بخش زیادی از فرآیند پایش را بر دوش می‌کشند. اما در نسخه‌های جدید این دیتابیس (SQL Server 2016 به بعد) ابزار جدیدی به اسم Query Store معرفی شده‌است. این ابزار سعی می‌کند تاریخچه‌ای از اجرای پرس و جوها و پلن‌های اجرایی را جمع‌آوری کند و سپس یک سری گزارش‌ها را تولید کند، به همین علت در ابتدا بهش «Flight data recorder» گفته می‌شد. به‌نظر پایه و اساس این ابزار این صفحه (+لینک) نسبتا قدیمی با پرس و جوهای کاربردی است.

تصویر زیر معماری کلی فرآیند Profiling در Query Store را نشان می‌دهد:

در این طراحی، فرآیند «پایش» به صورت ضمنی و نهان در هر جایی که یک پرس و جو بخواهد اجرا شود حضور دارد، یعنی عمل «پاییدن» خودش یه Cross-cutting requirement در طراحی این ابزار بوده‌است.

همانطور که می‌دانید SQL Server یکی از نقاط قوت‌اش بهینه‌ساز پرس و جو و قابلیت‌های حول آن است که [اغلب، نه لزوماً] سعی می‌کند بهترین پلن اجرایی را برای پرس و جوها تولید بکند (+لینک)، به عبارتی پیش از اجرای هر پرس و جو [در قالب Function یا SP]، دیتابیس یک پلن اجرایی برای آن تولید می‌کند. Query store هم گوش به زنگِ تولید این پلن‌ها (Query plan در نمودار) می‌ایستد و پس از تولید، آن‌را به همراه متن پرس و جو (Query text) در حافظه اصلی تحت عنوان «Query plan and text» نگه می‌دارد.

به عنوان یک وظیفه موازی و مجزای دیگر، جزئیات اجرای پرس و جو را نیز تحت عنوان «Query runtime statistics» در حافظه نوشته و بافر می‌کند. این اطلاعات بافر شده‌ در حافظه اصلی، به صورت ناهمگام (Asynchronous) در یک دیتابیس رابطه‌ای ذخیره می‌گردد و در نهایت بر روی دیسک نگه‌داری می‌شود.

ابزار Query store از طریق نسخه‌های جدید SSMS در دسترس است و می‌شود با تنظیمات مختلفی از جمله اینکه بازه‌های زمانی برای Profiling، اندازه فایل Logها و مدت زمان نگه‌داری Log ها و... چقدر باشد، آن را پیکربندی (configuration) کرد. +اینجا روند راه اندازی و تنظیمات اولیه به شکل سر راست توضیح داده شده‌است و +اینجا نیز تنظیمات پیشنهادی و برخی نکات مهم ارائه شده است.

پس از اینکه این ابزار را پیکربندی کردید، به این دسته‌ از گزارش‌ها دسترسی خواهید داشت:

بخش Regressed Queries:

پرس و جوهایی که اخیرا کند شده اند؛ به عبارتی پرس و جوهایی که پلن اجرایی شان به‌نسبت پلن‌های تولید شده قبلی وضعیت بدتری پیدا کرده باشند در اینجا دسته‌بندی می‌شوند. معمولا پلن‌ها، در حافظه اصلی Cache می‌شوند، بنابراین Profiler در دوره زمانی تعیین‌شده یا پیش‌فرض، Plan cache را آپدیت کرده و به صورت پلن‌های Regressed گزارش می‌کند.

زمانی‌که Workload سیستم بالا برود و کندی محسوس بشود، به صورت تجربی، ما اولین بخشی که در [در زمان رخداد کندی و کمی پس از رفع ‌شدن آن] بررسی می‌کنیم اینجاست. به‌ویژه موقعی‌که سیستم در پیک کاری خود باشد. به عنوان نمونه در سیستم‌های مالی در بازار سرمایه [مثلا سیستم مدیریت سفارشات سهام (OMS)] که بازه زمانی فعالیت محدود و مشخصی دارند [با پیک‌هایی متنوع] و رخدادهایی مثل بازگشایی بازار، تلاش برای headshot کردن سهم‌ها از طرف معامله‌گران، صف‌های خرید و فروش و... که نوعاً به صورت دسته‌ای و رقابتی اتفاق می‌افتند و با عملیات دیگه همبسته اند (Correlated)، این بخش کاربرد جالبی پیدا کرده و کمک می‌کند پرس و جوهای کند آشکار بشوند، اتفاقی که در اجرای منفرد و مجزای اون پرس و جو، در وضعیت بار کم، دیده نمی‌شده.

بخش Overall Resource Consumption:

میزان مصرف منابع مختلف منطقی و فیزیکی را در یک بازه زمانی مشخصی (به‌طور پیش‌فرض یک ماه گذشته) نشان می‌دهد. در واقع این بخش تجمیع داده‌های «Query runtime statistics» است که در معماری Profiling در تصویر اول پیداست.

این گزارش در 4 معیار کلی زیر و به صورت نمودار میله‌ای نشان داده می‌شود:

• مدت زمان اجرا به میلی‌ثانیه (Duration)
• تعداد اجرای پرس و جوها (Execution count)
• زمان اختصاص داده‌شده پردازنده به میلی‌ثانیه (CPU time)
• حجم داده‌های خواندن منطقی (Logical reads in KB)

به ازای هر بازه زمانی (هر روز)، در هر 4 معیار بالا، جزئیات آمده در تصویر زیر ارائه می‌شود.

هر کدام از این فیلدها به‌طرز جالبی، ‌می‌تواند سودمند باشد. مثلا با مقایسه Logical reads و Logical writes میشه یک نسبتی از میزان داده‌های نوشتنی - خواندنی به دست آورد (Write intensive vs. Read intensive بودن پرس و جوها)، یا به عنوان یه نمونه دیگر نسبت دسترسی به دیسک را در مقایسه با تعداد Logical reads سنجید.

یه مورد جالب دیگر مقداری است که برای «Temp DB Memory Used in KB» گزارش می‌کند؛ به شکل تجربی می‌تواند راهگشا باشد، به عنوان نمونه دیتابیسی را فرض کنید که با تعداد زیادی Table-valued function و SP دارد به‌اصطلاح کار می‌کند و متغیرهای Temp جدولی متنوعی هم داخل هر کدام تعریف شده است، این مقدار یک دیدی می‌دهد و میزان مصرف Temp DB توسط دیتابیس فعلی را در بازه مشخص گزارش می‌کند.

این گزارش‌ها به طور کلی برای بررسی‌های دوره‌ای گزینه مناسبی است؛ مثلا می‌شود پنجره زمانی را به اندازه بازه‌های بین releaseها تنظیم کرد و تاثیر بهبودها و تغییرات را سنجید.

بخش Top Resource Consuming Queries:
این گزارش نیز یکی از کاربردی‌ترین گزارش‌های Query Store هست. به ازای تمامی فیلد [یا سنجه]هایی که در گزارش قبلی [همین تصویر بالایی] ملاحظه کردید، گزارش جزیی و مقایسه‌ای می‌دهد که شامل موارد زیر است:

• پلن‌های اجرایی ایجاد شده به همراه نمودار عملکرد هر کدام (Plan summary)
• نمودار میله‌ای نزولی از پرس و جوها با توجه به سنجه انتخاب‌شده (مثلا Duration)
• جزئیات هر پلن اجرایی (شامل هزینه اجرایی هر بخش از پرس و جو)

نکته جالب این گزارش، در اینه که اساساً خود سنجه‌ها را یک Resource می‌بیند و پرس و جوهایی با بیشترین میزان از آن سنجه را به عنوان پرس و جوی پر هزینه تشخیص می‌دهد.

مثلا در سنجه Execution Count که تعداد اجرای هر پرس و جو را گزارش می‌کند، چندتا مورد زیر را می‌شود استنتاج کرد:

• مقایسه تعداد اجرای پرس و جوها و به دست آوردن یک تناسب بین درخواست‌های APP و اجراهای دیتابیس [جهت تصدیق تعداد درخواست‌ها در APP-side و DB-side].
• تشخیص رفتار نادرست در APP وقتی یک پرس و جوی متناظر با آن، با انحراف معیار بالاتری نسبت به بقیه اجرا گردد.
• تشخیص تعداد اجرای بالای پرس و جو و انجام فرآیند بهینه‌سازی با هدف معین، از جمله Index گذاری مناسب و استفاده از Memory-optimized tables و موارد دیگر.

یا در سنجه CPU time به راحتی می‌توان پرس و جوهای CPU-bound یا هر پرس و جویی با بیشترین تقاضای توان پردازشی را تشخیص داد. این سنجه به نوعی در مورد طراحی سرویس و بحث‌های تجاری هم می‌تواند به طراح فنی و تجاری محصول دید بدهد، به عنوان نمونه در طراحی API، میزان منابع مصرفی و پیش‌بینی در خصوص بحث‌های مطرح در SLA می‌تواند مفید باشد؛ منظورم به طور کلی دیدگرفتن هست و قطعا نمی‌تواند ملاک اصلی در سنجش کارایی در هر کاربردی باشد.

در سنجه Physical reads، ممکن است موارد زیر به چشم بیاید:

• عملکرد Indexها در سطح دیسک (مثلا هزینه به‌روزرسانی Clustered indexها)
• ارزیابی کارایی Indexها در پلن‌های تولید شده
• مقایسه پلن‌های تولیدشده و امکان Force کردن پلن‌ای که بهینه‌تر عمل کرده است.
• بررسی عملکرد Pageها و Partitionها
• بررسی انواع رویکردهای پیمایش شاخص‌ها و جدول‌ها (از جمله Index Seek، Index and table Scan، Key lookup و موارد مرتبط دیگر)

در سنجه Wait time، عموماً پرس و جوهای با «تعداد اجرای کم و کندی بالا»، و یا پرس و جوهای با «تعداد اجرای بالا و کندی محسوس» را به راحتی می‌توان تشخیص داد.

در این بخش نیز مشابه موارد قبلی می‌توان یک پلن اجرایی نسبت به پلن‌های دیگر ترجیح داد و به لیست Forced planها اضافه کرد.

بخش Queries With Forced Plans:

در این قسمت پلن‌هایی که برای پرس و جوهای مختلف به عنوان پلن Forced انتخاب شده است، آورده می‌شود.

در این تصویر، یک نمونه از Forced planهایی که پیش‌تر Force کردم را آوردم، ملاحظه می‌کنید که به ازای یک پرس و جو، 9 پلن اجرایی توسط SQL Server تشخیص داده شده است که بر اساس شاخص Avg Duration، پلن اجرایی با شناسه «15809» را به عنوان پلن بهتر، به صورت Forced انتخاب کرده‌ام.

یکی از قابلیت‌های جالبی که این بخش ارائه می‌دهد همین امکان مقایسه بین پلن‌های مختلف در مقایسه با پلنی است که Force شده است. به عبارتی در طول زمان می‌توان سنجید که آیا پلن Forceشده کارایی مطلوب را دارد یا خیر؟

علاوه بر این موارد، به ازای هر پلن Forceشده چندین مشخصه را هم گزارش می‌کند که می‌تواند به طراح پایگاه‌داده و بهنیه‌ساز دید دقیقی بدهد، از جمله موارد زیر:

• تعداد شکست در Force کردن پلن

این مورد به نظرم خیلی مهم است. ممکن است شما پیش از اعمال تغییراتی، مثلا حذف یا افزودن یک Index، یک پلن را force کرده باشید، سپس Indexای که در پلن مورد نظر تاثیرگذار بود حذف کنید، بنابراین پلن دیگر مثل سابق compile نخواهد شد و در نتیجه force کردن آن هم عملا با شکست (failure) روبرو خواهد شد.

• آخرین زمانی که پلن انتخاب‌شده compile شده است
• آخرین زمان اجرای پرس و جو
• آخرین زمان اجرای پرس و جو با پلن forceشده

بخش Queries With High Variation:

یکی از بخش‌های جذاب Query store همین نوع گزارش است. در این قسمت پرس و جوهایی که روند اجرایی آن‌ها الگوی پایداری نداشته باشد، گزارش‌شده و دسته‌بندی می‌شوند. به عنوان نمونه یک پرس و جو ممکن است بر مبنای سنجه‌های مختلف ارزیابی کارایی، گاهی کند و گاهی بهتر عمل کند و این روند به صورت نوسانی بدون الگوی خاصی ادامه پیدا کند که با احتمال بالایی در این بخش گزارش خواهد شد.

عموما در پایگاه داده SQL Server پرس و جوهایی که مشکل parameterization [جاگذاری مقادیر literal با متغیرهای placeholder به منظور پیش‌گیری از ایجاد چندین پلن تکراری برای یک پرس و جوی یکسان] را داشته باشند، در این قسمت مشاهده می‌شود.

به عنوان نمونه مطابق آن‌چه در نمودار بالایی ملاحظه می‌کنید، با انتخاب سنجه CPU Time و زیرسنجه‌ی انحراف معیار، پرس و جویی که از نظر زمان اجرایی و توان پردازشی در بازه زمانی داده‌شده رفتار متفاوتی داشته باشد، به‌طرز خوبی تفکیک می‌شود. برای بررسی بهتر می‌توان در زمان پیک کاری سامانه بازه زمانی Profiling را محدود به مقدار مشخصی کرد تا عملکرد پرس و جوها در قالب پنجره‌های زمانی مختلف سنجیده شود.

در خصوص مساله Parametrization در SQL Server اینجا (+لینک) به‌شکل خوبی صحبت شده‌است.

بخش Query Wait Statistics:

در این قسمت از جنبه‌های مختلفی از جمله حافظه (memory)، پردازنده (CPU)، IO شبکه (Network IO)، قفل‌ها (Locks) و...، پرس‌ و جوهای Wait شده برای دریافت منابع را می‌توان دید.

یکی از جذابیت‌های این بخش، گزارش سرراست بن‌بست‌ (Deadlock)ها است. برحسب تجربه‌، با گرفتن گزارش زیر پرس و جوهایی که بیشترین میزان Wait را در دسته Lock داشته باشند، با احتمال بالایی دچار بن‌بست از نوع به‌خصوصی [مثلا Keylock یا Page lock یا Row lock] شده اند.

Query Wait Statistics ---> Lock ---> Based on "max wait time"

برای نمونه در تصویر زیر یک گزارش از دیتابیسی با منطق تجاری نسبتا پیچیده در حوزه مالی را آورده ام.

در این نمودار، سه پرس و جو که زمان Wait یکسانی دارند، دقیقا سه SP و Functionای هستند که بر روی یک کلید اصلی (Primary clustered index) و یک کلید کاور تعریف‌شده (Non-clustered covered index) به مساله keylock برخورده اند.

البته برای پیداکردن بن‌بست‌ها بهتر است از Extended Eventها که کاملا منطبق بر این مساله اند استفاده بشود. با این حال گزارش‌های این بخش نیز می‌تواند کاربردی باشد.

صحبت پایانی:

در کل، زمانی‌که بخشی از منطق تجاری نسبتاً پیچیده‌ای را به دیتابیس انتقال داده‌باشید و یا حتی سمت APP هندل کرده باشید اما نهایتاً دیتابیس در تیررس درخواست‌های متنوع با نرخ اجرای بالا قرار گیرد، و از طرفی مشکلات کارایی و کندی پیش بیاید بدون اینکه دقیقا بدانید این موارد از کدامین بخش ناشی شده‌است، این ابزار به‌طرز شگفت‌انگیزی به کارتان خواهد آمد.

در حقیقت ما وقتی سراغ این ابزار رفتیم که ابزارهای دیگر مثل Activity monitor، SQL Profiler و... یا جزئیات کمتری را گزارش می‌کردند و یا اینکه UI خوبی برای Visualization جزئیات نداشتند. بنابراین پیدا کردن سرنخ تعدادی از مشکلات مرتبط با کارایی، Indexها و... بدون Query Store دشوار بود.