When/How to defrag DB/Tables? (Network Steve Forum)

When/How to defrag DB/Tables?

I have a SPF 2010 server and don't know much about the DB maintenance. (Sorry if my terminology is not correct)

I have read several pages on this typic (Database maintenance for SharePoint 2010 Products, sys.dm_db_index_physical_stats (Transact-SQL)) and if I am understanding these pages I need to do some defragmenting.

DBCC CHECKDB shows no allocation or consistency errors in the Wss_Content DB.

I then ran

select * 
From sys.dm_db_index_physical_stats (null, null, null, null, null)
where avg_fragmentation_in_percent > 20

And found 297 rows, a lot over 50%+ and many in the 90s. My Wss_Content DB ID is 7. I cut out most of the result.

Row DBID ObjID Index Part# IndexTypeDesc AllocUnit AvgFragIn% FragCount

1 4 46623209 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 99.2 125 1 125 2 4 110623437 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 97.6190476190476 126 1 126 3 4 206623779 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 98.4126984126984 126 1 126 4 4 526624919 0 1 HEAP IN_ROW_DATA 1 0 88.8888888888889 11 2.27272727272727 25 5 4 558625033 0 1 HEAP IN_ROW_DATA 1 0 85.7142857142857 9 1 9 6 4 852914110 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 80 5 1 5 7 4 1620200822 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 75 4 1 4 8 5 37575172 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 93.3333333333333 15 1 15 9 5 69575286 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 53.9267015706806 110 1.73636363636364 191 10 5 69575286 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 87.5 8 1 8 11 5 229575856 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 83.3333333333333 6 1 6 12 6 254623950 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 88.8888888888889 17 1.05882352941176 18 13 6 254623950 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 77.7777777777778 8 1.125 9 14 6 350624292 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 88.2352941176471 16 1.0625 17 15 6 350624292 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 94.1176470588235 17 1 17 16 6 389576426 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 96.551724137931 29 1 29 17 6 469576711 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 96.4539007092199 139 1.01438848920863 141 18 6 469576711 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 94.2446043165468 132 1.0530303030303 139 19 6 862626116 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 20 6 1019866700 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 98.1132075471698 106 1 106 21 6 1067866871 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 75 4 1 4 22 6 1378103950 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 80 5 1 5 23 6 1378103950 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 24 6 1554104577 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 25 7 14623095 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 99.625468164794 267 1 267 26 7 14623095 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 96.1538461538462 26 1 26 27 7 78623323 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 63.6363636363636 8 1.375 11 28 7 78623323 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 54.5454545454545 7 1.57142857142857 11 29 7 254623950 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 93.4579439252337 103 1.03883495145631 107 30 7 254623950 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 88.8888888888889 9 1 9 31 7 398624463 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 91.358024691358 75 1.08 81 32 7 398624463 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 90.2439024390244 76 1.07894736842105 82 33 7 549576996 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 95.4545454545455 22 1 22 34 7 549576996 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 83.3333333333333 6 1 6 35 7 549576996 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 85.7142857142857 7 1 7 36 7 565577053 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 99.1341991341991 462 1 462 37 7 565577053 3 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 98.4848484848485 462 1 462 38 7 702625546 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 92 48 1.04166666666667 50 39 7 862626116 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 64.8854961832061 87 1.50574712643678 131 40 7 862626116 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 41 7 862626116 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 42 7 862626116 6 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 75 4 1 4 43 7 926626344 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 94.4444444444444 18 1 18 44 7 958626458 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 77.7777777777778 8 1.125 9 45 7 958626458 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 75 8 1 8 46 7 1029578706 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 97.3684210526316 38 1 38 47 7 1067866871 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 88.8888888888889 9 1 9 48 7 1150627142 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 85 18 1.11111111111111 20 49 7 1150627142 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 84.6153846153846 13 1 13 50 7 1301579675 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 96.875 32 1 32 51 7 1541580530 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 87.5 8 1 8 52 7 1554104577 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 54.8387096774194 19 1.63157894736842 31 53 7 1573580644 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 75 4 1 4 54 7 1586104691 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 85.1851851851852 26 1.03846153846154 27 55 7 1650104919 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 56 7 1682105033 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 90 10 1 10 57 7 1762105318 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 71.4285714285714 22 1.27272727272727 28 58 7 1762105318 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 75 4 1 4 59 7 1762105318 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 75 4 1 4 60 7 1762105318 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 80 5 1 5 61 8 719055 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.8333333333333 24 1 24 62 8 719055 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 58.3333333333333 8 1.5 12 63 8 27147142 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 96.2962962962963 27 1 27 64 8 27147142 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 59.0909090909091 15 1.46666666666667 22 65 8 27147142 6 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 63.6363636363636 8 1.375 11 66 8 39671189 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 98.5915492957747 141 1.00709219858156 142 67 8 64719283 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.8333333333333 24 1 24 68 8 64719283 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 58.3333333333333 8 1.5 12 69 8 91147370 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.8333333333333 24 1 24 70 8 91147370 6 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 63.6363636363636 8 1.375 11 71 8 103671417 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 99.3055555555556 144 1 144 72 8 116195464 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 73 8 128719511 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 91.6666666666667 24 1 24 74 8 128719511 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 58.3333333333333 8 1.5 12 75 8 155147598 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.8333333333333 24 1 24 76 8 155147598 6 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 63.6363636363636 8 1.375 11 77 8 167671645 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 99.3055555555556 144 1 144 78 8 180195692 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 79 8 192719739 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.8333333333333 24 1 24 80 8 192719739 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 58.3333333333333 8 1.5 12 81 8 219147826 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 96 25 1 25 82 8 219147826 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 52.3809523809524 13 1.61538461538462 21 83 8 219147826 6 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 54.5454545454545 8 1.375 11 84 8 231671873 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 98.5915492957747 141 1.00709219858156 142 85 8 244195920 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 86 8 256719967 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.6521739130435 23 1 23 87 8 256719967 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 58.3333333333333 8 1.5 12 88 8 283148054 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.8333333333333 24 1 24 89 8 283148054 6 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 54.5454545454545 8 1.375 11 90 8 295672101 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 79.8882681564246 147 1.21768707482993 179 91 8 308196148 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 92 8 320720195 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 75.3846153846154 102 1.27450980392157 130 93 8 320720195 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 83.3333333333333 11 1.09090909090909 12 94 8 347148282 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 97.7777777777778 45 1 45 95 8 359672329 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 57.2254335260116 217 1.59447004608295 346 96 8 372196376 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 97 8 411148510 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 94.5121951219512 156 1.05128205128205 164 98 8 411148510 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 51 59 1.69491525423729 100 99 8 423672557 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 80.7266982622433 1551 1.22437137330754 1899 100 8 423672557 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 73.5729386892178 357 1.3249299719888 473 101 8 423672557 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 80.5555555555556 180 1.2 216 102 8 475148738 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 86.7083333333333 2096 1.14503816793893 2400 103 8 475148738 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 86.8852459016393 108 1.12962962962963 122 104 8 475148738 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 85.8333333333333 105 1.14285714285714 120 105 8 475148738 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 88.5496183206107 118 1.11016949152542 131 106 8 475148738 6 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 89.041095890411 66 1.10606060606061 73 107 8 484196775 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 85.3846153846154 114 1.14035087719298 130 108 8 484196775 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 83.3333333333333 11 1.09090909090909 12 109 8 535672956 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 80 5 1 5 110 8 548197003 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.6521739130435 23 1 23 111 8 548197003 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 58.3333333333333 8 1.5 12 112 8 587149137 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 76.9505127061971 1761 1.27370812038614 2243 113 8 587149137 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 75.7575757575758 461 1.2885032537961 594 114 8 587149137 3 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 77.0428015564202 205 1.25365853658537 257 115 8 599673184 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 116 8 612197231 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.4545454545455 22 1 22 117 8 612197231 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 63.6363636363636 8 1.375 11 118 8 638625318 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 62.7811213177067 2025 1.55901234567901 3157 119 8 638625318 3 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 66.2420382165605 111 1.41441441441441 157 120 8 638625318 4 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 67.3202614379085 109 1.40366972477064 153 121 8 638625318 5 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 87.1165644171779 146 1.11643835616438 163 122 8 638625318 6 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 67.741935483871 67 1.38805970149254 93 123 8 651149365 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 58.3061889250814 198 1.55050505050505 307 124 8 663673412 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 125 8 676197459 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.8333333333333 24 1 24 126 8 676197459 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 58.3333333333333 8 1.5 12 127 8 702625546 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 93.3333333333333 157 1.05095541401274 165 128 8 702625546 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 60.2150537634409 61 1.52459016393443 93 129 8 715149593 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 75.1054852320675 187 1.26737967914438 237 130 8 727673640 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 131 8 740197687 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.8333333333333 24 1 24 132 8 740197687 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 58.3333333333333 8 1.5 12 133 8 766625774 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.1923076923077 101 1.02970297029703 104 134 8 766625774 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 59.4202898550725 45 1.53333333333333 69 135 8 779149821 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 97.4683544303797 78 1.01282051282051 79 136 8 791673868 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 137 8 804197915 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.6521739130435 23 1 23 138 8 804197915 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 58.3333333333333 8 1.5 12 139 8 830626002 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 94.7368421052632 19 1 19 140 8 830626002 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 52.6315789473684 12 1.58333333333333 19 141 8 830626002 6 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 70 8 1.25 10 142 8 843150049 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 98.0392156862745 51 1 51 143 8 855674096 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 144 8 868198143 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 54.5454545454545 7 1.57142857142857 11 145 8 894626230 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 90 10 1 10 146 8 894626230 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 70 8 1.25 10 147 8 894626230 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 60 8 1.25 10 148 8 894626230 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 63.6363636363636 8 1.375 11 149 8 894626230 6 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 5 1.2 6 150 8 907150277 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 67.2727272727273 157 1.40127388535032 220 151 8 958626458 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.5752212389381 109 1.03669724770642 113 152 8 958626458 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 55.0724637681159 45 1.53333333333333 69 153 8 1061578820 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 93.5483870967742 88 1.05681818181818 93 154 8 1061578820 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 60 10 1.5 15 155 8 1069246864 0 1 HEAP IN_ROW_DATA 1 0 82.7586206896552 26 7.19230769230769 187 156 8 1069246864 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 59.6153846153846 32 1.625 52 157 8 1069246864 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 85 18 1.11111111111111 20 158 8 1101246978 0 1 HEAP IN_ROW_DATA 1 0 88.6792452830189 48 4.04166666666667 194 159 8 1101246978 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 93.3333333333333 43 1.04651162790698 45 160 8 1133247092 0 1 HEAP IN_ROW_DATA 1 0 86.5384615384615 46 4.3695652173913 201 161 8 1133247092 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 91.4893617021277 45 1.04444444444444 47 162 8 1165247206 0 1 HEAP IN_ROW_DATA 1 0 69.2307692307692 10 6 60 163 8 1165247206 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 78.5714285714286 12 1.16666666666667 14 164 8 1197247320 0 1 HEAP IN_ROW_DATA 1 0 75 4 2.25 9 165 8 1229247434 0 1 HEAP IN_ROW_DATA 1 0 76 39 5.84615384615385 228 166 8 1229247434 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 94.7368421052632 55 1.03636363636364 57 167 8 1293247662 0 1 HEAP IN_ROW_DATA 1 0 75 4 2.25 9 168 8 1901249828 0 1 HEAP IN_ROW_DATA 1 0 75 4 5.25 21 169 8 1901249828 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 80 5 1 5 170 8 1933249942 0 1 HEAP IN_ROW_DATA 1 0 75 7 5.42857142857143 38 171 8 1933249942 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 90.9090909090909 11 1 11 172 8 1933249942 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 173 8 1997250170 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 77.7777777777778 8 1.125 9 174 8 1997250170 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 75 4 1 4 175 8 2007678200 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 176 8 2020202247 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.8333333333333 24 1 24 177 8 2020202247 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 58.3333333333333 8 1.5 12 178 8 2029250284 0 1 HEAP IN_ROW_DATA 1 0 86.2068965517241 51 4.31372549019608 220 179 8 2029250284 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 96.2264150943396 53 1 53 180 8 2061250398 0 1 HEAP IN_ROW_DATA 1 0 53.5714285714286 16 10.125 162 181 8 2061250398 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.4545454545455 22 1 22 182 8 2071678428 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 183 8 2084202475 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 95.8333333333333 24 1 24 184 8 2084202475 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 58.3333333333333 8 1.5 12 185 8 2123154609 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 99.3055555555556 144 1 144 186 8 2135678656 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 187 9 85575343 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 75 4 1 4 188 9 261575970 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 66.6666666666667 3 1 3 189 10 21575115 0 1 HEAP IN_ROW_DATA 1 0 66.6666666666667 7 2.14285714285714 15 190 10 69575286 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 84.5070422535211 122 1.16393442622951 142 191 10 85575343 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 86.8922108575924 5612 1.1323948681397 6355 192 10 85575343 2 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 96.1352657004831 2807 1.03241895261845 2898 193 10 85575343 3 1 NONCLUSTERED INDEX IN_ROW_DATA 4 0 96.2898144907245 5553 1.02899333693499 5714 194 10 101575400 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 69.1796469366563 3461 1.39121641144178 4815 195 10 176719682 0 1 HEAP IN_ROW_DATA 1 0 96 26 4.19230769230769 109 196 10 192719739 0 1 HEAP IN_ROW_DATA 1 0 95.4545454545455 22 3.95454545454545 87 197 10 340196262 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 50.2840909090909 376 1.87234042553191 704 198 10 372196376 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 99.21875 256 1 256 199 10 372196376 7 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 86.0824742268041 341 1.13782991202346 388 200 10 386100416 0 1 HEAP IN_ROW_DATA 1 0 88.8888888888889 9 1 9 201 10 722101613 0 1 HEAP IN_ROW_DATA 1 0 80 5 1 5 202 10 724197630 1 1 CLUSTERED INDEX IN_ROW_DATA 3 0 52.8958915037894 14764 1.69804930913032 25070 203 10 724197630 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 98.0988593155894 263 1 263 204 10 724197630 4 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 97.7011494252874 261 1 261 205 10 724197630 5 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 97.864768683274 281 1 281 206 10 724197630 6 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 84.2443729903537 265 1.17358490566038 311 207 10 724197630 7 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 85.3383458646617 231 1.15151515151515 266 208 10 724197630 9 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 70.8456534594914 1233 1.37145174371452 1691 209 10 724197630 10 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 90.4458598726115 288 1.09027777777778 314 210 10 724197630 11 1 NONCLUSTERED INDEX IN_ROW_DATA 3 0 74.597606273215 3736 1.29710920770878 4846 211 10 834102012 0 1 HEAP IN_ROW_DATA 1 0 96.4285714285714 29 4.44827586206897 129 212 10 1396200024 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 84.1309823677582 342 1.16081871345029 397 213 10 1396200024 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 86.2068965517241 178 1.14044943820225 203 214 10 1396200024 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 85.4961832061069 230 1.13913043478261 262 215 10 1490104349 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 91.6666666666667 23 1.04347826086957 24 216 10 1490104349 2 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 70 8 1.25 10 217 10 1490104349 3 1 NONCLUSTERED INDEX IN_ROW_DATA 2 0 92.3076923076923 13 1 13 218 10 1634104862 1 1 CLUSTERED INDEX IN_ROW_DATA 2 0 80 5 1 5

Now from the reading it is suggesting Avg_Frag_in_% should be 10 but I am not clear how I specify what to defrag. Is it the entire DB or only the tables and how do I do that?

I am being cautious as this is my production SPF server, we are small and don't have a test server. I want to make sure the DBs are in good shape as we have been periodically having high CPU (sqlservr.exe) and can't seem to find the cause.

Thanks for your help in ad

January 13th, 2015 2:35am

Run the Health Analyzer rule "Databases used by SharePoint have fragmented indices.". This will defragment your indexes.

Free Windows Admin Tool Kit Click here and download it now

January 13th, 2015 3:44am

Thanks Trevor for the response.

I was just poking around in the rule definitions and saw that one and Ran it. How long should it take to complete and I am assuming that if i run the "sys.dm_db_index_physical_stats (null, null, null, null, null)" again I should not see the Avg_frag_in_% any higher than what?

This job is set to run daily and when I ran this report before i was getting similar results many in the 90+% and if it runs daily then how is it getting this high? To me its like its not doing anything to defrag or at least affect the numbers i am getting from the "sys.dm_db_index_physical_stats (null, null, null, null, null)" query.

As I mentioned I don't know much about the maintenance of this SPF server but it doesn't seem to be fixing the defragmentation?

January 13th, 2015 4:04am

