Automatic index management in Azure SQL Database

SELECT  name, desired_state_desc, actual_state_desc, reason_desc

FROM sys.database_automatic_tuning_options

When you enabled automatic index tuning for the Performance Recommendations page will identify indexes that can be created or dropped depending on query performance. Remember this feature isn't available for on-premises databases and It is only available for Azure SQL Database.

Creating new indexes can consume resources, and the timing of the index creations is critical to ensure no negative effect is felt on your workloads.

Azure SQL Database will monitor the resources required to implement new indexes to avoid causing performance degradation. The tuning action is postponed until the available resources are available, for example if resources are required for existing workloads and not available for creating an index.

Monitoring ensures any action taken won't harm performance. If an index is dropped and query performance noticeably degrades, the recently dropped index will be automatically recreated

Monitor open transactions awaiting commit or rollback run Query

 SELECT tst.session_id, [database_name] = db_name(s.database_id)

    , tat.transaction_begin_time

    , transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 

    , transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'

        WHEN 2 THEN 'Read-only transaction'

        WHEN 3 THEN 'System transaction'

        WHEN 4 THEN 'Distributed transaction' END

    , input_buffer = ib.event_info, tat.transaction_uow     

    , transaction_state  = CASE tat.transaction_state    

        WHEN 0 THEN 'The transaction has not been completely initialized yet.'

        WHEN 1 THEN 'The transaction has been initialized but has not started.'

        WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'

        WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'

        WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'

        WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'

        WHEN 6 THEN 'The transaction has been committed.'

        WHEN 7 THEN 'The transaction is being rolled back.'

        WHEN 8 THEN 'The transaction has been rolled back.' END 

    , transaction_name = tat.name, request_status = r.status

    , tst.is_user_transaction, tst.is_local

    , session_open_transaction_count = tst.open_transaction_count  

    , s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process

FROM sys.dm_tran_active_transactions tat 

INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id

INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 

LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id

CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib

ORDER BY tat.transaction_begin_time DESC;