آشنایی با Query Store – بخش چهارم

نوشته شده توسط: تیم فنی نیک آموز

تاریخ انتشار: ۱۰ بهمن ۱۴۰۰

آخرین بروزرسانی: 23 دی 1403

زمان مطالعه: 18 دقیقه

مقدمه

در بخش اول با Query Store با روش‌ها و متد‌هایی که برای انالیز و بررسی Query ‌ها وجود داشتند، آشنا شدیم و مزایا و معایب هر کدام را نیز شرح دادیم. در بخش دوم با معماری داخلی Query Store و روش منحصر به فردی که برای جمع آوری اطلاعات کوئری‌ها و سایر اطلاعات مرتبط با آن‌ها بود نیز آشنا شدیم و همچنین پارامتر‌هایی که برای تنظیمات مرتبط با این قابلیت بود نیز به صورت کلی تشریح شد. در بخش سوم مقاله آشنایی با Query Store نکاتی ارائه گردید که بتوانید در محیط‌های عملیاتی به شیوه بهتری نسبت به جمع آوری اطلاعات از پلن‌های اجرایی اقدام کنید. همچنین گزارشات و Catalog View‌های مرتبط با این بخش نیز تا حدودی بررسی شد. در این مقاله با Catalog view‌های بیشتری از Query Store آشنا خواهیم شد و همچنین کاربردهای Query Store در دنیای واقعی را بهتر خواهید شناخت. با توجه به این که سناریوها و مواردی که در مقاله عنوان شده است ، ارتباط مستقیمی با گزارشات مختلف Query Store دارد، لذا نیاز هست که جهت حفظ پیوستگی مقاله ،حتما شناخت کلی نسبت به معماری Query Store پارامتر ها و گزارشات آن داشته باشید

بررسی sys.query_store_query

در این View همان طور که از اسم ان نیز مشخص است اطلاعات مرتبط با کوئری‌های جمع اوری شده را مشاهده می‌کنید. به عنوان مثال زمانی که پلن اجرایی اجرایی ساخته می‌شود را می‌توانید مشاهده کنید. در بعضی از سناریوها که حساسیت اجرای کوئری‌ها به شدت بالاست این مدت زمان که Query شما کامپایل می‌شود نیز از اهمیت بالایی برخوردار هست که پلن‌های اجرایی زمان زیادی برای ایجاد شدن نیاز دارند یا خیر. لذا اطلاعات کامپایل Queryها که در این View بخش‌های مختلفی از ان را مشاهده می‌کنید نیز از اهمیت بالایی برخوردار هستند. همچنین نکته جالبی که در این View وجود دارد این هست که عیب‌یابی کوئری‌ها بسته به هر تکه کدی که اجرا می‌شود به صورت مجزا و برای همان عبارت هست. به عنوان مثال فرض کنید که پراسیجری شامل چندین بخش در حال اجرا شدن است و یکی از بخش‌ها باعث کندی این پراسیجر می شود. به همین دلیل با تحلیل بر روی اطلاعات این View می‌توانید هر بخش را به تنهایی انالیز و ارزیابی کنید. در شکل زیر می‌توانید سایر اطلاعات ارزشمندی که این View در اختیار ما قرار می‌دهد را مشاهده کنید. در عکس دوم که در ادامه مشاهده می‌کنید به تفکیک هر Query میانگین زمانی CPU، رم و مدت زمان اجرا نیز قابل نمایش هست.

بررسی sys.query_store_query_text

در این View، متن کوئری اجرا شده را می‌توانید مشاهده کنید. در فصل دوم به تفصیل در خصوص ارتباط بین این جداول صحبت شد. این View به صورت مستقیم با View ایی که در قسمت قبل معرفی شد ارتباط داشته و می‌توانید متن کوئری‌ها را استخراج کنید. با اسکریپت زیر می‌توانید عامل جوین این دو View را مشاهده نمایید:

SELECT *
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q
ON q.query_text_id = qt.query_text_id
INNER JOIN sys.objects o
ON o.object_id = q.object_id
WHERE o.name = '<object_name>'

همچنین اطلاعاتی که این View در اختیار ما قرار می‌دهد را نیز می‌توانید در شکل زیر مشاهده کنید:

بررسی sys.query_store_wait_stats

همان طورکه در فصل اول صحبت شد، استفاده از DMV‌ها برای انالیز Wait‌ها به دلیل این که به ازای کلیه دیتابیس در نظر گرفته می‌شود باعث می شود که نتوان به طور دقیق گفت که هر Query اجرا شده، دقیقا چه سهمی از Wait‌ها خواهد داشت. لذا این یکی از نقاط ضعفی بود که مبحث DMV‌ها داشت که توضیحات آن را می‌توانید در فصل اول این مقاله مطالعه کنید. در ۲۰۱۷SQL SERVER قابلیتی به Query Store اضافه شد که بتوانیم تحلیلی بر روی Wait‌ها در Query Store داشته باشیم. همچنین با توجه به این که Query Store در سطح هر دیتابیس فعال شده و جمع اوری دیتا را انجام می‌دهد لذا می‌توانیم با دقت بیشتری امار مرتبط با Wait‌ها را به ازای هر کوئری به تفکیک هر دیتابیس آنالیز کنیم. به همین دلیل زمانی که از ۲۰۱۷SQL SERVER یا ورژن‌های بالاتر استفاده می کنید این قابلیت در اختیار شما خواهد بود. همچنین با استفاده از اسکریپت زیر می‌توانید تحلیلی بر روی این Wait‌ها داشته باشید.

به عنوان مثال می‌خواهیم بر روی کلیه تریگرهایی که اجرا شده تحلیلی بر روی Waitهای سیستمی انجام دهیم. به دلیل وجود Object_id در هر بخش این امر به راحتی قابل رصد کردن هست. می‌توانید مطابق با استفاده از اسکریپت زیر نمونه ایی از این حالت را شبیه‌سازی کنید.

SELECT *
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q
ON q.query_text_id = qt.query_text_
INNER JOIN sys.objects o
ON o.object_id = q.object_id
INNER JOIN sys.query_store_plan p
ON p.query_id = q.query_id
INNER JOIN sys.query_store_wait_stats ws
ON ws.plan_id = p.plan_id
WHERE o.name = '<object_name>'

در شکل زیر اطلاعاتی که در این View می توانید انالیز کنید را مشاهده می کنید:

بررسی سناریوهای کاربری جهت استفاده از Query Store

ایجاد یک Baseline برای آنالیز و تحلیل اطلاعات کوئری‌ها وظیفه اصلی یک DBA هست که بر اساس ان سرباری که به سیستم عملیاتی اعمال می شود را تحلیل و بررسی کند. مسلما در محیط‌های عملیاتی زمان‌هایی وجود دارد که نرخ این سربار از سایر زمان‌ها به مراتب بیشتر بوده و به اصلاح در زمان‌های مختلف، دیتابیس شما ریتم‌های مختلفی را برای شما می‌نوازد. لذا باید دقت داشته باشید که این ریتم را به صورت مرتب بررسی کنید. در صورتی که این Baseline را داشته باشید مسلما هر زمانی که اتفاق مشکوکی در سیستم رخ دهد با روز قبل، هفته یا حتی ماه یا سال قبل هم قابل مقایسه هست که آیا این سرباری که بر سیستم تحمیل شده است عادی بوده یا اتفاق دیگری در حال رخ دادن هست. به عنوان مثال گزارشی که در خصوص پسرفت کوئری‌ها بود را مشاهده کردیم که در چه زمان‌هایی کوئری‌ها عملکرد بهینه خود را از دست خواهند داد یا زمان‌هایی که از یک نسخه پایین تر به نسخه بالاتر مهاجرت می‌کنیم باید دقت داشته باشیم که کندی و سربار اضافی به سیستم به هر دلیل اعمال نشود. لذا Query Store در این زمینه می‌تواند ارزش افزوده بسیار خوبی برای اطلاعات تحلیلی شما ایجاد کند. در ادامه به بعضی از این سناریو‌ها با جزییات بیشتری خواهیم پرداخت.

تشخیص نرمال بودن کارکرد سیستم

اگر به خاطر داشته باشید در بخش سوم پارامتری معرفی شد تحت عنوان INTERVAL_LENGTH_MINUTES که در چند بازه زمانی پیش فرض بتوانید نسبت به تحلیل اطلاعات کوئری‌های ذخیره شده اقدام کنید. این پارامتر نیز به صورت پیش فرض بر روی ۶۰ دقیقه تنظیم شده بود که اطلاعات جمع آوری شده را بسته به این بازه‌های زمانی از حالت فشرده خارج کرده و به تفصیل به ما نشان دهد. همچنین گزارشی تحت عنوان Overall Resource Consumption report نیز بررسی شد که نشان دهنده مصرف منابع به ازای کلیه کوئری‌های جمع آوری شده بود.

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

به عنوان مثال در اسکریپت زیر میخواهیم در ۱۰ روز گذشته، بر اساس زمان اجرای کوئری‌ها ارزیابی و تحلیلی داشته باشیم.

SELECT TOP 10 qt.query_sql_text,
q.query_id,
so.name,
so.type,
SUM(rs.count_executions * rs.avg_duration) AS 'Total Duration'
FROM sys.query_store_query_text qt
INNER JOIN sys.query_store_query q
ON qt.query_text_id = q.query_text_id
INNER JOIN sys.query_store_plan p
ON q.query_id = p.query_id
INNER JOIN sys.query_store_runtime_stats rs
ON p.plan_id = rs.plan_id
INNER JOIN sys.query_store_runtime_stats_interval rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
INNER JOIN sysobjects so
ON so.id = q.object_id
WHERE rsi.start_time >= DATEADD(DAY, -10, GETUTCDATE())
GROUP BY
qt.query_sql_text,
q.query_id,
so.name,
so.type
ORDER BY
SUM(rs.count_executions * rs.avg_duration_time) DESC

در این کتاب برای توصیف Baseline متن زیر بدین شکل مطرح شد است که با توجه به این که کیفیت ترجمه نیز باید حفظ شود لذا نیاز دیدم که متن انگلیسی آن را نیز در کنار کلیه توضیحات بالا مطالعه کنید:

A baseline is a workload you run against your server that sets a starting point for comparisons against future runs of the same workload. In theory, you would run your workload to make any necessary changes to your environment; the changes could be code or hardware changes, and you would rerun the same workload and compare and see what metrics have changed

در این کتاب سناریو‌هایی از محیط‌های واقعی در قالب داستان مطرح شده است. به عنوان مثال کارمندی شب هنگام بسته به ماهیت کاری که باید انجام می‌داد و گزارشی که به مدیر خود ارائه می‌کرد، طی کار با سیستم کندی‌های زیادی را مشاهده می‌کند و فیدبک این موضوع را به شما خواهد داد. در صورتی که این بیس لاین را به خوبی طراحی کرده باشید می‌توانید عملکرد سیستم را نسبت به شب قبل بررسی و تحلیل کنید. آیا این شخص گزارشات سنگینی از سیستم گرفته است یا مشکل دیگری به وجود آمده است که کندی خاصی از سمت این کاربر احساس شده؟ سوالات مختلفی می‌توان مطرح کرد که عوامل کندی سیستم را به ان انتصاب دهیم ولی ما در این جا Baseline ایی داریم و بر اساس این نقشه راه اطلاعاتی با جزییات دقیق‌تری در سیستم ذخیره کردیم. لذا کلیه عملکرد سیستم را می‌توانیم به دقت بررسی کنیم. با استفاده از نمودار‌هایی که در قسمت سوم این مقاله خدمت شما ارائه گردید می‌توانید عملکرد سیستم را در بازه‌های زمانی مختلف با یکدیگر مقایسه کنید.همان طور که در تصویر بالا مشاهده می‌کنید، زمانی که بر روی هر یک از میله‌ها کلیک کنید، با توجه به نمودار‌هایی که در هر بخش به صورت مجزا به نمایش درآمده است می‌توانیم تحلیل‌های خود را شروع کنیم. به عنوان مثال در یک تاریخ مشخص عملکرد سیستم را از لحاظ Logical reads میخواهیم بررسی کنیم. وقتی وارد این قسمت می‌شویم در مرحله بعد تحلیل‌ها به شکل تخصصی وارد این قسمت شده و حالا می‌توانیم عملکرد سیستم را در ساعت‌های مختلف تحلیل کنیم. به دست آوردن این الگو برای شما بسیار ارزشمند خواهد بود چرا سریعا متوجه یک روند غیر عادی خواهید شد.

بررسی دقیق تر عوامل پسرفت کوئری ها

همان طور که در قسمت‌های قبل گزارشات و داشبورد‌های تحلیلی Query Store را بررسی کردیم، یکی از مهمترین گزارشات، گزارش مرتبط با پسرفت کوئری‌ها یا Regressed Query‌ها بود. عوامل مختلفی در عملکرد یک کوئری سهیم هستند که باعث می‌شوند یک پلن نسبت به شرایط قبلی خود با آمار مختلفی نتایج خود را برگرداند. به روز نبودن Statistics‌ها یکی از این عوامل هست که نقش زیادی در تخمین صحیح یک پلن اجرایی ایفا می‌کند. در صورت به روز نبودن Stat‌ها پلن تخمینی و پلن واقعی تفاوت‌های زیادی در برخی پارامتر‌ها خواهند داشت که این تخمین اشتباه سربار زیادی به سیستم تحمیل خواهد کرد. همچنین تصور کنید که تغییراتی در ایندکس‌ها به وجود امده و به اشتباه ایندکس‌هایی از برخی جداول حذف شده که همین عامل، موجب پسرفتی در اجرای کوئری‌ها خواهد شد. به همین دلیل نیاز هست که پلن‌های قبلی و پلن جدید در این گزارش باید با یکدیگر مقایسه شود. یکی از قابلیت‌های این گزارش بررسی دو پلن هست که به شکل زیر می‌توانید این کار را انجام دهید:همچنین کلیه اپراتور‌هایی این پلن‌ها را می‌توانید به صورت مجزا بررسی کنید:

شناسایی منابع پرمصرف

گزارشی تحت عنوان Top Consuming Resource برای این مورد استفاده می‌شد که بتوانیم کوئری‌هایی را بررسی کنیم که بیشترین مصرف منابع را در سرور داشته‌اند. این طبقه‌بندی مطابق با لیست زیر قابل بررسی هست

Execution count
Duration (ms) (default)
CPU Time (ms)
Logical reads (KB)
Logical writes (KB)
Physical reads (KB)
CLR time (ms)
DOP
Memory consumption (KB)
Row count
Log memory used (KB)
Tempdb memory usage (KB)
Wait time (ms)

این گزارش به عنوان یکی دیگر از مهمترین کاربرد‌های Query Store معرفی شده است که می‌توانید عملیات‌های مختلفی بر روی هر کدام انجام دهید. به عنوان مثال بر اساس CPU Time به نحوی تجمیع اطلاعات انجام گیرد که در بازه‌های زمانی مختلف تحلیل‌های خوبی را به استخراج کنید.

پایداری سرور پس از ارتقا ورژن SQL Server

یکی دیگر از کاربرد‌های Query Store در زمان ارتقا ورژن SQL Server هست. همان طور که توضیح داده شد از نسخه ۲۰۱۴ تغییراتی در الگوریتم‌های مرتبط با پلن‌های اجرایی اعمال گردید که در بعضی از شرایط کوئری‌ها با بهبود زیادی در اجرا همراه بودند و در مواردی به عنوان گلوگاهی برای سیستم شناخته شده است. به همین دلیل نیاز هست که این ارتقا ورژن طبق یک Baseline ایی زیر نظر گرفته شود تا تغییر Compatibility level باعث بروز مشکل نشود. برای اطلاع از جزییات این موضوع می‌توانید در خصوص Cardinality Estimator (CE) که از ورژن ۲۰۱۴SQL SERVER همراه با تغییراتی بوده هست اطلاعات مختلفی کسب کنید. همچنین ماکروسافت از نسخه ۲۰۱۶ به بعد، اعمال برخی از تنظیمات را از سطح سرور به ازای هر دیتابیس عملیاتی کرد که شامل موارد زیر می‌شود:

MAXDOP
LEGACY_CARDINALITY ESTIMATION
PARAMETER_SNIFFING
QUERY_OPTIMIZER_HOTFIXES

قبل از SQL Server 2016 باید فلگی با شماره ۴۱۹۹ برای اعمال CU بر روی همه دیتابیس ها فعال می شد که انجام این کار ، فرایند عیب یابی را بسیار سخت تر می کرد . لذا در صورتی بر روی یک Instance چندین دیتابیس عملیاتی دارید می توانید با استفاده از LEGACY_CARDINALITY ESTIMATION به ازای هر دیتابیس ، این کار را انجام دهید و تغییرات CU را در آن مانیتور کنید . در شکل زیر ، فرایند ارتقا ورژن بدین شکل در نظر گرفته شده است :

مرحله اول: ارتقا ورژن انجام را انجام دهید ولی compatibility level آن را تغییر ندهید.
مرحله دوم: قابلیت Query Store را بر روی دیتابیس فعال کنید.
مرحله سوم: اجازه دهید فرایند جمع اوری دیتا مطابق با بیس لاین توسط Query Store انجام شود.
مرحله چهارم: در این مرحله ، compatibility level را مطابق با آخرین ورژنی که ارتقا انجام شده است ، تنظیم کنید.
مرحله پنجم: با استفاده از گزارش پسرفت کوئری ها (Regressed Query report) ، مشکل این کوئری ها را برطرف کنید.

مطابق با تصویری که در این کتاب ارائه شده است کلیه مراحل بالا بدین شکل طی پروسه ایی به شکل زیر توضیح داده شده است:همچنین در کنار این گزارش می توانید از قابلیت Automatic Plan Correction نیز برای اصلاح و بهینه کردن پلن های اجرایی نیز استفاده کنید. این قابلیت در نسخه SQL SERVER 2017 موجود هست . نکته ایی که در این مراحل وجود دارد، تعیین بازه زمانی مورد نیاز برای جمع آوری اطلاعات توسط Query Store هست که با توجه به سربار عملیاتی که دارید باید در بازه ایی در نظر گرفته شود که عمده گزارشات مختلف توسط کاربران مختلف اجرا شده یا عملیات‌های مختلف سیستمی انجام گردد. به عنوان مثال، سازمان‌هایی که از نرم‌افزار‌های ERP استفاده می‌کنند، در بازه‌های زمانی مختلف بهایابی را اجرا می‌کنند. عملیات بهایابی یکی از عملیاتی هست که سربار زیادی بر روی سیستم تحمیل می‌کند. لذا باید فرایند‌های شرکت خود را به خوبی زیر نظر بگیرید که عمده این موارد توسط سیستم جمع اوری شده تا در مرحله بعدی، تحلیل‌های آن انجام شود. لذا مدت زمان جمع آوری اطلاعات را با دقت کافی نسبت به فرایند‌های سازمان خود باید در نظر بگیرید.

استخراج و عیب یابی ad-hoc کوئری ها

یکی از مواردی که برای Query Store به تنهایی سربار مجزایی به حساب می‌آید، ad-hoc queries‌ هایی هست که تولید خواهد شد. با توجه به این که این کوئری‌ها به صورت تجمیع شده تحت قالب یک کوئری توسط Query Store قابل تجیمع نیست، لذا باعث مصرف بیشتری خواهد شد و حجم اطلاعات زیادی باید توسط Query Store جمع اوری شود. در نتیجه پلن‌های اجرایی مختلف با اطلاعات اماری مختلف ذخیره خواهد شد.

اسکریپت هایی زیر را اجرا کنید:

--Total Query Texts
SELECT COUNT(*) AS CountQueryTextRows
FROM sys.query_store_query_text;
--Total Queries
SELECT COUNT(*) AS CountQueryRows
FROM sys.query_store_query;
--Total distinct query hashes (different queries)
SELECT COUNT(DISTINCT query_hash) AS CountDifferentQueryRowsS
FROM sys.query_store_query;
--Total plans
SELECT COUNT(*) AS CountPlanRows
FROM sys.query_store_plan;
--Total unique query_plan_hash (different plans)
SELECT COUNT(DISTINCT query_plan_hash) AS CountDifferentPlanRows
FROM sys.query_store_plan;

با توجه به عکس بالا، زمانی که مقادیر دو فلش مشکی یا دو فلش قرمز را با یکدیگر مقایسه کردید و اختلافی مشاهده کردید، در ذخیره اطلاعاتی که انجام شده ، اطلاعات مرتبط با Ad-hoc Query‌ها را نیز ذخیره شده است. برای جلوگیری از این کار و صرفه جیی در مصرف منابع Query Store، می‌توانید پارامتر مرتبط با QUERY_CAPTURE_MODE را در حالت AUTO قراردهید که از ذخیره این کوئری‌ها جلوگیری شود. با اعمال اسکریپت‌های زیر در ابتدا در سطح سرور و در مرحله دوم در سطح دیتابیس، از ذخیره شدن این مدل اسکریپت‌ها خودداری خواهد شد. در مرحله اخر نیز اسم دیتابیس خود را نیز جایگزین دیتابیس [QueryStoreTest] کنید

EXEC sys.sp_configure 'show advanced options', '1'
GO
RECONFIGURE
WITH OVERRIDE
GO
EXEC sys.sp_configure 'optimize for ad hoc workloads', '1'
GO
RECONFIGURE
WITH OVERRIDE
GO
ALTER DATABASE [QueryStoreTest]
SET QUERY_STORE CLEAR;
ALTER DATABASE [QueryStoreTest]
SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = AUTO);

بررسی Wait ها در Query Store

در مبحث Wait‌ها، همان طور که در مقاله‌های قبلی اشاره شده بود، با ریست شدن سرور یا دستورات مرتبط امار و اطلاعات مرتبط همگی حذف می‌شدند. لذا با قابلیت هایی که Query Store در اختیار ما قرار داده است بررسی این سناریو‌ها از دو جنبه حاثز اهمیت هست. جنبه اول در خصوص ذخیره امار اطلاعات مرتبط در دیسک برای ایجاد بیس لاین از Wait‌ها هست و جنبه دوم بابت این که به ازای هر کوئری مشخص می‌توانیم امار Wait‌ها را با دقت بیشتری ارزیابی کنیم که این موضوع در فرایند Tuning بسیار راهگشاست. در صورتی که کتاب‌های زبان اصلی و مباحث مرتبط با بحث Wait‌ها را دنبال کنید، متوجه خواهید شد که یکی از سریع‌ترین روش‌های برای این که کندی‌های سیستم را استخراج کنید، بررسی امار مرتبط با Wait‌ها خواهد بود. در Sql server با انواع و اقسام Wait‌ها در شرایط مختلف رو به رو هستیم. اما لازم به ذکر هست که اولویت هر کدام از این Wait‌ها با یکدیگر متفاوت هست.

همچنین این Wait‌ها در گروه‌بندی‌های مختف طبقه‌بندی شده است. لذا زمانی که به صورت تخصصی میخواهیم بر روی مباحث مرتبط با CPU و Wait‌های ان تمرکز کنیم، کلیه Wait‌های مرتبط با این موضوع با اولویت‌های آن مشخص شده است که صرفا بر روی ان بتوانیم تحلیل‌هایی داشته باشیم. در خصوص Wait‌ها در مقالات مجزایی این دسته‌بندی‌ها به همراه روش‌ها و راهکار‌های رفع مشکلات مرتبط با آن قسمت به صورت کامل ارائه خواهد شد. در Query Store، ۲۳ نوع مختلف از Wait‌ها قابل تحلیل هستن که در تصاویر زیر مشاهده می‌کنید همان طور که توضیح داده شد همه Wait‌ها در مبحث Query store مورد بررسی قرار نخواهد گرفت. به عنوان مثال در بحث Backup & Restore با Wait‌هایی رو به رو هستیم که مستقیما مشکلات مرتبط با این موضوع را به ما نشان خواهد داد که فرایند Backup گیری باید به شکل بهتری انجام شود یا مشکل از سمت دیسک هست که باید مورد بررسی قرار گیرد. لذا این موضوعات عملا ارتباطی با مباحث Query و پلن‌های اجرایی و ماهیت Query Store ندارد و در شرایط دیگری مورد بررسی قرار خواهد گرفت. نکته جالبی که شاید در برخی اسکریپت‌ها نیز مشاهده کنید بدین شکل هست که زمانی هایی که قرار هست شرایط کلی سرور مورد بررسی قرار داده شود ، فقط Wait‌هایی با اولویت‌هایی بالاتر در اسکریپت قرار داده می‌شود که صرفا از ارائه اطلاعات غیر مرتبط با درجه اولویت پایین‌تر خودداری شود.

