RS.count_executions, = RS.max_duration / 1000, = RS.max_cpu_time / 1000, RS.max_rowcount, P.query_id, P.plan_id, P.last_execution_time, Check QDS - filter for the SQL text or the query_hash, whichever you know about the query Once you have the query_hash, the next step is to retrieve the plan_handle value from sys.dm_exec_query_stats, then use this plan_handle to get the actual execution plan. a table name or a specific join condition or column list, you can filter the SQL query text for that and then pick the query_hash that matches the query in question. If you know a significant section of the query, e.g. The steps below first try to identify the query_hash value of the query that you want to analyze. This depends on the performance tier and workload of the database: the plan cache on a "Basic" DTU database is small and plan eviction might occur within hours or even minutes, whereas databases with many vCores might hold the plan much longer. OUTER APPLY sys.dm_exec_query_plan_stats(P1.plan_handle) AS P2Ĭapturing the actual execution plan from a past execution is not as straightforward because the plan might have already been evicted from sys.dm_exec_cached_plans. OUTER APPLY sys.dm_exec_query_statistics_xml(R.session_id) AS P1 LEFT JOIN sys.databases D ON S.database_id = D.database_idĬROSS APPLY sys.dm_exec_sql_text(R.sql_handle) T INNER JOIN sys.dm_exec_sessions S ON R.session_id = S.session_id = SUBSTRING(T.text, R.statement_start_offset/2 + 1, (CASE WHEN R.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(max),T.text)) * 2 ELSE R.statement_end_offset END - R.statement_start_offset)/2), only captures plan statistics for queries which have started and completed after enabling these options enable lightweight query profiling to get the actual execution plan
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |