Rebuilt Index

Question

Rebuilt Index

haiao2000

Hall of Fame

Points: 3734
More actions
March 20, 2013 at 12:40 pm

#272373

Dear experts,
Our application is designed to support multiple SQL server version from 2000-current.
One of the thing the application does is it imports data to db on schedule basis, depends on each client, some imports may take couple days to complete (yes no mistake)
During the course of an import indexes may be very fragmented and import performance degrades. to overcome this issue we have index status check periodicly to dermine if indexes need to be rebuilt if so it rebuilds fragmented indexes automatically.
The way we handle this is we first check sys.dm_db_index_physical_stats and determine which indexes needed to be reindexed, and use the following logic to defrag them:
If SQL version=Enterprise
ALTER INDEX [indexX] ON [tableY] REBUILD WITH (FILLFACTOR = 80, ONLINE = ON);
Else
DBCC INDEXDEFRAG (0,'indexX','tableY') WITH NO_INFOMSGS;
End
My question to you is: Do you think what we have is sufficient or is there away to avoid using DBCC as my understanding DBCC is old method that may do thing less efficient?
Any suggestion to impove would be very appreciate.

Viewing 9 posts - 1 through 8 (of 8 total)

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

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

DBCC INDEXDEFRAG is deprecated, etc, etc. replacement is ALTER INDEX ... REORGANIZE

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

haiao2000 Hall of Fame Points: 3734 More actions · Answer 2

GilaMonster (3/20/2013)
DBCC INDEXDEFRAG is deprecated, etc, etc. replacement is ALTER INDEX ... REORGANIZE

Thanks Gail,

So Reorganize will work for SQL 2000 and newer?

Thanks!

SQL Guy 1 SSCoach Points: 15817 More actions · Answer 3

I suggest you to script out all indexes and drop them before data import. After it finishes, recreate from the script. It will improve you data loading performance, and it is independent from versions.

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

haiao2000 (3/20/2013)
GilaMonster (3/20/2013)
DBCC INDEXDEFRAG is deprecated, etc, etc. replacement is ALTER INDEX ... REORGANIZE
Thanks Gail,
So Reorganize will work for SQL 2000 and newer?

2005 and newer, same as your ALTER INDEX REBUILD statement and sys.dm_db_index_physical_stats.

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

haiao2000 Hall of Fame Points: 3734 More actions · Answer 5

2005 and newer, same as your ALTER INDEX REBUILD statement and sys.dm_db_index_physical_stats.

Thanks again Gail!

ScottPletcher SSC Guru Points: 100949 More actions · Answer 6

FYI, a FILLFACTOR of 80 is wasting space in many tables and perhaps even still have too little freespace in others.

You need to determine the best FILLFACTOR for each table separately, but until then, 80 is too low for a universal default.

Instead, use 95-98 for a table clustered on identity (or other ever-increasing key(s)), and try 90 for other tables. If you see too much fragementation on a given table too soon, you can lower the FILLFACTOR then.

REBUILD by default will use the existing FILLFACTOR, so after the first rebuild, don't specify the FILLFACTOR at all unless you explicitly want to change it.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

haiao2000 Hall of Fame Points: 3734 More actions · Answer 7

GilaMonster (3/20/2013)
DBCC INDEXDEFRAG is deprecated, etc, etc. replacement is ALTER INDEX ... REORGANIZE
2005 and newer, same as your ALTER INDEX REBUILD statement and sys.dm_db_index_physical_stats.

Gail

On my machine I have SQL 2008 R2, and look like ALTER INDEX ... REORGANIZE works, but on other version of SQL servers does this method work? Do I need to check for enterprise version at all?

Thanks!

haiao2000 Hall of Fame Points: 3734 More actions · Answer 8

ScottPletcher (3/20/2013)
FYI, a FILLFACTOR of 80 is wasting space in many tables and perhaps even still have too little freespace in others.
You need to determine the best FILLFACTOR for each table separately, but until then, 80 is too low for a universal default.
Instead, use 95-98 for a table clustered on identity (or other ever-increasing key(s)), and try 90 for other tables. If you see too much fragementation on a given table too soon, you can lower the FILLFACTOR then.
REBUILD by default will use the existing FILLFACTOR, so after the first rebuild, don't specify the FILLFACTOR at all unless you explicitly want to change it.

Scott,

Thanks, I will just set 90 as default value for now. It is probably an overkilled to determine/set fillfactor at table level

Thanks!