3

I am hitting database performance issues, so we rebuilt all our indexes as we haven't been doing a great job maintaining them. Rebuild ran for about 20 minutes. Then I found a query to see how my indexes are looking

SELECT 
t.NAME 'Table name',
i.NAME 'Index name',
ips.index_type_desc,
ips.alloc_unit_type_desc,
ips.index_depth,
ips.index_level,
ips.avg_fragmentation_in_percent,
ips.fragment_count,
ips.avg_fragment_size_in_pages,
ips.page_count,
ips.avg_page_space_used_in_percent,
ips.record_count,
ips.ghost_record_count,
ips.Version_ghost_record_count,
ips.min_record_size_in_bytes,
ips.max_record_size_in_bytes,
ips.avg_record_size_in_bytes,
ips.forwarded_record_count
FROM 
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
INNER JOIN  
sys.tables t ON ips.OBJECT_ID = t.Object_ID
INNER JOIN  
sys.indexes i ON ips.index_id = i.index_id AND ips.OBJECT_ID = i.object_id
WHERE
AVG_FRAGMENTATION_IN_PERCENT > 0.0
ORDER BY
AVG_FRAGMENTATION_IN_PERCENT, fragment_count

So most of my indexes have an average percentage fragmentation <1 But I have about 10 that are 85%, and another 10 that are 100%

I am no dba, and understand very little about what I am doing here, but surely this is very bad?

Also worth noting that the 100%'s are all primary keys

Am I in a world of trouble, or is my understanding of the stats incorrect?

note: worth mentioning my database is 24gb, and my drive has 46gb free out of 115gb

These are my worst indexes as per sql above

EDIT: as requested reindex script below

DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.['+name+']' AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
print @TableName
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR =     ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO

results of original index fragmentation qry attached as requested results

3
  • 4
    This is entirely expected for such small tables as the first 8 pages are allocated from mixed extents. This won't be causing your performance problems. Jun 1, 2016 at 16:58
  • 1
    You should be filtering out tiny tables where fragmentation is 100% irrelevant. Jun 1, 2016 at 16:58
  • You really need Ola Hallengren in your life. ola.hallengren.com/… Jun 2, 2016 at 1:36

2 Answers 2

8

I am hitting database performance issues, so we rebuilt all our indexes as we haven't been doing a great job maintaining them.

This is a knee-jerk response to a performance problem. Your page counts are less than 10 ! Rebuilding the indexes will be a least help in your case.

I would start by reading and understanding How to analyse SQL Server performance ?

0
1

Yes fragmentation will negatively impact performance.
Anything over 30% is bad but on a small table still would not have a major impact.
But on a small table defrag is fast so might as well do it.

That report you posted is 20 or so rows and starts on 96. You could also have > 30% on some big indexes. In rows 1-95 I can pretty much guarantee you have some problem indexes. This is a 24 gb database and you state indexes have not been maintained. What you list is not a problem but where there is smoke there is fire.

Rebuild all is a bit much.
The guildline from Microsoft is

5% and < = 30% ALTER INDEX REORGANIZE
30% ALTER INDEX REBUILD WITH (ONLINE = ON)*

Reorganize and Rebuild Indexes

There are scripts out there that will go though and reorganize verses rebuild versus skip.

I don't agree with Kin this is a knee response that will be a least help. Yes it looks like some smaller tables but defragmenting indexes is good starting point to looking at performance problems. Again you most likely have some big indexes that are fragmented.

You can slow down fragmentation by using a fill factor < 100. But your index will also use more space. On a small table just leave it alone.

You can also slow down fragmentation by inserting data in the order of the index.

After that look at your worse preforming queries one at a time and address performance.

6
  • 3
    The reason I called it as knee-jerk response as often index fragmentation is not the root cause. The OP should follow a methodology about ruling out the obvious ones - e.g. blocking. I would start by finding out what is sql server waiting on - so it is running slow ? I often see people randomly updating stats & rebuilding/reorg indexes - this has a side effect of invalidating all the query plans of the object. Look how the OP reacted we rebuilt all our indexes.
    – Kin Shah
    Jun 1, 2016 at 17:19
  • @Kin And I don't agree with your analysis it will be a least help. You don't know there are are not some big indexes with fragmentation. And I have no intent of down voting you answer.
    – paparazzo
    Jun 1, 2016 at 17:27
  • 2
    @Paparazzi We can all agree/disagree with all the reasoning we want. The truth or the methodology does not change. What believes you that index fragmentation is the problem here and the OP mentioned indexes with 100% fragmentation which only have 5-6 page count ?
    – Kin Shah
    Jun 1, 2016 at 17:37
  • 1
    @kin For the third time that is clearly not a complete report. There could be large indexes with significant fragmentation. I highly suspect there are. I state clearly "but on a small table still would not have a major impact". Really you would not follow Microsoft guidelines of 5-30 reorganize and > 30 rebuild as I proposed in my answer? Correct the listed indexes are not a problem. Your assertion of least help is not based on a full set of data.
    – paparazzo
    Jun 1, 2016 at 17:47
  • updated with my reindex scrip, and data where page count > 500
    – Crudler
    Jun 1, 2016 at 18:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.