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 12»»

fix fragmentation sql server Expand / Collapse
Author
Message
Posted Wednesday, September 8, 2010 9:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 7:43 PM
Points: 3, Visits: 45
Hi,

I just shrunk the database using DBCC SHRINK statement. what would be the best approach to fix the fragmentation after shrinking the database?
Post #982768
Posted Wednesday, September 8, 2010 11:17 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 2:09 PM
Points: 40,193, Visits: 36,597
Rebuild all indexes.


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 #982808
Posted Friday, September 10, 2010 2:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 3:42 AM
Points: 260, Visits: 368
Rebuild all indexes via alter <<index name>> on <<table name>> rebuild

then run exec sp_updatestats
Post #983609
Posted Wednesday, September 22, 2010 8:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 1:41 AM
Points: 170, Visits: 1,400
My understanding is that rebuilding all indexes will also update their statistics with full scan - so sp_updatestats will just replace the statistics with sampled ones.

Cheers,

JohnA

MCM: SQL2008
Post #991202
Posted Thursday, September 23, 2010 11:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 7:56 AM
Points: 1,392, Visits: 735
John__A is correct. Don't run the update stats sp after rebuilding all the indexes because you will be replacing stats that are based on a full scan with ones that are based on a sampled scan, possibly resulting in less accurate statistics.
Post #992220
Posted Friday, September 24, 2010 2:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 28, 2013 7:43 PM
Points: 3, Visits: 45
do i need to rebuild all indexes one by one for each tables? as there might be a hundreds of indexes all up
Post #992558
Posted Friday, September 24, 2010 3:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 3:42 AM
Points: 260, Visits: 368
I recommend to use MS script using sys.dm_db_index_physical_stats at http://msdn.microsoft.com/en-us/library/ms188917.aspx

Version 2008 R2, but versions up to 2005 are available.
Post #992567
Posted Friday, September 24, 2010 7:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 7:56 AM
Points: 1,392, Visits: 735
Or just make a maintenance plan that rebuilds them all in all databases. Schedule it for off hours though.
Post #992793
Posted Monday, August 29, 2011 3:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 23, 2013 11:52 PM
Points: 20, Visits: 28
Will "Rebuild all indexes" will also work with Indexes on composite primary keys?

Post #1166816
Posted Monday, August 29, 2011 4:27 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 7:56 AM
Points: 1,392, Visits: 735
Yes.
Post #1167238
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse