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_id name RankID FileSizeMB
20 2379_dev 1 95716.37500000000000
19 3349_dev 2 70851.62500000000000
17 1012_dev 3 32615.06250000000000
18 4701_dev 4 16369.56250000000000
15 3523_dev 5 7732.56250000000000
16 7185_dev 6 2713.18750000000000
10 0028_dev 7 1009.25000000000000
12 10305_dev 8 320.00000000000000
14 10304_dev 9 320.00000000000000
11 3486_dev 10 113.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
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.DBName DBSizeMB DBGroup
2039_prod 33011.5 1
0968_prod 6605.375 1
1885_prod 2655.375 1
2647_prod 1095.8125 1
3493_prod 595.8125 1
0860_prod 255.375 1
9699_prod 200 1
9804_prod 200 1
9875_prod 200 1
9944_prod 200 1
10009_prod 200 1
10068_prod 200 1
10126_prod 200 1
2025_prod 155.375 1
2916_prod 145.8125 1
3018_prod 112.6875 1
2425_prod 95.8125 1
3011_prod 95.8125 1
9239_prod 95.8125 1
8827_prod 95.8125 1
1526_prod 95.375 1
[highlight=#ffff11]46511.75 1 Total[/highlight]
2134_prod 105.375 2
9268_prod 95.8125 2
8582_prod 95.8125 2
2534_prod 95.8125 2
3043_prod 112.6875 2
10155_prod 95.8125 2
2925_prod 145.8125 2
2037_prod 155.375 2
10127_prod 200 2
10070_prod 200 2
10014_prod 200 2
9955_prod 200 2
9877_prod 200 2
9806_prod 200 2
9706_prod 200 2
2489_prod 245.8125 2
2130_prod 505.375 2
5458_prod 945.8125 2
3338_prod 2445.8125 2
5800_prod 5705.375 2
0801_prod 23605.375 2
[highlight=#ffff11]35756.0625 2 Total[/highlight]
3783_prod 17106.9375 3
0793_prod 5605.375 3
0217_prod 2355.375 3
2061_prod 905.375 3
1675_prod 505.375 3
2427_prod 245.8125 3
9724_prod 200 3
9811_prod 200 3
9889_prod 200 3
9957_prod 200 3
10022_prod 200 3
10083_prod 200 3
10128_prod 200 3
2468_prod 195.8125 3
2560_prod 145.8125 3
10161_prod 95.8125 3
2988_prod 112.6875 3
2605_prod 95.8125 3
8587_prod 95.8125 3
9277_prod 95.8125 3
1802_prod 105.375 3
[highlight=#ffff11]29067.1875 3 Total[/highlight]
1910_prod 105.375 4
9285_prod 95.8125 4
8755_prod 95.8125 4
2705_prod 95.8125 4
2929_prod 112.6875 4
10162_prod 95.8125 4
2593_prod 145.8125 4
2996_prod 195.8125 4
10130_prod 200 4
10087_prod 200 4
10023_prod 200 4
9971_prod 200 4
9899_prod 200 4
9821_prod 200 4
9766_prod 200 4
2941_prod 245.8125 4
2901_prod 445.8125 4
2721_prod 895.8125 4
2660_prod 2161.5 4
0645_prod 4905.375 4
4605_prod 14311.5 4
[highlight=#ffff11]25308.75 4 Total[/highlight]
1621_prod 13405.375 5
2550_prod 4795.8125 5
2510_prod 2145.8125 5
2717_prod 845.8125 5
2954_prod 395.8125 5
2933_prod 245.8125 5
9767_prod 200 5
9825_prod 200 5
9902_prod 200 5
9975_prod 200 5
10025_prod 200 5
10088_prod 200 5
10137_prod 200 5
2566_prod 195.8125 5
9569_prod 145.8125 5
2349_prod 95.8125 5
2719_prod 112.6875 5
0133_prod 106.125 5
2828_prod 95.8125 5
8767_prod 95.8125 5
9329_prod 95.8125 5
[highlight=#ffff11]24178.125 5 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
DBID DBName DBSizeMB DBGroup
202 2039_prod 33011.5 1
15 0327_prod 12905.375 1
31 0968_prod 6605.375 1
35 1117_prod 3555.375 1
63 1885_prod 2655.375 1
42 2382_prod 2105.375 1
79 2647_prod 1095.8125 1
59 1663_prod 755.375 1
318 3493_prod 595.8125 1
220 2075_prod 305.375 1
28 0860_prod 255.375 1
205 2045_prod 205.375 1
141 9699_prod 200 1
147 9775_prod 200 1
153 9804_prod 200 1
161 9827_prod 200 1
167 9875_prod 200 1
174 9910_prod 200 1
184 9944_prod 200 1
189 9977_prod 200 1
17 10009_prod 200 1
29 10032_prod 200 1
39 10068_prod 200 1
52 10092_prod 200 1
65 10126_prod 200 1
75 10141_prod 200 1
71 2650_prod 195.8125 1
195 2025_prod 155.375 1
214 2916_prod 145.8125 1
56 4640_prod 145.8125 1
298 2414_prod 145.75 1
276 3018_prod 112.6875 1
16 0185_prod 106.125 1
329 2453_prod 95.8125 1
302 2425_prod 95.8125 1
158 2848_prod 95.8125 1
269 3011_prod 95.8125 1
112 9081_prod 95.8125 1
119 9239_prod 95.8125 1
10 9517_prod 95.8125 1
105 8827_prod 95.8125 1
72 1526_prod 95.375 1
68720.75 1 Total
131 9583_prod 95.8125 2
120 9268_prod 95.8125 2
113 9082_prod 95.8125 2
93 8582_prod 95.8125 2
139 2807_prod 95.8125 2
410 2534_prod 95.8125 2
339 2463_prod 95.8125 2
249 2134_prod 105.375 2
77 10155_prod 95.8125 2
303 3043_prod 112.6875 2
255 2313_prod 112.6875 2
111 8848_prod 145.8125 2
221 2925_prod 145.8125 2
201 2037_prod 155.375 2
83 2661_prod 145.8125 2
169 2870_prod 195.8125 2
68 10127_prod 200 2
53 10100_prod 200 2
41 10070_prod 200 2
32 10034_prod 200 2
21 10014_prod 200 2
190 9982_prod 200 2
185 9955_prod 200 2
176 9916_prod 200 2
168 9877_prod 200 2
163 9828_prod 200 2
155 9806_prod 200 2
148 9780_prod 200 2
142 9706_prod 200 2
192 2019_prod 205.375 2
365 2489_prod 245.8125 2
272 2361_prod 295.8125 2
247 2130_prod 505.375 2
18 4196_prod 695.8125 2
135 5458_prod 945.8125 2
363 2487_prod 2045.8125 2
320 3338_prod 2445.8125 2
145 2814_prod 3505.375 2
159 5800_prod 5705.375 2
14 3666_prod 12011.5 2
45 0801_prod 23605.375 2
56699.125 2 Total
321 3783_prod 17106.9375 3
67 2364_prod 10805.375 3
30 0793_prod 5605.375 3
396 2520_prod 3245.8125 3
19 0217_prod 2355.375 3
107 4720_prod 1640.9375 3
213 2061_prod 905.375 3
286 3029_prod 695.8125 3
62 1675_prod 505.375 3
208 2908_prod 295.8125 3
304 2427_prod 245.8125 3
136 9669_prod 200 3
143 9724_prod 200 3
149 9785_prod 200 3
156 9811_prod 200 3
164 9830_prod 200 3
170 9889_prod 200 3
177 9917_prod 200 3
186 9957_prod 200 3
193 9984_prod 200 3
23 10022_prod 200 3
34 10038_prod 200 3
47 10083_prod 200 3
54 10112_prod 200 3
69 10128_prod 200 3
344 2468_prod 195.8125 3
178 2882_prod 195.8125 3
106 2729_prod 145.8125 3
436 2560_prod 145.8125 3
46 1289_prod 155.375 3
299 2416_prod 112.6875 3
259 2988_prod 112.6875 3
80 10161_prod 95.8125 3
89 1802_prod 105.375 3
343 2467_prod 95.8125 3
480 2605_prod 95.8125 3
225 2930_prod 95.8125 3
96 8587_prod 95.8125 3
115 9131_prod 95.8125 3
122 9277_prod 95.8125 3
133 9585_prod 95.8125 3
48143.875 3 Total
134 9592_prod 95.8125 4
123 9285_prod 95.8125 4
117 9162_prod 95.8125 4
98 8755_prod 95.8125 4
228 2935_prod 95.8125 4
90 2705_prod 95.8125 4
372 2496_prod 95.8125 4
140 1910_prod 105.375 4
81 10162_prod 95.8125 4
223 2929_prod 112.6875 4
182 2422_prod 112.6875 4
102 1829_prod 155.375 4
469 2593_prod 145.8125 4
108 2732_prod 145.8125 4
91 8558_prod 195.8125 4
261 2996_prod 195.8125 4
70 10130_prod 200 4
60 10114_prod 200 4
48 10087_prod 200 4
36 10051_prod 200 4
26 10023_prod 200 4
194 9988_prod 200 4
187 9971_prod 200 4
180 9929_prod 200 4
172 9899_prod 200 4
165 9838_prod 200 4
157 9821_prod 200 4
150 9789_prod 200 4
144 9766_prod 200 4
137 9681_prod 200 4
233 2941_prod 245.8125 4
104 8807_prod 295.8125 4
197 2901_prod 445.8125 4
101 2723_prod 695.8125 4
99 2721_prod 895.8125 4
181 1996_prod 1505.375 4
456 2660_prod 2161.5 4
130 1889_prod 3205.375 4
55 0645_prod 4905.375 4
44 10172_prod 9655.375 4
50 4605_prod 14311.5 4
43059.4375 4 Total
74 1621_prod 13405.375 5
22 0613_prod 9605.375 5
426 2550_prod 4795.8125 5
20 0280_prod 2705.375 5
386 2510_prod 2145.8125 5
78 2351_prod 1255.375 5
95 2717_prod 845.8125 5
114 5049_prod 626 5
241 2954_prod 395.8125 5
175 1983_prod 255.375 5
227 2933_prod 245.8125 5
138 9683_prod 200 5
146 9767_prod 200 5
152 9793_prod 200 5
160 9825_prod 200 5
166 9868_prod 200 5
173 9902_prod 200 5
183 9933_prod 200 5
188 9975_prod 200 5
196 9997_prod 200 5
27 10025_prod 200 5
37 10062_prod 200 5
49 10088_prod 200 5
64 10118_prod 200 5
73 10137_prod 200 5
442 2566_prod 195.8125 5
109 2734_prod 145.8125 5
129 9569_prod 145.8125 5
179 1992_prod 155.375 5
217 2423_prod 112.6875 5
97 2719_prod 112.6875 5
38 0133_prod 106.125 5
268 2349_prod 95.8125 5
380 2504_prod 95.8125 5
151 2828_prod 95.8125 5
243 2962_prod 95.8125 5
100 8767_prod 95.8125 5
118 9177_prod 95.8125 5
125 9329_prod 95.8125 5
88 8527_prod 95.8125 5
40822.75 5 Total
257445.9375 Grand 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
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
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)
1 44921
2 45546
3 46272
4 45626
5 74991
--Jeff Moden
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply