Sfrgttk

Why is there an extra space when I type "ls" on the Desktop?

School performs periodic password audits. Is my password compromised?

3.5% Interest Student Loan or use all of my savings on Tuition?

Can a space-faring robot still function over a billion years?

Why doesn't "adolescent" take any articles in "listen to adolescent agonising"?

Drawing the Möbius band and the Klein bottle

What is better: yes / no radio, or simple checkbox?

Are there other characters in the Star Wars universe who had damaged bodies and needed to wear an outfit like Darth Vader?

What is Tony Stark injecting into himself in Iron Man 3?

Forcing Mathematica's Integrate to give more general answers

Is "cogitate" an appropriate word for this?

When to use the term transposed instead of modulation?

What is the oldest European royal house?

An Undercover Army

Dukha vs legitimate need

Has a sovereign Communist government ever run, and conceded loss, on a fair election?

Does the US political system, in principle, allow for a no-party system?

Sundering Titan and basic normal lands and snow lands

Can you run a ground wire from stove directly to ground pole in the ground

Should we avoid writing fiction about historical events without extensive research?

Who is at the mall?

Deal the cards to the players

Is there a way to find out the age of climbing ropes?

What is the purpose of a disclaimer like "this is not legal advice"?

Split databases into groups of roughly equal datasize

How do I row count into groups?SQL Server split mdf into multiple filesSplit/explode comma delimited string field into SQL querysplit string for calculationsMost cost efficient way to page through a poorly ordered table?Split URL string into columnsWhen should you split a database into 2-3 databases for performance reasons?Split date range into separate recordsWhat are the main reasons to split a Data Warehouse into multiple databases?How can we manage cross-database dependencies across environments?

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?

sql-server t-sql

share|improve this question

edited May 22 '16 at 11:59

Billy Rauséus

asked May 22 '16 at 11:17

Billy RauséusBilly Rauséus

63

bumped to the homepage by Community♦ 11 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  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
  

  
  
  
  

 | 
show 1 more comment

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?

sql-server t-sql

share|improve this question

edited May 22 '16 at 11:59

Billy Rauséus

asked May 22 '16 at 11:17

Billy RauséusBilly Rauséus

63

bumped to the homepage by Community♦ 11 mins ago

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

• 
  
  
  

  
  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
  

  
  
  
  

 | 
show 1 more comment

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?

sql-server t-sql

share|improve this question

edited May 22 '16 at 11:59

Billy Rauséus

asked May 22 '16 at 11:17

Billy RauséusBilly Rauséus

63

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

share|improve this question

edited May 22 '16 at 11:59

Billy Rauséus

asked May 22 '16 at 11:17

Billy RauséusBilly Rauséus

63

share|improve this question

edited May 22 '16 at 11:59

Billy Rauséus

asked May 22 '16 at 11:17

Billy RauséusBilly Rauséus

63

asked May 22 '16 at 11:17

Billy RauséusBilly Rauséus

63

asked May 22 '16 at 11:17

Billy RauséusBilly Rauséus

63

1 Answer
1

active

oldest

votes

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

share|improve this answer

answered May 22 '16 at 13:05

AlexAlex

29329

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f139199%2fsplit-databases-into-groups-of-roughly-equal-datasize%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

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

share|improve this answer

answered May 22 '16 at 13:05

AlexAlex

29329

add a comment | 

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

share|improve this answer

answered May 22 '16 at 13:05

AlexAlex

29329

add a comment | 

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

share|improve this answer

answered May 22 '16 at 13:05

AlexAlex

29329

;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

share|improve this answer

answered May 22 '16 at 13:05

AlexAlex

29329

answered May 22 '16 at 13:05

AlexAlex

29329

answered May 22 '16 at 13:05

AlexAlex

29329