.NET Tips | بررسی کارآیی کوئری‌ها در SQL Server - قسمت سوم - جمع آوری اطلاعات آماری کوئری‌ها توسط DMO's

اندازه‌ی قلم متن

تخمین مدت زمان مطالعه‌ی مطلب: چهار دقیقه

Extended events ای که در قسمت قبل بررسی شدند، جهت جمع آوری اطلاعات آماری تک کوئری‌ها مورد استفاده قرار می‌گیرند؛ اما Dynamic management objects یا به اختصار DMO's، تجمعی عمل می‌کنند (برای مثال جهت محاسبه‌ی میانگین logical reads چند کوئری مانند هم). متن یک کوئری و پلن آن، توسط DMO's مختلفی قابل استخراج هستند. متن یک کوئری توسط sys.dm_exec_sql_text قابل استخراج است و برای دسترسی به کوئری پلن‌ها از sys.dm_exec_query_plan، sys.dm_exec_cached_plans و sys.dm_exec_text_query_plan استفاده می‌شود. در این حالت برای دسترسی به اطلاعات آماری از sys.dm_exec_query_stats و sys.dm_exec_function_stats کمک گرفته خواهد شد.

استفاده از Dynamic management objects برای جمع آوری اطلاعات آماری کوئری‌ها

در ادامه در طی چند مثال، روش استخراج اطلاعات آماری کوئری‌ها را توسط DMO's بررسی می‌کنیم.

دریافت متن کوئری‌های در حال اجرا

توسط کوئری زیر که توسط تابع sys.dm_exec_sql_text اجرا می‌شود، می‌توان لیست کوئری‌های در حال اجرای بر روی بانک‌های اطلاعاتی جاری را بدست آورد:

SELECT
    [r].[session_id],
    DB_NAME([r].[database_id]) [DatabaseName],
    [t].[text]
FROM sys.dm_exec_requests [r]
CROSS APPLY sys.dm_exec_sql_text([r].sql_handle) [t];
GO

در اینجا text، همان متن کوئری است و هربار که این کوئری اجرا می‌شود، نتیجه‌ی متفاوتی را بر اساس کوئری‌هایی که در آن لحظه در حال اجرا هستند، دریافت خواهیم کرد.
تابع sys.dm_exec_sql_text برای اجرا نیاز به یک sql_handle دارد که آن‌را از طریق sys.dm_exec_requests می‌توان تامین کرد.

دریافت پلن کوئری‌های در حال اجرا

توسط کوئری زیر که توسط تابع sys.dm_exec_query_plan اجرا می‌شود، می‌توان لیست پلن کوئری‌های در حال اجرای بر روی بانک‌های اطلاعاتی جاری را بدست آورد:

SELECT
    [r].[session_id],
    DB_NAME([r].[database_id]) [DatabaseName],
    [t].[text],
    [p].[query_plan]
FROM sys.dm_exec_requests [r]
CROSS APPLY sys.dm_exec_sql_text([r].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([r].[plan_handle]) [p];
GO

تابع sys.dm_exec_query_plan برای اجرا نیاز به یک plan_handle دارد که آن‌را از طریق sys.dm_exec_requests می‌توان تامین کرد.
حاصل این کوئری، به همراه text یا اصل متن کوئری‌های در حال اجرا و همچنین query_plan، یا همان اطلاعات XML ای پلن که در قسمت اول، نمونه‌ای از آن‌را بررسی کردیم، می‌باشد که با کلیک بر روی هر کدام در management studio، نمایش گرافیکی آن‌ها ظاهر خواهد شد. البته این پلن‌ها، تنها تخمین‌ها را به همراه دارند؛ چون از کش خوانده می‌شوند.

دریافت لیست پلن‌های کش شده

توسط Viewای به نام sys.dm_exec_cached_plans می‌توان به لیست پلن‌های کش شده‌ی در سیستم دسترسی یافت:

SELECT *
FROM sys.dm_exec_cached_plans;

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

SELECT
    [r].[session_id],
    DB_NAME([r].[database_id]) [DatabaseName],
    [cp].[objtype],
    [cp].[size_in_bytes],
    [t].[text],
    [p].[query_plan]
FROM sys.dm_exec_requests [r]
CROSS APPLY sys.dm_exec_sql_text([r].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([r].[plan_handle]) [p]
    JOIN sys.dm_exec_cached_plans [cp]
    ON [r].[plan_handle] = [cp].[plan_handle];
GO

مزیت این کوئری نسبت به موارد قبلی، وجود ستون‌های جدید objtype و size_in_bytes است که بیانگر نوع کوئری، مانند AdHoc و اندازه‌ی پلن در کش هستند.

دریافت متن پلن‌های تو در تو و عمیق

با استفاده از تابع sys.dm_exec_text_query_plan می‌توان به متن پلن‌های عمیق دسترسی یافت. در این حالت خروجی کوئری در management studio به صورت یک لینک قابل کلیک ظاهر نمی‌شود و صرفا یک متن قابل کپی است که می‌توان آن‌را با پسوند sqlplan برای بررسی‌های بعدی، ذخیره کرد:

SELECT
    [r].[session_id],
    DB_NAME([r].[database_id]) [DatabaseName],
    [tq].[query_plan]
FROM sys.dm_exec_requests [r]
CROSS APPLY sys.dm_exec_text_query_plan([r].plan_handle, 0, -1) [tq];
GO

در اینجا اعداد 0 و 1- به معنای ابتدا و انتهای batch هستند.

دریافت اطلاعات آماری کوئری‌های درحال اجرا

توسط viewای به نام sys.dm_exec_query_stats می‌توان به اطلاعات آماری کوئری‌های در حال اجرا دسترسی یافت:

SELECT *
FROM sys.dm_exec_query_stats;
GO

این کوئری تعداد ستون‌های قابل توجهی را به همراه دارد مانند Physical reads، logical reads و .... به همین جهت نیاز است اطلاعات مفید آن‌را فیلتر کرد:

SELECT
    [qs].[last_execution_time],
    [qs].[execution_count],
    [qs].[total_logical_reads]/[qs].[execution_count] [AvgLogicalReads],
    [qs].[max_logical_reads],
    [t].[text],
    [p].[query_plan]
FROM sys.dm_exec_query_stats [qs]
CROSS APPLY sys.dm_exec_sql_text([qs].sql_handle) [t]
CROSS APPLY sys.dm_exec_query_plan([qs].[plan_handle]) [p]
WHERE [qs].[execution_count] > 25
    OR [qs].[total_logical_reads] > 10000
ORDER BY [qs].[total_logical_reads]/[qs].[execution_count] DESC;
GO

این کوئری در حقیقت ترکیبی است از کوئری‌هایی که تاکنون نوشتیم و در آن text و query_plan از sys.dm_exec_sql_text و sys.dm_exec_query_plan تامین شده‌اند، به همراه تعدادی ستون مفید sys.dm_exec_query_stats مانند last_execution_time و AvgLogicalReads. به علاوه در اینجا کوئری‌هایی که بیشتر از 25 بار اجرا شده‌اند و یا total_logical_reads آن‌ها بیش از 10 هزار بوده، در خروجی ظاهر خواهند شد (مفهوم تجمعی بودن DMO's).

از SQL Server 2016 به بعد، امکان دریافت اطلاعات آماری توابع نیز میسر شده‌است:

SELECT *
FROM sys.dm_exec_function_stats;
GO

یک نکته: قابلیت جدیدی تحت عنوان Query Store از زمان SQL Server 2016 معرفی شد‌ه‌است و کار آن دریافت تمام اطلاعاتی است که تاکنون بررسی کردیم و تفاوت آن، در ذخیره شده بودن آن است. یعنی این اطلاعات را داخل بانک اطلاعاتی در حال بررسی ذخیره می‌کند که شامل متن و پلن کوئری و همچنین اطلاعات آماری آن است که توسط DMO's تهیه می‌شود.

بررسی کارآیی کوئری‌ها در SQL Server - قسمت سوم - جمع آوری اطلاعات آماری کوئری‌ها توسط DMO's

مطالب مشابه