Indexes getting highly fragmented during normal usage of systemHow to prevent transaction log getting full during index reorganize?SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errorsWhy do we need to rebuild and reorganize Indexes in SQL ServerMaintenance plansIndex file growing endlessly - how to fix it?Database logs growing during maintenance planDatetime Clustered Index Keeps getting fragmentedRebuild index online without risk to other requestsVerify this TSQL to find the indexes fragmented over 50%SELECT TOP 1 from a very large table on an index column is very slow, but not with reverse order (“desc”)
how can i make this execution plan more efficient?
Why did moving the mouse cursor cause Windows 95 to run more quickly?
How would an Amulet of Proof Against Detection and Location interact with the Comprehend Languages spell?
Do I need to be legally qualified to install a Hive smart thermostat?
Boss has banned cycling to work because he thinks it's unsafe
Why do we need a bootloader separate than our application program in MCU's?
Does Evolution Sage proliferate Blast Zone when played?
Minimizing medical costs with HSA
Do we have a much compact and generalized version of erase–remove idiom?
Platform Event Design when Subscribers are Apex Triggers
Interview Question - Card betting
Speeding up thousands of string parses
Data normalization before or after train-test split?
Term for a character that only exists to be talked to
How to supply water to a coastal desert town with no rain and no freshwater aquifers?
Bypass with wrong cvv of debit card and getting OTP
How to travel between two stationary worlds in the least amount of time? (time dilation)
How can solar sailed ships be protected from space debris?
Does a multiclassed wizard start with a spellbook?
Are "confidant" and "confident" homophones?
Sleepy tired vs physically tired
What is the addition in the re-released version of Avengers: Endgame?
Do intermediate subdomains need to exist?
Convenience stores in India
Indexes getting highly fragmented during normal usage of system
How to prevent transaction log getting full during index reorganize?SQL Server Rebuild/Reorganize Fragmented Indexes causes CRC error or consistency errorsWhy do we need to rebuild and reorganize Indexes in SQL ServerMaintenance plansIndex file growing endlessly - how to fix it?Database logs growing during maintenance planDatetime Clustered Index Keeps getting fragmentedRebuild index online without risk to other requestsVerify this TSQL to find the indexes fragmented over 50%SELECT TOP 1 from a very large table on an index column is very slow, but not with reverse order (“desc”)
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I am using MS SQL Server 2014 Standard Edition in my project and recently I have encountered an issue with index fragmentation getting high during normal usage of the system by the users.
Currently indexes are rebuild once per day during maintenance window, but after certain operations some of the indexes can get quite high (above 30%). Other issue is that in case there is big amount of data to process, the fragmentation occurs during single process, so we end up with process that starts rather quickly, but as soon as fragmentation kicks in its performance drops drastically.
What can I do in such situation? It is rather impossible for me to get additional maintenance window(s).
I was thinking about rebuilding only some of indexes periodically, but I am afraid that it will end up with locks or even deadlocks on the database.
sql-server sql-server-2014 fragmentation index-maintenance
edited Jun 26 at 4:54
Paul White♦
57k15 gold badges299 silver badges473 bronze badges
asked Jun 25 at 16:08
MichalMichal
211 bronze badge
add a comment |
I am using MS SQL Server 2014 Standard Edition in my project and recently I have encountered an issue with index fragmentation getting high during normal usage of the system by the users.
Currently indexes are rebuild once per day during maintenance window, but after certain operations some of the indexes can get quite high (above 30%). Other issue is that in case there is big amount of data to process, the fragmentation occurs during single process, so we end up with process that starts rather quickly, but as soon as fragmentation kicks in its performance drops drastically.
What can I do in such situation? It is rather impossible for me to get additional maintenance window(s).
I was thinking about rebuilding only some of indexes periodically, but I am afraid that it will end up with locks or even deadlocks on the database.
sql-server sql-server-2014 fragmentation index-maintenance
edited Jun 26 at 4:54
Paul White♦
57k15 gold badges299 silver badges473 bronze badges
asked Jun 25 at 16:08
MichalMichal
211 bronze badge
What are your tables / database size? Have you checked your queries and execution plans? Even that fragmentation can hurt the performance, I don't think they are the only reason of the performance issues. There could be other issues, like blocking or other processes running at the same time.
– dbamex
Jun 25 at 16:31
add a comment |
I am using MS SQL Server 2014 Standard Edition in my project and recently I have encountered an issue with index fragmentation getting high during normal usage of the system by the users.
Currently indexes are rebuild once per day during maintenance window, but after certain operations some of the indexes can get quite high (above 30%). Other issue is that in case there is big amount of data to process, the fragmentation occurs during single process, so we end up with process that starts rather quickly, but as soon as fragmentation kicks in its performance drops drastically.
What can I do in such situation? It is rather impossible for me to get additional maintenance window(s).
I was thinking about rebuilding only some of indexes periodically, but I am afraid that it will end up with locks or even deadlocks on the database.
sql-server sql-server-2014 fragmentation index-maintenance
edited Jun 26 at 4:54
Paul White♦
57k15 gold badges299 silver badges473 bronze badges
asked Jun 25 at 16:08
MichalMichal
211 bronze badge
I am using MS SQL Server 2014 Standard Edition in my project and recently I have encountered an issue with index fragmentation getting high during normal usage of the system by the users.
Currently indexes are rebuild once per day during maintenance window, but after certain operations some of the indexes can get quite high (above 30%). Other issue is that in case there is big amount of data to process, the fragmentation occurs during single process, so we end up with process that starts rather quickly, but as soon as fragmentation kicks in its performance drops drastically.
What can I do in such situation? It is rather impossible for me to get additional maintenance window(s).
I was thinking about rebuilding only some of indexes periodically, but I am afraid that it will end up with locks or even deadlocks on the database.
sql-server sql-server-2014 fragmentation index-maintenance
sql-server sql-server-2014 fragmentation index-maintenance
edited Jun 26 at 4:54
Paul White♦
57k15 gold badges299 silver badges473 bronze badges
asked Jun 25 at 16:08
MichalMichal
211 bronze badge
edited Jun 26 at 4:54
Paul White♦
57k15 gold badges299 silver badges473 bronze badges
asked Jun 25 at 16:08
MichalMichal
211 bronze badge
edited Jun 26 at 4:54
Paul White♦
57k15 gold badges299 silver badges473 bronze badges
edited Jun 26 at 4:54
Paul White♦
57k15 gold badges299 silver badges473 bronze badges
edited Jun 26 at 4:54
Paul White♦
57k15 gold badges299 silver badges473 bronze badges
57k15 gold badges299 silver badges473 bronze badges
asked Jun 25 at 16:08
MichalMichal
211 bronze badge
asked Jun 25 at 16:08
MichalMichal
211 bronze badge
asked Jun 25 at 16:08
MichalMichal
211 bronze badge
211 bronze badge
What are your tables / database size? Have you checked your queries and execution plans? Even that fragmentation can hurt the performance, I don't think they are the only reason of the performance issues. There could be other issues, like blocking or other processes running at the same time.
– dbamex
Jun 25 at 16:31
add a comment |
What are your tables / database size? Have you checked your queries and execution plans? Even that fragmentation can hurt the performance, I don't think they are the only reason of the performance issues. There could be other issues, like blocking or other processes running at the same time.
– dbamex
Jun 25 at 16:31
What are your tables / database size? Have you checked your queries and execution plans? Even that fragmentation can hurt the performance, I don't think they are the only reason of the performance issues. There could be other issues, like blocking or other processes running at the same time.
– dbamex
Jun 25 at 16:31
What are your tables / database size? Have you checked your queries and execution plans? Even that fragmentation can hurt the performance, I don't think they are the only reason of the performance issues. There could be other issues, like blocking or other processes running at the same time.
– dbamex
Jun 25 at 16:31
add a comment |
2 Answers
2
active
oldest
votes
Some options:
Just update statistics
You mentioned that fragmentation was causing performance issues. A lot of times it seems like fragmentation is the issue, when in reality the problem is bad statistics (due to how much data has changed in the table).
You might be better off just updating statistics on the tables involved:
- immediately after these large operations, or
- on a schedule (weekly, daily, or even a couple times a day - depending on how often issues crop up)
Rebuilding indexes causes statistics to be updated, which can reinforce the perception that fragmentation is the problem. These operations are generally far less disruptive and resource intensive than an index rebuild.
UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN;
You could also play around with "sample percent" to avoid scanning the whole table:
UPDATE STATISTICS dbo.YourTableName WITH SAMPLE 50 PERCENT;
Take care when testing different sample rates, as there is a tipping point where sampled stats can take longer than FULLSCAN. This depends on multiple factors (such as your data, and resources available). Erin Stellato has a great post on the subject here: Sample Size and the Duration of UPDATE STATISTICS: Does It Matter?
Avoid common causes of fragmentation
You may be able to change your schema / workload to avoid common causes of fragmentation, and thus slow or stop the problem before it starts. The go-to example would be to avoid having a uniqueidentifier primary key being filled via NEWID().
More generally, a good clustered index should be narrow, unique, static, and ever-increasing.
Rebuild indexes online
If rebuilding indexes during normal user activity is extremely important, you may have to try to justify upgrading to the Enterprise Edition of SQL Server. Then you could specify the index rebuild to occur "ONLINE":
ALTER INDEX [IX_YourIndex] REBUILD WITH (ONLINE = ON);
This can allow user activity to continue during the index operation, other than at the very end (which should be brief). Of course, the rebuild still uses server resources, so it could impact overall performance.
I put this last because it has a big price tag on it. You should definitely explore the other options before going this direction, and test to make sure REBUILDs will actually fix the problem.
answered Jun 25 at 16:48
Josh DarnellJosh Darnell
10.1k3 gold badges23 silver badges51 bronze badges
add a comment |
If you can properly confirm that fragmentation really is a problem, for example because read performance drops measurably when read-ahead becomes less effective, or because the system is struggling to keep up with the number of page splits, you might look at gently adjusting the fill factor on the most critical indexes. This will improve write performance, at a cost to reads and the number of pages that can be cached (in the limited buffer pool available with Standard Edition).
Don't go crazy with low fill factors to start with. Pick a manageable number of the most problematic indexes, make an estimate of the amount of free space needed to avoid too many splits during the normal workload between maintenance windows, and test the effects of setting the lower fill factor (normally no lower than about 90% for the first test). Do not just blindly set a lower fill factor for all indexes.
Bear in mind that nine times out of ten, the system has other, bigger, problems than those directly attributable to sub-optimally set fill factors. You should be monitoring the important performance aspects of the system, and be able to make the case for any changes based on a sound technical analysis of real historical data.
Reading material:
Specify Fill Factor for an Index in the product documentation
5 Things About Fillfactor by Kendra Little
Does index fill factor affect fragmentation? by Erik Darling
Stop Worrying About SQL Server Fragmentation by Brent Ozar
What is the Best Value for Fill Factor in SQL Server by Eric Blinn
SQL Server Index and Statistics Maintenance by Ola Hallengren
answered Jun 26 at 6:04
Paul White♦Paul White
57k15 gold badges299 silver badges473 bronze badges
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
);
);
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%2fdba.stackexchange.com%2fquestions%2f241387%2findexes-getting-highly-fragmented-during-normal-usage-of-system%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Some options:
Just update statistics
You mentioned that fragmentation was causing performance issues. A lot of times it seems like fragmentation is the issue, when in reality the problem is bad statistics (due to how much data has changed in the table).
You might be better off just updating statistics on the tables involved:
- immediately after these large operations, or
- on a schedule (weekly, daily, or even a couple times a day - depending on how often issues crop up)
Rebuilding indexes causes statistics to be updated, which can reinforce the perception that fragmentation is the problem. These operations are generally far less disruptive and resource intensive than an index rebuild.
UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN;
You could also play around with "sample percent" to avoid scanning the whole table:
UPDATE STATISTICS dbo.YourTableName WITH SAMPLE 50 PERCENT;
Take care when testing different sample rates, as there is a tipping point where sampled stats can take longer than FULLSCAN. This depends on multiple factors (such as your data, and resources available). Erin Stellato has a great post on the subject here: Sample Size and the Duration of UPDATE STATISTICS: Does It Matter?
Avoid common causes of fragmentation
You may be able to change your schema / workload to avoid common causes of fragmentation, and thus slow or stop the problem before it starts. The go-to example would be to avoid having a uniqueidentifier primary key being filled via NEWID().
More generally, a good clustered index should be narrow, unique, static, and ever-increasing.
Rebuild indexes online
If rebuilding indexes during normal user activity is extremely important, you may have to try to justify upgrading to the Enterprise Edition of SQL Server. Then you could specify the index rebuild to occur "ONLINE":
ALTER INDEX [IX_YourIndex] REBUILD WITH (ONLINE = ON);
This can allow user activity to continue during the index operation, other than at the very end (which should be brief). Of course, the rebuild still uses server resources, so it could impact overall performance.
I put this last because it has a big price tag on it. You should definitely explore the other options before going this direction, and test to make sure REBUILDs will actually fix the problem.
answered Jun 25 at 16:48
Josh DarnellJosh Darnell
10.1k3 gold badges23 silver badges51 bronze badges
add a comment |
Some options:
Just update statistics
You mentioned that fragmentation was causing performance issues. A lot of times it seems like fragmentation is the issue, when in reality the problem is bad statistics (due to how much data has changed in the table).
You might be better off just updating statistics on the tables involved:
- immediately after these large operations, or
- on a schedule (weekly, daily, or even a couple times a day - depending on how often issues crop up)
Rebuilding indexes causes statistics to be updated, which can reinforce the perception that fragmentation is the problem. These operations are generally far less disruptive and resource intensive than an index rebuild.
UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN;
You could also play around with "sample percent" to avoid scanning the whole table:
UPDATE STATISTICS dbo.YourTableName WITH SAMPLE 50 PERCENT;
Take care when testing different sample rates, as there is a tipping point where sampled stats can take longer than FULLSCAN. This depends on multiple factors (such as your data, and resources available). Erin Stellato has a great post on the subject here: Sample Size and the Duration of UPDATE STATISTICS: Does It Matter?
Avoid common causes of fragmentation
You may be able to change your schema / workload to avoid common causes of fragmentation, and thus slow or stop the problem before it starts. The go-to example would be to avoid having a uniqueidentifier primary key being filled via NEWID().
More generally, a good clustered index should be narrow, unique, static, and ever-increasing.
Rebuild indexes online
If rebuilding indexes during normal user activity is extremely important, you may have to try to justify upgrading to the Enterprise Edition of SQL Server. Then you could specify the index rebuild to occur "ONLINE":
ALTER INDEX [IX_YourIndex] REBUILD WITH (ONLINE = ON);
This can allow user activity to continue during the index operation, other than at the very end (which should be brief). Of course, the rebuild still uses server resources, so it could impact overall performance.
I put this last because it has a big price tag on it. You should definitely explore the other options before going this direction, and test to make sure REBUILDs will actually fix the problem.
answered Jun 25 at 16:48
Josh DarnellJosh Darnell
10.1k3 gold badges23 silver badges51 bronze badges
add a comment |
Some options:
Just update statistics
You mentioned that fragmentation was causing performance issues. A lot of times it seems like fragmentation is the issue, when in reality the problem is bad statistics (due to how much data has changed in the table).
You might be better off just updating statistics on the tables involved:
- immediately after these large operations, or
- on a schedule (weekly, daily, or even a couple times a day - depending on how often issues crop up)
Rebuilding indexes causes statistics to be updated, which can reinforce the perception that fragmentation is the problem. These operations are generally far less disruptive and resource intensive than an index rebuild.
UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN;
You could also play around with "sample percent" to avoid scanning the whole table:
UPDATE STATISTICS dbo.YourTableName WITH SAMPLE 50 PERCENT;
Take care when testing different sample rates, as there is a tipping point where sampled stats can take longer than FULLSCAN. This depends on multiple factors (such as your data, and resources available). Erin Stellato has a great post on the subject here: Sample Size and the Duration of UPDATE STATISTICS: Does It Matter?
Avoid common causes of fragmentation
You may be able to change your schema / workload to avoid common causes of fragmentation, and thus slow or stop the problem before it starts. The go-to example would be to avoid having a uniqueidentifier primary key being filled via NEWID().
More generally, a good clustered index should be narrow, unique, static, and ever-increasing.
Rebuild indexes online
If rebuilding indexes during normal user activity is extremely important, you may have to try to justify upgrading to the Enterprise Edition of SQL Server. Then you could specify the index rebuild to occur "ONLINE":
ALTER INDEX [IX_YourIndex] REBUILD WITH (ONLINE = ON);
This can allow user activity to continue during the index operation, other than at the very end (which should be brief). Of course, the rebuild still uses server resources, so it could impact overall performance.
I put this last because it has a big price tag on it. You should definitely explore the other options before going this direction, and test to make sure REBUILDs will actually fix the problem.
answered Jun 25 at 16:48
Josh DarnellJosh Darnell
10.1k3 gold badges23 silver badges51 bronze badges
Some options:
Just update statistics
You mentioned that fragmentation was causing performance issues. A lot of times it seems like fragmentation is the issue, when in reality the problem is bad statistics (due to how much data has changed in the table).
You might be better off just updating statistics on the tables involved:
- immediately after these large operations, or
- on a schedule (weekly, daily, or even a couple times a day - depending on how often issues crop up)
Rebuilding indexes causes statistics to be updated, which can reinforce the perception that fragmentation is the problem. These operations are generally far less disruptive and resource intensive than an index rebuild.
UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN;
You could also play around with "sample percent" to avoid scanning the whole table:
UPDATE STATISTICS dbo.YourTableName WITH SAMPLE 50 PERCENT;
Take care when testing different sample rates, as there is a tipping point where sampled stats can take longer than FULLSCAN. This depends on multiple factors (such as your data, and resources available). Erin Stellato has a great post on the subject here: Sample Size and the Duration of UPDATE STATISTICS: Does It Matter?
Avoid common causes of fragmentation
You may be able to change your schema / workload to avoid common causes of fragmentation, and thus slow or stop the problem before it starts. The go-to example would be to avoid having a uniqueidentifier primary key being filled via NEWID().
More generally, a good clustered index should be narrow, unique, static, and ever-increasing.
Rebuild indexes online
If rebuilding indexes during normal user activity is extremely important, you may have to try to justify upgrading to the Enterprise Edition of SQL Server. Then you could specify the index rebuild to occur "ONLINE":
ALTER INDEX [IX_YourIndex] REBUILD WITH (ONLINE = ON);
This can allow user activity to continue during the index operation, other than at the very end (which should be brief). Of course, the rebuild still uses server resources, so it could impact overall performance.
I put this last because it has a big price tag on it. You should definitely explore the other options before going this direction, and test to make sure REBUILDs will actually fix the problem.
answered Jun 25 at 16:48
Josh DarnellJosh Darnell
10.1k3 gold badges23 silver badges51 bronze badges
edited Jun 25 at 20:11
answered Jun 25 at 16:48
Josh DarnellJosh Darnell
10.1k3 gold badges23 silver badges51 bronze badges
answered Jun 25 at 16:48
Josh DarnellJosh Darnell
10.1k3 gold badges23 silver badges51 bronze badges
answered Jun 25 at 16:48
Josh DarnellJosh Darnell
10.1k3 gold badges23 silver badges51 bronze badges
10.1k3 gold badges23 silver badges51 bronze badges
add a comment |
add a comment |
If you can properly confirm that fragmentation really is a problem, for example because read performance drops measurably when read-ahead becomes less effective, or because the system is struggling to keep up with the number of page splits, you might look at gently adjusting the fill factor on the most critical indexes. This will improve write performance, at a cost to reads and the number of pages that can be cached (in the limited buffer pool available with Standard Edition).
Don't go crazy with low fill factors to start with. Pick a manageable number of the most problematic indexes, make an estimate of the amount of free space needed to avoid too many splits during the normal workload between maintenance windows, and test the effects of setting the lower fill factor (normally no lower than about 90% for the first test). Do not just blindly set a lower fill factor for all indexes.
Bear in mind that nine times out of ten, the system has other, bigger, problems than those directly attributable to sub-optimally set fill factors. You should be monitoring the important performance aspects of the system, and be able to make the case for any changes based on a sound technical analysis of real historical data.
Reading material:
Specify Fill Factor for an Index in the product documentation
5 Things About Fillfactor by Kendra Little
Does index fill factor affect fragmentation? by Erik Darling
Stop Worrying About SQL Server Fragmentation by Brent Ozar
What is the Best Value for Fill Factor in SQL Server by Eric Blinn
SQL Server Index and Statistics Maintenance by Ola Hallengren
answered Jun 26 at 6:04
Paul White♦Paul White
57k15 gold badges299 silver badges473 bronze badges
add a comment |
If you can properly confirm that fragmentation really is a problem, for example because read performance drops measurably when read-ahead becomes less effective, or because the system is struggling to keep up with the number of page splits, you might look at gently adjusting the fill factor on the most critical indexes. This will improve write performance, at a cost to reads and the number of pages that can be cached (in the limited buffer pool available with Standard Edition).
Don't go crazy with low fill factors to start with. Pick a manageable number of the most problematic indexes, make an estimate of the amount of free space needed to avoid too many splits during the normal workload between maintenance windows, and test the effects of setting the lower fill factor (normally no lower than about 90% for the first test). Do not just blindly set a lower fill factor for all indexes.
Bear in mind that nine times out of ten, the system has other, bigger, problems than those directly attributable to sub-optimally set fill factors. You should be monitoring the important performance aspects of the system, and be able to make the case for any changes based on a sound technical analysis of real historical data.
Reading material:
Specify Fill Factor for an Index in the product documentation
5 Things About Fillfactor by Kendra Little
Does index fill factor affect fragmentation? by Erik Darling
Stop Worrying About SQL Server Fragmentation by Brent Ozar
What is the Best Value for Fill Factor in SQL Server by Eric Blinn
SQL Server Index and Statistics Maintenance by Ola Hallengren
answered Jun 26 at 6:04
Paul White♦Paul White
57k15 gold badges299 silver badges473 bronze badges
add a comment |
If you can properly confirm that fragmentation really is a problem, for example because read performance drops measurably when read-ahead becomes less effective, or because the system is struggling to keep up with the number of page splits, you might look at gently adjusting the fill factor on the most critical indexes. This will improve write performance, at a cost to reads and the number of pages that can be cached (in the limited buffer pool available with Standard Edition).
Don't go crazy with low fill factors to start with. Pick a manageable number of the most problematic indexes, make an estimate of the amount of free space needed to avoid too many splits during the normal workload between maintenance windows, and test the effects of setting the lower fill factor (normally no lower than about 90% for the first test). Do not just blindly set a lower fill factor for all indexes.
Bear in mind that nine times out of ten, the system has other, bigger, problems than those directly attributable to sub-optimally set fill factors. You should be monitoring the important performance aspects of the system, and be able to make the case for any changes based on a sound technical analysis of real historical data.
Reading material:
Specify Fill Factor for an Index in the product documentation
5 Things About Fillfactor by Kendra Little
Does index fill factor affect fragmentation? by Erik Darling
Stop Worrying About SQL Server Fragmentation by Brent Ozar
What is the Best Value for Fill Factor in SQL Server by Eric Blinn
SQL Server Index and Statistics Maintenance by Ola Hallengren
answered Jun 26 at 6:04
Paul White♦Paul White
57k15 gold badges299 silver badges473 bronze badges
If you can properly confirm that fragmentation really is a problem, for example because read performance drops measurably when read-ahead becomes less effective, or because the system is struggling to keep up with the number of page splits, you might look at gently adjusting the fill factor on the most critical indexes. This will improve write performance, at a cost to reads and the number of pages that can be cached (in the limited buffer pool available with Standard Edition).
Don't go crazy with low fill factors to start with. Pick a manageable number of the most problematic indexes, make an estimate of the amount of free space needed to avoid too many splits during the normal workload between maintenance windows, and test the effects of setting the lower fill factor (normally no lower than about 90% for the first test). Do not just blindly set a lower fill factor for all indexes.
Bear in mind that nine times out of ten, the system has other, bigger, problems than those directly attributable to sub-optimally set fill factors. You should be monitoring the important performance aspects of the system, and be able to make the case for any changes based on a sound technical analysis of real historical data.
Reading material:
Specify Fill Factor for an Index in the product documentation
5 Things About Fillfactor by Kendra Little
Does index fill factor affect fragmentation? by Erik Darling
Stop Worrying About SQL Server Fragmentation by Brent Ozar
What is the Best Value for Fill Factor in SQL Server by Eric Blinn
SQL Server Index and Statistics Maintenance by Ola Hallengren
answered Jun 26 at 6:04
Paul White♦Paul White
57k15 gold badges299 silver badges473 bronze badges
answered Jun 26 at 6:04
Paul White♦Paul White
57k15 gold badges299 silver badges473 bronze badges
answered Jun 26 at 6:04
Paul White♦Paul White
57k15 gold badges299 silver badges473 bronze badges
answered Jun 26 at 6:04
Paul White♦Paul White
57k15 gold badges299 silver badges473 bronze badges
57k15 gold badges299 silver badges473 bronze badges
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f241387%2findexes-getting-highly-fragmented-during-normal-usage-of-system%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
What are your tables / database size? Have you checked your queries and execution plans? Even that fragmentation can hurt the performance, I don't think they are the only reason of the performance issues. There could be other issues, like blocking or other processes running at the same time.
– dbamex
Jun 25 at 16:31