How to cancel a synapse on-demand query?

Question

Is there a way to cancel a query that's been been started? I'm unsure if pressing "cancel query" in synapse studio actually cancels it as the query seems to continue in the background.

Answer 1

The procedure that @Ronen Ariely described above is related to SQL Pools (aka SQL DW).

Here is the procedure for SQL on-demand.

Step 1: Find the session which you want to kill using query bellow.

SELECT   
    'Running' as [Status],  
    Transaction_id as [Request ID],  
    'SQL On-demand' as [SQL Resource],  
    s.login_name as [Submitter],  
    s.Session_Id as [Session ID],  
    req.start_time as [Submit time],  
    req.command as [Request Type],  
    SUBSTRING(  
        sqltext.text,   
        (req.statement_start_offset/2)+1,     
        (  
            (  
                CASE req.statement_end_offset    
                    WHEN -1 THEN DATALENGTH(sqltext.text)    
                    ELSE req.statement_end_offset    
                END - req.statement_start_offset  
            )/2  
        ) + 1  
    ) as [Query Text],  
    req.total_elapsed_time as [Duration]  
FROM   
    sys.dm_exec_requests req  
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) sqltext  
    JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id   

Step 2: Use the value in the Session ID column to kill the process which you want to. For example if the Session ID is 81 then execute the following command

kill 81  

Answer 2

Good day,

Is there a way to cancel a query that's been been started?

Actions like "cancel" are not related to the server side but to the client tool which you are using. In the server side we have thread, tasks, and running queries which can be "killed" and not canceled.

When you use a client tool like SSMS or ADS then you have the option to use the "cancel" button but the fact is that this simply execute a simple "kill" query.

So, in short... the answer is yes we can kill a running query using the transact sql command: kill

The full procedure you should use is:

step 1: find the session which you want to kill using the bellow query

SELECT * FROM sys.dm_pdw_exec_sessions  
where [status] = 'Active' and not sql_spid = @@SPID  
GO  

Step 2: use the value in the [session_id] column to kill the process which you want to. for example if the session_id is 'SID210' then execute the following command

kill 'SID210'  
GO  

----------

💬Ronen Ariely
Personal Site | Blog | Facebook | Linkedin

Answer 3

Hi @Travis ,

Welcome to Microsoft Q&A platform and thanks for using this forum.

Updated Answer
As per the latest update from Product team, when you click on Cancel query from synapse studio, currently the queries are cancelled only in client (not end-end). We have escalated this to the product team and they have opened a work item to fix this issue. Once the issue is fixed the queries should be cancelled end-to-end.

As a workaround, in order to kill the On Demand SQL query running in background, please see below latest response from @Stefan Azarić (Product team) on how to get the session ID which you would want to kill and using the session ID, you can kill the query instance.

Hope this info helps. Please let us know if you have further queries.

Apologies for the inconvenience and thank you so much for bringing this to our attention.

Thank you