Azure SQL Database scripts

I thought that this would be useful for others – couple of scripts that I have been using in Azure for my SQL Databases.

--************************--
-- Azure SQL DB Scripts--
--**************************

-- Wait Stats / 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
-- Currently active sessions
SELECT
sys.dm_exec_sessions.session_id AS [Spid],
CASE is_user_process
WHEN 1
THEN 'User'
WHEN 0
THEN 'System'
END
AS [Session],
HOST_NAME,
PROGRAM_NAME,
login_name,
Status,
cpu_time ,
total_scheduled_time ,
total_elapsed_time
FROM sys.dm_db_session_space_usage INNER join sys.dm_exec_sessions
ON sys.dm_exec_sessions.session_id = sys.dm_db_session_space_usage.session_id

-- Returns information about operations performed on databases
-- Context of Master
SELECT * FROM sys.dm_operation_status
ORDER BY start_time

-- Returns CPU, I/O, and memory consumption for an Azure SQL Database
SELECT
AVG(avg_cpu_percent) AS 'Average CPU Utilization In Percent',
MAX(avg_cpu_percent) AS 'Maximum CPU Utilization In Percent',
AVG(avg_data_io_percent) AS 'Average Data IO In Percent',
MAX(avg_data_io_percent) AS 'Maximum Data IO In Percent',
AVG(avg_log_write_percent) AS 'Average Log Write Utilization In Percent',
MAX(avg_log_write_percent) AS 'Maximum Log Write Utilization In Percent',
AVG(avg_memory_usage_percent) AS 'Average Memory Usage In Percent',
MAX(avg_memory_usage_percent) AS 'Maximum Memory Usage In Percent'
FROM sys.dm_db_resource_stats;

--DTU usage tracked over Timed intervals
SELECT end_time,
(SELECT Max(v)
FROM (VALUES (avg_cpu_percent), (avg_data_io_percent), (avg_log_write_percent)) AS
value(v)) AS [avg_DTU %]
FROM sys.dm_db_resource_stats;

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