Tag Archives: sql

Some useful Sql Azure Database Data Management Views (DMV)

I’ll just share some useful DMV’s for monitoring Sql Azure Database

Real Time Information

(it’s actually near-real time)

Run this queries against your Database

Real Time Session and Connection Information

SELECT
  s.session_id
  ,s.login_time
  ,s.host_name
  ,s.program_name
  ,s.login_name
  ,s.status
  ,s.cpu_time
  ,s.memory_usage
  ,s.total_scheduled_time
  ,s.total_elapsed_time
  ,s.last_request_start_time
  ,s.last_request_end_time
  ,s.reads   writes
  ,s.logical_reads
  ,st1.text
  , c.*
FROM sys.dm_exec_connections AS c
JOIN sys.dm_exec_sessions AS s
ON c.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) as st1
order by c.num_reads desc

Using sys.dm_exec_connections and sys.dm_exec_sessions together you can gather some nice information about whats happening right now in your DB

Real Time Resource Usage Stats

select * from sys.dm_db_resource_stats

With this DMV we get information about avg resource usages(%cpu,%data_io,etc)

Real Time CPU And Query Plan Data

SELECT TOP 50 
qs.creation_time
, qs.last_execution_time
, qs.execution_count
, qs.total_worker_time as total_cpu_time
, qs.max_worker_time as max_cpu_time
, qs.total_elapsed_time
, qs.max_elapsed_time
, qs.total_logical_reads
, qs.max_logical_reads
, qs.total_physical_reads
, qs.max_physical_reads
,t.[text], qp.query_plan
, t.dbid
, t.objectid
, t.encrypted
, qs.plan_handle
, qs.plan_generation_num 
FROM sys.dm_exec_query_stats qs 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
ORDER BY qs.total_worker_time DESC

This DMV executed in the right time can give you a lot of high value information to troubleshoot performance issues.

Historical telemetry data

Run this queries against the master. They’re actually not run against your own master, they access Microsoft Meta-Databases. Some of this queries may take quite a lot time.

Historical connection data

select * from sys.database_connection_stats

Historical connection data

Historical resource usage

select * from sys.resource_stats

Very similar to sys.dm_db_resource_stats but with data from all your Db lifetime. Very helpful no identify recurrent periods of high DTU ussage