Category Archives: Azure

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

Change Size and Tier of Azure Virtual Machine (VM) using PowerShell Set-AzureVMSize InstanceSize Valid Strings

I was creating a PowerShell script to Scale Virtual Machines, but I couldn’t find a single place with the valid InstanceSize Strings. I was specially interested in being able to change the VM Tier (Basic / Standard). So I came with this list, after executing Set-AzureVMSize with wrong parameters :P

Here’s the list with the valid inputs (at 12 November 2014):

  • ExtraSmall
  • Small
  • Medium
  • Large
  • ExtraLarge
  • A5
  • A6
  • A7
  • A8
  • A9
  • Basic_A0
  • Basic_A1
  • Basic_A2
  • Basic_A3
  • Basic_A4
  • Standard_D1
  • Standard_D2
  • Standard_D3
  • Standard_D4
  • Standard_D11
  • Standard_D12
  • Standard_D13
  • Standard_D14

Equivalency beetween new and old Naming:

  • ExtraSmall => Standard A0
  • Small => Standard A1
  • Medium => Standard A2
  • Large => Standard A3
  • ExtraLarge => Standard A4

If you want to scale to a Standard A3, you can use:

Get-AzureVM -ServiceName $vmName -Name $vmName | Set-AzureVMSize –InstanceSize “Large” | Update-AzureVM

As you can see, the InstanceSize string is used also to change the VM Tier (Basic or Standard). If we want to switch our Stanard A3 to a Basic A0, we can use:

Get-AzureVM -ServiceName $vmName -Name $vmName | Set-AzureVMSize –InstanceSize “Basic_A0” | Update-AzureVM

The InstanceSize string is a bit messy, specially for the most used instances (Extra Small, Small, etc). I assume that this “messiness” if to provide backward compatibility