Index fragmentation and reorganizing database pagesSQL Database physical file fragmentationWhat prevents a MS SQL Server database from shrinking?Does index fill factor become meaningless on large tables?Index rebuilding on mirrored database sql server 2008Index reorganizing slow after index rebuild with MS SQLMSSQL Server Express: How to reclaim unused spaceSlow MSSQL on DB (~2TB) - Index / fragmentation?MSSQL-Server: rebuild vs reorganize Index and Update StatisticsCause of page fragmentation on “large” server with xfs, 20 disks and CephReclaiming free space in filegroup with single chronological partition
What makes an item an artifact?
PhD - Well known professor or well known school?
Can an Aarakocra use a shield while flying?
Smooth switching between 12 V batteries, with a toggle switch
Words that signal future content
If you had a giant cutting disc 60 miles diameter and rotated it 1000 rps, would the edge be traveling faster than light?
How to chain Python function calls so the behaviour is as follows
What is the actual quality of machine translations?
What's up with this leaf?
What is wrong with this proof that symmetric matrices commute?
How to tell your grandparent to not come to fetch you with their car?
The eyes have it
When conversion from Integer to Single may lose precision
Hottest Possible Hydrogen-Fusing Stars
Payment instructions allegedly from HomeAway look fishy to me
How to officially communicate to a non-responsive colleague?
Implement Homestuck's Catenative Doomsday Dice Cascader
BGP multihome issue
Movie about a boy who was born old and grew young
Why doesn’t a normal window produce an apparent rainbow?
What could have caused a rear derailleur to end up in the back wheel suddenly?
Was the Tamarian language in "Darmok" inspired by Jack Vance's "The Asutra"?
Preventing Employees from either switching to Competitors or Opening Their Own Business
Should I compare a std::string to "string" or "string"s?
Index fragmentation and reorganizing database pages
SQL Database physical file fragmentationWhat prevents a MS SQL Server database from shrinking?Does index fill factor become meaningless on large tables?Index rebuilding on mirrored database sql server 2008Index reorganizing slow after index rebuild with MS SQLMSSQL Server Express: How to reclaim unused spaceSlow MSSQL on DB (~2TB) - Index / fragmentation?MSSQL-Server: rebuild vs reorganize Index and Update StatisticsCause of page fragmentation on “large” server with xfs, 20 disks and CephReclaiming free space in filegroup with single chronological partition
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
Say you have a database with heavy index fragmentation. Say this database also has a lot of free space due to frequent deletes in its data file. This free space is not contiguous.
If I rebuild all indexes to remove fragmentation and then reorganize the database pages so allocated pages and free pages are contiguous, would this cause further fragmentation in my indexes?
I guess the question can be posed as: if it matters, which should I do first, reorganize or rebuild?
sql-server fragmentation
edited May 21 at 13:46
yagmoth555♦
12.7k31842
migrated from stackoverflow.com Mar 9 '11 at 13:20
This question came from our site for professional and enthusiast programmers.
add a comment |
Say you have a database with heavy index fragmentation. Say this database also has a lot of free space due to frequent deletes in its data file. This free space is not contiguous.
If I rebuild all indexes to remove fragmentation and then reorganize the database pages so allocated pages and free pages are contiguous, would this cause further fragmentation in my indexes?
I guess the question can be posed as: if it matters, which should I do first, reorganize or rebuild?
sql-server fragmentation
edited May 21 at 13:46
yagmoth555♦
12.7k31842
migrated from stackoverflow.com Mar 9 '11 at 13:20
This question came from our site for professional and enthusiast programmers.
When I say reorganize, I do not mean reorganizing indexes, but the actual database file under Tasks/Shrink/File for instance, or using DBCC command.
– TiQ
Mar 9 '11 at 13:07
1
shrinking files is usually a bad idea....
– Mitch Wheat
Mar 9 '11 at 13:07
I never said I would shrink the files, just reorganize pages so free space is contigous.
– TiQ
Mar 9 '11 at 13:13
@TiQ: two lines up: "but the actual database file under Tasks/Shrink/File "
– Mitch Wheat
Mar 9 '11 at 13:14
Making the white space contigous within the database file doesn't do much if anything for performance.
– mrdenny
Mar 27 '11 at 16:59
add a comment |
Say you have a database with heavy index fragmentation. Say this database also has a lot of free space due to frequent deletes in its data file. This free space is not contiguous.
If I rebuild all indexes to remove fragmentation and then reorganize the database pages so allocated pages and free pages are contiguous, would this cause further fragmentation in my indexes?
I guess the question can be posed as: if it matters, which should I do first, reorganize or rebuild?
sql-server fragmentation
edited May 21 at 13:46
yagmoth555♦
12.7k31842
Say you have a database with heavy index fragmentation. Say this database also has a lot of free space due to frequent deletes in its data file. This free space is not contiguous.
If I rebuild all indexes to remove fragmentation and then reorganize the database pages so allocated pages and free pages are contiguous, would this cause further fragmentation in my indexes?
I guess the question can be posed as: if it matters, which should I do first, reorganize or rebuild?
sql-server fragmentation
sql-server fragmentation
edited May 21 at 13:46
yagmoth555♦
12.7k31842
edited May 21 at 13:46
yagmoth555♦
12.7k31842
edited May 21 at 13:46
yagmoth555♦
12.7k31842
edited May 21 at 13:46
yagmoth555♦
12.7k31842
edited May 21 at 13:46
yagmoth555♦
12.7k31842
12.7k31842
asked Mar 9 '11 at 13:05
TiQ
migrated from stackoverflow.com Mar 9 '11 at 13:20
This question came from our site for professional and enthusiast programmers.
migrated from stackoverflow.com Mar 9 '11 at 13:20
This question came from our site for professional and enthusiast programmers.
When I say reorganize, I do not mean reorganizing indexes, but the actual database file under Tasks/Shrink/File for instance, or using DBCC command.
– TiQ
Mar 9 '11 at 13:07
1
shrinking files is usually a bad idea....
– Mitch Wheat
Mar 9 '11 at 13:07
I never said I would shrink the files, just reorganize pages so free space is contigous.
– TiQ
Mar 9 '11 at 13:13
@TiQ: two lines up: "but the actual database file under Tasks/Shrink/File "
– Mitch Wheat
Mar 9 '11 at 13:14
Making the white space contigous within the database file doesn't do much if anything for performance.
– mrdenny
Mar 27 '11 at 16:59
add a comment |
When I say reorganize, I do not mean reorganizing indexes, but the actual database file under Tasks/Shrink/File for instance, or using DBCC command.
– TiQ
Mar 9 '11 at 13:07
1
shrinking files is usually a bad idea....
– Mitch Wheat
Mar 9 '11 at 13:07
I never said I would shrink the files, just reorganize pages so free space is contigous.
– TiQ
Mar 9 '11 at 13:13
@TiQ: two lines up: "but the actual database file under Tasks/Shrink/File "
– Mitch Wheat
Mar 9 '11 at 13:14
Making the white space contigous within the database file doesn't do much if anything for performance.
– mrdenny
Mar 27 '11 at 16:59
When I say reorganize, I do not mean reorganizing indexes, but the actual database file under Tasks/Shrink/File for instance, or using DBCC command.
– TiQ
Mar 9 '11 at 13:07
When I say reorganize, I do not mean reorganizing indexes, but the actual database file under Tasks/Shrink/File for instance, or using DBCC command.
– TiQ
Mar 9 '11 at 13:07
1
1
shrinking files is usually a bad idea....
– Mitch Wheat
Mar 9 '11 at 13:07
shrinking files is usually a bad idea....
– Mitch Wheat
Mar 9 '11 at 13:07
I never said I would shrink the files, just reorganize pages so free space is contigous.
– TiQ
Mar 9 '11 at 13:13
I never said I would shrink the files, just reorganize pages so free space is contigous.
– TiQ
Mar 9 '11 at 13:13
@TiQ: two lines up: "but the actual database file under Tasks/Shrink/File "
– Mitch Wheat
Mar 9 '11 at 13:14
@TiQ: two lines up: "but the actual database file under Tasks/Shrink/File "
– Mitch Wheat
Mar 9 '11 at 13:14
Making the white space contigous within the database file doesn't do much if anything for performance.
– mrdenny
Mar 27 '11 at 16:59
Making the white space contigous within the database file doesn't do much if anything for performance.
– mrdenny
Mar 27 '11 at 16:59
add a comment |
1 Answer
1
active
oldest
votes
You should do one or the other, but not both. The outcome of both operations is the same thing. Which one you should do will depend on how fragmented your index is, and if you want the stats updated at the same time (rebuild updates the stats for the index).
It will also depend on what edition you have. Rebuilding indexes is an offline operations unless you have the Enterprise edition or higher of SQL Server 2005 or newer.
Typically people will start by defrag their indexes when the fragmentation shows as less than 70% and rebuild when it is higher than 70%.
answered Mar 9 '11 at 15:53
mrdennymrdenny
26.4k43566
The last paragraph is totally out of whack. Leave alone when < 10%, defrag when < 30%, rebuild when over 30% is what I've seen.
– Jon Seigel
Mar 27 '11 at 15:51
Every system, and every index should probably have different rebuild vs. defrag numbers as the data in each index has different data, and each index will perform differently under load.
– mrdenny
Mar 27 '11 at 16:57
Agreed, but why defrag when it's higher than 70%? I don't get that. Did you transpose rebuild and defrag in that sentence?
– Jon Seigel
Mar 27 '11 at 17:01
Yeah, I think I did get them backwards (granted it's pretty early at the moment, at least for me). When fragmentation is low defrag is usually faster, when fragmentation is higher rebuild is usually faster. I'll update my answer.
– mrdenny
Mar 27 '11 at 17:04
add a comment |
Your Answer
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "2"
;
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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
);
);
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f245175%2findex-fragmentation-and-reorganizing-database-pages%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You should do one or the other, but not both. The outcome of both operations is the same thing. Which one you should do will depend on how fragmented your index is, and if you want the stats updated at the same time (rebuild updates the stats for the index).
It will also depend on what edition you have. Rebuilding indexes is an offline operations unless you have the Enterprise edition or higher of SQL Server 2005 or newer.
Typically people will start by defrag their indexes when the fragmentation shows as less than 70% and rebuild when it is higher than 70%.
answered Mar 9 '11 at 15:53
mrdennymrdenny
26.4k43566
The last paragraph is totally out of whack. Leave alone when < 10%, defrag when < 30%, rebuild when over 30% is what I've seen.
– Jon Seigel
Mar 27 '11 at 15:51
Every system, and every index should probably have different rebuild vs. defrag numbers as the data in each index has different data, and each index will perform differently under load.
– mrdenny
Mar 27 '11 at 16:57
Agreed, but why defrag when it's higher than 70%? I don't get that. Did you transpose rebuild and defrag in that sentence?
– Jon Seigel
Mar 27 '11 at 17:01
Yeah, I think I did get them backwards (granted it's pretty early at the moment, at least for me). When fragmentation is low defrag is usually faster, when fragmentation is higher rebuild is usually faster. I'll update my answer.
– mrdenny
Mar 27 '11 at 17:04
add a comment |
You should do one or the other, but not both. The outcome of both operations is the same thing. Which one you should do will depend on how fragmented your index is, and if you want the stats updated at the same time (rebuild updates the stats for the index).
It will also depend on what edition you have. Rebuilding indexes is an offline operations unless you have the Enterprise edition or higher of SQL Server 2005 or newer.
Typically people will start by defrag their indexes when the fragmentation shows as less than 70% and rebuild when it is higher than 70%.
answered Mar 9 '11 at 15:53
mrdennymrdenny
26.4k43566
The last paragraph is totally out of whack. Leave alone when < 10%, defrag when < 30%, rebuild when over 30% is what I've seen.
– Jon Seigel
Mar 27 '11 at 15:51
Every system, and every index should probably have different rebuild vs. defrag numbers as the data in each index has different data, and each index will perform differently under load.
– mrdenny
Mar 27 '11 at 16:57
Agreed, but why defrag when it's higher than 70%? I don't get that. Did you transpose rebuild and defrag in that sentence?
– Jon Seigel
Mar 27 '11 at 17:01
Yeah, I think I did get them backwards (granted it's pretty early at the moment, at least for me). When fragmentation is low defrag is usually faster, when fragmentation is higher rebuild is usually faster. I'll update my answer.
– mrdenny
Mar 27 '11 at 17:04
add a comment |
You should do one or the other, but not both. The outcome of both operations is the same thing. Which one you should do will depend on how fragmented your index is, and if you want the stats updated at the same time (rebuild updates the stats for the index).
It will also depend on what edition you have. Rebuilding indexes is an offline operations unless you have the Enterprise edition or higher of SQL Server 2005 or newer.
Typically people will start by defrag their indexes when the fragmentation shows as less than 70% and rebuild when it is higher than 70%.
answered Mar 9 '11 at 15:53
mrdennymrdenny
26.4k43566
You should do one or the other, but not both. The outcome of both operations is the same thing. Which one you should do will depend on how fragmented your index is, and if you want the stats updated at the same time (rebuild updates the stats for the index).
It will also depend on what edition you have. Rebuilding indexes is an offline operations unless you have the Enterprise edition or higher of SQL Server 2005 or newer.
Typically people will start by defrag their indexes when the fragmentation shows as less than 70% and rebuild when it is higher than 70%.
answered Mar 9 '11 at 15:53
mrdennymrdenny
26.4k43566
edited Mar 27 '11 at 17:05
answered Mar 9 '11 at 15:53
mrdennymrdenny
26.4k43566
answered Mar 9 '11 at 15:53
mrdennymrdenny
26.4k43566
answered Mar 9 '11 at 15:53
mrdennymrdenny
26.4k43566
26.4k43566
The last paragraph is totally out of whack. Leave alone when < 10%, defrag when < 30%, rebuild when over 30% is what I've seen.
– Jon Seigel
Mar 27 '11 at 15:51
Every system, and every index should probably have different rebuild vs. defrag numbers as the data in each index has different data, and each index will perform differently under load.
– mrdenny
Mar 27 '11 at 16:57
Agreed, but why defrag when it's higher than 70%? I don't get that. Did you transpose rebuild and defrag in that sentence?
– Jon Seigel
Mar 27 '11 at 17:01
Yeah, I think I did get them backwards (granted it's pretty early at the moment, at least for me). When fragmentation is low defrag is usually faster, when fragmentation is higher rebuild is usually faster. I'll update my answer.
– mrdenny
Mar 27 '11 at 17:04
add a comment |
The last paragraph is totally out of whack. Leave alone when < 10%, defrag when < 30%, rebuild when over 30% is what I've seen.
– Jon Seigel
Mar 27 '11 at 15:51
Every system, and every index should probably have different rebuild vs. defrag numbers as the data in each index has different data, and each index will perform differently under load.
– mrdenny
Mar 27 '11 at 16:57
Agreed, but why defrag when it's higher than 70%? I don't get that. Did you transpose rebuild and defrag in that sentence?
– Jon Seigel
Mar 27 '11 at 17:01
Yeah, I think I did get them backwards (granted it's pretty early at the moment, at least for me). When fragmentation is low defrag is usually faster, when fragmentation is higher rebuild is usually faster. I'll update my answer.
– mrdenny
Mar 27 '11 at 17:04
The last paragraph is totally out of whack. Leave alone when < 10%, defrag when < 30%, rebuild when over 30% is what I've seen.
– Jon Seigel
Mar 27 '11 at 15:51
The last paragraph is totally out of whack. Leave alone when < 10%, defrag when < 30%, rebuild when over 30% is what I've seen.
– Jon Seigel
Mar 27 '11 at 15:51
Every system, and every index should probably have different rebuild vs. defrag numbers as the data in each index has different data, and each index will perform differently under load.
– mrdenny
Mar 27 '11 at 16:57
Every system, and every index should probably have different rebuild vs. defrag numbers as the data in each index has different data, and each index will perform differently under load.
– mrdenny
Mar 27 '11 at 16:57
Agreed, but why defrag when it's higher than 70%? I don't get that. Did you transpose rebuild and defrag in that sentence?
– Jon Seigel
Mar 27 '11 at 17:01
Agreed, but why defrag when it's higher than 70%? I don't get that. Did you transpose rebuild and defrag in that sentence?
– Jon Seigel
Mar 27 '11 at 17:01
Yeah, I think I did get them backwards (granted it's pretty early at the moment, at least for me). When fragmentation is low defrag is usually faster, when fragmentation is higher rebuild is usually faster. I'll update my answer.
– mrdenny
Mar 27 '11 at 17:04
Yeah, I think I did get them backwards (granted it's pretty early at the moment, at least for me). When fragmentation is low defrag is usually faster, when fragmentation is higher rebuild is usually faster. I'll update my answer.
– mrdenny
Mar 27 '11 at 17:04
add a comment |
Thanks for contributing an answer to Server Fault!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
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
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fserverfault.com%2fquestions%2f245175%2findex-fragmentation-and-reorganizing-database-pages%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
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
Required, but never shown
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
Required, but never shown
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
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
When I say reorganize, I do not mean reorganizing indexes, but the actual database file under Tasks/Shrink/File for instance, or using DBCC command.
– TiQ
Mar 9 '11 at 13:07
1
shrinking files is usually a bad idea....
– Mitch Wheat
Mar 9 '11 at 13:07
I never said I would shrink the files, just reorganize pages so free space is contigous.
– TiQ
Mar 9 '11 at 13:13
@TiQ: two lines up: "but the actual database file under Tasks/Shrink/File "
– Mitch Wheat
Mar 9 '11 at 13:14
Making the white space contigous within the database file doesn't do much if anything for performance.
– mrdenny
Mar 27 '11 at 16:59