در Query Store به دو روش می‌توانیم امار مرتبط با Wait‌ها را مشاهده کنیم. روش اول با استفاده از گزارشاتی هست که از در اختیار ما قرار داده می‌شد و در قسمت‌های قبلی، شیوه دسترسی به این گزارشات و تحلیل‌های زمانی آن نیز بررسی گردید. روش دوم با استفاده از نمونه اسکریپت های زیر هست که می‌توانیم امار مرتبط را به ازای هر جدول یا به صورت کلی بررسی کنیم. حتما دقت داشته باشید که در Catalog view‌های مرتبط با این موضوع، مجددا فیلد‌ها و سایر اطلاعاتی که این View در اختیار ما قرار می‌دهد را بررسی کنید. توضیحات هر فیلد نیز به صورت مجزا در سایت رسمی ماکروسافت ارائه شده است که در انتهای این مقاله لینک‌های مرتبط را ارائه خواهیم کرد. در ادامه اسکریپت آن را بررسی خواهیم کرد.

نمونه اسکریپت بررسی Wait‌های Query Store به ازایه یک جدول خاص:

SELECT qsws.wait_category_desc,
qsws.total_query_wait_time_ms,
qsws.avg_query_wait_time_ms,
qsws.stdev_query_wait_time_ms
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan AS qsp
ON qsp.query_id = qsq.query_id
JOIN sys.query_store_wait_stats AS qsws
ON qsws.plan_id = qsp.plan_id
JOIN sys.query_store_runtime_stats_interval AS qsrsi
ON qsrsi.runtime_stats_interval_id = qsws.runtime_stats_interval_id
WHERE qsq.object_id = OBJECT_ID('dbo.ProductByCost');

روش دوم با استفاده از اسکریپت زیر هست که سرجمع کلیه زمان‌هایی که Wait‌ها داشتند را می‌توانید مشاهده کنید:

select
qt.query_text_id,
q.query_id,
p.plan_id,
sum(total_query_wait_time_ms) as sum_total_wait_ms
from sys.query_store_wait_stats ws
join sys.query_store_plan p on ws.plan_id = p.plan_id
join sys.query_store_query q on p.query_id = q.query_id
join sys.query_store_query_text qt on q.query_text_id = qt.query_text_id
group by qt.query_text_id, q.query_id, p.plan_id
order by sum_total_wait_ms desc

همچنین زمانی که می‌خواهید امار کلیه Wait‌ها را داشته باشید می‌توانید از اسکریپت زیر استفاده کنید:

