Azure

Will post shortly. Padala Kameswaraiah

SQL Server 2019 – Compatibility Level in Azure


I am not sure when this became available but for Azure SQL Database 150 compatibility level is now available. Last time I created a database few weeks ago, only level 140 ( SQL Server 2017) was available so I think it is a recent thing.

Upon some testing, if you create a new Azure SQL Database by default it is 150 as per the screen shot below (from the script action command)



Via SSMS (SQL Server Management Studio) you can view the database properties, you will clearly see the new level.



How to find blocking queries in SQL Azure

The query below will display the top ten running queries that have been the longest total elapsed time and are blocking other queries.

SELECT TOP 10 r.session_id, r.plan_handle,  r.[sql_handle], r.request_id,      r.start_time, r.[status],      r.command, r.database_id,      
r.[user_id], r.wait_type,      r.wait_time, r.last_wait_type,      r.wait_resource, r.total_elapsed_time,      r.cpu_time, r.transaction_isolation_level,      r.row_count, st.[text]  
FROM sys.dm_exec_requests r  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st  
WHERE r.blocking_session_id = 0       
and r.session_id IN  (SELECT distinct(blocking_session_id)  FROM sys.dm_exec_requests)  
GROUP BY r.session_id, r.plan_handle,      r.[sql_handle], r.request_id,      r.start_time, r.[status],      r.command, r.database_id,      r.[user_id], r.wait_type,      r.wait_time, r.last_wait_type,      r.wait_resource, r.total_elapsed_time,      r.cpu_time, r.transaction_isolation_level,      r.row_count, st.[text]  

ORDER BY r.total_elapsed_time DESC


No comments:

Post a Comment