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.
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
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.
case when sys.database_usage.sku = 'web'
when sys.database_usage.sku = 'business'
end ) as 'cost'
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.
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.
Have you written a blog post or article related to this feature and want to get them added here?