April 9, 2012 at 3:13 pm
The background for this is that I am trying to create a table that gives a list of all databases in a server (could be up to 500 databases) and assign a group to them 1-5 based on database size. I am trying to create a table that would be used to parallelize reindex/rebuild proc on multiple databases at the same time. I don't want all the largest size databases in the same group I want to have it somewhat evenly spread out in each group to try to minimize processing time.
I am still in the testing phase to see what the best way is to group together the dbs but I would like to start with this. I have tried using Ntile but that doesn't work with this, I can't figure out the best way to get the db groupings based on size.
This is what I have so far.
Select database_id, name, ROW_NUMBER() over( partition by owner_sid order by FileSizeMB DESC)as RankID, sub.FileSizeMB
--INTO #temp
from sys.databases ds
INNER JOIN (select
db.[database_id] as 'DBID'
,db.[name] as 'Database Name'
,SUM((((CAST(af. as DECIMAL(18,4)) * 8192) /1024) /1024)) as 'FileSizeMB'
from sys.databases db
inner join sys.master_files af
on db.database_id = af.database_id
where db.state=0 and db.owner_sid<>0x01 and db.database_id not in (25,5,6,13,9,8)
GROUP BY db.database_id, db.name)sub ON ds.database_id=sub.[DBID]
where state=0 and owner_sid<>0x01 and database_id not in (25,5,6,13,9,8)
order by RankID
This is the results of the above query
database_idnameRankIDFileSizeMB
20 2379_dev195716.37500000000000
19 3349_dev270851.62500000000000
17 1012_dev332615.06250000000000
18 4701_dev416369.56250000000000
15 3523_dev57732.56250000000000
16 7185_dev62713.18750000000000
10 0028_dev71009.25000000000000
12 10305_dev8320.00000000000000
14 10304_dev9320.00000000000000
11 3486_dev10113.62500000000000
What I would like to have is in this case where there are only 10 databases and there would be only 2 dbs per group 1 and 10 would be together, 2 and 9, 3 and 8 etc.
Does this make sense? Any help would be appreciated
Thanks
April 9, 2012 at 7:56 pm
pamozer (4/9/2012)
Does this make sense?
Perfectly.
WITH
ctePreAgg AS
( --=== Find the total MB of each database and number both forwards and backwards
SELECT [RowNumAsc] = ROW_NUMBER() OVER (ORDER BY SUM(af.) ASC )-1,
[RowNumDesc]= ROW_NUMBER() OVER (ORDER BY SUM(af.) DESC)-1,
[DBID] = db.[database_id],
[DBName] = db.[name],
[DBSizeMB] = CAST(SUM(af.)/128.0 AS DECIMAL(9,2))
FROM sys.databases db
INNER JOIN sys.master_files af
ON db.database_id = af.database_id
WHERE db.state = 0
AND db.owner_sid <> 0x01
AND db.database_id NOT IN (25,5,6,13,9,8)
GROUP BY db.database_id, db.name
)
SELECT [DBID], [DBName], [DBSizeMB],
[DBGroup] = CASE -- This does the "magic" numbering using
-- a simple conditional MODULO
WHEN RowNumDesc <= RowNumAsc
THEN RowNumDesc % 5 + 1
ELSE RowNumAsc % 5 + 1
END
FROM ctePreAgg
ORDER BY [RowNumDesc]
;
I also simplified your MB calculation. There are 128 pages per MB.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2012 at 10:05 am
Thank You. So now looking at the results more clearly, this does give me closer to what I am looking for the only thing is when you have more databases I am trying to keep each group approximately the same amount megabytes.DBNameDBSizeMBDBGroup
2039_prod33011.51
0968_prod6605.3751
1885_prod2655.3751
2647_prod1095.81251
3493_prod595.81251
0860_prod255.3751
9699_prod2001
9804_prod2001
9875_prod2001
9944_prod2001
10009_prod2001
10068_prod2001
10126_prod2001
2025_prod155.3751
2916_prod145.81251
3018_prod112.68751
2425_prod95.81251
3011_prod95.81251
9239_prod95.81251
8827_prod95.81251
1526_prod95.3751
[highlight=#ffff11]46511.751 Total[/highlight]
2134_prod105.3752
9268_prod95.81252
8582_prod95.81252
2534_prod95.81252
3043_prod112.68752
10155_prod95.81252
2925_prod145.81252
2037_prod155.3752
10127_prod2002
10070_prod2002
10014_prod2002
9955_prod2002
9877_prod2002
9806_prod2002
9706_prod2002
2489_prod245.81252
2130_prod505.3752
5458_prod945.81252
3338_prod2445.81252
5800_prod5705.3752
0801_prod23605.3752
[highlight=#ffff11]35756.06252 Total[/highlight]
3783_prod17106.93753
0793_prod5605.3753
0217_prod2355.3753
2061_prod905.3753
1675_prod505.3753
2427_prod245.81253
9724_prod2003
9811_prod2003
9889_prod2003
9957_prod2003
10022_prod2003
10083_prod2003
10128_prod2003
2468_prod195.81253
2560_prod145.81253
10161_prod95.81253
2988_prod112.68753
2605_prod95.81253
8587_prod95.81253
9277_prod95.81253
1802_prod105.3753
[highlight=#ffff11]29067.18753 Total[/highlight]
1910_prod105.3754
9285_prod95.81254
8755_prod95.81254
2705_prod95.81254
2929_prod112.68754
10162_prod95.81254
2593_prod145.81254
2996_prod195.81254
10130_prod2004
10087_prod2004
10023_prod2004
9971_prod2004
9899_prod2004
9821_prod2004
9766_prod2004
2941_prod245.81254
2901_prod445.81254
2721_prod895.81254
2660_prod2161.54
0645_prod4905.3754
4605_prod14311.54
[highlight=#ffff11]25308.754 Total[/highlight]
1621_prod13405.3755
2550_prod4795.81255
2510_prod2145.81255
2717_prod845.81255
2954_prod395.81255
2933_prod245.81255
9767_prod2005
9825_prod2005
9902_prod2005
9975_prod2005
10025_prod2005
10088_prod2005
10137_prod2005
2566_prod195.81255
9569_prod145.81255
2349_prod95.81255
2719_prod112.68755
0133_prod106.1255
2828_prod95.81255
8767_prod95.81255
9329_prod95.81255
[highlight=#ffff11]24178.1255 Total[/highlight]
Any thoughts on How I can do that?
April 10, 2012 at 10:12 am
This gets me a bit closer.
SELECT NTILE(10) OVER (PARTITION BY groupmb ORDER BY database_id) AS groupid, database_id, NAME,sub.DataMB, sub.groupmb
FROM (
Select database_id, name, dbi.DataMB,
CASE WHEN dbi.DataMB <1000 THEN 1
WHEN dbi.DataMB <5000 THEN 2
WHEN dbi.DataMB<10000 THEN 3
ELSE 4 END AS groupmb
--INTO #temp
from sys.databases ds
INNER JOIN CustomerDbInfo dbi ON ds.name=dbi.databasename
where state=0 and owner_sid<>0x01 and database_id not in (25,5,6,13,9,8)) sub
ORDER BY groupid
DBIDDBNameDBSizeMBDBGroup
2022039_prod33011.51
150327_prod12905.3751
310968_prod6605.3751
351117_prod3555.3751
631885_prod2655.3751
422382_prod2105.3751
792647_prod1095.81251
591663_prod755.3751
3183493_prod595.81251
2202075_prod305.3751
280860_prod255.3751
2052045_prod205.3751
1419699_prod2001
1479775_prod2001
1539804_prod2001
1619827_prod2001
1679875_prod2001
1749910_prod2001
1849944_prod2001
1899977_prod2001
1710009_prod2001
2910032_prod2001
3910068_prod2001
5210092_prod2001
6510126_prod2001
7510141_prod2001
712650_prod195.81251
1952025_prod155.3751
2142916_prod145.81251
564640_prod145.81251
2982414_prod145.751
2763018_prod112.68751
160185_prod106.1251
3292453_prod95.81251
3022425_prod95.81251
1582848_prod95.81251
2693011_prod95.81251
1129081_prod95.81251
1199239_prod95.81251
109517_prod95.81251
1058827_prod95.81251
721526_prod95.3751
68720.751 Total
1319583_prod95.81252
1209268_prod95.81252
1139082_prod95.81252
938582_prod95.81252
1392807_prod95.81252
4102534_prod95.81252
3392463_prod95.81252
2492134_prod105.3752
7710155_prod95.81252
3033043_prod112.68752
2552313_prod112.68752
1118848_prod145.81252
2212925_prod145.81252
2012037_prod155.3752
832661_prod145.81252
1692870_prod195.81252
6810127_prod2002
5310100_prod2002
4110070_prod2002
3210034_prod2002
2110014_prod2002
1909982_prod2002
1859955_prod2002
1769916_prod2002
1689877_prod2002
1639828_prod2002
1559806_prod2002
1489780_prod2002
1429706_prod2002
1922019_prod205.3752
3652489_prod245.81252
2722361_prod295.81252
2472130_prod505.3752
184196_prod695.81252
1355458_prod945.81252
3632487_prod2045.81252
3203338_prod2445.81252
1452814_prod3505.3752
1595800_prod5705.3752
143666_prod12011.52
450801_prod23605.3752
56699.1252 Total
3213783_prod17106.93753
672364_prod10805.3753
300793_prod5605.3753
3962520_prod3245.81253
190217_prod2355.3753
1074720_prod1640.93753
2132061_prod905.3753
2863029_prod695.81253
621675_prod505.3753
2082908_prod295.81253
3042427_prod245.81253
1369669_prod2003
1439724_prod2003
1499785_prod2003
1569811_prod2003
1649830_prod2003
1709889_prod2003
1779917_prod2003
1869957_prod2003
1939984_prod2003
2310022_prod2003
3410038_prod2003
4710083_prod2003
5410112_prod2003
6910128_prod2003
3442468_prod195.81253
1782882_prod195.81253
1062729_prod145.81253
4362560_prod145.81253
461289_prod155.3753
2992416_prod112.68753
2592988_prod112.68753
8010161_prod95.81253
891802_prod105.3753
3432467_prod95.81253
4802605_prod95.81253
2252930_prod95.81253
968587_prod95.81253
1159131_prod95.81253
1229277_prod95.81253
1339585_prod95.81253
48143.8753 Total
1349592_prod95.81254
1239285_prod95.81254
1179162_prod95.81254
988755_prod95.81254
2282935_prod95.81254
902705_prod95.81254
3722496_prod95.81254
1401910_prod105.3754
8110162_prod95.81254
2232929_prod112.68754
1822422_prod112.68754
1021829_prod155.3754
4692593_prod145.81254
1082732_prod145.81254
918558_prod195.81254
2612996_prod195.81254
7010130_prod2004
6010114_prod2004
4810087_prod2004
3610051_prod2004
2610023_prod2004
1949988_prod2004
1879971_prod2004
1809929_prod2004
1729899_prod2004
1659838_prod2004
1579821_prod2004
1509789_prod2004
1449766_prod2004
1379681_prod2004
2332941_prod245.81254
1048807_prod295.81254
1972901_prod445.81254
1012723_prod695.81254
992721_prod895.81254
1811996_prod1505.3754
4562660_prod2161.54
1301889_prod3205.3754
550645_prod4905.3754
4410172_prod9655.3754
504605_prod14311.54
43059.43754 Total
741621_prod13405.3755
220613_prod9605.3755
4262550_prod4795.81255
200280_prod2705.3755
3862510_prod2145.81255
782351_prod1255.3755
952717_prod845.81255
1145049_prod6265
2412954_prod395.81255
1751983_prod255.3755
2272933_prod245.81255
1389683_prod2005
1469767_prod2005
1529793_prod2005
1609825_prod2005
1669868_prod2005
1739902_prod2005
1839933_prod2005
1889975_prod2005
1969997_prod2005
2710025_prod2005
3710062_prod2005
4910088_prod2005
6410118_prod2005
7310137_prod2005
4422566_prod195.81255
1092734_prod145.81255
1299569_prod145.81255
1791992_prod155.3755
2172423_prod112.68755
972719_prod112.68755
380133_prod106.1255
2682349_prod95.81255
3802504_prod95.81255
1512828_prod95.81255
2432962_prod95.81255
1008767_prod95.81255
1189177_prod95.81255
1259329_prod95.81255
888527_prod95.81255
40822.755 Total
257445.9375Grand Total
April 10, 2012 at 3:55 pm
pamozer (4/10/2012)
Thank You. So now looking at the results more clearly, this does give me closer to what I am looking for the only thing is when you have more databases I am trying to keep each group approximately the same amount megabytes....{snip}...
Any thoughts on How I can do that?
Heh... shoot! I wish you'd said that to begin with! I did some "bin stacking" code on a nearly identical problem just about 10 days ago. I'll lookup my code when I get home from work tonight.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2012 at 3:57 pm
Sorry About that. I appreciate your help. I have been playing around with this and am getting closer. But I would definitely like to see what you have come up with.
Thanks Again
April 10, 2012 at 5:56 pm
So this is what I came up with. It looks a bit clunky but it does seem to work. I am curious to see what yours looks like. I do need to make sure that whatever the number of partitions I want that I don't get more than I wanted. I have to create a job for each partition.
-------------Set Desired Number of Partitions------------------
DECLARE @NumofPartitions INT
SET @NumofPartitions=10
DECLARE @DesiredPartitionSize INT
--Get list of all databases and their size
Select ds.database_id, ds.name, mf.size/128 AS DataMB
INTO #temp
from sys.databases ds
INNER JOIN sys.master_files AS mf ON ds.database_id = mf.database_id
where ds.state=0 and ds.owner_sid<>0x01 and ds.database_id not in (25,5,6,7,99,17,20,18,13,9,8) AND mf.data_space_id=0
---Remove the min and max amounts to try toe eliminate skewing and then get the desired max size for each partition
DECLARE @skewed int
SET @skewed=(SELECT MAX(DataMB)+MIN(t.DataMB) FROM #temp AS t)
SELECT @DesiredPartitionSize=(SUM(DataMB)-@skewed)/@NumofPartitions
FROM #temp;
CREATE TABLE #DatabasePartitions(DatabaseID INT, DatabaseName VARCHAR(128), DatabaseMB INT, GroupID INT)
---Prepare to loop through databases where desired size is less than biggest databases and give them their own group.
DECLARE @Partition INT
SET @Partition=1
SELECT *
INTO #GreaterThanPartition
FROM #temp AS t
WHERE DataMB>@DesiredPartitionSize
DECLARE @DatabaseID INT
SET @DatabaseID=(SELECT TOP 1 Database_ID FROM #GreaterThanPartition ORDER BY #GreaterThanPartition.DataMB DESC)
----------Loop through databases that have greater database size than desiredpartition
WHILE @DatabaseID IS NOT NULL
BEGIN
INSERT INTO #DatabasePartitions
( DatabaseID ,
DatabaseName ,
DatabaseMB ,
GroupID
)
Select Database_ID,
Name,
DataMB,
@Partition AS GroupID
FROM #GreaterThanPartition AS gtp
WHERE gtp.database_id=@DatabaseID
DELETE FROM #GreaterThanPartition
WHERE #GreaterThanPartition.database_id=@DatabaseID
SET @DatabaseID=(SELECT TOP 1 Database_ID FROM #GreaterThanPartition ORDER BY #GreaterThanPartition.DataMB DESC)
SET @Partition=@Partition+1
End
-------------------------Get all databases that were not included in the above loop
SELECT *
INTO #Partition
FROM #temp AS t
WHERE NOT EXISTS(SELECT dp.databaseid FROM #DatabasePartitions AS dp WHERE t.database_id=dp.DatabaseID)
--Get your initial database
SET @DatabaseID=(SELECT TOP 1 Database_ID FROM #Partition WHERE #Partition.DataMB<>0 ORDER BY #Partition.DataMB DESC)
--Get your initial partition based on what you already have in the table from above
SELECT @Partition=(SELECT ISNULL(MAX(GroupID),0)+1 FROM #DatabasePartitions)
---Loop through databases and assign group based summing of total size
WHILE@DatabaseID IS NOT NULL
Begin
---Checking to see if there are already dbs in the table or if the total database size for a partition is under the limit
-- if it is continue
IF ((SELECT SUM(DatabaseMB) FROM #DatabasePartitions WHERE #DatabasePartitions.GroupID=@Partition) IS NULL) OR
((SELECT SUM(DatabaseMB) FROM #DatabasePartitions WHERE #DatabasePartitions.GroupID=@Partition)<=@DesiredPartitionSize)
Begin
INSERT INTO #DatabasePartitions
( DatabaseID ,
DatabaseName ,
DatabaseMB ,
GroupID
)
Select Database_ID,
Name,
DataMB,
@Partition AS GroupID
FROM #Partition AS gtp
WHERE gtp.database_id=@DatabaseID
DELETE FROM #Partition
WHERE #Partition.database_id=@DatabaseID
SET @DatabaseID=(SELECT TOP 1 Database_ID FROM #Partition WHERE #Partition.DataMB<>0 ORDER BY #Partition.DataMB ASC)
END
ELSE---You are over the desired partition for the last group so update the partition number unless you are out of partitions
BEGIN
SELECT @Partition=(SELECT ISNULL(MAX(GroupID),0)+1 FROM #DatabasePartitions)
IF @Partition>@NumofPartitions
SET @Partition=@Partition-1
ELSE
SET @Partition=@Partition
INSERT INTO #DatabasePartitions
( DatabaseID ,
DatabaseName ,
DatabaseMB ,
GroupID
)
Select Database_ID,
Name,
DataMB,
@Partition AS GroupID
FROM #Partition AS gtp
WHERE gtp.database_id=@DatabaseID
DELETE FROM #Partition
WHERE #Partition.database_id=@DatabaseID
SET @DatabaseID=(SELECT TOP 1 Database_ID FROM #Partition WHERE #Partition.DataMB<>0 ORDER BY #Partition.DataMB DESC)
END
END
April 10, 2012 at 8:13 pm
I think you'll find that the following balances "the load" quite nicely. You can easily change the number of database groups (starting at "0", by the way) just by changing one variable assignment which could easily be parameterized in a stored procedure.
As much as I despise RBAR, there are certain very rare places where you just can do without it. If I were to put this code into a production database, I believe it would only be the 2nd or 3rd proc I've ever promoted to production with a WHILE loop in it. And, no, I don't believe this could be done with an rCTE and still have a variable to control the number of groups. I'll be mighty impressed and appreciative if someone manages to pull that little feat off.
Here's the code. I used your actual output to create the test data. As always, the real details are in the code comments. All of the groups are nicely within 18 bytes of each other because of the number of databases you have.
--===== Conditionally drop Temp Table(s) to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#DBInfo','U') IS NOT NULL
DROP TABLE #DBInfo
;
GO
CREATE TABLE #DBInfo
(
RowNum INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
[DBID] INT NOT NULL,
DBName VARCHAR(128) NOT NULL,
DBSizeMB DECIMAL(19,4) NOT NULL,
DBGroup TINYINT NOT NULL DEFAULT 0
)
;
--===== For production, uncomment the following code.
-- This will store the database data in descending
-- order according to database size which is very important.
-- INSERT INTO #DBInfo
-- ([DBID],DBName,DBSizeMB)
-- SELECT [DBID] = db.[database_id],
-- [DBName] = db.[name],
-- [DBSizeMB] = CAST(SUM(af.)/128.0 AS DECIMAL(9,2))
-- FROM sys.databases db
-- INNER JOIN sys.master_files af
-- ON db.database_id = af.database_id
-- WHERE db.state = 0
-- AND db.owner_sid <> 0x01
-- AND db.database_id NOT IN (25,5,6,13,9,8)
-- GROUP BY db.database_id, db.name
-- ORDER BY [DBSizeMB] DESC
--;
--===== Replace the following (large) section of test data code
-- with the query above. This is just here in case others
-- want to play with your test data.
INSERT INTO #DBInfo
([DBID],DBName,DBSizeMB)
SELECT [DBID],DBName,DBSizeMB
FROM (
SELECT 10,'9517_prod',95.8125 UNION ALL
SELECT 14,'3666_prod',12011.5 UNION ALL
SELECT 15,'0327_prod',12905.375 UNION ALL
SELECT 16,'0185_prod',106.125 UNION ALL
SELECT 17,'10009_prod',200 UNION ALL
SELECT 18,'4196_prod',695.8125 UNION ALL
SELECT 19,'0217_prod',2355.375 UNION ALL
SELECT 20,'0280_prod',2705.375 UNION ALL
SELECT 21,'10014_prod',200 UNION ALL
SELECT 22,'0613_prod',9605.375 UNION ALL
SELECT 23,'10022_prod',200 UNION ALL
SELECT 26,'10023_prod',200 UNION ALL
SELECT 27,'10025_prod',200 UNION ALL
SELECT 28,'0860_prod',255.375 UNION ALL
SELECT 29,'10032_prod',200 UNION ALL
SELECT 30,'0793_prod',5605.375 UNION ALL
SELECT 31,'0968_prod',6605.375 UNION ALL
SELECT 32,'10034_prod',200 UNION ALL
SELECT 34,'10038_prod',200 UNION ALL
SELECT 35,'1117_prod',3555.375 UNION ALL
SELECT 36,'10051_prod',200 UNION ALL
SELECT 37,'10062_prod',200 UNION ALL
SELECT 38,'0133_prod',106.125 UNION ALL
SELECT 39,'10068_prod',200 UNION ALL
SELECT 41,'10070_prod',200 UNION ALL
SELECT 42,'2382_prod',2105.375 UNION ALL
SELECT 44,'10172_prod',9655.375 UNION ALL
SELECT 45,'0801_prod',23605.375 UNION ALL
SELECT 46,'1289_prod',155.375 UNION ALL
SELECT 47,'10083_prod',200 UNION ALL
SELECT 48,'10087_prod',200 UNION ALL
SELECT 49,'10088_prod',200 UNION ALL
SELECT 50,'4605_prod',14311.5 UNION ALL
SELECT 52,'10092_prod',200 UNION ALL
SELECT 53,'10100_prod',200 UNION ALL
SELECT 54,'10112_prod',200 UNION ALL
SELECT 55,'0645_prod',4905.375 UNION ALL
SELECT 56,'4640_prod',145.8125 UNION ALL
SELECT 59,'1663_prod',755.375 UNION ALL
SELECT 60,'10114_prod',200 UNION ALL
SELECT 62,'1675_prod',505.375 UNION ALL
SELECT 63,'1885_prod',2655.375 UNION ALL
SELECT 64,'10118_prod',200 UNION ALL
SELECT 65,'10126_prod',200 UNION ALL
SELECT 67,'2364_prod',10805.375 UNION ALL
SELECT 68,'10127_prod',200 UNION ALL
SELECT 69,'10128_prod',200 UNION ALL
SELECT 70,'10130_prod',200 UNION ALL
SELECT 71,'2650_prod',195.8125 UNION ALL
SELECT 72,'1526_prod',95.375 UNION ALL
SELECT 73,'10137_prod',200 UNION ALL
SELECT 74,'1621_prod',13405.375 UNION ALL
SELECT 75,'10141_prod',200 UNION ALL
SELECT 77,'10155_prod',95.8125 UNION ALL
SELECT 78,'2351_prod',1255.375 UNION ALL
SELECT 79,'2647_prod',1095.8125 UNION ALL
SELECT 80,'10161_prod',95.8125 UNION ALL
SELECT 81,'10162_prod',95.8125 UNION ALL
SELECT 83,'2661_prod',145.8125 UNION ALL
SELECT 88,'8527_prod',95.8125 UNION ALL
SELECT 89,'1802_prod',105.375 UNION ALL
SELECT 90,'2705_prod',95.8125 UNION ALL
SELECT 91,'8558_prod',195.8125 UNION ALL
SELECT 93,'8582_prod',95.8125 UNION ALL
SELECT 95,'2717_prod',845.8125 UNION ALL
SELECT 96,'8587_prod',95.8125 UNION ALL
SELECT 97,'2719_prod',112.6875 UNION ALL
SELECT 98,'8755_prod',95.8125 UNION ALL
SELECT 99,'2721_prod',895.8125 UNION ALL
SELECT 100,'8767_prod',95.8125 UNION ALL
SELECT 101,'2723_prod',695.8125 UNION ALL
SELECT 102,'1829_prod',155.375 UNION ALL
SELECT 104,'8807_prod',295.8125 UNION ALL
SELECT 105,'8827_prod',95.8125 UNION ALL
SELECT 106,'2729_prod',145.8125 UNION ALL
SELECT 107,'4720_prod',1640.9375 UNION ALL
SELECT 108,'2732_prod',145.8125 UNION ALL
SELECT 109,'2734_prod',145.8125 UNION ALL
SELECT 111,'8848_prod',145.8125 UNION ALL
SELECT 112,'9081_prod',95.8125 UNION ALL
SELECT 113,'9082_prod',95.8125 UNION ALL
SELECT 114,'5049_prod',626 UNION ALL
SELECT 115,'9131_prod',95.8125 UNION ALL
SELECT 117,'9162_prod',95.8125 UNION ALL
SELECT 118,'9177_prod',95.8125 UNION ALL
SELECT 119,'9239_prod',95.8125 UNION ALL
SELECT 120,'9268_prod',95.8125 UNION ALL
SELECT 122,'9277_prod',95.8125 UNION ALL
SELECT 123,'9285_prod',95.8125 UNION ALL
SELECT 125,'9329_prod',95.8125 UNION ALL
SELECT 129,'9569_prod',145.8125 UNION ALL
SELECT 130,'1889_prod',3205.375 UNION ALL
SELECT 131,'9583_prod',95.8125 UNION ALL
SELECT 133,'9585_prod',95.8125 UNION ALL
SELECT 134,'9592_prod',95.8125 UNION ALL
SELECT 135,'5458_prod',945.8125 UNION ALL
SELECT 136,'9669_prod',200 UNION ALL
SELECT 137,'9681_prod',200 UNION ALL
SELECT 138,'9683_prod',200 UNION ALL
SELECT 139,'2807_prod',95.8125 UNION ALL
SELECT 140,'1910_prod',105.375 UNION ALL
SELECT 141,'9699_prod',200 UNION ALL
SELECT 142,'9706_prod',200 UNION ALL
SELECT 143,'9724_prod',200 UNION ALL
SELECT 144,'9766_prod',200 UNION ALL
SELECT 145,'2814_prod',3505.375 UNION ALL
SELECT 146,'9767_prod',200 UNION ALL
SELECT 147,'9775_prod',200 UNION ALL
SELECT 148,'9780_prod',200 UNION ALL
SELECT 149,'9785_prod',200 UNION ALL
SELECT 150,'9789_prod',200 UNION ALL
SELECT 151,'2828_prod',95.8125 UNION ALL
SELECT 152,'9793_prod',200 UNION ALL
SELECT 153,'9804_prod',200 UNION ALL
SELECT 155,'9806_prod',200 UNION ALL
SELECT 156,'9811_prod',200 UNION ALL
SELECT 157,'9821_prod',200 UNION ALL
SELECT 158,'2848_prod',95.8125 UNION ALL
SELECT 159,'5800_prod',5705.375 UNION ALL
SELECT 160,'9825_prod',200 UNION ALL
SELECT 161,'9827_prod',200 UNION ALL
SELECT 163,'9828_prod',200 UNION ALL
SELECT 164,'9830_prod',200 UNION ALL
SELECT 165,'9838_prod',200 UNION ALL
SELECT 166,'9868_prod',200 UNION ALL
SELECT 167,'9875_prod',200 UNION ALL
SELECT 168,'9877_prod',200 UNION ALL
SELECT 169,'2870_prod',195.8125 UNION ALL
SELECT 170,'9889_prod',200 UNION ALL
SELECT 172,'9899_prod',200 UNION ALL
SELECT 173,'9902_prod',200 UNION ALL
SELECT 174,'9910_prod',200 UNION ALL
SELECT 175,'1983_prod',255.375 UNION ALL
SELECT 176,'9916_prod',200 UNION ALL
SELECT 177,'9917_prod',200 UNION ALL
SELECT 178,'2882_prod',195.8125 UNION ALL
SELECT 179,'1992_prod',155.375 UNION ALL
SELECT 180,'9929_prod',200 UNION ALL
SELECT 181,'1996_prod',1505.375 UNION ALL
SELECT 182,'2422_prod',112.6875 UNION ALL
SELECT 183,'9933_prod',200 UNION ALL
SELECT 184,'9944_prod',200 UNION ALL
SELECT 185,'9955_prod',200 UNION ALL
SELECT 186,'9957_prod',200 UNION ALL
SELECT 187,'9971_prod',200 UNION ALL
SELECT 188,'9975_prod',200 UNION ALL
SELECT 189,'9977_prod',200 UNION ALL
SELECT 190,'9982_prod',200 UNION ALL
SELECT 192,'2019_prod',205.375 UNION ALL
SELECT 193,'9984_prod',200 UNION ALL
SELECT 194,'9988_prod',200 UNION ALL
SELECT 195,'2025_prod',155.375 UNION ALL
SELECT 196,'9997_prod',200 UNION ALL
SELECT 197,'2901_prod',445.8125 UNION ALL
SELECT 201,'2037_prod',155.375 UNION ALL
SELECT 202,'2039_prod',33011.5 UNION ALL
SELECT 205,'2045_prod',205.375 UNION ALL
SELECT 208,'2908_prod',295.8125 UNION ALL
SELECT 213,'2061_prod',905.375 UNION ALL
SELECT 214,'2916_prod',145.8125 UNION ALL
SELECT 217,'2423_prod',112.6875 UNION ALL
SELECT 220,'2075_prod',305.375 UNION ALL
SELECT 221,'2925_prod',145.8125 UNION ALL
SELECT 223,'2929_prod',112.6875 UNION ALL
SELECT 225,'2930_prod',95.8125 UNION ALL
SELECT 227,'2933_prod',245.8125 UNION ALL
SELECT 228,'2935_prod',95.8125 UNION ALL
SELECT 233,'2941_prod',245.8125 UNION ALL
SELECT 241,'2954_prod',395.8125 UNION ALL
SELECT 243,'2962_prod',95.8125 UNION ALL
SELECT 247,'2130_prod',505.375 UNION ALL
SELECT 249,'2134_prod',105.375 UNION ALL
SELECT 255,'2313_prod',112.6875 UNION ALL
SELECT 259,'2988_prod',112.6875 UNION ALL
SELECT 261,'2996_prod',195.8125 UNION ALL
SELECT 268,'2349_prod',95.8125 UNION ALL
SELECT 269,'3011_prod',95.8125 UNION ALL
SELECT 272,'2361_prod',295.8125 UNION ALL
SELECT 276,'3018_prod',112.6875 UNION ALL
SELECT 286,'3029_prod',695.8125 UNION ALL
SELECT 298,'2414_prod',145.75 UNION ALL
SELECT 299,'2416_prod',112.6875 UNION ALL
SELECT 302,'2425_prod',95.8125 UNION ALL
SELECT 303,'3043_prod',112.6875 UNION ALL
SELECT 304,'2427_prod',245.8125 UNION ALL
SELECT 318,'3493_prod',595.8125 UNION ALL
SELECT 320,'3338_prod',2445.8125 UNION ALL
SELECT 321,'3783_prod',17106.9375 UNION ALL
SELECT 329,'2453_prod',95.8125 UNION ALL
SELECT 339,'2463_prod',95.8125 UNION ALL
SELECT 343,'2467_prod',95.8125 UNION ALL
SELECT 344,'2468_prod',195.8125 UNION ALL
SELECT 363,'2487_prod',2045.8125 UNION ALL
SELECT 365,'2489_prod',245.8125 UNION ALL
SELECT 372,'2496_prod',95.8125 UNION ALL
SELECT 380,'2504_prod',95.8125 UNION ALL
SELECT 386,'2510_prod',2145.8125 UNION ALL
SELECT 396,'2520_prod',3245.8125 UNION ALL
SELECT 410,'2534_prod',95.8125 UNION ALL
SELECT 426,'2550_prod',4795.8125 UNION ALL
SELECT 436,'2560_prod',145.8125 UNION ALL
SELECT 442,'2566_prod',195.8125 UNION ALL
SELECT 456,'2660_prod',2161.5 UNION ALL
SELECT 469,'2593_prod',145.8125 UNION ALL
SELECT 480,'2605_prod',95.8125
) d ([DBID],DBName,DBSizeMB)
ORDER BY DBSizeMB DESC
;
--========================================================================================
-- Presets (made all the presets compatible with all versions of SQL Server)
--========================================================================================
--===== Conditionally drop Temp Table(s) to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#Accumulator','U') IS NOT NULL
DROP TABLE #Accumulator
;
--===== Suppress the auto-display of rowcounts because we're going to use a loop.
-- This will actually help the loop run faster.
SET NOCOUNT ON
;
--===== If anything in the loop goes wrong, this will force a "quit" and do a rollback.
-- You'll see why we use a transaction in a bit.
SET XACT_ABORT ON
;
--===== Declare some obviously named variables
DECLARE @CurrentDB INT, --Current RowNum we're working on in the #DBInfo table
@DBGroups INT, --This could be a parameter for a stored procedure
@DBCount INT, --Total count of rows in the #DBInfo table
@DBSize DECIMAL(19,4), --DB size from the current row in the #DBInfo table
@TgtDBGroup INT --The DBGroup in the accumulator table with the least # of MBytes.
;
--===== Preset the variables
SELECT @DBGroups = 5,
@DBCount = MAX(RowNum),
@CurrentDB = 1
FROM #DBInfo
;
--===== Create the accumulator table where we'll keep track
-- of the total MBytes assigned to each DBGroup.
SELECT TOP (@DBGroups)
DBGroup = IDENTITY(INT,0,1),
MBytes = CAST(0 AS DECIMAL(19,4))
INTO #Accumulator
FROM sys.all_columns ac1
;
--========================================================================================
-- All set. Make the DBGroup assignments for each DB in the #DBInfo table.
--========================================================================================
BEGIN TRANSACTION --This will add a bit more speed to the loop because it will commit
--all of the rows we change all at once instead of individually.
WHILE @CurrentDB <= @DBCount
BEGIN
--===== Get the file size from the current file row.
SELECT @DBSize = DBSizeMB
FROM #DBInfo
WHERE RowNum = @CurrentDB
;
--===== Find the DBGroup with the least number of MBytes assigned
SELECT TOP 1
@TgtDBGroup = DBGroup
FROM #Accumulator
ORDER BY MBytes ASC, DBGroup ASC
;
--===== Add the MBytes to the DBGroup we just found in the accumulator.
UPDATE #Accumulator
SET MBytes = MBytes + @DBSize
WHERE DBGroup = @TgtDBGroup
;
--===== Assign the DBGroup we just found to the DB row.
UPDATE #DBInfo
SET DBGroup = @TgtDBGroup
WHERE RowNum = @CurrentDB
;
--===== Get ready to read the next file row
SELECT @CurrentDB = @CurrentDB + 1
;
END
;
COMMIT
;
--========================================================================================
-- Display the results
--========================================================================================
SELECT * FROM #Accumulator; --To show that the DBGroups are all just about equal.
SELECT * FROM #DBInfo; --This is the output you wanted with DBGroup assignments.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 10, 2012 at 8:57 pm
BTW, here's the group count by MB Size when I run your code for 5 partitions.
GroupID(No column name)
144921
245546
346272
445626
574991
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2012 at 9:24 am
This looks great. Thank you so much. So you are basically everytime you go around checking the accumulator table for the group that has the smallest size and then adding the next database to that group. Is that correct? Just want to make sure I understand.
thanks
April 11, 2012 at 10:11 am
pamozer (4/11/2012)
This looks great. Thank you so much. So you are basically everytime you go around checking the accumulator table for the group that has the smallest size and then adding the next database to that group. Is that correct? Just want to make sure I understand.thanks
That's correct. You also have to understand that the "source" of information is in descending order by DB size. Without that, you have a random sort which, of course, won't work correctly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply