One of the challenges of working in SQL Azure database is ensuring your database is positioned in the correct service tier so that you are not overpaying or underperforming. SQL Azure database introduced a new DMV and system view that tracks performance related to Database Throughput Units (DTU). It's not going to answer a whole lot of questions by itself, but it can be helpful to view over-time stats for baselines. Used with other monitoring tools, query execution stats, etc., you should be able to dial in performance and find a good match for your service tier.

The sys.dm_db_resource_stats DMV is only available in the target user database and displays the last hour of performance data, to get anything beyond that you need to look at the sys view in the master database.

-- Basic view, last hour. Make sure you are in the target user database.

SELECT * FROM sys.dm_db_resource_stats

-- Last hour of performance using the sys.dm_db_resource_stats DMV.

SELECT DISTINCT
   MIN(end_time) AS StartTime
  ,MAX(end_time) AS EndTime
  ,CAST(AVG(avg_cpu_percent) AS decimal(4,2)) AS Avg_CPU
  ,MAX(avg_cpu_percent) AS Max_CPU
  ,CAST(AVG(avg_data_io_percent) AS decimal(4,2)) AS Avg_IO
  ,MAX(avg_data_io_percent) AS Max_IO
  ,CAST(AVG(avg_log_write_percent) AS decimal(4,2)) AS Avg_LogWrite
  ,MAX(avg_log_write_percent) AS Max_LogWrite
  ,CAST(AVG(avg_memory_usage_percent) AS decimal(4,2)) AS Avg_Memory
  ,MAX(avg_memory_usage_percent) AS Max_Memory    
FROM sys.dm_db_resource_stats

-----------------------------------------------------------------------------------
-- Performance over period of time. Uses the sys.resource_stats table in master db

DECLARE @StartDate date = DATEADD(day, -7, GETDATE()) -- 7 Days

SELECT DISTINCT
   MAX(database_name) AS DatabaseName
  ,MAX(sku) AS PlatformTier
  ,MAX(storage_in_megabytes) AS StorageMB
  ,MIN(end_time) AS StartTime
  ,MAX(end_time) AS EndTime
  ,CAST(AVG(avg_cpu_percent) AS decimal(4,2)) AS Avg_CPU
  ,MAX(avg_cpu_percent) AS Max_CPU
  ,CAST(AVG(avg_data_io_percent) AS decimal(4,2)) AS Avg_IO
  ,MAX(avg_data_io_percent) AS Max_IO
  ,CAST(AVG(avg_log_write_percent) AS decimal(4,2)) AS Avg_LogWrite
  ,MAX(avg_log_write_percent) AS Max_LogWrite
FROM sys.resource_stats
WHERE database_name = 'YourDatabaseName'
   AND start_time > @StartDate;

----------------------------------------------------------------------------
-- Find how well the database workload fits in to its current service tier.

SELECT
   (COUNT(database_name) - SUM(CASE WHEN avg_cpu_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [CPU Fit %]
  ,(COUNT(database_name) - SUM(CASE WHEN avg_log_write_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [Log Write Fit %]
  ,(COUNT(database_name) - SUM(CASE WHEN avg_data_io_percent >= 40 THEN 1 ELSE 0 END) * 1.0) / COUNT(database_name) * 100 AS [Data IO Fit %]
FROM sys.resource_stats
WHERE database_name = 'YourDatabaseName' 
   AND start_time > DATEADD(day, -7, GETDATE());

If the query above returns a value of less than 99.9 for any of the three resources, then you probably have some kind of bottleneck that needs addressing. Look for poor performing queries in the sys.dm_exec_query_stats DMV. If you can't solve your problems with tuning, you might have to make the jump to a higher tier.

This should help you get started dialing in your service tier for a SQL Azure Database.

Data

Capture DTU Performance Stats in SQL Azure Database

Nisor ness