fix fragmentation sql server

Question

fix fragmentation sql server

ronald_yoh

SSC Enthusiast

Points: 197
More actions
September 8, 2010 at 9:02 pm

#225943

Hi,
I just shrunk the database using DBCC SHRINK statement. what would be the best approach to fix the fragmentation after shrinking the database?

Viewing 15 posts - 1 through 14 (of 14 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Rebuild all indexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Halcyon Ten Centuries Points: 1331 More actions · Answer 2

Rebuild all indexes via alter <<index name>> on <<table name>> rebuild

then run exec sp_updatestats

SQLCharger SSCommitted Points: 1958 More actions · Answer 3

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

shaun.stuart SSCertifiable Points: 7070 More actions · Answer 4

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.

ronald_yoh SSC Enthusiast Points: 197 More actions · Answer 5

do i need to rebuild all indexes one by one for each tables? as there might be a hundreds of indexes all up

Halcyon Ten Centuries Points: 1331 More actions · Answer 6

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.

shaun.stuart SSCertifiable Points: 7070 More actions · Answer 7

Or just make a maintenance plan that rebuilds them all in all databases. Schedule it for off hours though.

JPJ SSC Enthusiast Points: 146 More actions · Answer 8

Will "Rebuild all indexes" will also work with Indexes on composite primary keys?

shaun.stuart SSCertifiable Points: 7070 More actions · Answer 9

shaun.stuart

SSCertifiable

Points: 7070

August 29, 2011 at 4:27 pm

#1374765

Yes.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 10

ronald_yoh (9/24/2010)
do i need to rebuild all indexes one by one for each tables? as there might be a hundreds of indexes all up

You should do as Gail suggested, Rebuild all Indexes.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

JPJ SSC Enthusiast Points: 146 More actions · Answer 11

Great! I am looking for a script that loops through the indexes so all of them can be fixed in one click.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Scripts to rebuild the indexes.

http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

http://www.mssqltips.com/sqlservertip/1367/sql-server-script-to-rebuild-all-indexes-for-all-tables-and-all-databases/

http://blogs.msdn.com/b/joaol/archive/2008/01/28/script-to-rebuild-and-reorganize-database-indexes-sql-server-2005.aspx

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

JPJ SSC Enthusiast Points: 146 More actions · Answer 13

Those are links of best sources that I am looking to. Thanks!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 14

JPJ (8/29/2011)
Those are links of best sources that I am looking to. Thanks!

😎

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/