I rebuilt index for my tables yersterday morning, today when I check using
SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I read tha
t
high logic fragmentaion is not good, why it happened?
ThanksIs this a Heap? How many pages in the index? Can you post the results of
DBCC SHOWCONTIG?
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
> Hi,
> I rebuilt index for my tables yersterday morning, today when I check using
> SHOWCONFIG, one of my table has more than 99% logic fragmentaion, I read
> that
> high logic fragmentaion is not good, why it happened?
> Thanks|||here is the result
I changed my table name and index name)table1 859150106 PK__table1__3429BB53 1
0 38408 NULL NULL NULL NULL NULL 0 5
935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
table1 859150106 IX_table1 15 0 6178 NUL
L NULL NULL NULL NULL 0 812 NULL NUL
L 95.079950799507998 773 813 99.579154968261719 NULL
table1 859150106 IX_table1_1 16 0 4327 N
ULL NULL NULL NULL NULL 0 595 NULL N
ULL 90.771812080536918 541 596 99.884445190429687 NULL
table1 859150106 IX_table1_2 25 0 6625 N
ULL NULL NULL NULL NULL 0 850 NULL N
ULL 97.414806110458287 829 851 99.864151000976563 NULL
table1 859150106 IX_table1_3 28 0 5046 N
ULL NULL NULL NULL NULL 0 787 NULL N
ULL 80.076142131979694 631 788 99.841461181640625 NULL
"Andrew J. Kelly" wrote:
> Is this a Heap? How many pages in the index? Can you post the results of
> DBCC SHOWCONTIG?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:D4962947-BA4B-4C83-BB2B-1E09FAD2BD1F@.microsoft.com...
>
>|||That is pretty hard to make out. It is better to show the regular output
and not with TABLERESULTS option. But it seems like you do have a clustered
index on this. What is the datatype of the Column(s) in the Clustered
index? Did you shrink the database or is the AutoShrink option turned on?
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...[vbcol=seagreen]
> here is the result
I changed my table name and index name)> table1 859150106 PK__table1__3429BB53 1 0 38408 NULL NULL NULL NULL NULL 0
> 5935 NULL NULL 80.879380053908363 4801 5936 99.614662170410156 NULL
> table1 859150106 IX_table1 15 0 6178 NULL NULL NULL NULL NULL 0 812 NULL
> NULL 95.079950799507998 773 813 99.579154968261719 NULL
> table1 859150106 IX_table1_1 16 0 4327 NULL NULL NULL NULL NULL 0 595 NULL
> NULL 90.771812080536918 541 596 99.884445190429687 NULL
> table1 859150106 IX_table1_2 25 0 6625 NULL NULL NULL NULL NULL 0 850 NULL
> NULL 97.414806110458287 829 851 99.864151000976563 NULL
> table1 859150106 IX_table1_3 28 0 5046 NULL NULL NULL NULL NULL 0 787 NULL
> NULL 80.076142131979694 631 788 99.841461181640625 NULL
>
> "Andrew J. Kelly" wrote:
>|||sorry, I do have a numerica field (primary key) clusterd index.
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 1, database ID: 3
TABLE level scan performed.
- Pages Scanned........................: 38433
- Extent Switches.......................: 5950
- Scan Density [Best Count:Actual Count]......: 80.74% [4805:5951]
- Logical Scan Fragmentation ..............: 99.56%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 15, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 6177
- Extent Switches.......................: 788
- Scan Density [Best Count:Actual Count]......: 97.97% [773:789]
- Logical Scan Fragmentation ..............: 99.58%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 16, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 4329
- Extent Switches.......................: 548
- Scan Density [Best Count:Actual Count]......: 98.72% [542:549]
- Logical Scan Fragmentation ..............: 99.88%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 25, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 6629
- Extent Switches.......................: 840
- Scan Density [Best Count:Actual Count]......: 98.57% [829:841]
- Logical Scan Fragmentation ..............: 99.86%
DBCC SHOWCONTIG scanning 'table1' table...
Table: 'table1' (859150106); index ID: 32, database ID: 3
LEAF level scan performed.
- Pages Scanned........................: 5046
- Extent Switches.......................: 638
- Scan Density [Best Count:Actual Count]......: 98.75% [631:639]
- Logical Scan Fragmentation ..............: 99.84%
"Andrew J. Kelly" wrote:
> That is pretty hard to make out. It is better to show the regular output
> and not with TABLERESULTS option. But it seems like you do have a cluster
ed
> index on this. What is the datatype of the Column(s) in the Clustered
> index? Did you shrink the database or is the AutoShrink option turned on?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
>
>|||And also the database has AutoShrink option on.
"Andrew J. Kelly" wrote:
> That is pretty hard to make out. It is better to show the regular output
> and not with TABLERESULTS option. But it seems like you do have a cluster
ed
> index on this. What is the datatype of the Column(s) in the Clustered
> index? Did you shrink the database or is the AutoShrink option turned on?
> --
> Andrew J. Kelly SQL MVP
>
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:0DE5034D-B921-4FBD-A7D4-DB548C4ACEE9@.microsoft.com...
>
>|||ok - here's what it looks like has happened:
1) you've rebuilt all your indexes and acheived near perfect fragmentation
2) shrink has run and totally reversed the fragmentation status - to be the
worst possible as far as DBCC SHOWCONTIG is concerned
Fragmentation is when the next logical page in an index is not the next
physical page in an index, moving from left to right logically (and from the
beginning of the file to the end, physically). Shrink starts at the end of
the file and works towards the front, moving single pages. Looks like its
made every page appear 'fragmented' to showcontig. Unfortunately, they are
also fragmented as the buffer pool sees them and so no readahead can occur
and your range scan performance will be terrible.
The simple lesson here is: don't ever run auto-shrink. The database needs
the extra space most of the time. Andrew - can you provide a link to Tibor's
'don't run shrink' webpage?
You should also read the whitepaper below which goes into detail on
fragmentation and when/how to get rid of 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.
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:4851F94F-34A2-48B7-8C2A-BBE678CF970E@.microsoft.com...[vbcol=seagreen]
> And also the database has AutoShrink option on.
> "Andrew J. Kelly" wrote:
>
output[vbcol=seagreen]
clustered[vbcol=seagreen]
on?[vbcol=seagreen]
NULL 0[vbcol=seagreen]
NULL[vbcol=seagreen]
NULL[vbcol=seagreen]
NULL[vbcol=seagreen]
NULL[vbcol=seagreen]
results[vbcol=seagreen]
check[vbcol=seagreen]|||Yes it was the shrinking that did it. Have a look here:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Andrew J. Kelly SQL MVP
"Jen" <Jen@.discussions.microsoft.com> wrote in message
news:A01DBDCB-EA16-4295-A69B-55A36DDE0412@.microsoft.com...[vbcol=seagreen]
> sorry, I do have a numerica field (primary key) clusterd index.
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 1, database ID: 3
> TABLE level scan performed.
> - Pages Scanned........................: 38433
> - Extent Switches.......................: 5950
> - Scan Density [Best Count:Actual Count]......: 80.74% [4805:5951
]
> - Logical Scan Fragmentation ..............: 99.56%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 15, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 6177
> - Extent Switches.......................: 788
> - Scan Density [Best Count:Actual Count]......: 97.97% [773:789]
> - Logical Scan Fragmentation ..............: 99.58%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 16, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 4329
> - Extent Switches.......................: 548
> - Scan Density [Best Count:Actual Count]......: 98.72% [542:549]
> - Logical Scan Fragmentation ..............: 99.88%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 25, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 6629
> - Extent Switches.......................: 840
> - Scan Density [Best Count:Actual Count]......: 98.57% [829:841]
> - Logical Scan Fragmentation ..............: 99.86%
> DBCC SHOWCONTIG scanning 'table1' table...
> Table: 'table1' (859150106); index ID: 32, database ID: 3
> LEAF level scan performed.
> - Pages Scanned........................: 5046
> - Extent Switches.......................: 638
> - Scan Density [Best Count:Actual Count]......: 98.75% [631:639]
> - Logical Scan Fragmentation ..............: 99.84%
> "Andrew J. Kelly" wrote:
>|||> Andrew - can you provide a link to Tibor's
> 'don't run shrink' webpage?
Here goes. I'd appreciate much if you let me know if you find anything incor
rect or that should be
expanded upon.
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:eOgH$vnAFHA.1452@.TK2MSFTNGP11.phx.gbl...
> ok - here's what it looks like has happened:
> 1) you've rebuilt all your indexes and acheived near perfect fragmentation
> 2) shrink has run and totally reversed the fragmentation status - to be th
e
> worst possible as far as DBCC SHOWCONTIG is concerned
> Fragmentation is when the next logical page in an index is not the next
> physical page in an index, moving from left to right logically (and from t
he
> beginning of the file to the end, physically). Shrink starts at the end of
> the file and works towards the front, moving single pages. Looks like its
> made every page appear 'fragmented' to showcontig. Unfortunately, they are
> also fragmented as the buffer pool sees them and so no readahead can occur
> and your range scan performance will be terrible.
> The simple lesson here is: don't ever run auto-shrink. The database needs
> the extra space most of the time. Andrew - can you provide a link to Tibor
's
> 'don't run shrink' webpage?
> You should also read the whitepaper below which goes into detail on
> fragmentation and when/how to get rid of it.
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
> Regards.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Jen" <Jen@.discussions.microsoft.com> wrote in message
> news:4851F94F-34A2-48B7-8C2A-BBE678CF970E@.microsoft.com...
> output
> clustered
> on?
> NULL 0
> NULL
> NULL
> NULL
> NULL
> results
> check
>
No comments:
Post a Comment