Estimate the price of moving a database to Azure SQL Managed Instance

azure-pricing-managedinstance-IO-estimate.sql

/*
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)