Home
%3CLINGO-SUB%20id%3D%22lingo-sub-339921%22%20slang%3D%22en-US%22%3EConfigMgr%202012%20Rebuilding%20Indexes%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-339921%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20size%3D%221%22%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23ff0000%22%3E%20First%20published%20on%20TECHNET%20on%20Feb%2027%2C%202014%3C%2FFONT%3E%20%3C%2FSTRONG%3E%20%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%221%22%3E%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Fblogs.technet.microsoft.com%2Fumairkhan%2F2014%2F02%2F27%2Fconfigmgr-2012-rebuilding-indexes-criteria%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fblogs.technet.microsoft.com%2Fumairkhan%2F2014%2F02%2F27%2Fconfigmgr-2012-rebuilding-indexes-criteria%2F%3C%2FA%3E%3C%2FSTRONG%3E%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Folks%2C%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3ETodays%E2%80%99%20post%20is%20about%20the%20%E2%80%98Rebuilding%20the%20Indexes%E2%80%99%20criteria%20used%20by%20the%20ConfigMgr%20Maintenance%20task%20when%20it%20runs%20as%20per%20schedule.%20There%20were%20questions%20regarding%20which%20indexes%20are%20actually%20rebuild%20touched%20and%20what%20are%20not.%3C%2FP%3E%0A%3CP%3EThe%20option%20for%20enabling%20the%20Rebuild%20Indexes%20is%20shown%20below%20%E2%80%93%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20501px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F75310i3435F081E479BAAB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%20%3CBR%20%2F%3E%3CU%3ECriteria%3A%3C%2FU%3E%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E-%20The%20rebuilding%20of%20the%20indexes%20happens%20for%20the%20objects%20only%20if%20their%20FRAGMENTATION%20PERCENT%20is%20more%20than%2010%25.%3C%2FP%3E%0A%3CP%3E-%20The%20index%20of%20type%20%E2%80%98HEAP%E2%80%99%20are%20not%20considered%20(type%20%3D%200)%20for%20rebuild.%20Generally%20the%20types%20that%20are%20rebuild%20are%20CLUSTERED%2C%20NONCLUSTERED%20and%20XML.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20below%20query%20can%20be%20ran%20to%20find%20the%20tables%20whose%20indexes%20would%20be%20rebuild%20if%20the%20Maintenance%20task%20runs.%20%5BSorted%20by%20the%20Most%20Fragmented%20first%5D%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%20id%3D%22codeSnippetWrapper%22%3E%0A%3CDIV%20id%3D%22codeSnippet%22%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20%23f4f4f4%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eselect%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Edistinct%3C%2FSPAN%3E%20sch.name%20%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20white%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%2B%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23006080%3B%22%3E'.'%3C%2FSPAN%3E%20%2B%20OBJECT_NAME(stat.object_id)%2C%20ind.name%2C%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Econvert%3C%2FSPAN%3E(%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eint%3C%2FSPAN%3E%2Cstat.avg_fragmentation_in_percent)%20%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20%23f4f4f4%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eas%3C%2FSPAN%3E%20Fragmentation_percent%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20white%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Efrom%3C%2FSPAN%3E%20sys.dm_db_index_physical_stats(DB_ID()%2C%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3ENULL%3C%2FSPAN%3E%2C%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3ENULL%3C%2FSPAN%3E%2C%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3ENULL%3C%2FSPAN%3E%2C%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23006080%3B%22%3E'LIMITED'%3C%2FSPAN%3E)%20stat%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20%23f4f4f4%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Ejoin%3C%2FSPAN%3E%20%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20white%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3Esys.indexes%20ind%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eon%3C%2FSPAN%3E%20%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20%23f4f4f4%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3Estat.object_id%3Dind.object_id%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eand%3C%2FSPAN%3E%20stat.index_id%3Dind.index_id%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20white%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Ejoin%3C%2FSPAN%3E%20%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20%23f4f4f4%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3Esys.objects%20obj%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eon%3C%2FSPAN%3E%20obj.object_id%3Dstat.object_id%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20white%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Ejoin%3C%2FSPAN%3E%20%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20%23f4f4f4%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3Esys.schemas%20sch%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eon%3C%2FSPAN%3E%20obj.schema_id%3Dsch.schema_id%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20white%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Ewhere%3C%2FSPAN%3E%20ind.name%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eis%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Enot%3C%2FSPAN%3E%20%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20%23f4f4f4%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Enull%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eand%3C%2FSPAN%3E%20stat.avg_fragmentation_in_percent%20%26gt%3B%2010.0%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eand%3C%2FSPAN%3E%20ind.type%20%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20white%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%26gt%3B%200%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20%23f4f4f4%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%26nbsp%3B%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20white%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eorder%3C%2FSPAN%3E%20%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20%23f4f4f4%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eby%3C%2FSPAN%3E%20%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Econvert%3C%2FSPAN%3E(%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%230000ff%3B%22%3Eint%3C%2FSPAN%3E%2Cstat.avg_fragmentation_in_percent)%20%3C%2FPRE%3E%0A%3CPRE%20style%3D%22box-sizing%3A%20border-box%3B%20overflow%3A%20visible%3B%20font-family%3A%20'Courier%20New'%2C%20courier%2C%20monospace%3B%20font-size%3A%208pt%3B%20display%3A%20block%3B%20padding%3A%200px%3B%20margin%3A%200em%3B%20line-height%3A%2012pt%3B%20color%3A%20black%3B%20word-break%3A%20break-all%3B%20overflow-wrap%3A%20break-word%3B%20background-color%3A%20white%3B%20border%3A%201px%20none%20%23cccccc%3B%20border-radius%3A%204px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20left%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%20direction%3A%20ltr%3B%20width%3A%20823.625px%3B%22%3Edesc%3C%2FPRE%3E%0A%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%0A%3CP%3E%3CSTRONG%3ESample%20Output%3A%3C%2FSTRONG%3E%3C%2FP%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20658px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F75311i9E749286A7250C36%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23000000%22%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Candara'%2Csans-serif%3B%22%3EFor%20%3CFONT%20color%3D%22%23ff0000%22%3Eindexes%20%26gt%3B30%25%20fragmented%3C%2FFONT%3E%20we%20use%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Candara'%2Csans-serif%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%22%3Ealter%20index%20%3CINDEXNAME%3E%20on%20%3CTABLENAME%3E%20REBUILD%20WITH%20(ONLINE%3DON)%3C%2FTABLENAME%3E%3C%2FINDEXNAME%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Candara'%2Csans-serif%3B%22%3EIf%20that%20fails%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%22%3Ealter%20index%20%3CINDEXNAME%3E%20%26nbsp%3Bon%20%3CTABLENAME%3E%20%26nbsp%3BREBUILD%20WITH%20(ONLINE%3DOFF)%3C%2FTABLENAME%3E%3C%2FINDEXNAME%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Candara'%2Csans-serif%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Candara'%2Csans-serif%3B%22%3EFor%20%3CFONT%20color%3D%22%23ff0000%22%3E%26lt%3B%2030%25%20fragmented%3C%2FFONT%3E%20we%20just%20reorganize%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Candara'%2Csans-serif%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-size%3A%2010.0pt%3B%20font-family%3A%20'Courier%20New'%3B%22%3Ealter%20index%20%3CINDEXNAME%3E%20%26nbsp%3B%26nbsp%3Bon%20%3CTABLENAME%3E%20%26nbsp%3B%26nbsp%3BREORGANIZE%3C%2FTABLENAME%3E%3C%2FINDEXNAME%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSTRONG%3EHow%20to%20track%20this%20in%20logs%3F%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWe%20log%20reindexing%20in%20the%20SMSDBMon.log%20in%20the%20%25ConfigMgr%25%5CLogs%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%0A%3CP%3EStart%3C%2FP%3E%0A%3CPRE%3ESMS_DATABASE_NOTIFICATION_MONITOR%20%20%20%205804%20(0x16ac)%20%20%20%20Task%20Rebuild%20Indexes%20is%20due%20now.%20%0ASMS_DATABASE_NOTIFICATION_MONITOR%20%20%20%205804%20(0x16ac)%20%20%20%20Executing%20Rebuild%20Indexes%20%3C%2FPRE%3E%0A%3CP%3EEnd%3C%2FP%3E%0A%3CPRE%3ESMS_DATABASE_NOTIFICATION_MONITOR%20%20%20%205804%20(0x16ac)%20%20%20%20Reindexing%20completed%3C%2FPRE%3E%0A%3CP%3EHope%20it%20Helps%20!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%20style%3D%22box-sizing%3A%20border-box%3B%20margin%3A%200px%200px%2010px%3B%20color%3A%20%23333333%3B%20font-family%3A%20'Segoe%20UI'%2C%20Tahoma%2C%20Arial%2C%20'Helvetica%20Neue'%2C%20Helvetica%2C%20sans-serif%3B%20font-size%3A%2014px%3B%20font-style%3A%20normal%3B%20font-variant-ligatures%3A%20normal%3B%20font-variant-caps%3A%20normal%3B%20font-weight%3A%20400%3B%20letter-spacing%3A%20normal%3B%20orphans%3A%202%3B%20text-align%3A%20start%3B%20text-indent%3A%200px%3B%20text-transform%3A%20none%3B%20white-space%3A%20normal%3B%20widows%3A%202%3B%20word-spacing%3A%200px%3B%20-webkit-text-stroke-width%3A%200px%3B%20background-color%3A%20%23ffffff%3B%20text-decoration-style%3A%20initial%3B%20text-decoration-color%3A%20initial%3B%22%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20font-size%3A%20medium%3B%20color%3A%20%230000ff%3B%20background-color%3A%20%23ffff00%3B%22%3E%3CSTRONG%20style%3D%22box-sizing%3A%20border-box%3B%20font-weight%3A%20bold%3B%22%3EUmair%20Khan%3CBR%20%2F%3E%3C%2FSTRONG%3E%3C%2FSPAN%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20font-size%3A%20medium%3B%22%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20'times%20new%20roman'%3B%22%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20background-color%3A%20%23ffff00%3B%22%3E%3CSTRONG%20style%3D%22box-sizing%3A%20border-box%3B%20font-weight%3A%20bold%3B%22%3ESupport%20Escalation%20Engineer%3C%2FSTRONG%3E%3C%2FSPAN%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%20style%3D%22box-sizing%3A%20border-box%3B%20font-weight%3A%20bold%3B%22%3E%7C%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20color%3A%20%23ffff00%3B%20background-color%3A%20%23000000%3B%22%3EMicrosoft%20System%20Center%20Configuration%20Manager%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-339921%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20TECHNET%20on%20Feb%2027%2C%202014%20Hi%20Folks%2C%20Todays%E2%80%99%20post%20is%20about%20the%20%E2%80%98Rebuilding%20the%20Indexes%E2%80%99%20criteria%20used%20by%20the%20ConfigMgr%20Maintenance%20task%20when%20it%20runs%20as%20per%20schedule.%3C%2FP%3E%3C%2FLINGO-TEASER%3E
Microsoft

First published on TECHNET on Feb 27, 2014

https://blogs.technet.microsoft.com/umairkhan/2014/02/27/configmgr-2012-rebuilding-indexes-criteria/

 

Hi Folks,

Todays’ post is about the ‘Rebuilding the Indexes’ criteria used by the ConfigMgr Maintenance task when it runs as per schedule. There were questions regarding which indexes are actually rebuild touched and what are not.

The option for enabling the Rebuild Indexes is shown below –


Criteria:

- The rebuilding of the indexes happens for the objects only if their FRAGMENTATION PERCENT is more than 10%.

- The index of type ‘HEAP’ are not considered (type = 0) for rebuild. Generally the types that are rebuild are CLUSTERED, NONCLUSTERED and XML.

 

The below query can be ran to find the tables whose indexes would be rebuild if the Maintenance task runs. [Sorted by the Most Fragmented first]

 

 select distinct sch.name 
+ '.' + OBJECT_NAME(stat.object_id), ind.name, convert(int,stat.avg_fragmentation_in_percent) 
as Fragmentation_percent
 from sys.dm_db_index_physical_stats(DB_ID(),NULL,NULL,NULL,'LIMITED') stat
 join 
sys.indexes ind on 
stat.object_id=ind.object_id and stat.index_id=ind.index_id
 join 
sys.objects obj on obj.object_id=stat.object_id
 join 
sys.schemas sch on obj.schema_id=sch.schema_id
 where ind.name is not 
null and stat.avg_fragmentation_in_percent > 10.0 and ind.type 
> 0
 
 order 
by convert(int,stat.avg_fragmentation_in_percent) 
desc

 

Sample Output:

 

 

For indexes >30% fragmented we use

 

alter index <indexname> on <tablename> REBUILD WITH (ONLINE=ON)

If that fails

alter index <indexname>  on <tablename>  REBUILD WITH (ONLINE=OFF)

 

For < 30% fragmented we just reorganize

 

alter index <indexname>   on <tablename>   REORGANIZE

 

How to track this in logs? 

 

We log reindexing in the SMSDBMon.log in the %ConfigMgr%\Logs

Start

SMS_DATABASE_NOTIFICATION_MONITOR    5804 (0x16ac)    Task Rebuild Indexes is due now. 
SMS_DATABASE_NOTIFICATION_MONITOR    5804 (0x16ac)    Executing Rebuild Indexes 

End

SMS_DATABASE_NOTIFICATION_MONITOR    5804 (0x16ac)    Reindexing completed

Hope it Helps !

 

Umair Khan
Support Escalation Engineer | Microsoft System Center Configuration Manager