Optimization time takes too long.

Question

Optimization time takes too long.

Leo-696352

SSCrazy

Points: 2708
More actions
November 5, 2008 at 3:08 am

#124190

Hi,
I have setup the Optimization in DB Maintenance Plan to run 3 time in a week. But it took 4 hours to rebuild the index for 65GB of one database. I have attached the result below -
Database EMPLOYEE: Index Rebuild (leaving 100%% free space)...
Rebuilding indexes for table 'AA'
Rebuilding indexes for table 'BB'
Rebuilding indexes for table 'CC'
Rebuilding indexes for table 'DD'
Rebuilding indexes for table 'EE'
Rebuilding indexes for table 'FF'
Rebuilding indexes for table 'GG'
Rebuilding indexes for table 'HH'
** Execution Time: 3 hrs, 59 mins, 49 secs **
I think it shouldn't take that long. Anybody know why taking too long for this process.
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

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

krayknot SSCertifiable Points: 7998 More actions · Answer 1

Leo (11/5/2008)
Hi,
I have setup the Optimization in DB Maintenance Plan to run 3 time in a week. But it took 4 hours to rebuild the index for 65GB of one database. I have attached the result below -
Database EMPLOYEE: Index Rebuild (leaving 100%% free space)...
Rebuilding indexes for table 'AA'
Rebuilding indexes for table 'BB'
Rebuilding indexes for table 'CC'
Rebuilding indexes for table 'DD'
Rebuilding indexes for table 'EE'
Rebuilding indexes for table 'FF'
Rebuilding indexes for table 'GG'
Rebuilding indexes for table 'HH'
** Execution Time: 3 hrs, 59 mins, 49 secs **
I think it shouldn't take that long. Anybody know why taking too long for this process.
Thanks.

are you running the scripts in off hours or in on hours

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Leo-696352 SSCrazy Points: 2708 More actions · Answer 2

Leo-696352

SSCrazy

Points: 2708

November 5, 2008 at 3:36 am

#894114

Off hours. Actually 23:30.

krayknot SSCertifiable Points: 7998 More actions · Answer 3

Leo (11/5/2008)
Off hours. Actually 23:30.

What the execution plan says??

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Leo-696352 SSCrazy Points: 2708 More actions · Answer 4

Leo-696352

SSCrazy

Points: 2708

November 5, 2008 at 3:48 am

#894123

Hi,

Sorry, What do you mean by that?

krayknot SSCertifiable Points: 7998 More actions · Answer 5

Leo (11/5/2008)
Hi,
Sorry, What do you mean by that?

in the Query menu select the option "Display estimated execution plan" to know the query execution details and also check the client Statistics

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Leo-696352 SSCrazy Points: 2708 More actions · Answer 6

Hi,

I can't becasue it run by job and that is part of DB Maintenance plan. I am not running under quary analyser.

Any other idea?

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Leo (11/5/2008)
I think it shouldn't take that long.

That's about the amount of time I'd expect it to take. If you want to save a bit of time, lookup DBCC SHOWCONTIG in Books Online... there, you will find a script that will reindex indexes only if they need it.

--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 8

I agree with Jeff, 4 hrs to redo indexing on 65GB isn't unreasonable unless you have pretty good hardware.

I would check out IO stalls (fn_virtualfilestats). Also, how much free space do you have in the database?

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

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 9

One other thing: did you verify that there are no other batch jobs, maintenance jobs (such as backups or checkdbs), etc running between 2330 and 0330?

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

Leo-696352 SSCrazy Points: 2708 More actions · Answer 10

No. Nothing is running.

I will try that again tonight and see how it goes.

Thanks

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

It is usually not necessary to re-index so often. Once per week or less is probably plenty.

Leo-696352 SSCrazy Points: 2708 More actions · Answer 12

Er.....

I am not fully understand what you said, you mean not to reindex or reorganise the index even fragmentation level is above 20%, are you sure?

According from the book online - need indexrebuild if fragmentation level is < 30%,

dbreindex if fragmentation level is > 30%.

Leo