شناسایی کوئری‌های کند SQLServer با Query Store

بهبود عملکرد SQL Server با Query Store: چگونه کوئری‌های کندتر از حد معمول را شناسایی کنیم؟

به عنوان یک مدیر پایگاه داده (DBA)، ممکن است هر از گاهی با درخواست‌هایی از سوی توسعه‌دهندگان یا کاربران نهایی مواجه شوید که ادعا می‌کنند یک کوئری خاص کندتر از حد معمول اجرا می‌شود. اغلب، این فقط یک حس درونی است. با این حال، با ابزار قدرتمند Query Store در SQL Server، می‌توانید به طور دقیق بررسی کنید که آیا واقعاً یک کوئری با کندی غیرمعمول مواجه شده است یا خیر.

این مقاله به شما نشان می‌دهد که چگونه با استفاده از Query Store در SQL Server 2016 و نسخه‌های جدیدتر، کوئری‌هایی را که کندتر از میانگین تاریخی خود اجرا می‌شوند، شناسایی کنید. این روش به شما کمک می‌کند تا کوئری‌های SQL مشکل‌ساز را قبل از اینکه تأثیر منفی بر عملکرد کلی سیستم بگذارند، بیابید و عملکرد SQL Server خود را بهینه سازید.

استفاده مقدماتی از Query Store: یافتن ۵ کوئری برتر از نظر میانگین زمان اجرا

قبل از اینکه به جزئیات پیچیده‌تر بپردازیم، اجازه دهید یادآوری کنیم که چگونه می‌توان به سرعت با استفاده از Query Store، پنج کوئری برتر از نظر میانگین مدت زمان اجرا را شناسایی کرد. این یک دید کلی از پرمصرف‌ترین کوئری‌ها ارائه می‌دهد و گام اولیه در بهینه‌سازی کوئری‌های SQL Server است.

SELECT TOP 5
    q.query_id,
    q.object_id,
    object_name(q.object_id) AS 'Object Name',
    qt.query_text_id,
    LEFT(qt.query_sql_text, 100) AS 'Query Text',
    qs.avg_duration,
    qs.last_execution_time
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
    ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats AS qs
    ON q.query_id = qs.query_id
ORDER BY qs.avg_duration DESC;

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

اکنون به بخش اصلی می‌رسیم: چگونه کوئری‌هایی را بیابیم که در حال حاضر کندتر از حد معمول خود اجرا می‌شوند. ایده این است که عملکرد فعلی یک کوئری (مثلاً در ۳۰ دقیقه گذشته) را با میانگین عملکرد تاریخی آن (مثلاً در ۲۴ ساعت گذشته به جز ۳۰ دقیقه اخیر) مقایسه کنیم. این رویکرد به شما امکان می‌دهد تا ناهنجاری‌ها را شناسایی کرده و بر نظارت بر عملکرد کوئری‌ها تمرکز کنید.

گام به گام: مقایسه عملکرد فعلی با میانگین تاریخی

برای انجام این مقایسه، از داده‌های Query Store استفاده می‌کنیم تا معیارهای زیر را استخراج کنیم:

• عملکرد فعلی: میانگین زمان اجرای یک کوئری در یک بازه زمانی کوتاه اخیر (مثلاً ۳۰ دقیقه گذشته).
• میانگین تاریخی: میانگین زمان اجرای همان کوئری در یک بازه زمانی طولانی‌تر گذشته (مثلاً ۲۴ ساعت گذشته، به استثنای بازه زمانی فعلی).
• انحراف معیار (Standard Deviation): برای سنجش پراکندگی داده‌های تاریخی و تشخیص اینکه آیا عملکرد فعلی به طور قابل توجهی خارج از محدوده نرمال است.

کوئری پیچیده‌ای که در ادامه می‌آید، این مقایسه‌ها را انجام می‌دهد. این کوئری تمام کوئری‌های SQL Server را بررسی می‌کند، میانگین زمان اجرای آن‌ها در بازه‌های زمانی مختلف را محاسبه کرده و در نهایت کوئری‌هایی را که عملکردشان بیش از یک حد مشخص (مثلاً ۱۰ درصد) از میانگین تاریخی بدتر شده است، شناسایی می‌کند. همچنین، این کوئری شامل محاسبه انحراف معیار است که به ما کمک می‌کند تا ببینیم عملکرد فعلی تا چه حد از حالت عادی فاصله گرفته است و آیا این کندی، یک اتفاق طبیعی است یا نشانه‌ای از یک مشکل جدی‌تر.

-- Current time filter (e.g., last 30 minutes)
DECLARE @CurrentTimeWindowMinutes INT = 30;

-- Historical time filter (e.g., last 24 hours)
DECLARE @HistoricalTimeWindowHours INT = 24;

