Fragmentation still remain same after Index Rebuilding...

Question

Post reply

Fragmentation still remain same after Index Rebuilding...

MSQLDBA

SSCrazy

Points: 2783
More actions
February 11, 2012 at 11:58 am

#268204

Hi Friends,
I am facing a problem while Rebuilding indexes on one of my database,
i use the follwoing script to rebuild all indexes of my database
USE [dbname]
Go
EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"
GO
Sp_updatestats
GO
My database infromation;
Database size = 6GB
Data Free space = 16%
Log Free Space = 94%
Auto growth = Restricted
Cluster Indexes = 30 with >50% avg_fragmentation
Heap tables = 20 with >75% avg_fragmentation
after successfull completion of the above script avg_fragmentation of cluster indexes remain same.
Please help me out.
Thanks in advance.
________________________________________
M.I.
[font="Times New Roman"]
Learning is a path with no destination...
[/font]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

How big are the indexes? How many pages?

Your rebuild won't affect heaps, that's why they don't change.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MSQLDBA SSCrazy Points: 2783 More actions · Answer 2

GilaMonster (2/11/2012)
How big are the indexes? How many pages?
Your rebuild won't affect heaps, that's why they don't change.

The Index size is >=502544KB, and

Pages are >=277 on each cluster and non-cluster index.

________________________________________
M.I.
[font="Times New Roman"]

Learning is a path with no destination...

[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Too small to worry about.

http://sqlskills.com/BLOGS/PAUL/post/Where-do-the-Books-Online-index-fragmentation-thresholds-come-from.aspx [/url]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

MSQLDBA SSCrazy Points: 2783 More actions · Answer 4

Hi Gila,

But on server performance problem, how i can resolve that?

________________________________________
M.I.
[font="Times New Roman"]

Learning is a path with no destination...

[/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

That's a whole nother problem, and the first thing you need to do is identify the cause, not guess the cause.

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/

http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-2/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

MSQLDBA (2/12/2012)
Hi Gila,
But on server performance problem, how i can resolve that?

Get a professional to come in and give your system a performance review. Find problems, fix them, and MENTOR you on how to do the same. Win-Win-Win

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

leokalas Valued Member Points: 68 More actions · Answer 7

Try and increase your free space to be more than 20% and try again.

In regards to fragmentation also try and run CONTIG.

http://technet.microsoft.com/en-us/sysinternals/bb897428

Contig is a single-file defragmenter that attempts to make files contiguous on disk. Its perfect for quickly optimizing files that are continuously becoming fragmented, or that you want to ensure are in as few fragments as possible

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Index fragmentation != file fragmentation. If running a file system defrag tool on SQL databases, SQL Server must be stopped for the duration. While there are tools that claim they can defrag open files, there have been cases where those tools have caused problems running on an online SQL database.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

leokalas Valued Member Points: 68 More actions · Answer 9

Hi,

Not sure if you have a requirement for the system to be online or if there an opportunity in the evening to run maintenance...

RE- EXEC sp_MSforeachtable @command1="print '?'", @command2="ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)"

As you are trying to do a rebuild offline...then the suggested CONTIG will only work properly with database Offline and even detached.

For Online re-indexing see reference below for further options.

http://www.microsoft.com/technet/prodtechnol/sql/2005/onlineindex.mspx

Summary: Introduced in SQL Server 2005 Enterprise Edition, the online index feature provides a powerful way to perform maintenance operations such as rebuilding or creating indexes in a production system without sacrificing DML concurrency. This paper provides a detailed discussion of the index process and provides guidelines and best practices for implementing this feature in a production environment.

Scott Meddows SSC Veteran Points: 285 More actions · Answer 10

Is the table a heap or does it have a clustered index?