I triggered a very inefficient query against a Azure SQL Server instance that has been running now for hours. I have tried looking at the active spid and trying to kill the process that started it but couldn’t find it.

On the performance reports I get a query id. Maybe there is a way to trace the queryid back to a spid and terminate it?

I also can't restart the Azure SQL Server database to terminate it. I tried scaling up and down but no luck.

I can find it by querying:

SELECT * FROM sys.query_store_plan where query_id = 1111

What can I do?

  • Well, what's in sys.dm_exec_requests? – Aaron Bertrand Dec 11 '18 at 14:16
  • Is it still running? If so, run sp_who2 ‘active’ which will give you the SPID, which you can then Kill(spid). if you’re not sure which it is then run it through DBCCInputBuffer(spid) – pix1985 Dec 11 '18 at 15:25
  • Nothing much really. I can't also see nothing strange by doing sp_who2. All are in sleeping or background mode and the only "running" is my own process to do the query (tried KILL on it and couldn't). The way I know it is running is by doing an "Overall Resource Consumption" query where the query is shown as still running (over a day already). – despuestambien Dec 11 '18 at 15:41
  • Might it be that the query id is not related to a running query in the background but rather the same query executed over and over by the application? My concern was that I had triggered a very expensive (with 3 nested SELECTs) on a table with 2Million records that was running in the background, but maybe this is not the case? – despuestambien Dec 11 '18 at 16:09
  • Can you show a screen shot that suggests the query is still running? If it's not in sys.dm_exec_requests and it doesn't show up in sp_who2 then I think your performance report is either wrong or being misinterpreted. – Aaron Bertrand Dec 11 '18 at 19:42

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.