SQL Azure has support for few DMV's, which can be used to get information about database. As these DMV's are user database scoped, we must login into database and also user need to have VIEW DATABASE STATE permission.

1. sys.dm_db_partition_stats 
2. sys.database_usage
3. sys.bandwidth.usage

sys.dmdbpartition_stats:

This DMV can be used to get the size of database and various objects in database. To Calculate Database size, we can use below query.

select   sum(reserved_page_count) * 8.0 / 1024 as “size in MB”    from    sys.dm_db_partition_stats

sys.database_usage:

This DMV can be used to get the billing information specific to database. This DMV contains the number of instances of web or business databases on every date. This DMV has 3 columns, time,sku and quantity.

  time: Represent the timestamp
  sku : Indicates type of database, business or web
  quantity: Indicates number of databases on given day.

We can use this DMV information to calculate Database usage cost.

select sku, 
    sum (
    case when sys.database_usage.sku = 'web' 
            then (quantity*9.99/31)   
        when sys.database_usage.sku = 'business' 
            then (quantity*99.99/31)   
    end ) as 'cost'     
    from sys.database_usage     
    where datepart(yy,time) = datepart(yy, getutcdate()) and     
    datepart(mm,time) = datepart(mm, getutcdate())     
    group by sku

Note: 9.99$,99.99$ are costs of web and business databases respectively. These bound to change in future.

sys.bandwidth_usage:

This DMV can be used to get information about the data transfer in/out from databases. This DMV consists of 6 columns.

  a) **Database_Name:** Name of the database from/to which data is transferred.    
  b) **Direction:** Direction of data transferred. Possible values are Ingress and Engress. Ingress refers to data moving into azure database and Engress refers to data moving out of azure database.    
  c) **Class:** Type of bandwidth used. Possible values are Internal and External. Internal refers to data moving into azure platform and External refers to data moving out of  azure platform    
  d) **Time:** Time of data transferred.    
  e) **Time_Period :** Indicates whether data transferred in peak period or off-peak period.    
  f) **Quantity:** Size of data transferred

According to costs of various types of data transfer, you can use this information in this DMV to calculate data transfer cost.

• Reference 1  [2 clicks]
• Reference 2  [2 clicks]
• Reference 3  [32134 clicks]

Category :	SU1(Feb 2010)
Release :	All
Tags :	DMV