Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rebuild or Reorganise Index? Expand / Collapse
Author
Message
Posted Thursday, April 10, 2014 5:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:33 AM
Points: 40, Visits: 151
Hi Guys,

Its me again..!!

I have a fact table, below is some more information on it,

# Records Reserved (KB) Data (KB) Indexes (KB) Unused (KB)
579,126,119 389,356,128 247,399,656 141,672,088 284,384

For simplicity please assume every metric is above 100GB. except the unused KB.

There are 8 indexes on this table, i ran the 'index physical statistic report' and it recommends me to perform reorganize on 4 of the indexes and rebuild on the other 4. How to go about maintaining index ifor such tables?

We have nearly 1000 Business objects (reporting tool) reports reading this table


Thanks for all your help.
Post #1560376
Posted Thursday, April 10, 2014 5:58 AM This worked for the OP Answer marked as solution
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
As per your requirement and setup you can perform ALTER INDEX on object level for specific index or for all indexes. Also you can perform REBUILD or REORGANIZE.

HTH


---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
Post #1560379
Posted Thursday, April 10, 2014 6:05 AM This worked for the OP Answer marked as solution


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,752, Visits: 28,150
I'd suggest looking into using Michelle Ufford's scripts for maintaining your indexes. That way you don't have to write something yourself.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1560380
Posted Thursday, April 10, 2014 6:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:33 AM
Points: 40, Visits: 151
hi grant, could you share the link please?
Post #1560403
Posted Thursday, April 10, 2014 6:58 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:50 PM
Points: 13,752, Visits: 28,150
Try this one.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1560405
Posted Thursday, April 10, 2014 7:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 5:33 AM
Points: 40, Visits: 151
thank you grant , my bad i was missing the 'defrag' keyword in my search ..

below is the link i used

http://lmgtfy.com/?q=Michelle+Ufford%27s+scripts+for+maintaining+your+indexes
Post #1560407
Posted Thursday, April 10, 2014 7:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 12:57 PM
Points: 246, Visits: 664
I would also consider taking a look at Ola Hallegren's scripts. He has lots of documentation. The "recommended" maintenance is if fragmentatation is over 30%, rebuild, if it is between 5% and 30 %, reorg, and if it is less than 5% (or the table is very small) do nothing. Those numbers may or may not work for your environment.

http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Post #1560437
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse