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?