SELECT
    q.query_id,
    q.object_id,
    object_name(q.object_id) AS 'Object Name',
    qt.query_text_id,
    LEFT(qt.query_sql_text, 100) AS 'Query Text',
    qs.avg_duration AS 'Current Avg Duration (microseconds)',
    qsi_last_24h.avg_duration AS 'Historical Avg Duration (microseconds)',
    (qs.avg_duration * 1.0 / qsi_last_24h.avg_duration) AS 'Duration Factor vs Historical',
    qsi_last_24h.stdev_duration AS 'Historical Duration Standard Deviation',
    CASE
        WHEN qs.avg_duration > (qsi_last_24h.avg_duration + (qsi_last_24h.stdev_duration * 1.0))
        THEN 'Slower than Normal (above 1 StdDev)'
        ELSE 'Within Normal Range'
    END AS 'Performance Status'
FROM sys.query_store_query AS q
JOIN sys.query_store_query_text AS qt
    ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_runtime_stats AS qs
    ON q.query_id = qs.query_id
    AND qs.last_execution_time >= DATEADD(minute, -@CurrentTimeWindowMinutes, GETUTCDATE()) -- Current performance window
LEFT JOIN (
    SELECT
        qsi.query_id,
        AVG(qsi.avg_duration) AS avg_duration,
        STDEV(qsi.avg_duration) AS stdev_duration
    FROM sys.query_store_runtime_stats AS qsi
    WHERE
        qsi.last_execution_time >= DATEADD(hour, -@HistoricalTimeWindowHours, GETUTCDATE()) -- Historical window
        AND qsi.last_execution_time  (qsi_last_24h.avg_duration * 1.1) -- Filter for queries at least 10% slower
ORDER BY
    (qs.avg_duration * 1.0 / qsi_last_24h.avg_duration) DESC;

نکات مهم در مورد کوئری بالا:

• تعیین بازه زمانی: متغیرهای @CurrentTimeWindowMinutes و @HistoricalTimeWindowHours به شما امکان می‌دهند تا بازه‌های زمانی مورد نظر خود را برای مقایسه عملکرد فعلی و تاریخی تنظیم کنید.
• مقایسه با میانگین تاریخی: ستون 'Duration Factor vs Historical' نشان می‌دهد که میانگین زمان اجرای فعلی کوئری چند برابر میانگین تاریخی آن است. عدد ۱.۱ در عبارت qsi_last_24h.avg_duration * 1.1 به معنی فیلتر کردن کوئری‌هایی است که حداقل ۱۰ درصد کندتر از میانگین تاریخی خود اجرا می‌شوند. شما می‌توانید این ضریب را بر اساس نیازهای خود تغییر دهید.
• تحلیل انحراف معیار: ستون 'Performance Status' با استفاده از انحراف معیار تاریخی ('Historical Duration Standard Deviation') تعیین می‌کند که آیا عملکرد فعلی کوئری در محدوده طبیعی نوسانات آن قرار دارد یا خیر (یعنی از یک انحراف معیار بیشتر شده است). این بخش از تحلیل به شما کمک می‌کند تا بین کندی‌های طبیعی و کندی‌های ناشی از یک مشکل واقعی تمایز قائل شوید.
• شناسایی کوئری‌های مشکل‌ساز: خروجی این کوئری، لیستی از کوئری‌های SQL را ارائه می‌دهد که نیاز به توجه و بهینه‌سازی SQL دارند.

بصری‌سازی داده‌های Query Store با Power BI

برای تحلیل عمیق‌تر و نظارت بر عملکرد SQL Server به صورت گرافیکی، می‌توانید داده‌های استخراج شده از Query Store را به Power BI منتقل کنید. با استفاده از Power BI، قادر خواهید بود:

• روندها (Trends) و ناهنجاری‌های (Anomalies) عملکرد کوئری‌ها را در طول زمان بصری‌سازی کنید.
• داشبوردهای سفارشی برای پایش مداوم کوئری‌های کند ایجاد کنید.
• الگوهای عملکردی را شناسایی کرده و به صورت فعالانه به مشکلات پاسخ دهید.

این رویکرد نه تنها بهینه‌سازی کوئری را آسان‌تر می‌کند، بلکه به اشتراک‌گذاری بینش‌های عملکردی با تیم‌های دیگر را نیز تسهیل می‌بخشد.

نتیجه‌گیری

Query Store در SQL Server ابزاری فوق‌العاده قدرتمند است که به مدیران پایگاه داده (DBA) و توسعه‌دهندگان کمک می‌کند تا مسائل مربوط به عملکرد کوئری را به طور موثر رصد و عیب‌یابی کنند. با مقایسه عملکرد فعلی یک کوئری با میانگین تاریخی آن، می‌توانید مشکلات را قبل از اینکه بحرانی شوند، شناسایی و برطرف کنید.

استفاده از رویکرد شرح داده شده در این مقاله، توانایی شما را در بهینه‌سازی SQL Server و حفظ عملکرد بهینه پایگاه داده به طرز چشمگیری افزایش می‌دهد. این یک گام حیاتی در مدیریت عملکرد پایگاه داده (DPM) و تضمین تجربه کاربری روان است.