May 5, 2009 at 6:51 am
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.
May 5, 2009 at 6:53 am
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.
May 5, 2009 at 8:05 am
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
May 5, 2009 at 9:42 am
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
May 12, 2009 at 8:45 pm
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.
May 12, 2009 at 10:27 pm
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
May 13, 2009 at 10:30 am
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."
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply