Strategizing your Index Maintenance in SQL Server

Index maintenance is part of the DBA’s life. Surely you have a maintenance task for this somewhere running daily or weekly on your server. For the junior DBA, the rebuilding of indexes task will include a scheduled job that will reindex all indexes in the database. Imagine this task running on all your indexes and if your tables have a clustered index in them, which is most likely, this job is actually rewriting your entire database. This is a waste of precious resources on your server, unnecessary read/write activity and never mind the transaction log blowing out of proportions.

There is a more effective strategy for creating a maintenance plan for rebuilding fragmented indexes. The more effective approach would be to reindex only where needed and just leave the rest untouched. If it doesn’t need fixing, then don’t touch it at all.

First step would be to list all indexes in your database, like having an inventory of all indexes. You can query the DMV sys.dm_db_index_physical_stats for this. You may run a similar query below.

SELECT OBJECT_NAME(i.OBJECT_ID) AS TableName,
i.name AS IndexName,
iss.index_type_descASIndexType,
iss.avg_fragmentation_in_percent AS FragmentationPercent
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) iss
INNER JOIN sys.indexes i ON i.object_id = iss.object_id AND i.index_id = iss.index_id
ORDER BY iss.avg_fragmentation_in_percent DESC

Now that you have a gauge of how fragmented your indexes are, basing from the results of the query above, you can use your better judgement on which indexes to REBUILD or REORGANIZE and rewrite your maintenance task where applicable. To better understand the query results, if the avg_fragmentation_in_percent is less than 5%, then don’t touch it all. If the value is between 5% and 30% then run reorganize with the option ALTER INDEX REORGANIZE. If the avg_fragmentation_in_percent is above 30% then by all means run rebuild with the optionALTER INDEX REBUILD WITH (ONLINE=ON).

The (ONLINE=ON) option allows users to still use the index while the REBUILD task is ongoing. This feature is limited to enterprise edition of SQL Server 2005 and later only.

As a general rule, check your index fragmentation percentage daily or weekly then run your REBUILD or REORGANIZE task as needed only. This way you do not trigger unnecessary activity in your server and waste resources.

For more information on the guidelines, visit link http://support.microsoft.com/kb/2755960

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman

Just another WordPress.com site

Paul Turley's SQL Server BI Blog

from the world of Microsoft SQL Server BI and reporting

TroubleshootingSQL

Explaining the bits and bytes of SQL Server

Coding Tips

We will improve your knowledge

SQL Studies

My SQL Studies

It's Hemlock Time

We don't make sense. It's innate.

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Microsoft BI consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Stevespevack's Blog

IT Recruitment

SQL Problems and Solutions

Sharing the knowledge & experience of SQL, DBA & BI

Glenn Berry's SQL Server Performance

Semi-random musings about SQL Server performance

Algonquin Studios Blog

Ideas and opinions on software development, web design, customer service, sales and marketing...and food

SutoCom Solutions

Success & Satisfaction with the Cloud

Terli Srinivasa rao Blog

Sql server and Tableau practices

AccessAdp.com

SQL Server Architect | MCTIP: Database Administrator | Microsoft Office | Hadoop

Follow

Get every new post delivered to your Inbox.

Join 46 other followers

%d bloggers like this: