Reorgnize index

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Reorgnize index

Reorgnize index

Rate Topic

Display Mode

Topic Options

Author

Message

sqlfriends

Posted Tuesday, October 30, 2012 4:55 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 5:35 PM
Points: 1,601, Visits: 2,779

When I use sql server maintenance plan, there is a task called reorganize index.

If I setup a job to reorgnize an index, for example every 6 hours, should each time be shorter than previous one?
I mean, does it only reorganize those defragmented, or everytime it wil reorganize all indexes ?

Thanks

Post #1379076

GilaMonster

Posted Tuesday, October 30, 2012 5:03 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

sqlfriends (10/30/2012)

If I setup a job to reorgnize an index, for example every 6 hours, should each time be shorter than previous one?

Maybe. If you mean reorganise, yes, it likely will be. If rebuild, no

I mean, does it only reorganize those defragmented, or everytime it wil reorganize all indexes ?

It will reorganise every single index in the DB every time it runs.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1379080

sqlfriends

Posted Tuesday, October 30, 2012 5:10 PM

SSCommitted

Group: General Forum Members
Last Login: Today @ 5:35 PM
Points: 1,601, Visits: 2,779

Thanks, I DO mean reorgnize.
But if everytime it will reorganize every single index in the db every time it runs, why it maybe each time is shorter than the other?

thanks

Post #1379086

George M Parker

Posted Tuesday, October 30, 2012 6:16 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, May 09, 2013 1:13 PM
Points: 516, Visits: 1,004

That is one of the downsides to the index maintenance plans in SQL Server. Most DBA's that I know use a more targeted approach that checks the fragmentation levels of the index and either reorganizes them or rebuilds them based on a threshold parameter. There are many script examples available on the internet, we run a customized version of the one that Andew Kelley published in SQL Server magazine some time ago.

Post #1379109

GilaMonster

Posted Wednesday, October 31, 2012 1:13 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 37,687, Visits: 29,946

Because reorganise has less to do on a index that's a little fragmented and more to do on an index that's very fragmented.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1379170

IgorMi

Posted Wednesday, October 31, 2012 10:29 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 4:19 PM
Points: 1,878, Visits: 1,446

sqlfriends (10/30/2012)

No matter what your solution for reorganizing indexes is, sometimes the reorganizing will be faster, sometimes slower. It depends on the fragmentation.

Regards
IgorMi

Post #1379423

« Prev Topic | Next Topic »

Permissions