SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Index Framentation


Index Framentation

Author
Message
tmmutsetse
tmmutsetse
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1956 Visits: 498
Hi,
I have tables in databases with avg_fragmentation of 86% and a page count of 26 and even less page count on some tables.I set up a job to rebuild and organise these indexes using Ola's script.There is no change after the job runs thrice successfully .the avg_fragmentation is the still the same.How can i solve this?

Thanks
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)SSC Guru (121K reputation)

Group: General Forum Members
Points: 121436 Visits: 18756
You don't - it doesn't need solving. Indexes of less than, say, 1000 pages don't benefit from maintenance. Indeed Ola's code may even specifically exclude them.

John
NorthernSoul
NorthernSoul
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4615 Visits: 7746
There is no point on a page count of 26 and I think Ola's scripts will only run when it's over 1000 pages (not 100% sure on this though).
Generally don't worry about it if it's below 1000 pages.

Thanks
jasona.work
jasona.work
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38449 Visits: 15440
Another thing to check is, what is the data type of the key column(s) of the index?

I've got several tables with 90+% fragmentation which I don't even bother with defragmenting, because the key columns (in the clustered index, no less,) are GUIDs.
tmmutsetse
tmmutsetse
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1956 Visits: 498
Thank you to you all
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)SSC Guru (198K reputation)

Group: General Forum Members
Points: 198122 Visits: 18538
tmmutsetse - Monday, February 5, 2018 6:16 AM
Hi,
I have tables in databases with avg_fragmentation of 86% and a page count of 26 and even less page count on some tables.I set up a job to rebuild and organise these indexes using Ola's script.There is no change after the job runs thrice successfully .the avg_fragmentation is the still the same.How can i solve this?

Thanks


checking your stats will get you the most mileage

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)

Group: General Forum Members
Points: 786185 Visits: 45917
Perry Whittle - Wednesday, February 28, 2018 6:45 AM
tmmutsetse - Monday, February 5, 2018 6:16 AM
Hi,
I have tables in databases with avg_fragmentation of 86% and a page count of 26 and even less page count on some tables.I set up a job to rebuild and organise these indexes using Ola's script.There is no change after the job runs thrice successfully .the avg_fragmentation is the still the same.How can i solve this?

Thanks


checking your stats will get you the most mileage


+1000 to that.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lincoln Burrows
Lincoln Burrows
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15112 Visits: 1653
here is How to Reduce Fragmentation: http://www.sqlserverlogexplorer.com/how-to-find-database-fragmentation/



Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)SSC Guru (786K reputation)

Group: General Forum Members
Points: 786185 Visits: 45917
Lj Burrows - Thursday, March 8, 2018 1:56 AM


That's a "good" traditional article on the subject... with absolutely no mal intent directed towards you or that author (neither of you have done the experiments I have and you're both operating on "Best Practice" recommendations) the problem is that most such articles are based on supposed "Best Practices" and the testing I've been doing for about the last 3 months prove that such traditional methods actually cause performance problems because people wait too long to defrag or have a 100% fill factor on out of sorted order indexes. In the former, all the page splits that have been held off suddenly happen on the day before it's detected for defrag and in the later, page splits go absolutely crazy just as soon as the defrag is done. That is, unless the table has gotten large enough for the "Reorg" portion of most code to kick in and then it doesn't do any better than not defragging at all.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search