Say I have 460 indexes that need to be rebuilt.... :w00t:

Question

Post reply

Say I have 460 indexes that need to be rebuilt.... :w00t:

dndaughtery

SSChampion

Points: 11290
More actions
November 6, 2014 at 2:24 pm

#312381

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?

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

arnipetursson SSCertifiable Points: 6557 More actions · Answer 1

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?

dndaughtery SSChampion Points: 11290 More actions · Answer 2

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.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

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

ScottPletcher SSC Guru Points: 101091 More actions · Answer 4

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Stuart Davies SSCoach Points: 19505 More actions · Answer 5

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

Dird SSCertifiable Points: 5081 More actions · Answer 6

*This is a test*

Edit: There seems to be an issue creating new threads but not commenting on existing ones?

Dird

dndaughtery SSChampion Points: 11290 More actions · Answer 7

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?

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 8

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dndaughtery SSChampion Points: 11290 More actions · Answer 9

dndaughtery

SSChampion

Points: 11290

November 7, 2014 at 8:29 am

#1757232

At the present the fill factor on all indexes are 0

ScottPletcher SSC Guru Points: 101091 More actions · Answer 10

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

dndaughtery SSChampion Points: 11290 More actions · Answer 11

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

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 12

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 13

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)