Advice on Fragmentation and Indexes

Question

Advice on Fragmentation and Indexes

andy.bain

Valued Member

Points: 68
More actions
May 5, 2009 at 6:51 am

#220827

Hi,
I'm trying to get a better (or should I say simple!) understanding of fragmentation and indexes in the hope of improving database performance.
Through various web searches I found a SQL statement that identifies fragmentation levels in my DB:
SELECT * FROM sys.dm_db_index_physical_stats
(DB_ID(N'databasename'), NULL, NULL, NULL , 'LIMITED');
This contained clustered, non-clustered, and heap indexes. I ran a DB maintenance plan to rebuild and reorganise the indexes. I understand that this should not be able to do anything with the heap indexes however the results, although improved, still left a number of clustered and non-clustered indexes with the same level of fragmentation as previously. My first question is: Why?
I then took a look at the indexes within SQL Server 2005 and on the Fragmentation tab noted the fragmentation level which was still high (90%+ in some cases). The only way I could reduce this was by altering the fill factor manually to a low value. I believe this is fine for tables where data is changed a lot but for more static or read-only tables the value should be high. My concern is whether I should be changing these values manually and whether it will have an affect on performance. Am I better just leaving SQL Server to manage these kind of things for me and that the only intervention I should make is my weekly DB Maintenance Plan?
Thanks
Andy.

Viewing 7 posts - 1 through 6 (of 6 total)

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

andy.bain Valued Member Points: 68 More actions · Answer 1

Sorry, one thing I forgot to ask.

If we have tables with no index, are there any benefits in adding indexes if the number of rows is minimal? Some of our tables only have 1 row, some have 10-20 rows but will never grow any bigger. Should we just leave these un-indexed or are there still performance benefits in adding an index?

Thanks

Andy.

Jack Corbett SSC Guru Points: 184388 More actions · Answer 2

I can't answer all your questions, but I will comment.

You either need to rebuild OR reorganize indexes not both. The rule of thumb is to rebuild if fragmentation is greater than 30% and reorganize between 10 and 30%.

I don't know why a rebuild or reorganize would leave the fragmentation level high, it should reduce it significantly, although changing the fill factor can solve this as you have noted.

In general I always include a primary key in each table which defaults to a clustered index. Is there a performance gain for small, static tables probably not.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Probably because the tables are small. Small tables tend not to defrag, and there's very little point anyway. Fragmentation's only a real issue on large tables when queries are doing large range-scans of the data pages.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

John Najarian-387971 SSC Enthusiast Points: 143 More actions · Answer 4

Newbie, it depends on how the sql query is constructed if indexes are required. In a query it is better if the table with the least rows is to the right of the join than the left. The table scan will be shorter.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

John Najarian (5/12/2009)
In a query it is better if the table with the least rows is to the right of the join than the left. The table scan will be shorter.

Not true. Order of tables listed in a query has no effect on the order that SQL will process them (unless the forceorder hint is applied)

See - http://sql-4-life.blogspot.com/2009/03/order-of-inner-joins.html

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 6

To add to Jack's information - do not defragment or reindex tables less than 64k pages (one extent). Since they are stored on mixed extents it does nothing. Remember SQL Server reads extents (8 - 8k pages) and writes pages (8k).

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."