November 6, 2014 at 2:24 pm
I have 460 indexes ona database with a fragmentation percentage at 30% or greater. I have a sproc for handling this by loading the Alter Index script into a cursor and executing the script for each index. I'm thinking of updating this to only rebuild say 25 of the ones with the qorst fragmentation%. Scheduling it in a job and running every night until my indexes are rebuild with a fill factor of 80%. My question is: Am I being to conservative with rebuilding only 25 per night or should I just let this run for all?
November 6, 2014 at 2:30 pm
Depends on your system and how long his will take.
Are you running enterprise edition and are you planning to rebuilh with online option?
Have you run this in test?
November 6, 2014 at 2:35 pm
Yes,it is Enterprise edition and plan on running somethink like the following:
ALTER INDEX PK_QNXTClaimSubmitKeys ON dbo.QNXTClaimSubmitKeys REBUILD WITH (FILLFACTOR = 80)
The db will be online but during a time of day when no one is using it.
November 6, 2014 at 2:43 pm
I suggest that you take a look at Ola Hallengren's index maintenance procedure.
It takes care of rebuilding/defragmenting indexes based on fragmentation level and skips the indexes with low fragmentation or small page count.
It can be found at ola.hallengren.com and it's kind of a standard.
-- Gianluca Sartori
November 6, 2014 at 3:41 pm
1)
fill factor of 80%
That's pretty low. How did you decide on that? You really need to tailor for each table's situation and not just apply a low value to all tables. If the base table is already, say, 5GB, you've just made it a minimum of 6GB, and likely 6.2 or so.
2) Have you reviewed index usage stats and missing index stats first? Maybe some of the indexes need removed rather than rebuilt. And maybe some indexes need rebuilt with different column(s) than they have now.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 7, 2014 at 3:30 am
spaghettidba (11/6/2014)
I suggest that you take a look at Ola Hallengren's index maintenance procedure.It takes care of rebuilding/defragmenting indexes based on fragmentation level and skips the indexes with low fragmentation or small page count.
It can be found at ola.hallengren.com and it's kind of a standard.
+100000
With scripts of this quality - there's no need to do your own, we've been using it for a couple of years now with no complaints at all
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
November 7, 2014 at 6:33 am
*This is a test*
Edit: There seems to be an issue creating new threads but not commenting on existing ones?
November 7, 2014 at 7:09 am
After thinking on it I'm beginning to think that if I have Hallengren's index maintenance procedure running in a job then I can probably leave the Fill Factor at 0 since the job will run nightly if any index get fragmented it will be handled that night. What do you guys think?
November 7, 2014 at 8:07 am
dndaughtery (11/7/2014)
After thinking on it I'm beginning to think that if I have Hallengren's index maintenance procedure running in a job then I can probably leave the Fill Factor at 0 since the job will run nightly if any index get fragmented it will be handled that night. What do you guys think?
I think that wholesale changes of the Fill Factor without understanding why the original settings were set that way are a possible recipe for a performance disaster.
--Jeff Moden
November 7, 2014 at 8:29 am
At the present the fill factor on all indexes are 0
November 7, 2014 at 8:38 am
Fillfactor 0 is potentially extremely dangerous to performance, and can be very prone to fragmentation, esp. on a table where it's common to lengthen [n]varchar column(s).
I also noted above that the fillfactor should be carefully set for each table. However, realistically, that is initially not really possible when dealing with 460+ indexes. Therefore, you simply must use some rough guidelines.
For tables clustered by identity, try ~95. For others, start with ~90-95.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
November 7, 2014 at 2:16 pm
I am running the following script to get the indexes that have fragmentation over 30%. The result set is returning some indexes that I don't see in Object exlorer for the table Index combination in the return set. Why is this?
SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('Portal'), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent >= 30 AND indexstats.page_count > 1000
ORDER BY indexstats.avg_fragmentation_in_percent DESC
November 7, 2014 at 9:36 pm
dndaughtery (11/7/2014)
At the present the fill factor on all indexes are 0
Then you should be good to go with Ola's proc.
--Jeff Moden
November 7, 2014 at 9:37 pm
dndaughtery (11/7/2014)
I am running the following script to get the indexes that have fragmentation over 30%. The result set is returning some indexes that I don't see in Object exlorer for the table Index combination in the return set. Why is this?SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
indexstats.avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('Portal'), NULL, NULL, NULL, 'DETAILED') indexstats
INNER JOIN sys.indexes i ON i.OBJECT_ID = indexstats.OBJECT_ID
AND i.index_id = indexstats.index_id
WHERE indexstats.avg_fragmentation_in_percent >= 30 AND indexstats.page_count > 1000
ORDER BY indexstats.avg_fragmentation_in_percent DESC
Some may be for blobs and many will be for the different levels in the B-Tree, if that's what you mean. You'll also get info back on all the HEAPs.
--Jeff Moden
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply