![]() ![]() The new sys.dm_exec_query_plan_stats function only returns reasonable results if a successful execution of a query has been logged on sys.dm_exec_cached_plans, and if the LAST_QUERY_PLAN_STATS option had been enabled before the query was executed. Therefore, before starting to troubleshoot any scenario that requires the actual execution plan, you should explicitly enable both options before taking further steps:ĪLTER DATABASE SCOPED CONFIGURATION SET LIGHTWEIGHT_QUERY_PROFILING = ON ĪLTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON ![]() Either option can be switched on or off by the database administrator. While the "lightweight profiling" option is enabled by default on Azure SQL Database, the "last query plan statistics" option needs to be enabled separately at the database level. It complements the older sys.dm_exec_query_statistics_xml, which returns the query execution plan for in-flight requests with intermediate, transient execution statistics. This feature was introduced with SQL Server 2019 (15.x) and Azure SQL Database and allows collecting row count information for all query executions. Lightweight profiling v3 comes with a new sys.dm_exec_query_plan_stats function, which returns the equivalent of the last known actual execution plan for most queries (the "last query plan statistics"). The approach described in this article uses the Lightweight query execution statistics profiling infrastructure v3. Actual execution plans contain more details than the estimated execution plans that are available through the Query Store (QDS). The actual execution plan is important for query performance troubleshooting, as it provides runtime information that can explain slow query performance. It retrieves the information directly from the runtime statistics, without having to extract the query first and having to run it separately outside of the application. This article provides you with steps about how to capture the actual execution plan of a query. How to capture the actual execution plan of a query in Azure SQL Database ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |