Index Reorg and Rebuild

Question

Post reply

Index Reorg and Rebuild

jralston88

SSCommitted

Points: 1931
More actions
August 17, 2012 at 8:39 am

#260216

My understanding with these tasks, if the fragmentation is greater than 10% and less than 40%, reorg. If it is greater than 40%, rebuild.
I'd like to set some jobs to run these tasks against our 2005 and 2008 instances. Currently there are none. This includes standard and enterprise edition, stand alone and cluster environments.
My question, can this be scripted in such a way as to review the database fragmentation level and determine what task should be run. I'm a bit confused as I can see in SSMS there are maintenance plans to accomplish these tasks, however there does not appear to be any check for the fragmentation level.
What am I missing? Any comments / URLs are appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

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

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

http://ola.hallengren.com/Versions.html

http://sqlfool.com/2011/06/index-defrag-script-v4-1/

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

Take a look at Ola's scripts in my signature, he has a good comprehensive maintenance script which will do what you want.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

jralston88 (8/17/2012)
My understanding with these tasks, if the fragmentation is greater than 10% and less than 40%, reorg. If it is greater than 40%, rebuild.
I'd like to set some jobs to run these tasks against our 2005 and 2008 instances. Currently there are none. This includes standard and enterprise edition, stand alone and cluster environments.
My question, can this be scripted in such a way as to review the database fragmentation level and determine what task should be run. I'm a bit confused as I can see in SSMS there are maintenance plans to accomplish these tasks, however there does not appear to be any check for the fragmentation level.
What am I missing? Any comments / URLs are appreciated.

Please note that those are guidelines. It is possibile that these values may need to be adjusted for specific tables/indexes.

sqlkhan SSC Journeyman Points: 97 More actions · Answer 4

What I have understand You want to see which db need to be re-configure

or re-build

run this script

use master

SELECT database_id,OBJECT_ID,Index_id,avg_fragmentation_in_percent

from Sys.dm_db_index_physical_stats (null,null,null,null,null)

--avg_fragmentation_in_percent if Between 3-5 need Reconfigure

--Above 30% Rebuild when user are not connected

--Database Id can be seen in Master database system view

--1-4 are system database

use master

SELECT [name],[database_id]

FROM [master].[sys].[databases]

--Just started learning week ago

Thanks to Lowell,GSquared,Lynn Pettis and Rest DBA's

jralston88 SSCommitted Points: 1931 More actions · Answer 5

Thank you everyone for your responses, I've got some work in front of me.