Blogs

.NET to SQL to Project Server and everything in between
Welcome to Blogs Sign in | Join | Help
in Search
Home Blogs

Lara's Blog

Microsoft SQL Server

Smart Index Defrag/Reindex for a Consolidated SQL Server 2005 Environment

(Update:  An ONLINE version of this procedure is now available) 

I am sure we all have our own version of a "smart" reindex script for SQL Server 2000.  This is a script that assesses the current level of fragmentation, and only executes a reindex or indexdefrag if the fragmentation falls below a certain threshold.  In the 2000 world I have always preferred using "smart" index maintenance scripts for several reasons, including:

  1. Index defragmentation is a logged activity.  By running a dbcc dbreindex on all indexes in your larger tables you can artificially inflate your transaction log. 
  2. Address your index fragmentation can lock pages or tables (depending on how you decide to defrag your indexes).  If your index is not heavily fragmented, you are needlessly locking resources and decreasing concurrency.
  3. Performing index maintenance on all indexes in a database can take much longer than necessary.  With shrinking maintenance windows, this typically is not acceptable.

Well, SQL Server 2005 offers options to rebuild indexes without locking resources, or rebuild only a partition (although we are talking Enterprise features here).  These are certainly improvements in index maintenance.  Even so, we all need to continue to use "smart" index maintenance scripts.  We should only address index fragmentation where it is necessary and not arbitrarily take the "easy" way out by running some sp_msforeachtable statement with a dbcc dbreindex parameter. 

Attached is one that I have developed which is quite handy in a consolidated environment.  In such an environment, one would prefer to have a single copy of a script and simply pass in a database name as a parameter to rebuild or reorganize indexes that are deemed "fragmented".  I envision this type of environment would have a database that is designed for DBA scripts; I sometimes see people name these DBAStuff, DBAScripts, DBA, or the like. 

The script accepts three parameters.  The first parameter, @maxfrag, specifies the level of minimum level of fragmentation before the index must be rebuilt/reorganized.  The parameter @maxdensity specifies the minimum average page fullness before the index must be rebuilt/reorganized.  The final parameter @databasename allows you to specify which database you want to assess.

The beauty of this script is in the use of the new dynamic management view sys.dm_db_index_physical_stats (one of a few reasons that this script will not work in SQL Server 2000 or on a SQL Server 2005 database which has the compatibility mode set to 80).  This dmv allows you to pass in a database id as a parameter and returns a slew of useful index information in a table format.

I hope that the attached gives you something from which to base your own scripts.  Let me know your opinions, and if you find this practice useful and necessary. 

Published Wednesday, August 16, 2006 10:15 PM by Lara Rubbelke
Filed under: ,

Attachment(s): RebuildIndex_Smart_Update.zip

Comments

 

Bart said:

Nice script, I wish it worked with SQL 2000.  I am looking for a script that works like this for SQL 2000.  If you know of one, please let me know.
Thanks,
Bart
SQL Newbie
September 21, 2006 9:20 AM
 

Lara Rubbelke said:

Hi Bart,  I have added a SQL Server 2000 version as well.  Have fun reindexing!
September 28, 2006 8:11 PM
 

Barry said:

Great script. thanks.
October 13, 2006 5:38 AM
 

Dimitri said:

I try to use the script for SQL Server 2005, but when i call the stored procedure i get

the error message below

Msg 207, Level 16, State 1, Line 7

Invalid column name 'tableid'.

Msg 207, Level 16, State 1, Line 7

Invalid column name 'tableid'.

Msg 207, Level 16, State 1, Line 7

Invalid column name 'indexid'.

Best regards

November 27, 2006 7:34 AM
 

Lara Rubbelke said:

Hi Dimitri,

I am not sure if your issue relates to tables which are named using reserved keywords - one of my customers had this issue when using the script.  I posted the updated version of the script which adds brackets around the database, schema, table name, and index name.  This cleared up some known issues at my customer, and perhaps will clear up your issue.  If not, please respond to lrubbelke at digineer dot com.  When we clear up your issue I will post to the blog our outcome.

Thanks again!

November 27, 2006 9:47 AM
 

Dimitri said:

Hi ...

I've fixed my issue ! .. In fact my instance was in case sensitive mode ... !

I change all the script to lower case, and it's work fine now !

Sorry ;)

Dimitri

November 30, 2006 9:34 AM
 

Ken said:

I run this script on 2005 and get this:

Msg 102, Level 15, State 1, Procedure ap_RebuildIndexes, Line 45

Incorrect syntax near '('.

December 18, 2006 6:05 PM
 

Lara Rubbelke said:

Hi Ken,

A couple of suggestions:  

You indicate that you try to run this on a 2005 database.  You cannot create this procedure in a 2005 database with the compatability mode set to 2000 (although you can pass this database name as a parameter and the procedure will execute as expected).  If this is not your production database, open the database properties and select options.  If the compatability mode is set to 2000, change it to 2005.

If this is a production database, please do not change this setting without regression testing the change.  You can create a new empty 2005 database just for this procedure, if necessary.  

HTH!

December 20, 2006 12:36 AM
 

Nick Naz said:

Lara,

Where can I find a SQL2000 version of the code for re-indexing?  Can you please send me the link?

NN

May 8, 2007 6:08 PM
 

Lara Rubbelke said:

Hi Nick,

Unfortunately, I did not receive your email address in your request.  The 2000 version is included in the zip file which you can download from this blog entry.  If you still have problems accessing the zip file, please feel free to email me at lrubbelke at digineer dot com.  Thanks!

May 8, 2007 7:53 PM
 

Keir said:

I am running this on a 2000 database and im getting this error:

Msg 208, Level 16, State 6, Procedure AP_Maintenance_RebuildIndexes_2000, Line 164

Invalid object name 'AP_Maintenance_RebuildIndexes_2000'.

I am using Microsoft SQL Server Management Studio Express to run it. Let me know what im doing wrong. Since I am a complete Novice.

July 15, 2007 5:21 PM
 

Lara Rubbelke said:

Hi Keir,

You will need to make sure that you are in the correct database where you created the procedure.  You can use the selection box from the toolbar, or use the following text prior to the exec statement.

USE InsertDatabaseNameHere

GO

EXEC AP_Maintenance_RebuildIndexes_2000...

Hope this helps!

July 15, 2007 8:57 PM
 

Keir said:

Hey Lara,

As I said before, I'm a novice when it comes to SQL where would I find the selection box on the toolbar?  I have like 5 Databases that im trying to defrag, and Im am getting very confused understanding SQL. Any hints or tips would be great. and THANKS!! in advance for all your help.

Keir

July 16, 2007 9:04 AM
 

Lara Rubbelke said:

Hi Keir,

Well, there are a few ways to execute this process.  It sounds like you are using SQL Server Express, is this true?  If so, you are a bit limited for automating the process.  Here is how to manually execute the process:

1.  In Management Studio, open a new query.  

2.  Let’s assume, for example, that the procedure is in a database named SysAdmin.  If you want to execute the procedure against your AdventureWorks database, type the following into the query window:  

USE SysAdmin

GO

exec AP_Maintenance_RebuildIndexes_2000 10, 90, 'AdventureWorks'

3.  Press F5 or click the Execute button on the toolbar.

This will address the fragmentation in AdventureWorks.  You will change the last parameter in the stored procedure for each database you want to defragment.

If you need more help, please feel free to email me at lara at rubbelke dot com.  Good luck!

July 16, 2007 9:14 PM
 

Anders said:

Hi

If i want to use online mode, is this right?

...

SELECT @command = @command + ' REBUILD WITH (ONLINE = ON)';

...

July 30, 2007 3:22 AM
 

Lara Rubbelke said:

Hi Anders,

This is a very good question - and very timely!  I am about to post an update to this process which will provide an option to run the process using ONLINE.  There are some caveats which you need to consider when running the process using ONLINE.  Watch for updates - probably later today.

July 30, 2007 8:28 AM
 

Lara's Blog said:

One of the many reasons I why I love consulting is the constant evolution and challenge. You may have

July 30, 2007 10:36 AM
 

Lara Rubbelke said:

One of the many reasons I why I love consulting is the constant evolution and challenge. You may have

July 30, 2007 10:42 AM
 

Jennifer McVey said:

Can you clarify ONLINE vs OFFLINE?  And, is there a way to script at the beginning of this SQL statement to put the database offline.  Learning...documentation is either way overkill or not enough.  Thank you for your time & knowledge.

August 14, 2007 12:46 PM
 

Paul said:

Will this work on an MS SQL 2005 server that is not using partitions?

September 28, 2007 7:04 AM
 

Luis Cavazos said:

Hi.

Could you please provide the link to the SQL 2000 reindex online script that you developed?

I can't seem to locate it.

Thanks!

October 7, 2007 8:38 PM
 

Lara Rubbelke said:

Hi Luis,

You can find the zip on the blog in the section Attachments.  This section is right before the comments begin.

October 7, 2007 9:19 PM
 

Bart said:

exec ap_RebuildIndexes 15,75,'CRONUS50'

results in the error :

Msg 208, Level 16, State 1, Line 1

Invalid object name 'CRONUS50.sys.Partitions'.

Can you tell me what's the problem ? I don't use partitioning and have just started to explore  SQL Server 2005 (I use the evaluation edition)

October 11, 2007 1:31 PM
 

Lara Rubbelke said:

Is your database named CRONUS50?  The third parameter for the procedure is for a database, not server name.

If this isn't the problem, check that the database is in 90 compatibility.  Good luck!

October 11, 2007 2:08 PM
 

Bart said:

Hi,

My Databasename is indeed CRONUS50 and the compatibility level is set to 90.

Is there something else i can check ?

Bart

October 14, 2007 3:47 PM
 

Ken said:

This is an excellent script, especially for one new to DBA.  I am using SQL Server 2000 and would like to take the output and place it into a text file.  How would I accomplish that?

Thanks,

Ken

November 16, 2007 3:00 PM
 

darenhan said:

Invalid object name 'AP_Maintenance_RebuildIndexes_2000'.

this error can be solved by

changing

ALTER PROCEDURE AP_Maintenance_RebuildIndexes_2000

to

CREATE PROCEDURE AP_Maintenance_RebuildIndexes_2000

December 11, 2007 2:43 PM
 

Rod Fage said:

I have found the solution to the 2000 compatibility issue. The error message is as follows:

Msg 102, Level 15, State 1, Procedure ap_RebuildIndexes, Line 69

Incorrect syntax near '('.

The solution is to remove the inline db_id(@databasename) reference from "FROM sys.dm_db_index_physical_stats(db_id(@databasename), NULL, NULL, NULL, 'SAMPLED') AS fi "

Just declare an integer var and set it to the db_id.  Below is my recommended update:

...

DECLARE @db_id INT

SET @db_id = db_id(@databasename)

INSERT INTO #work_to_do(

IndexID, Tableid,  IndexType, PartitionNumber, CurrentDensity, CurrentFragmentation

)

SELECT

fi.index_id

, fi.object_id

, fi.index_type_desc AS IndexType

, cast(fi.partition_number as varchar(10)) AS PartitionNumber

, fi.avg_page_space_used_in_percent AS CurrentDensity

, fi.avg_fragmentation_in_percent AS CurrentFragmentation

FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, 'SAMPLED') AS fi

WHERE (fi.avg_fragmentation_in_percent >= @maxfrag

OR fi.avg_page_space_used_in_percent < @maxdensity)

AND page_count> 8

AND fi.index_id > 0

...

Hope this helps someone.

ROD>

December 11, 2007 4:38 PM
 

aj said:

Do you need to update statistics after running this script?

There is a query that is currently being executed that sometimes uses a query plan that runs for 20 minutes instead of 1 second.  This appears to be due to the # of estimated rows not being correct, or perhaps due to too many non-clustered indexes on the table.

Updating statistics on the clustered index appears to solve the issue until the next load.

Is it recommended to update statistics after this job?  Are there any other settings available to tweak this? (Fillfactor, etc?)

January 11, 2008 1:08 PM
 

Ola Hallengren said:

I would like to share some code for optimizing indexes dynamically that works a little differently than Lara's. It is available on http://ola.hallengren.com. You'll need IndexOptimize.sql, CommandExecute.sql, DatabaseSelect.sql and Documentation.html.

Ola Hallengren

http://ola.hallengren.com

January 13, 2008 9:43 AM
 

Letteer Lines said:

Smart Index Defrag/Reindex

March 19, 2008 7:51 AM
 

Technical Musings said:

Problem: SQL Server 2005 queries that used to be fast are now rather slow. You've already tuned the query with good indexes and optimized structures. You've checked the running jobs with Activity Monitor and don't see anything that should be...

May 23, 2008 10:10 PM
New Comments to this post are disabled

This Blog

Syndication

News
All content is copyrighted by the blog owner.
Powered by Community Server, by Telligent Systems