Be it SQL Server or the improved version of SQL Server on the cloud called as SQL Azure, when it comes to monitoring, there is nothing as powerful as DMVs (Dynamic Management Views). For a primer, Dynamic Management Views (DMVs) are a set of virtual views which expose the internal states of SQL Server deployment. This is an important step to monitor and troubleshoot any SQL Server deployments from on-premise to cloud-enabled versions.
In this blog post, let us take a tour of some of the powerful DMVs that we can use when working with SQL Azure. Not all DMVs that are available for the on-premise deployment are available on SQL Azure and vice versa. We’ll look now at some of the common DMVs one must know when working with SQL Azure.
Some of the basic DMVs from sessions, requests, connections etc are common between both the environments. Hence a DBA will relate to these easily.
-- Will return the Server Name we are running on SELECT @@Servername -- DMV: dm_exec_connections gets the connection information SELECT getdate() as "RunDateTime", c.* FROM sys.dm_exec_connections c Go -- DMV: dm_exec_sessions gives the current sessions SELECT getdate() as "RunDateTime", s.* FROM sys.dm_exec_sessions s Go --DMV: dm_exec_requests gives the active sessions/spids currently SELECT getdate() as "RunDateTime", st.text, r.* FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as st GO
Troubleshooting for Reads / Writes and Blocking
End of the day SQL Azure runs a similar SQL Server instance behind the scenes. So the troubleshooting that involves reads and writes can be got from query plans and query stats DMVs. A typical top 10 query would look like:
would look like: -- Get top 10 queries by total_logical_reads SELECT TOP 10 getdate() AS "Today", st.text, qp.query_plan, (qs.total_logical_reads/qs.execution_count) AS avg_logical_reads, (qs.total_logical_writes/qs.execution_count) AS avg_logical_writes, (qs.total_physical_reads/qs.execution_count) AS avg_phys_reads, qs.* FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp ORDER BY qs.total_logical_reads DESC GO -- Get top 10 queries by total_logical_writes SELECT TOP 10 getdate() AS "Today", st.text, qp.query_plan, (qs.total_logical_reads/qs.execution_count) AS avg_logical_reads, (qs.total_logical_writes/qs.execution_count) AS avg_logical_writes, (qs.total_physical_reads/qs.execution_count) AS avg_phys_reads, qs.* FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp ORDER BY qs.total_logical_writes DESC GO
I have mentioned in my blogs previously that blocking is inevitable when working with SQL Server, but the idea is to reduce the amount of time blocking occurs in the system.
-- SQL Statement which are blocking SELECT wait_type , wait_time , wait_resource , database_id , blocking_session_id , r.command , qt.dbid ,SUBSTRING(qt.text , CASE WHEN (r.statement_start_offset IS NULL OR r.statement_start_offset <= 1) THEN 1 ELSE (r.statement_start_offset/2) END , CASE WHEN r.statement_end_offset IS NULL THEN len(qt.text) ELSE ((r.statement_end_offset-r.statement_start_offset)/2) END ) AS stmt , qp.query_plan FROM sys.dm_exec_requests r CROSS APPLY sys.dm_Exec_sql_text(sql_handle) qt CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp WHERE wait_type <> ''
Special DMVs in SQL Azure
Working on any cloud system means we need to know how we are utilizing our bandwidth. This is critical because we pay based on bandwidth usage. In addition to this, SQL Azure also gives us capabilities to create copies of our database or readonly purposes. These are also exposed via various DMVs. Let me list them for your reference.
|To get information about the bandwidth used by each database in your SQL Database server.||sys.bandwidth_usage|
|To get information about the database state of a database that is being copied.||sys.databases|
|To get information about the replica databases on a given server.||sys.dm_database_copies|
|To get information about all the replica databases of a given source database.||sys.dm_continuous_copy_status|
Illustrating a simple usage of how a replication lag can be identified on SQL Azure:
-- To verify Replication Lag and Replica Status SELECT partner_server ,last_replication ,replication_lag_sec ,replication_state ,replication_state_desc FROM sys.dm_continuous_copy_status
In this blog, we saw some of the basic DMV queries one can use when working with SQL Azure. We just scratched the surface when working with DMVs in this blog. In future blogs we will discuss some additional DMVs one can use as DBAs.