The release cycle of SQL Azure is 4 times a year, and new functionality is…
If you have been working with any other SQL server edition before you most likely used the KILL statement to kill a slow running process or a process that are blocking other transactions. In SQL Azure there is no KILL statement and you can’t kill the blocking connection, but on the other hand, SQL Azure will kill the processes that have been running for more than 5 minutes or is using to much resources.
I guess it’s okey that it’s handled by the platform, but in a high perfomance OLTP system 5 minutes is a long time to wait for a connection that is blocking lots of other processes. To avoid that problem you should make sure you optimize your code, split large data modifications in to smaller blocks and commit your transactions as soon as possible to avoid blockings in the first place. I guess you already heard that recommendation for T-SQL before, and as you can see, it’s even more important in SQL Azure. Follow the guidelines described in http://windowsazurecat.com/2010/10/best-practices-for-handling-transient-conditions-in-sql-azure-client-applications/ to handle connections that may be terminated.
If you want to know whats currently executing in your SQL Azure database you can use a script like this one:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT s.session_id, s.login_name, s.last_request_start_time, s.last_request_end_time, s.open_transaction_count, s.prev_error, r.start_time, r.status, COALESCE(object_name(st.objectid),’AdHoc’), st.text, r.cpu_time, r.reads, r.writes, r.logical_reads, r.granted_query_memory, r.row_count, r.wait_type, r.wait_time, r.last_wait_type, r.blocking_session_id, r.open_transaction_count, qt.query_plan FROM sys.dm_exec_sessions s INNER JOIN sys.dm_exec_requests r ON s.session_id = r.session_id LEFT JOIN sys.dm_tran_session_transactions t ON s.session_id=t.session_id CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) st CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) qt WHERE s.is_user_process=1
This will give you some execution statistics, the code that is executing, the query plan and some wait stats.