Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Estimate the price of moving a database to Azure SQL Managed Instance
/*
This script is intended to help estimate the expected pricing for a database to be migrated to an Azure SQL Managed Instance.
This only captures the I/O activity directly associated with user queries. Background processes are different between
Managed Instances and SQL Server on-premises, so this number should not be treated as an exact prediction.
Most workloads have periodic ups and downs at least weekly, and sample periods shorter than one month
or especially one week are much more likely to be estimated from a highly-skewed sample period.
If you are using preview pricing, GA, or have any other factor affecting the pricing, update the parameter below.
*/
DECLARE @pricePerMegaIo NUMERIC(9,3) = 0.20; --Standard (preview) pricing as of 2018-10-11
------------------------------------
DECLARE @millisecondsPerMonth NUMERIC(10,0) = 30.4 * 24 * 60 * 60 * 1000;
SELECT
DB_NAME(file_stats.database_id) AS [database_name]
, SUM(file_stats.dollars_per_month_reads) AS [dollars_per_month_reads]
, SUM(file_stats.dollars_per_month_writes) AS [dollars_per_month_writes]
, CONVERT(NUMERIC(5,3), AVG(file_stats.sample_months)) AS [sample_months]
FROM (
SELECT
vfs.database_id
, CONVERT(NUMERIC(19,4), CONVERT(NUMERIC(19,3), vfs.num_of_reads) / (vfs.sample_ms/1000)) AS [reads_per_second]
, CONVERT(NUMERIC(19,4), CONVERT(NUMERIC(19,3), vfs.num_of_writes) / (vfs.sample_ms/1000)) AS [writes_per_second]
, vfs.sample_ms / @millisecondsPerMonth AS [sample_months]
, CONVERT(MONEY, (@pricePerMegaIo * (CONVERT(NUMERIC(13,0), vfs.num_of_reads) / 1000000)) / (vfs.sample_ms / @millisecondsPerMonth)) AS [dollars_per_month_reads]
, CONVERT(MONEY, (@pricePerMegaIo * (CONVERT(NUMERIC(13,0), vfs.num_of_writes) / 1000000)) / (vfs.sample_ms / @millisecondsPerMonth)) AS [dollars_per_month_writes]
FROM sys.dm_io_virtual_file_stats (NULL, NULL) vfs
) file_stats
GROUP BY ROLLUP (database_id)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment