November 5, 2008 at 3:08 am
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.
November 5, 2008 at 3:31 am
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
November 5, 2008 at 3:36 am
Off hours. Actually 23:30.
November 5, 2008 at 3:46 am
Leo (11/5/2008)
Off hours. Actually 23:30.
What the execution plan says??
kshitij kumar
kshitij@krayknot.com
www.krayknot.com
November 5, 2008 at 3:48 am
Hi,
Sorry, What do you mean by that?
November 5, 2008 at 3:52 am
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
November 5, 2008 at 5:11 am
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?
November 5, 2008 at 10:13 pm
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
Change is inevitable... Change for the better is not.
November 6, 2008 at 8:25 am
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
November 6, 2008 at 8:26 am
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
November 6, 2008 at 8:55 am
No. Nothing is running.
I will try that again tonight and see how it goes.
Thanks
November 6, 2008 at 9:09 am
It is usually not necessary to re-index so often. Once per week or less is probably plenty.
November 6, 2008 at 1:16 pm
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply