SQL Defrag

Question

SQL Defrag

williamminor

SSCommitted

Points: 1664
More actions
January 8, 2008 at 6:30 pm

#121764

Is there a way to determine how long it will take to defrag a certain index?

Viewing 15 posts - 1 through 15 (of 17 total)

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

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 1

Well..... kinda... DBCC INDEXDEFRAG will report back once every 5 minutes with a % complete... so you could start a run and get the % complete... the math is pretty simple there...

If it completes before 5 minutes passes, your answer is "something less than 5 minutes". 😛

If it comes back with a percentage like 4%, the answer would be approximately

100.0/4*5 where the 4 is the percentage complete and 5 is "5 minutes".

You can abort the process at any time without a rollback. Any work accomplished will be retained.

I did a test on a Clustered key of a million row table with no fragmentation... took 37 seconds... probably not a good estimate for something that is fragmented. DEFRAG on non-clustered index, same table, took almost 0 seconds. Sorry I don't have a highly fragmented table to demo with 😛

Indexes with little fragmentation don't take long to run. If they are highly fragmented, they can take much longer. Estimates based only on table size will be incorrect.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

williamminor SSCommitted Points: 1664 More actions · Answer 2

williamminor

SSCommitted

Points: 1664

January 9, 2008 at 6:51 am

#766142

Thanks

-WM

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 3

Please remember that INDEXDEFRAG will be deprecated. Try using DBREINDEX or ALTER INDEX REORGANIZE.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

Short answer - no. Long answer - well ... let ... me ... think ... for ... a ... while ... ... ... no. 🙂

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

As Jeff said, you can guess, but the amount of time it takes is also dependant on the other activity on the system, especially activity on the table you are defragmenting.

williamminor SSCommitted Points: 1664 More actions · Answer 6

Thanks all

New job and everyone here wants times for everything

-WM

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Sounds like it is time for you to trot out one of my favorite lines from the movie 'The Princess Bride' - "get used to disapointment". 😀

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

er.rajeshsingh Newbie Points: 7 More actions · Answer 8

hi all ............

why do we use dfrag. and what are the tools we use in defrag ....:)

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 9

Think "Library"... it's easier to find the books if they're all in order... it's just about that simple. There are tools for defragging disks and tools for defragging database files and tools for defragging table indexes... most of them can be found in Books Online... most expensive disk systems come with their own defrag for the operating system level stuff.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 10

We defrag to remove frag. :w00t:

Fragmentation takes several forms. Basically when data is either not on disk sequentially or is out-of-order internally on the pages is fragmentation. Net result is suboptimal performance.

May I suggest reading any one (or several) SQL Server administration books or taking a course or two. Books Online is a MARVELOUS free resource, as is any number of web resources. Internet searches will return a wealth of information too.

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

Paul Randal One Orange Chip Points: 29544 More actions · Answer 11

RML51 (1/10/2008)
Please remember that INDEXDEFRAG will be deprecated. Try using DBREINDEX or ALTER INDEX REORGANIZE.

DBREINDEX is also deprecated and doesn't run the indexdefrag algorithm - it does offline index rebuilds. You should use ALTER INDEX ... REBUILD instead of that do index rebuilds.

The whitepaper at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx has descriptions of each command, as does the BOL I wrote for DBCC INDEXDEFRAG and SHOWCONTIG. The whitepaper is for 2000 but nearly all of it translates to 2005 and 2008 nicely.

Wow - there's a lot of discussion about fragmentation this week - and nice to see lots of people using my DBCC INDEXDEFRAG 🙂

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 12

Paul Randal (1/28/2008)
RML51 (1/10/2008)
Please remember that INDEXDEFRAG will be deprecated. Try using DBREINDEX or ALTER INDEX REORGANIZE.
DBREINDEX is also deprecated and doesn't run the indexdefrag algorithm - it does offline index rebuilds. You should use ALTER INDEX ... REBUILD instead of that do index rebuilds.

Paul - Can you cite an MS source stating that DBREINDEX is being deprecated? I haven't seen that. What I have seen is that if you run ALTER INDEX REORGs you still have to periodically run DBREINDEXes. We're moving toward being a 24/7 shop and I'm looking for alternatives. We have blocking issues trying to get DBREINDEX run.

Thanks

Paul Randal One Orange Chip Points: 29544 More actions · Answer 13

I am the source. I helped deprecate it when I ran the SQL Server Storage Engine dev team that wrote the new code for SQL Server 2005.

From Books Online in SQL Server 2005 for DBCC DBREINDEX:

Important:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use ALTER INDEX instead.

I hope this is convincing enough.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

RML51 SSCarpal Tunnel Points: 4410 More actions · Answer 14

Thanks, Paul. You're right. My document must be old.