Split databases into groups of roughly equal datasize

Question

I am trying to split all databases from sys.databases into 7 roughly equally sized Groups based on data ROWS size.

I want a table that contains every database and these databases are in 7 different Groups (Days of weeks - monday thru sunday).

Code that Groups into 7 Groups, but not equal size:

SELECT
    db.name,
    NTILE(7) OVER(ORDER BY DbSize.TotalDataSize) as bucket, --how to order?
    DbSize.TotalDataSize * 8 / 1024 as size_mb
FROM sys.databases AS [db]
CROSS APPLY
    (
        SELECT SUM(mf.size) AS TotalDataSize
        FROM sys.master_files AS [mf]
        WHERE mf.database_id = db.database_id
        AND mf.type_desc = 'ROWS'
    ) AS DbSize

How can I order so not all big databases end up in the same bucket?

Needs to support SQL Server 2008 and newer.

Edit: it doesn't matter if, let's say, 1 database is huge and others very tiny. The big database would then (optimally) reside in its own Group, but could still be larger than any other. The important thing is that not the 2-3 biggest databases are in the same Group.

I could do a cursor and for each database, see which Group has the least amount of data in it, but I felt it must be some better way to achieve this using set-based code?

Answer 1

A bit late to the party, but...

Here is a 3 group example which replaces a cursor with a CTE. The same can be extended to more groups, just a little longer with the some of the case statements, but the same pattern. A scaler UDF would be a good fit here in place of some of the case logic.

Basically you work from highest to lowest size and assign to the group with the lowest cumulative value, using the group number as a tie breaker.

WITH TestData AS (
    SELECT * FROM (
        VALUES ('DB1',753100)
            ,('DB2',380472)
            ,('DB3',312521)
            ,('DB4',813185)
            ,('DB5',141455)
            ,('DB6',745083)
            ,('DB7',153428)
            ,('DB8',454101)
            ,('DB9',567718)
            ,('DB10',722005)
            ,('DB11',146021)
            ,('DB12',930332)
            ,('DB13',836972)
    ) AS V(DBName, Size)
)
, Sequenced AS (
    SELECT *, ROW_NUMBER () OVER (PARTITION BY (SELECT 1) ORDER BY Size DESC) AS Seq FROM TestData 
)
, Balancer AS (
    SELECT *, 1 AS GroupNum, Size AS Group1Total, 0 AS Group2Total, 0 AS Group3Total FROM Sequenced WHERE SEQ = 1
    UNION ALL SELECT 
        Sequenced.*
        , CASE 
            WHEN Group3Total < Group2Total AND Group3Total < Group1Total THEN 3
            WHEN Group2Total < Group1Total THEN 2 
            ELSE 1  
        END AS GroupNum
        , CASE 
            WHEN Group3Total < Group2Total AND Group3Total < Group1Total THEN Group1Total
            WHEN Group2Total < Group1Total THEN Group1Total 
            ELSE Group1Total + Sequenced.Size  
        END AS Group1Total
        , CASE 
            WHEN Group3Total < Group2Total AND Group3Total < Group1Total THEN Group2Total
            WHEN Group2Total < Group1Total THEN Group2Total + Sequenced.Size 
            ELSE Group2Total 
        END AS Group2Total
        , CASE 
            WHEN Group3Total < Group2Total AND Group3Total < Group1Total THEN Group3Total + Sequenced.Size 
            ELSE Group3Total
        END AS Group3Total
        FROM Sequenced 
        JOIN Balancer ON  Balancer.Seq = Sequenced.Seq - 1

)
SELECT * FROM Balancer 

Answer 2

The very crude method is to pair up largest database with smallest, then 2nd largest with 2nd smallest and so on. This should work assuming that your database sizes are evenly distributed.
Implementation has been hacked toghether in 5 min, so can probably be improved a lot:

;WITH DBSizes AS
(
SELECT
    db.name,
    DbSize.TotalDataSize * 8 / 1024 as size_mb,
    ROW_NUMBER() OVER( ORDER BY DbSize.TotalDataSize ) AS RankedSizeInc,
    ROW_NUMBER() OVER( ORDER BY DbSize.TotalDataSize DESC ) AS RankedSizeDes
FROM sys.databases AS [db]
CROSS APPLY
    (
        SELECT SUM(mf.size) AS TotalDataSize
        FROM sys.master_files AS [mf]
        WHERE mf.database_id = db.database_id
        AND mf.type_desc = 'ROWS'
    ) AS DbSize
)
SELECT *, NTILE(7) OVER(ORDER BY RankedSizeInc DESC) AS Quartile
FROM
    -- Pair-up largest and smallest DBs toghether, then 2nd largest and 2nd smallest and so on
    -- TOP 50 PERCENT removes duplicates produced by UNION ALL
    ( SELECT TOP 50 PERCENT *
    FROM(
        -- Return DBs in order of increasing size
        SELECT name, size_mb, RankedSizeInc
        FROM DBSizes AS a
        UNION ALL
        -- Return DBs in order of decreasing size
        SELECT name, size_mb, RankedSizeDes
        FROM DBSizes AS b
    ) AS c
    ORDER BY RankedSizeInc
) AS d