Azure SQL Database Resumable Online Index Rebuild

If you know me by now I like rebuilding indexes and that is no different in Azure. Now we have the ability to resume a paused rebuilding operation rather than cancelling it (Feature currently in public preview). I like this because I have the flexibility to pause it if I feel that it is taking up too much DTU (Database Transaction Unit) usage hence I can free up resources for other operations.

Continue reading

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