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:

    NTILE(7) OVER(ORDER BY DbSize.TotalDataSize) as bucket, --how to order?
    DbSize.TotalDataSize * 8 / 1024 as size_mb
FROM sys.databases AS [db]
        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?

  • 3
    It's not just a matter of ordering - say you have a single 500GB database, and then 6 50GB databases. Or say you have 6 500GB databases, and 10 5GB databases. There's no way you can order this to guarantee that the groups will be even remotely equal.
    – Brent Ozar
    May 22, 2016 at 11:45
  • Hi, thanks for commenting. Could've been more clear. It doesn't matter if there is one 500GB database in Group 1 and 6 10MB databases in the other Groups. May 22, 2016 at 11:53
  • 2
    You can't use NTILE, it divides it based on number of database. If you take a running total of the amount, starting from the biggest, and divide that by 1/7th of the total amount, you'll get the databases divided roughly to 7 parts. Maybe that's good enough.
    – James Z
    May 22, 2016 at 12:13
  • @JamesZ, the only drawback I can see is that if you have 1 very large DB, with all others much smaller it will produce less than 7 parts.
    – Alex
    May 22, 2016 at 13:45
  • Thanks for the help everyone. However the CTE answer didn't Group them equally enough after testing. I solved the problem using a good ole' cursor after all.. It was enough for my need. It's only maintenance scripts that runs rarely. I just looped through all databases (order by size desc) and put them into the Group with least amount of data currently allocated... Don't know how to Close this question here or what to do. May 26, 2016 at 12:49

2 Answers 2


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 (
        VALUES ('DB1',753100)
    ) AS V(DBName, Size)
, Sequenced AS (
, Balancer AS (
    SELECT *, 1 AS GroupNum, Size AS Group1Total, 0 AS Group2Total, 0 AS Group3Total FROM Sequenced WHERE SEQ = 1
        , 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 

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:

    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]
        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
    -- 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
        -- 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.