By far I have written quite a few articles to monitor and manage SQL Server (find it here under “DBA Related Articles” section) but what about SQL Azure?
Note: if you are new to Cloud and SQL Azure, do look at “Microsoft Windows / SQL Azure:” section to get understanding from scratch.
Microsoft has very good concept of system views to provide the very important and crucial information to the admin. SQL Azure is not an exception. Herewith, I am going to explain some of common and very powerful system views which cater you with so many important information you required to accomplish your admin task in SQL Azure.
sys.dm_exec_connections : This view will return a row per each connection made toward SQL Azure database. Some of the important fields are session_id, connect_time, num_read, num_write, last_read, last_write and many more.
sys.dm_exec_sessions: This DMV will return all the active connection no matter whether it is user connection or system’s connection for internal task. Important fields from this DMV are session_id, login_time, host_name, program_name, login_name
sys.dm_exec_requests: This system view will cater your need to know each request comes to your database. Some of the important fields are session_id, start_time, status, command, database_id, user_id, blocking_session_id, wait_type, percent_complete.
sys.dm_tran_database_transactions: This DMV will return database level transaction; here is the list of important columns from this DMV: transaction_id, database_id, database_transaction_begin_time, database_transaction_type, database_transaction_state.
sys.dm_db_partition_stats: This DMV returns page & row-count information for each partition in current database. Some of the important fields from this DMV are partition_id, object_id, used_page_count, reserve_page_count, row_count to name a few.
I will have detail follow up article which will return meaningful information by joining few of the DMVs together till then have fun !!!
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.