1

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?

  • 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 '16 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. – Billy Rauséus May 22 '16 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 '16 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 '16 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. – Billy Rauséus May 26 '16 at 12:49
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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