Tara Kizer Blog

Tara Kizer

Defragmenting Indexes in SQL Server 2005

by Tara Kizer in  

EDIT: There is a new version of this stored procedure.

I've made a couple of changes to my defragment indexes stored procedure, isp_ALTER_INDEX, based upon feedback I've received from my blog readers.  For those unfamiliar with this object, it defragments indexes in SQL Server 2005 using the ALTER INDEX command.  Check the comment header block for its documentation.

Here are the changes:

  1. Added support for database and object names with special characters.
  2. Added support for indexes with the ALLOW_PAGE_LOCKS option enabled.  Online reindexing is not available for indexes with this option enabled.
  3. Fixed the code so that it handles "gaps" in the temp table.  In previous versions, you would hit a gap in the identity values of the temp table if rows had been deleted from it due to @minRowCount.  This issue caused all indexes after the gap to not get defragmented.

In the next version, I will add a logging feature so that you can see what indexes were defragmented, see how bad the fragmentation was, and perhaps do some trending on the data.

Let me know if you'd like me to add any other features or if you find a bug.

You can download the stored procedure here.

Legacy Comments
Saggi Neumann
2008-09-06		 re: Defragmenting Indexes in SQL Server 2005
Hey Tara,

Why not use QUOTENAME(@dbName) instead of '[' + @dbName + ']' (and the rest of the identifiers)?

In my procedures I usually use page_count in order to ignore small tables (which is also returned by "LIMITED" physical stats).

Some other features I've implemented or wanted to implement:
1. Log table - which indexes have been defragmented, level of fragmentation, start+end time
2. Maintenance window timeframe parameter - stop defargmenting when reaching or getting close to the end of maintenance window.

Cheers,
Saggi
Tony Fountain
2008-09-08		 re: Defragmenting Indexes in SQL Server 2005
Hi Tara, I see you released this update. I'm going to borrow your code if you don't mind (why else would you post it!) :). Anyway, I am going to make a few changes to it though, mainly cosmetic. We have a few development standards I need to conform this to (i.e. naming conventions, error handling, etc). Everyone has them but noone loves them :).

Also, aside from that, I saw Saggi's suggestion about the maintenance window timeframe parameter and thought that would be rather useful. But to compliment that, is there a better way to process the indexes? For example, largest tables with worst fragmentation first? Is there a way to really identify what index would be the most beneficial to defragment first?

Aside from that, if I make any changes I think are worthwhile, would you want me to send you a copy?
JJEugene
2008-09-12		 re: Defragmenting Indexes in SQL Server 2005
Thanks for posting this code. I haven't had a chance to study it, but it is generous of you to share and to update it. I think I will find it very useful.
Rolf Spindler
2008-09-12		 re: Defragmenting Indexes in SQL Server 2005
Hello Tara,

what do you think about a paramter "FILLFACTOR" if you are using the REBUILD option.

ALTER INDEX ALL ON xxx
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON


or is it better to use the default values?

Best wishes
Rolf
Tara
2008-09-12		 re: Defragmenting Indexes in SQL Server 2005
Rolf, it is better not to include that option so that we don't make any changes. If the option is not there, SQL Server will use the current setting. A fillfactor of 80 might be on some tables and 100 on others. This is the type of scenario where we don't want to change anything as those were set for a reason.
mike123
2008-09-28		 re: Defragmenting Indexes in SQL Server 2005
great thank you very much, very helpful .... I really look forward to the next version..... also, is this code compatibile with sql2008 ? [should I try and see ? :) ]
Alex
2008-10-03		 re: Defragmenting Indexes in SQL Server 2005
Hi tara. I've got a syntax error running your procedure, on this line "FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)", the error is 170, incorrect syntax near '('.

win2003 server, sql 2000 server.
Tara
2008-10-03		 re: Defragmenting Indexes in SQL Server 2005
Alex, the stored procedure is for SQL Server 2005 as ALTER INDEX isn't available in 2000.
Alex
2008-10-04		 re: Defragmenting Indexes in SQL Server 2005
thanx tara, i found what i was looking in your blog for 2000 sql. but do you have a script to delete old files (like backup) to a certain days? thanx
Tony
2008-10-05		 re: Defragmenting Indexes in SQL Server 2005
Hi Tara, I have implemented your defrag indexes storedproc. It errors out on database names that have spaces in them.
I'm getting the error "Incorrect syntax near '-' ". I tried passing the database name with [] wrapped around the name but it still errors out with "cannot find object [My Database].dbo.table because it does not exist or you do not have permissions".
What do I have to modify so your storedproc can hande databases with spaces in the name. I told management not to name databases with spaces but they wouldn't listen. ahhh, so annoying. Thanks, Tony.
Tara
2008-10-06		 re: Defragmenting Indexes in SQL Server 2005
Tony, what is the database name so that I can test on my side? I did test my code against databases with spaces in the name, so I'm not sure why it is failing on your end. But I'll certainly test it.

Also, what is the database compatibility level of the database where you created my object?
Tara
2008-10-06		 re: Defragmenting Indexes in SQL Server 2005
Alex, see my code in isp_Backup: http://weblogs.sqlteam.com/tarad/archive/2008/08/19/Backup-SQL-Server-Databases.aspx

It does what you want plus a lot more.
Tony
2008-10-28		 re: Defragmenting Indexes in SQL Server 2005
Hi Tara, the database name is "HFF GMDB" without the quotes. The database compat level is 90.
This is the error message:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '.'.
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '.'.

Sorry it's so long. Thanks
Tony
Fedor
2008-11-13		 re: Defragmenting Indexes in SQL Server 2005
I have found a bug in your script. @lobData is initialized by 1 inside 'while' loop if an expression inside 'if' statement is true and 'select' returns a row. This value remains in @lobData for all next iteration even if expression inside 'if' statement is false. This causes ignoring online build of index, although this can be done.


WHILE @@ROWCOUNT <> 0
BEGIN

...

IF @indexType = 'CLUSTERED INDEX'
BEGIN
-- CHARACTER_MAXIMUM_LENGTH column will equal -1 for max size or xml
SET @sql = '
SELECT @lobData = 1

...