Keep in mind that the # of pages is very important when considering fragmentation. E.g. if you have 2 pages, you could have 100% fragmentation, but there is no performance impact. If you have 10s of thousands of pages with 90% fragmentation, then that is a problem.

Free Windows Admin Tool Kit Click here and download it now

January 13th, 2015 2:29pm

So I refined my query to

Use Wss_Content
Select * 
From sys.dm_db_index_physical_stats (null, null, null, null, null)
Where avg_fragmentation_in_percent > 20
And	page_count > 1000

and returned 14 rows, so does this report indicate any action is required?

database_id	object_id	index_id	partition_number	index_type_desc	alloc_unit_type_desc	index_depth	index_level	avg_fragmentation_in_percent	fragment_count	avg_fragment_size_in_pages	page_count
4	366624349	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	0	34.1791618608228	1037	2.50819672131148	2601
7	1605580758	1	1	CLUSTERED INDEX	IN_ROW_DATA	4	0	26.1075547426713	48141	3.27089175546831	157464
7	2098106515	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	0	26.7961165048544	605	3.40495867768595	2060
8	423672557	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	0	80.7266982622433	1551	1.22437137330754	1899
8	475148738	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	0	86.7083333333333	2096	1.14503816793893	2400
8	587149137	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	0	76.9505127061971	1761	1.27370812038614	2243
8	638625318	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	0	62.7811213177067	2025	1.55901234567901	3157
10	85575343	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	0	86.8173258003766	5622	1.13340448239061	6372
10	85575343	2	1	NONCLUSTERED INDEX	IN_ROW_DATA	3	0	96.1432506887052	2813	1.0323498044792	2904
10	85575343	3	1	NONCLUSTERED INDEX	IN_ROW_DATA	4	0	96.301465457083	5571	1.02889965894812	5732
10	101575400	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	0	69.076222038111	3466	1.39296018465089	4828
10	724197630	1	1	CLUSTERED INDEX	IN_ROW_DATA	3	0	52.9409420578624	14788	1.69691641871788	25094
10	724197630	9	1	NONCLUSTERED INDEX	IN_ROW_DATA	3	0	70.8628841607565	1234	1.37115072933549	1692
10	724197630	11	1	NONCLUSTERED INDEX	IN_ROW_DATA	3	0	74.5720767168488	3737	1.2975648916243	4849

