An Azure SQL instance is running at DTU of 399% approx. This is slowing my whol application down.
A worker role started a number of Stored Procedures last night and seems to have sent the processing through the roof.
Is there any way to tell exactly what processes/procedures are causing the high DTU? Is there any way to end those processes/procedures?
I am new to Azure so appreciate any pointers that may help me to determine the exact cause and cure for my crazy DTU %.
You can use the view sys.dm_exec_requests to see running processes, and sys.dm_exec_query_stats to see stats on queries that have run in the past but still have their plans in cache, including frequency of running and average resource use per query.
These views include a column called SQL_HANDLE which can be used to probe the function sys.dm_exec_sql_text() documented here
(This function can also be joined directly to the first two tables I mentioned -- there are examples in the doc.)
There's a good blog on the topic here
Once you find a running query that you want to kill, you can issue the KILL command, based on the session_id reported in sys.dm_exec_requests, from an interactive query tool or the portal's SQL interface as long as you have admin privileges.
