How SQL Server DMVs Can Be a Savior in SQL Azure, Too

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.

Basic DMVs

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.