I am just trying to eliminate any reason for performance issue, which is normally very good considering we have a small SP server with little data relative to most implementations. But periodically the sqlservr.exe process hits 100% and can run like that for 4-8 days. Then it seems to recover without any user intervention.

January 14th, 2015 7:56pm

Such an extended period of time probably excludes the indexes as being the source of the issue. None the less, you could manually defragment those indexes (via T-SQL) as a one time job, and let SharePoint take over from there.

Free Windows Admin Tool Kit Click here and download it now

January 14th, 2015 8:52pm

Thanks Trevor, I don't work with DB maintenance and have search T-SQL and I am a bit lost at this point. Can you provide an example on how to do this or a link to something that provides detail on how to actually defrag. I am assuming it is done via a query in SQL Studio?

Thanks.

January 15th, 2015 9:21pm

I did find a MS article on how to defrag a table "https://msdn.microsoft.com/en-us/library/ms189858.aspx", but is this the only way to do this, one table at a time? The WSS_Content DB has 117 tables, most of which are at 90+% fragmented.

I tested the query below and it worked but as stated on one of over 100 tables.

USE WSS_Content;
GO
ALTER INDEX ALL ON dbo.workflow
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON);
GO

Can someone confirm that if I want to defrag all tables in a DB it will be 1 table at a time?

Free Windows Admin Tool Kit Click here and download it now

July 16th, 2015 4:41pm

This topic is archived. No further replies will be accepted.