Determining which indexes are fragmented and need rebuilding?

SQL Server Administration
,
1

I am in the process of revamping my index rebuild routine. It stores the Fragmentation (into a history table) before reorganising / rebuilding an index [if it is fragmented]. I find the historical information useful, but I am using

DBCC SHOWCONTIG (MyTableObjectID) WITH ALL_INDEXES, TABLERESULTS, FAST

which doesn't give me the record size stats etc.

So two questions:

  1. Should I switch to using sys.dm_db_index_physical_stats instead of DBCC SHOWCONTIG?

  2. Should I stop using FAST (for DBCC SHOWCONTIG, or if sys.dm_db_index_physical_stats then use DETAILED instead of LIMITED) in order to get the additional information because I suspect that would also be useful to see retrospectively / historically?

Clearly I need to worry about collecting DETAILS stats on large tables, but we run this overnight and I'm not sure I care (within reason!) how long it takes. Perhaps I should set a threshold ROWCOUNT and use FAST / LIMITED on tables that have more rows than that?

2

Hmmm ... been Googling and Researching ... one thing that troubles me is lots of references to MS's recommendation:

"Microsoft recommends that you reorganize your index if the avg_fragmentation_in_percent value is less than or equal to 30% and rebuild the index if the value is greater than 30%."

Why is that?

My view is that I want to Reorganise large indexes, and Rebuild small ones. I think this keeps the TLog activity to a minimum and avoids extending the MDF file (although there must come a point where a Rebuild of a large index is better than a Reorganize)

There must also come a point where Reorganise cannot tidy up empty pages or something similar, and then a Rebuild is necessary to reclaim those pages - but if empty pages etc. are not hurting performance I would delay that Rebuild until scheduled downtime [on a 24/7/365 database]

Part of my thinking is that we optimise indexes every night - Why would I wait until the weekend if the indexes are fragmenting and reducing performance, and thus for the rest of the week users are going to be wasting time?? So, in my case, perhaps really bad fragmentation will not happen because the index REORGANISE's happen every night [if needed]

So now I am trying to work out what "thresholds" to set. I've been happy with the thresholds I was using, but that was for my APP and I'm now trying to set up index maintenance on a 3rd party app and even after REORGANIZE I'm still seeing unacceptable levels of fragmentation. I need to record more information about the fragmentation of the indexes, before/after REORGANIZE, to try to see what is not happening [which REBUILD does fix]

3

what do you think about Ola Hallengren's index optimize script?

4

Ola has some great stuff. Huge complexity though, which is fine IMHO if you want to use it as-is (and it probably suits 90%?? of situations), but quite hard to decipher what it is doing it you want to use it as the basis of a derived work (I don't think it has the sort of logging that I want, for example)

5

This is most likely something you already have:

USE master; 
GO
TRUNCATE TABLE Scrub.dbo.Fragmentation; 

DECLARE @strSQL NVARCHAR(2000) 
--SET @strSQL = 'IF ''?'' IN (''master'', ''model'', ''msdb'', ''tempdb'', ''SQLdmRepository'', '
--    + '''ReportServer$DB1SQLTempDB'', ''ReportServer$DB1SQL'', ''distribution'') RETURN; '
SET @strSQL = 'IF ''?'' NOT IN (''DB1'', ''DB2'', ''DB3'') RETURN; '
    + 'USE ?; '
    + 'INSERT INTO Scrub.dbo.Fragmentation (DBName, TableName, ExecuteCode, avg_frag_percent) '
    + 'SELECT ''?'' AS DBName, OBJECT_NAME(ips.object_id) AS TableName, '
    + 'CASE WHEN ips.avg_fragmentation_in_percent < 30 '
    + 'THEN ''ALTER INDEX '' + QUOTENAME(inx.name) + '' ON '' + QUOTENAME(OBJECT_NAME(ips.object_id)) + '' REORGANIZE; --'' '
    + 'ELSE ''ALTER INDEX '' + QUOTENAME(inx.name) + '' ON '' + QUOTENAME(OBJECT_NAME(ips.object_id)) + '' REBUILD; --'' '
    + 'END AS ExecuteCode, ips.avg_fragmentation_in_percent '
    + 'FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'') ips '
    + 'INNER JOIN sys.indexes   inx ON ips.object_id = inx.object_id and ips.index_id = inx.index_id '
    + 'WHERE 1 = 1 '
    + 'AND ips.avg_fragmentation_in_percent > 5 '
    + 'AND OBJECT_SCHEMA_NAME(ips.object_id) IS NOT NULL AND page_count > 1000 AND ips.index_id > 0 '
    + 'ORDER BY DBName, TableName DESC; '; 
--print @strSQL 

EXECUTE sp_MSForEachdB @strSQL;
go

SELECT DBName, TableName, ExecuteCode, avg_frag_percent FROM Scrub.dbo.Fragmentation 
WHERE avg_frag_percent > 75
ORDER BY DBName, TableName, ExecuteCode DESC;
6

fwiw Minion Reindex is free, does everything that Ola's scripts do, hides the complexity (unless you really want to know). is dead easy to use and highly configurable.

7

Thanks. Indeed similar to what I have & have seen. I've now had the chance to run it and compare against what my code is up to etc.

I'm still not sure about this "< 30% REORGANISE" approach. In particular REORGANIZE of small tables is very ineffective and whilst I can see that small indexes don't benefit very much from maintenance I quite like to REBUILD them so they are in Apple-pie-order.

Couple of observations in case useful for you? the code will select the same index twice if it has alloc_unit_type_desc = "LOB_DATA" (as well as "IN_ROW_DATA") which may mean for you?? that it is unnecessarily reorganising / rebuilding an index twice (or give duplicate violation on insert into Scrub.dbo.Fragmentation)

Other pedantic point, I don't suppose your people are as daft as the 3rd party APP builders we have, but our DB's have "unusual" characters in their names, so important to change:

    + 'USE ?; '
to
    + 'USE [?]; '
8

I vote for Graz to add the ESP function to SQLTeam - like the elevators in "The Hitch-hikers Guide to the Galaxy". They "Arrive on the floor you are on before you know you need one" !!!

I've only had a brief read so far, but it looks very interesting. Many thanks.

Link for anyone that is interested in having a look: http://minionware.net/

9

Kristen, The reorganize is as you indicated open for debate.

As to small files, the 1000 page_count should exclude them.

Good point about the braces. I am also switching from the undocumented sp_MSForEachDB (using sys.databases).
I am constantly reading forums like this, white papers, blogs, etc. for tweaks to use.