Monitoring SQL Azure Database & Server

Monitoring SQL Azure Database & Server

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

http://Extreme-Advice.com

http://www.sqlhub.com

Quick Learn

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

3 thoughts on “Monitoring SQL Azure Database & Server”

  1. Ritesh,

    Your readers may find it useful to know about a monitoring service for the Azure platform that supports active monitoring and alerts for the SQL Azure and SQL Azure/Federations platforms. Feel free to check out

  2. I not sure where you are getting your information, but great topic. I needs to spend some time learning much more or understanding more. Thanks for excellent information I was looking for this information for my mission.

Comments are closed.