استفاده از 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
تابع 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
حاصل این کوئری، به همراه text یا اصل متن کوئریهای در حال اجرا و همچنین query_plan، یا همان اطلاعات XML ای پلن که در قسمت اول، نمونهای از آنرا بررسی کردیم، میباشد که با کلیک بر روی هر کدام در management studio، نمایش گرافیکی آنها ظاهر خواهد شد. البته این پلنها، تنها تخمینها را به همراه دارند؛ چون از کش خوانده میشوند.
دریافت لیست پلنهای کش شده
توسط Viewای به نام sys.dm_exec_cached_plans میتوان به لیست پلنهای کش شدهی در سیستم دسترسی یافت:
SELECT * FROM sys.dm_exec_cached_plans;
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
دریافت متن پلنهای تو در تو و عمیق
با استفاده از تابع 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
دریافت اطلاعات آماری کوئریهای درحال اجرا
توسط viewای به نام sys.dm_exec_query_stats میتوان به اطلاعات آماری کوئریهای در حال اجرا دسترسی یافت:
SELECT * FROM sys.dm_exec_query_stats; GO
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
از SQL Server 2016 به بعد، امکان دریافت اطلاعات آماری توابع نیز میسر شدهاست:
SELECT * FROM sys.dm_exec_function_stats; GO
یک نکته: قابلیت جدیدی تحت عنوان Query Store از زمان SQL Server 2016 معرفی شدهاست و کار آن دریافت تمام اطلاعاتی است که تاکنون بررسی کردیم و تفاوت آن، در ذخیره شده بودن آن است. یعنی این اطلاعات را داخل بانک اطلاعاتی در حال بررسی ذخیره میکند که شامل متن و پلن کوئری و همچنین اطلاعات آماری آن است که توسط DMO's تهیه میشود.