Azure SQL Database DMVs

The following DMVs shown within this blog post are some of the more common ones that I am finding really useful within the Azure world, hopefully I am not the only one.

Sys.geo_replication_links

This query will return primary and secondary databases in a geo-replication partnership. Here you will get the status of replication which could be in 1 of 3 states – pending, seeding or catch-up. You should run this under the master database.

Pending – The creation of the secondary database is being scheduled (preparation state).

Seeding – The link between the primary and secondary is taking place but not yet fully synced.

Catch-up – Databases in the replication partnership are transactionally consistent.

SELECT
database_id,
modify_date,
partner_server,
partner_database,
replication_state,
replication_state_desc
FROM sys.geo_replication_links

dong

If you run the query provided to you via BOL for geo_replication_links you will get an error (an incorrect column name) https://msdn.microsoft.com/en-us/library/mt575501.aspx

Msg 207, Level 16, State 1, Line 16 Invalid column name ‘is_target_role’. Msg 207, Level 16, State 1, Line 17 Invalid column name ‘is_non_redable_secondary’.

sys.dm_geo_replication_link_status

This query will need to be executed under the context of the user database. This query returns replication lag and last replication time for my secondary database.

dong2.JPG

sys.dm_operation_status  

This query needs to be executed under the master database. From this you will be able to get alot of information around activity that has occurred on the logical server.

 SELECT * FROM sys.dm_operation_status
 ORDER BY start_time DESC;

For example you will see below that I recently deleted a database.

dong5

Wait stats

My favourite – waits stats scoped to the database.

WITH Waits AS
(SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
FROM sys.dm_db_wait_stats
)
SELECT W1.wait_type,
CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
FROM Waits AS W1
INNER JOIN Waits AS W2
ON W2.rn <= W1.rn
GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
HAVING SUM(W2.pct) - W1.pct <  99

dsfdfsfsg

Advertisements

3 thoughts on “Azure SQL Database DMVs

  1. Pingback: Azure SQL Database DMVs - SQL Server Blog - SQL Server - Toad World

  2. Pingback: DMVs For Azure SQL Database – Curated SQL

  3. Pingback: Copying your SQL Database | All about SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s