1

While performing analysis observed that particular procedure was running in 10 mins on SQL VM, however for some reason it is taking 1 min in SQL Managed instance. This is happening in production environment.

To analyze further trying to get actual execution plan and which is taking way longer to execute like more than 30 mins and it doesn’t completed. Does anybody has any idea why it is taking very long to execute?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Vikrant More
  • 5,182
  • 23
  • 58
  • 90
  • 2
    You can get the estimated execution plan without running the proc. Even though the estimated plan and actual plan are not always the same - you are more likely to get an idea of what the problem is. Very Important note: If the procedure uses a loop or a cursor then it may generate dozens, hundred or even 1000's of execution plans. If that is the case it will be difficult to capture the execution plan (depending on what level of access you have - DMV/DMFs, Query Store, etc). – Alan Burstein Nov 26 '19 at 19:04
  • what is your question ? To get all poor performing query ? or To know why this specific query take 10 mins on SQL VM, however for some reason it is taking 1 min SSMS ? – KumarHarsh Nov 27 '19 at 11:10
  • @AlanBurstein - Understood, since this procedure using temp tables and some nested SP's and using this temp table in nested procedure won't allow me to get estimated execution plan as well. – Vikrant More Nov 27 '19 at 11:55
  • @KumarHarsh - it is taking 1 min in SQL Managed instance through SSMS but SSMS is not the problem. My main concern is to if a query taking way longer, then how should we generate actual execution plan for that particular procedure for analysis. – Vikrant More Nov 27 '19 at 12:11

1 Answers1

0

@VikrantMore, here is the query to check the queries which are using tempdb and it's execution plan. See this can helps you. 

SELECT
t1.session_id
, t1.request_id
, task_alloc_GB = CAST((t1.task_alloc_pages * 8./1024./1024.) AS NUMERIC(10,1))
, task_dealloc_GB = CAST((t1.task_dealloc_pages * 8./1024./1024.) AS NUMERIC(10,1))
, host= CASE WHEN t1.session_id <= 50 then 'SYS' else s1.host_name end
, s1.login_name
, s1.status
, s1.last_request_start_time
, s1.last_request_end_time
, s1.row_count
, s1.transaction_isolation_level
, query_text=
    COALESCE((SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
      (CASE WHEN statement_end_offset = -1
          THEN LEN(CONVERT(nvarchar(MAX),text)) * 2
               ELSE statement_end_offset
          END - t2.statement_start_offset)/2)
    FROM sys.dm_exec_sql_text(t2.sql_handle)) , 'Not currently executing')
, query_plan=(SELECT query_plan FROM sys.dm_exec_query_plan(t2.plan_handle))
FROM
(SELECT session_id, request_id
, task_alloc_pages=SUM(internal_objects_alloc_page_count +   
user_objects_alloc_page_count)
, task_dealloc_pages = SUM (internal_objects_dealloc_page_count + 
user_objects_dealloc_page_count)
FROM sys.dm_db_task_space_usage
GROUP BY session_id, request_id) AS t1
LEFT JOIN sys.dm_exec_requests AS t2 ON
t1.session_id = t2.session_id
and t1.request_id = t2.request_id
LEFT JOIN sys.dm_exec_sessions AS s1 ON
t1.session_id=s1.session_id
WHERE
t1.session_id > 50 -- ignore system unless you suspect there's a problem there
and t1.session_id <> @@SPID -- ignore this request itself
ORDER BY t1.task_alloc_pages DESC;
GO
Naveen Kumar
  • 582
  • 2
  • 8
  • 25
  • @Naven Kumar Hey, I just tried this out an it is a very useful query. I think it does not show the "actual" execution plan but the "estimated" execution plan, which indeed can be very different. – Nico Shabadoo Sep 18 '20 at 09:14