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.
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
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’.
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.
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.
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