Query hang on single user database

Question

Query hang on single user database

Matt Esterly 0

We created a new 24 vcore SQL Azure instance and are getting hangs for 81 minutes with no timeouts on an update which previous took seconds. There is only one user and no blocking session

SELECT 
    s.login_name,
    DB_NAME(r.database_id) AS database_name,
    r.command,
    r.status,
    r.start_time,
    DATEDIFF(MINUTE, r.start_time, GETDATE()) AS runtime_minutes,
    r.cpu_time,
    r.wait_type,
    r.wait_time,
    r.last_wait_type,
    r.blocking_session_id,
    t.text AS query_text,
    qp.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) qp
JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id
WHERE r.session_id > 50  -- Exclude system sessions
  AND r.session_id <> @@SPID  -- Exclude this current query
ORDER BY r.total_elapsed_time DESC;

User's image

-- update query which is normally sub second < 100k records
 update f
                set f.freight_fk = f.tracking_shipper
                from fulfillment  f  with(nolock)
                join freight_cost fc with(nolock) on
                    fc.tracking_shipper = f.tracking_shipper
                    and f.tenant_key  = fc.tenant_key
                    and f.freight_fk is null
                where
                    f.tracking_shipper is not null

                    and (fc.fulfillment_fk is null or fc.fulfillment_fk = f.tracking_shipper)

1 answer

Your answer

Answer 1

With this scant information, it is impossible to say what might be going on.

The one sample of the wait type, suggests that the query is CPU bound.

This in its turn suggest that it may be a plan issue. While the query may have run in sub-second earlier, there is not really any guarantee that it will continue to do so. If the plan has fallen out of cache, there will be a new compilation, and if statistics have changed, or there are other changes, the optimizer may make a different choice of query plan. And, yes, sometimes that can be a plan which is disastrously slower than the previous one.

I would suggest that you peek into Query Store to verify the hypothesis. In Query Store you can also force the old plan, provided that it is still there. (It is not clear to me how you got the data into the new instance.)

The query as such could in my opinion be better written like this.

update f
set    f.freight_fk = f.tracking_shipper
from   fulfillment  f  
where  f.tracking_shipper is not null
  and  f.freight_fk is null
  and  exists (select *
               from   freight_cost fc 
               where  fc.tracking_shipper = f.tracking_shipper
                 and  fc.tenant_key     = f.tenant_key
                 and  (fc.fulfillment_fk is null or fc.fulfillment_fk = f.tracking_shipper))

Whether this will be faster, I don't know, but I think it expresses the intent better. And I removed the two NOLOCK which should not be there.

Just to make sure: You are talking about Azure SQL Managed Instance and not Azure SQL Database?

Share via

Query hang on single user database

1 answer

Your answer