Ramblings of a DBA

Tara Kizer
posts - 165, comments - 832, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work
<< How to run a process using different credentials | Home | File Growth Settings for SQL Server Databases >>

Defragmenting Indexes in SQL Server 2005

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.

Print | posted on Wednesday, September 03, 2008 9:56 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# 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
9/6/2008 10:44 PM | Saggi Neumann
Gravatar

# 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?
9/8/2008 12:38 PM | Tony Fountain
Gravatar

# 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.
9/12/2008 8:11 AM | JJEugene
Gravatar

# 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
9/12/2008 12:19 PM | Rolf Spindler
Gravatar

# 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.
9/12/2008 12:22 PM | Tara
Gravatar

# 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 ? :) ]

9/28/2008 9:23 AM | mike123
Gravatar

# 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.
10/3/2008 5:46 AM | Alex
Gravatar

# re: Defragmenting Indexes in SQL Server 2005

Alex, the stored procedure is for SQL Server 2005 as ALTER INDEX isn't available in 2000.
10/3/2008 9:28 AM | Tara
Gravatar

# 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
10/4/2008 8:14 AM | Alex
Gravatar

# 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.
10/5/2008 3:32 PM | Tony
Gravatar

# 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?
10/6/2008 2:27 PM | Tara
Gravatar

# 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.
10/6/2008 2:28 PM | Tara
Gravatar

# 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
10/28/2008 3:44 PM | Tony
Gravatar

# 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

...
11/13/2008 7:49 AM | Fedor
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET

Copyright © Tara Kizer