WITH [Waits] AS
(SELECT
[wait_type],
[wait_time_ms] / 1000.0 AS [WaitS],
([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
[signal_wait_time_ms] / 1000.0 AS [SignalS],
[waiting_tasks_count] AS [WaitCount],
۱۰۰.۰ * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
FROM sys.dm_os_wait_stats
WHERE [wait_type] NOT IN (
N'BROKER_EVENTHANDLER',
N'BROKER_RECEIVE_WAITFOR',
N'BROKER_TASK_STOP',
N'BROKER_TO_FLUSH',
N'BROKER_TRANSMITTER',
N'CHECKPOINT_QUEUE',
N'CHKPT',
N'CLR_AUTO_EVENT',
N'CLR_MANUAL_EVENT',
N'CLR_SEMAPHORE',
N'CXCONSUMER',
N'DBMIRROR_DBM_EVENT',
N'DBMIRROR_EVENTS_QUEUE',
N'DBMIRROR_WORKER_QUEUE',
N'DBMIRRORING_CMD',
N'DIRTY_PAGE_POLL',
N'DISPATCHER_QUEUE_SEMAPHORE',
N'EXECSYNC',
N'FSAGENT',
N'FT_IFTS_SCHEDULER_IDLE_WAIT',
N'FT_IFTSHC_MUTEX',
N'HADR_CLUSAPI_CALL',
N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
N'HADR_LOGCAPTURE_WAIT',
N'HADR_NOTIFICATION_DEQUEUE',
N'HADR_TIMER_TASK',
N'HADR_WORK_QUEUE',
N'KSOURCE_WAKEUP',
N'LAZYWRITER_SLEEP',
N'LOGMGR_QUEUE',
N'MEMORY_ALLOCATION_EXT',
N'ONDEMAND_TASK_QUEUE',
N'PARALLEL_REDO_DRAIN_WORKER',
N'PARALLEL_REDO_LOG_CACHE',
N'PARALLEL_REDO_TRAN_LIST',
N'PARALLEL_REDO_WORKER_SYNC',
N'PARALLEL_REDO_WORKER_WAIT_WORK',
N'PREEMPTIVE_OS_FLUSHFILEBUFFERS',
N'PREEMPTIVE_XE_GETTARGETSTATE',
N'PVS_PREALLOCATE',
N'PWAIT_ALL_COMPONENTS_INITIALIZED',
N'PWAIT_DIRECTLOGCONSUMER_GETNEXT',
N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
N'QDS_ASYNC_QUEUE',
N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
N'QDS_SHUTDOWN_QUEUE',
N'REDO_THREAD_PENDING_WORK',
N'REQUEST_FOR_DEADLOCK_SEARCH',
N'RESOURCE_QUEUE',
N'SERVER_IDLE_CHECK',
N'SLEEP_BPOOL_FLUSH',
N'SLEEP_DBSTARTUP',
N'SLEEP_DCOMSTARTUP',
N'SLEEP_MASTERDBREADY',
N'SLEEP_MASTERMDREADY',
N'SLEEP_MASTERUPGRADED',
N'SLEEP_MSDBSTARTUP',
N'SLEEP_SYSTEMTASK',
N'SLEEP_TASK',
N'SLEEP_TEMPDBSTARTUP',
N'SNI_HTTP_ACCEPT',
N'SOS_WORK_DISPATCHER',
N'SP_SERVER_DIAGNOSTICS_SLEEP',
N'SQLTRACE_BUFFER_FLUSH',
N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
N'SQLTRACE_WAIT_ENTRIES',
N'VDI_CLIENT_OTHER',
N'WAIT_FOR_RESULTS',
N'WAITFOR',
N'WAITFOR_TASKSHUTDOWN',
N'WAIT_XTP_RECOVERY',
N'WAIT_XTP_HOST_WAIT',
N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
N'WAIT_XTP_CKPT_CLOSE',
N'XE_DISPATCHER_JOIN',
N'XE_DISPATCHER_WAIT',
N'XE_TIMER_EVENT'
)
AND [waiting_tasks_count] > 0
)
SELECT
MAX ([W1].[wait_type]) AS [WaitType],
CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
MAX ([W1].[WaitCount]) AS [WaitCount],
CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S],
CAST ('https://www.sqlskills.com/help/waits/' + MAX ([W1].[wait_type]) as XML) AS [Help/Info URL]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2] ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX( [W1].[Percentage] ) < 95; -- percentage threshold
GO

اسکریپت زیر از سلسله مقالات آقای Paul S. Randal استخراج شده که یکی از پرکاربرد‌ترین اسکریپت تحلیلی برای بررسی Wait‌های سیستمی هست. می‌توانید در کنار تحلیل‌هایی که از اسکریپت‌های Query Store دارید، از این مدل اسکریپت ها استفاده کنید. همچنین برای این که اولویت این Wait‌ها را نیز به خوبی متوجه شوید، می‌توانید از سایت ایشان برای این موضوع استفاده کنید که لینک مقاله ها در انتهای همین مقاله خدمت شما عزیزان ارائه خواهد شد.

سخن پایانی

در این مقاله سعی کردیم مباحث مرتبط با Catalog view‌ها را بررسی کردیم تا یک اشنایی کلی با انواع این View‌های سیستمی داشته باشید. در مرحله بعد سناریو‌های کاربردی Query Store را مورد بررسی قرار دادیم و متوجه شدیم که در چه شرایطی می‌توانیم از گزارشات و قابلیت‌های Query Store استفاده کنیم که یکی از مهمترین بخش‌هایی که مطرح شد بحث ارتقا ورژن‌های Sql server بود که بتوانیم با کمترین هزینه بهترین خروجی را دریافت کنیم و کندی‌هایی که ممکن است در این حالت پیش آید را به مراتب مدیریت خواهید کرد. در قسمت اخر نیز مباحث مرتبط با Wait‌ها را بررسی کردیم و اسکریپت های معرفی شد که بتوانید تحلیل‌های خوبی بر روی کوئری‌ها یا حتی وضعیت کلی سرور داشته باشید. در قسمت‌های بعدی با قابلیتی در Query Store اشنا خواهید شد که به صورت خودکار پلن‌های اجرایی در بهینه‌ترین حالت اجرا شوند و به صورت هوشمند بر اساس وضعیت پیشرفت یا پسرفت کوئری‌ها تصمیم گیری برای انتخاب بهترین پلن انجام می‌شود.