USE TEMPDB
GO
IF OBJECT_ID('dbo.GetStorageSpaceCapacity') IS NULL EXEC('CREATE PROC dbo.GetStorageSpaceCapacity AS');
GO
ALTER PROCEDURE dbo.GetStorageSpaceCapacity
-- GetStorageSpaceCapacity v1.1
-- (C) 2020, Vedran Kesegic
-- Feedback: vedran.kesegic@gmail.com
-- http://blog.sqlxdetails.com
--
--	For given physical disks calculates max capacity of Virtual Disk (VD) that can fit. Depends on resiliency type and number of columns (properties of VD).
--
-- Microsoft FAQ: https://social.technet.microsoft.com/wiki/contents/articles/11382.storage-spaces-frequently-asked-questions-faq.aspx
-- HISTORY
-- 10.08.2020 Vedran Kesegic	v1.0 Initial version
-- 15.11.2022 Vedran Kesegic	v1.1 Bugfix: In some configurations calculation showed less capacity than actually achievable. Works slower but correct.
(	@ResiliencyType NVARCHAR(50), -- SIMPLE, MIRROR/2WAYMIRROR, 3WAYMIRROR, PARITY/SINGLEPARITY, DOUBLEPARITY
	@NumberOfColumns INT,
	@DiskSizesGB NVARCHAR(1000), -- CSV with disk sizes in GB, any order. Eg: '1000, 1000,2000'
	@Debug BIT = 0
) AS
BEGIN
	SET NOCOUNT, XACT_ABORT ON;
	DECLARE
		@disksWritten INT, -- disks written at once, int. Calculate from Columns and VD Type. Eg 3 columns mirror has DW=6
		@capacityPct FLOAT, -- 0-1. Capacity per Disks Written. Calculate from Columns and VD type. Eg, mirror has 0.50, Simple has 1.00
		@totalDisks INT, -- total disks in Storage Pool
		@minDisks INT, -- minimaln num of disks for that resiliency type. Just for a check.
		@minColumns INT,
		@disksPerColumn INT -- determined solely by resiliency
	IF ISNULL(@NumberOfColumns, 0) < 1 OR @NumberOfColumns > 128 THROW 50000, 'Number of columns must be in 1-128 range', 1

	SELECT DiskId = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
		CapacityGB = CONVERT(INT, s.value), FreeGB = CONVERT(INT, s.value)
	INTO #disks
	FROM STRING_SPLIT(@DiskSizesGB, ',') s
	WHERE CONVERT(INT, s.value) > 0;
	SET @totalDisks = @@ROWCOUNT;
	IF @totalDisks = 0 THROW 50000, 'Please specify valid array of disk sizes in GB, Comma-Separated List', 1

	SELECT @disksWritten = v.DisksPerColumn * @NumberOfColumns, @capacityPct = v.CapacityPct, @minDisks = v.MinDisks, @disksPerColumn = v.DisksPerColumn,
		@minColumns=v.MinDisks / v.DisksPerColumn
	FROM
	(	VALUES
		('SIMPLE', 1, 1., 1),
		('MIRROR', 2, 0.5, 2),
		('2WAYMIRROR', 2, 0.5, 2),
		('3WAYMIRROR', 3, 1./3, 3), -- actually, min is 5 disks which is artificial limit my MS to have a "voting quorum"
		('PARITY',       1, (@NumberOfColumns-1.)/@NumberOfColumns, 3),
		('SINGLEPARITY', 1, (@NumberOfColumns-1.)/@NumberOfColumns, 3),
		('DOUBLEPARITY', 1, (@NumberOfColumns-2.)/@NumberOfColumns, 5)
	) v(ResiliencyType, DisksPerColumn, CapacityPct, MinDisks)
	WHERE v.ResiliencyType = @ResiliencyType;
	IF @@ROWCOUNT <> 1 THROW 50000, 'Valid resiliencies are: SIMPLE, MIRROR, 2WAYMIRROR, 3WAYMIRROR, PARITY, SINGLEPARITY, DOUBLEPARITY', 1;

	IF @Debug = 1 BEGIN
		SELECT totalDisks=@totalDisks, minDisks=@minDisks, disksWritten = @disksWritten, capacityPct = @capacityPct
		SELECT [disks]='DEBUG', * FROM #disks
	END

	IF @totalDisks < @minDisks THROW 50000, 'More drives are needed for that type of resiliency', 1;
	IF @NumberOfColumns < @minColumns THROW 50000, 'Column count must be higher for that resiliency', 1;
	IF @totalDisks < @NumberOfColumns * @disksPerColumn THROW 50000, 'More drives are needed for that number of columns and resiliency combination', 1;

	-- Let's calculate capacity!
	-----------------------------
	-- We use only: @disksWritten, @capacityPct, and array of disk sizes
	DECLARE @TopN TABLE(DiskId INT PRIMARY KEY, FreeGB INT NOT NULL);
	DECLARE @chunkSizeGB INT, -- how many GB are written (allocated) per disk. Aka "Interleave" or allocation unit per disk.
		@capacityGB FLOAT = 0 -- result
	WHILE 1=1 BEGIN
		DELETE @TopN; -- otherwise rows would pile-up
		INSERT INTO @TopN
		--SELECT TOP(@disksWritten) d.DiskId, d.FreeGB
		SELECT TOP(@disksWritten) d.DiskId, 1
		FROM #disks d
		WHERE d.FreeGB > 0
		ORDER BY d.FreeGB DESC, d.DiskId DESC
		IF @@ROWCOUNT < @disksWritten BREAK; -- no more disks that could fit this write

		--SELECT @chunkSizeGB = MIN(t.FreeGB) FROM @TopN t;
		SELECT @chunkSizeGB = 1;
		SET @capacityGB += @chunkSizeGB * @disksWritten * @capacityPct; -- stripe size * capacity%

		IF @Debug = 1 SELECT capacityPct=@capacityPct, chunkSizeGB=@chunkSizeGB, disksWritten=@disksWritten, Multiplied= @capacityPct * @chunkSizeGB * @disksWritten, NewCapacity=@capacityGB
		IF @Debug = 1 SELECT t.* FROM @TopN t ORDER BY t.FreeGB DESC, t.DiskId DESC

		-- Decrease free space on disks
		UPDATE d SET d.FreeGB -= @chunkSizeGB
		FROM #disks d
			JOIN @TopN t ON t.DiskId = d.DiskId

	END;

	-- Summary result. Note that these do NOT have to add-up, except in SIMPLE resiliency!
	SELECT
		DiskGB = SUM(d.CapacityGB), -- raw sum of all disks
		LeftoverGB = SUM(d.FreeGB),  -- how much leftover because disks are uneven size
		CapacityGB = CONVERT(INT, ROUND(@capacityGB, 0)) -- how much data can fit
	FROM #disks d

	-- Per disk
	SELECT d.DiskId, DiskGB = d.CapacityGB, LeftoverGB = d.FreeGB FROM #disks d ORDER BY d.DiskId
END
GO
/*
EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='SIMPLE', @NumberOfColumns=2, @DiskSizesGB='1000, 1000'
EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='SIMPLE', @NumberOfColumns=2, @DiskSizesGB='1000, 1000, 2000' -- added new 2TB drive
EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='SIMPLE', @NumberOfColumns=2, @DiskSizesGB='1000, 1000, 2500' -- added 2.5TB drive

EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='SIMPLE', @NumberOfColumns=1, @DiskSizesGB='1000, 2000, 3000'
EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='SIMPLE', @NumberOfColumns=2, @DiskSizesGB='1000, 2000, 3000'
EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='SIMPLE', @NumberOfColumns=3, @DiskSizesGB='1000, 2000, 3000'

EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='MIRROR', @NumberOfColumns=1, @DiskSizesGB='1000, 1000'
EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='MIRROR', @NumberOfColumns=1, @DiskSizesGB='1000, 1000, 2000'
EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='MIRROR', @NumberOfColumns=1, @DiskSizesGB='1000, 1000, 2500'
EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='MIRROR', @NumberOfColumns=1, @DiskSizesGB='1200, 1200, 1200' -- Capacity 1800GB, 0 leftover!

EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='PARITY', @NumberOfColumns=3, @DiskSizesGB='1000, 1000, 2000, 3000, 4000'
EXEC dbo.GetStorageSpaceCapacity @ResiliencyType='PARITY', @NumberOfColumns=4, @DiskSizesGB='1000, 1000, 2000, 3000, 4000'
*/