All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000
Kay
http://www.sql-server-performance.co...showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
|||Based on the result set provided and viewing the clustered index, your extent
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since the
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" and
> "LogicalFragmentation", I paid special attention to "Extent Swithces" which
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022 12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022 12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>
Showing posts with label extent. Show all posts
Showing posts with label extent. Show all posts
Sunday, March 25, 2012
DBCC SHOWCONTIG and Extent Switches
All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your extent
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since the
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" and
> "LogicalFragmentation", I paid special attention to "Extent Swithces" which
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022 12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022 12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.com/dt_dbcc_showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your extent
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since the
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" and
> "LogicalFragmentation", I paid special attention to "Extent Swithces" which
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022 12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022 12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>
DBCC SHOWCONTIG and Extent Switches
All,
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.c..._showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your exten
t
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since th
e
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" an
d
> "LogicalFragmentation", I paid special attention to "Extent Swithces" whic
h
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSiz
e
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensit
y
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.32
2
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.41
7
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 16902
2
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 37
0
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 1
2
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 1
6
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>sql
I executed the following statement at my production server.
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
which provided me the following resultset. Besides "AveragePageDensity" and
"LogicalFragmentation", I paid special attention to "Extent Swithces" which
is quite high.
Is there any performance gain if I reduce th Extent Switces? How can I
lessen the value of Extent Switches.
TIA
Kay
ObjectName ObjectId IndexName
IndexId
Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity
ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
89.321 84.615 66 78 1.898 18.056
package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
99.541 92.857 39 42 1.307 16.667
package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
99.772 96.078 49 51 0.258 13.725
package_description 1450240717 PK_package_description 1 0 3226 169022
44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
package_description 1450240717 package_description73 2 0 296 169022 12
12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
package_description 1450240717 idx_package_state 4 0 341 169022 12 12
12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
package_description 1450240717 idx_package_available 7 0 298 169022 12
12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
package_description 1450240717 tpackage_description 255 0 114597
340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
99.999 17.153
pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
100.000 1 1 0.000 0.000Kay
http://www.sql-server-performance.c..._showcontig.asp
"Kay" <CallDBA@.hotmail.com> wrote in message
news:OmnmxiiBGHA.3840@.TK2MSFTNGP15.phx.gbl...
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity"
> and "LogicalFragmentation", I paid special attention to "Extent Swithces"
> which is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes
> AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation
> ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.322
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.417
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 169022
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
> 12 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 370
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 12
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 16
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
> 12 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>|||Based on the result set provided and viewing the clustered index, your exten
t
switches are fine. Extent switches is the number of times the DBCC statement
moved off an extent while it was scanning the pages in the extent. You would
expect an extent switch to happen after the whole extent had been scanned.
The most useful line of output would be the ScanDensity BestCount
ActualCount. This is your measure of fragmentation. The best count is the
ideal number of extents, where as the actual count is the actual number of
extents use to hold the data pages.
I noticed the AveragePageDensity is around 89.321, which indicates a
fillfactor of 90 set for the clustered index. In a clustered index, since th
e
leaf level contains the data, you can use FILLFACTOR to control how much
space to leave in the table itself. By reserving free space, you can avoid
splitting pages to make room for a new entry. NOTE that FILLFACTOR is not
maintained; it only indicates how much space is reserved with the existing
data at the time the index is built. If you need to, you can use the DBCC
DBREINDEX command to rebuild the index and reestablish the original
FILLFACTOR specified, also reducing fragmentation.
"Kay" wrote:
> All,
>
> I executed the following statement at my production server.
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> which provided me the following resultset. Besides "AveragePageDensity" an
d
> "LogicalFragmentation", I paid special attention to "Extent Swithces" whic
h
> is quite high.
>
> Is there any performance gain if I reduce th Extent Switces? How can I
> lessen the value of Extent Switches.
>
> TIA
>
> Kay
>
> ObjectName ObjectId IndexName
> IndexId
> Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSiz
e
> ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensit
y
> ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
> package 1845581613 PK_package 1 0 527 224182 15 15 15 0 72 77 864.32
2
> 89.321 84.615 66 78 1.898 18.056
> package 1845581613 package80 5 0 306 224182 9 9 9 0 42 41 37.169
> 99.541 92.857 39 42 1.307 16.667
> package 1845581613 package69 6 0 388 224182 9 12 11.98 0 51 50 18.41
7
> 99.772 96.078 49 51 0.258 13.725
> package_description 1450240717 PK_package_description 1 0 3226 16902
2
> 44 434 138.77 0 413 412 720.535 91.098 97.821 404 413 0.217 10.412
> package_description 1450240717 package_description73 2 0 296 169022
12
> 12 12 0 42 43 101.716 98.743 84.091 37 44 1.351 14.286
> package_description 1450240717 idx_package_name 3 0 1904 169022 8 37
0
> 77.871 0 249 636 1005.600 87.576 37.363 238 637 11.922 22.088
> package_description 1450240717 idx_package_state 4 0 341 169022 12 1
2
> 12 0 47 129 1156.680 85.709 33.077 43 130 14.663 21.277
> package_description 1450240717 idx_package_hours 5 0 396 169022 16 1
6
> 16 0 54 79 413.181 94.895 62.500 50 80 5.303 22.222
> package_description 1450240717 idx_package_cost 6 0 411 169022 16 16
> 16 0 56 115 693.576 91.431 44.828 52 116 8.273 17.857
> package_description 1450240717 idx_package_available 7 0 298 169022
12
> 12 12 0 42 48 155.369 98.080 77.551 38 49 2.349 16.667
> package_description 1450240717 tpackage_description 255 0 114597
> 340665 32 8094 2076.854 0 14336 14335 1916.141 76.326 99.923 14325 14336
> 99.999 17.153
> pass_quiz_options 1877581727 0 0 1 2 35 45 40 0 1 0 8012.000 1.013
> 100.000 1 1 0.000 0.000
>
>
>
>sql
DBCC Showcontig and extent fragmentation
I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?
Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?
|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent[vbcol=seagreen]
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
both[vbcol=seagreen]
I
>
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?
Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?
|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent[vbcol=seagreen]
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
both[vbcol=seagreen]
I
>
DBCC Showcontig and extent fragmentation
I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
both[vbcol=seagreen]
I[vbcol=seagreen]
>
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
both[vbcol=seagreen]
I[vbcol=seagreen]
>
DBCC Showcontig and extent fragmentation
I have a large table that I cannot seem to bring the extent fragmentation
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
> >I have a large table that I cannot seem to bring the extent fragmentation
> > down to an aceptable level. Fist the logical scan density and logical
> > fragmentation were extreamly high so I rebuit the index. This helped
both
> > the logical scan density and logical fragmentation but the extent
> > fragmentation stayed at 65%. I then did an index defrag and the extent
> > fragmentation stayed at 65%. I then Reindexed again and the extent
> > fragmentation dropped to 33%. I defraged and reindexed once more and it
> > droped to 21%. I could not get this number to drop any more from 21% so
I
> > droped the index and recreated it; - and it came back with an extent
> > fragmentation of 34%. Is there a way, short of moving to another file
> > group
> > to get my extent fragmentation down without jumping through all these
> > hoops?
>
down to an aceptable level. Fist the logical scan density and logical
fragmentation were extreamly high so I rebuit the index. This helped both
the logical scan density and logical fragmentation but the extent
fragmentation stayed at 65%. I then did an index defrag and the extent
fragmentation stayed at 65%. I then Reindexed again and the extent
fragmentation dropped to 33%. I defraged and reindexed once more and it
droped to 21%. I could not get this number to drop any more from 21% so I
droped the index and recreated it; - and it came back with an extent
fragmentation of 34%. Is there a way, short of moving to another file group
to get my extent fragmentation down without jumping through all these hoops?Hi Kirk
Extent scan fragmentation (ESF) measures how contiguous the extents in your
table or index are. To have no ESF, you would basically need to have free
space in your file equal to the total size of your structure, and it would
need to be allocated all at once, which SQL Server doesn't do. Even if you
have the contiguous space, you still might end up with ESF. If while you are
rebuilding your structures, other users are doing any kind of work that
requires allocation of extents, their allocations could come from an extent
right next to the one you just got, so your next allocation would be
separated from your previous one.
Why do you want to get it down lower? If you don't have any other
fragmentation, ESF just isn't worth worrying about, IMO.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
>I have a large table that I cannot seem to bring the extent fragmentation
> down to an aceptable level. Fist the logical scan density and logical
> fragmentation were extreamly high so I rebuit the index. This helped both
> the logical scan density and logical fragmentation but the extent
> fragmentation stayed at 65%. I then did an index defrag and the extent
> fragmentation stayed at 65%. I then Reindexed again and the extent
> fragmentation dropped to 33%. I defraged and reindexed once more and it
> droped to 21%. I could not get this number to drop any more from 21% so I
> droped the index and recreated it; - and it came back with an extent
> fragmentation of 34%. Is there a way, short of moving to another file
> group
> to get my extent fragmentation down without jumping through all these
> hoops?|||Also, if you have multiple files, extent scan fragmentation is meaningless
(as documented in Books OnLine). No amount of rebuilding/defragging will get
it down in that case because the algorithm doesn't cope with multiple files.
You should read the whitepaper below which will explain about fragmentation
and when you should be worried about it.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:uid1C2SvEHA.3872@.TK2MSFTNGP11.phx.gbl...
> Hi Kirk
> Extent scan fragmentation (ESF) measures how contiguous the extents in
your
> table or index are. To have no ESF, you would basically need to have free
> space in your file equal to the total size of your structure, and it would
> need to be allocated all at once, which SQL Server doesn't do. Even if you
> have the contiguous space, you still might end up with ESF. If while you
are
> rebuilding your structures, other users are doing any kind of work that
> requires allocation of extents, their allocations could come from an
extent
> right next to the one you just got, so your next allocation would be
> separated from your previous one.
> Why do you want to get it down lower? If you don't have any other
> fragmentation, ESF just isn't worth worrying about, IMO.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "kirk1880" <kirk1880@.discussions.microsoft.com> wrote in message
> news:13DDB330-4ADD-4B67-B627-623CAB48C95E@.microsoft.com...
> >I have a large table that I cannot seem to bring the extent fragmentation
> > down to an aceptable level. Fist the logical scan density and logical
> > fragmentation were extreamly high so I rebuit the index. This helped
both
> > the logical scan density and logical fragmentation but the extent
> > fragmentation stayed at 65%. I then did an index defrag and the extent
> > fragmentation stayed at 65%. I then Reindexed again and the extent
> > fragmentation dropped to 33%. I defraged and reindexed once more and it
> > droped to 21%. I could not get this number to drop any more from 21% so
I
> > droped the index and recreated it; - and it came back with an extent
> > fragmentation of 34%. Is there a way, short of moving to another file
> > group
> > to get my extent fragmentation down without jumping through all these
> > hoops?
>
Sunday, February 19, 2012
DBCC CHECKDB shows errors
Results show 0 allocation errors and 42 consistency errors that all seem to be from one table. That is to the extent I understand the results. The errors are 8929, 8939, 8965, 8964 all for the same object id and varying text ids. The repair_fast and repair_rebuild did not work. Its calling for the repair_with_data_loss. Are there any work arounds? Does this mean that the table is corrupt? Is this a torn data page due to this table? If I need to run the repair_with_data_loss is there an easy way to determine which data has been lost? Welcome any feedback.Here are the general recommendations for handling a suspect or corrupt database:
0. Ensure you have a backup strategy that you can use to recover from hardware failures (including
corruption). I recommend performing both database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and KB for the error
numbers that CHECKDB gives you. There might be specific info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.; search Books Online and KB
for those errors. You don't want this to happen again! If the database is suspect, the file might
have been in use by for instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of course. If the database is
suspect, then the NO_TRUNCATE option for the RESTORE command must be used. Also, you might want to
do a file backup of the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per step 4, then you will
most probably have zero dataloss. You should restore the latest clean database backup and the
subsequent log backups including the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a secondary option but this
will often result in loss of data. Additional solutions, depending on the errors, may be to manually
rebuild non-clustered indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect" the database using
sp_resetstatus. Read about it (books online, KB, google etc). It might help but if the database is
too damaged, it might just pop back to suspect again. There's also something called "emergency mode"
which is a "panic" status you can set in order to try to get data out of a damaged database. I think
the name of that option speaks for itself. Again search the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you through the steps
appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bush" <anonymous@.discussions.microsoft.com> wrote in message
news:77FBEDC8-9B02-4CAB-970E-A96DC0F47EF9@.microsoft.com...
> Results show 0 allocation errors and 42 consistency errors that all seem to be from one table.
That is to the extent I understand the results. The errors are 8929, 8939, 8965, 8964 all for the
same object id and varying text ids. The repair_fast and repair_rebuild did not work. Its calling
for the repair_with_data_loss. Are there any work arounds? Does this mean that the table is
corrupt? Is this a torn data page due to this table? If I need to run the repair_with_data_loss is
there an easy way to determine which data has been lost? Welcome any feedback.|||Further to Tibor's comments, yes, you have corruption in your table. There
are no workarounds except restoring from a backup or running
repair_allow_data_loss (which will have to delete some data to fix this
particular corruption). There is no supported way of determining what data
has been lost apart from analyzing the error messages from DBCC.
I recommend you open a case with PSS who can help you address the
corruptions and also help you determine what caused them.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bush" <anonymous@.discussions.microsoft.com> wrote in message
news:77FBEDC8-9B02-4CAB-970E-A96DC0F47EF9@.microsoft.com...
> Results show 0 allocation errors and 42 consistency errors that all seem
to be from one table. That is to the extent I understand the results. The
errors are 8929, 8939, 8965, 8964 all for the same object id and varying
text ids. The repair_fast and repair_rebuild did not work. Its calling for
the repair_with_data_loss. Are there any work arounds? Does this mean
that the table is corrupt? Is this a torn data page due to this table? If
I need to run the repair_with_data_loss is there an easy way to determine
which data has been lost? Welcome any feedback.
0. Ensure you have a backup strategy that you can use to recover from hardware failures (including
corruption). I recommend performing both database and log backup in most situations.
1. If you can run DBCC CHECKDB against the database: Search Books Online and KB for the error
numbers that CHECKDB gives you. There might be specific info for that type of error.
2. Find out why this happened. Check eventlog, do HW diagnostics etc.; search Books Online and KB
for those errors. You don't want this to happen again! If the database is suspect, the file might
have been in use by for instance an anti-virus program and restarting SQL Server might be all that
is needed - but you still want to read logs etc to find out what happened.
3. If there is a hardware problem, ensure the faulty hardware is replaced.
4. Backup the log. This assumes that log backup schedule is in place, of course. If the database is
suspect, then the NO_TRUNCATE option for the RESTORE command must be used. Also, you might want to
do a file backup of the mdf and ldf files, for extra safety.
5. Restore is the best thing to do now. If you managed to backup log as per step 4, then you will
most probably have zero dataloss. You should restore the latest clean database backup and the
subsequent log backups including the one taken in above step.
If the database isn't suspect, then DBCC with a REPAIR option might be a secondary option but this
will often result in loss of data. Additional solutions, depending on the errors, may be to manually
rebuild non-clustered indexes, manually drop and reload a table if the data is static, and so on.
If the database is suspect, a secondary option can be to try to "un-suspect" the database using
sp_resetstatus. Read about it (books online, KB, google etc). It might help but if the database is
too damaged, it might just pop back to suspect again. There's also something called "emergency mode"
which is a "panic" status you can set in order to try to get data out of a damaged database. I think
the name of that option speaks for itself. Again search the net for info.
If you feel uncertain with above steps, I recommend letting MS hand-hold you through the steps
appropriate for your particular situation.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bush" <anonymous@.discussions.microsoft.com> wrote in message
news:77FBEDC8-9B02-4CAB-970E-A96DC0F47EF9@.microsoft.com...
> Results show 0 allocation errors and 42 consistency errors that all seem to be from one table.
That is to the extent I understand the results. The errors are 8929, 8939, 8965, 8964 all for the
same object id and varying text ids. The repair_fast and repair_rebuild did not work. Its calling
for the repair_with_data_loss. Are there any work arounds? Does this mean that the table is
corrupt? Is this a torn data page due to this table? If I need to run the repair_with_data_loss is
there an easy way to determine which data has been lost? Welcome any feedback.|||Further to Tibor's comments, yes, you have corruption in your table. There
are no workarounds except restoring from a backup or running
repair_allow_data_loss (which will have to delete some data to fix this
particular corruption). There is no supported way of determining what data
has been lost apart from analyzing the error messages from DBCC.
I recommend you open a case with PSS who can help you address the
corruptions and also help you determine what caused them.
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bush" <anonymous@.discussions.microsoft.com> wrote in message
news:77FBEDC8-9B02-4CAB-970E-A96DC0F47EF9@.microsoft.com...
> Results show 0 allocation errors and 42 consistency errors that all seem
to be from one table. That is to the extent I understand the results. The
errors are 8929, 8939, 8965, 8964 all for the same object id and varying
text ids. The repair_fast and repair_rebuild did not work. Its calling for
the repair_with_data_loss. Are there any work arounds? Does this mean
that the table is corrupt? Is this a torn data page due to this table? If
I need to run the repair_with_data_loss is there an easy way to determine
which data has been lost? Welcome any feedback.
Subscribe to:
Posts (Atom)