After running dbcc check db I am getting:
----
--
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:681848) in database ID 7 is marked allocated in the GAM, but no SG
AM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:681856) in database ID 7 is marked allocated in the GAM, but no SG
AM or IAM has allocated it.
.
.
.
.
CHECKDB found 2 allocation errors and 0 consistency errors in database.
repair_allow_data_loss is the minimum repair level for the errors found by D
BCC CHECKDB.
----
--
I also ran dbcc check with repair_allow_data_loss but it did not help me.
Thank you,
ktfoh dear.
Finding the object for that extent is at best a very difficult task.
I would first back up the corrupt database, then reorg it (rebuild the clust
ered indexes). If it is still not fixed it is very likely that the error is
on a heap table. I have had success adding clustered indexes to the heap t
ables and then dropping the
clustered index.
You final option may be to bcp all of the data out, empty all of the tables
and bcp it back in.|||With all due respect, none of what's below is relevant to this problem
(8905s) at all. The extent is marked allocated in the GAM but nothing is
actually using it. This also means its not available for anything to
allocate it because the allocation code already thinks its allocated.
There are no operations you can do to any of the tables which will affect
this extent - because none of the tables have it allocated and it's not
available for being allocated. The only way to fix this error is to restore
from your backups or to run repair. Ktf, which version of SQL Server are you
running on?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:86D05800-3256-4674-8610-3A473386D7AE@.microsoft.com...
> oh dear.
> Finding the object for that extent is at best a very difficult task.
> I would first back up the corrupt database, then reorg it (rebuild the
clustered indexes). If it is still not fixed it is very likely that the
error is on a heap table. I have had success adding clustered indexes to
the heap tables and then dropping the clustered index.
> You final option may be to bcp all of the data out, empty all of the
tables and bcp it back in.|||It is a clustered MS SQL 2K (active/passive) with its twin passive site to u
se for disasters using double-take as the real time replication software. I
did restore from the back up several times for different reason but even I r
ecreated a blank database w
ith lookup data only but I every time this error stayed there even doing dbc
c with repair option on. This is a mid size but complex database and I had a
significant improvement in performance. Again I have everything working per
fect for long time except t
his allocation error.
For my blank db I suppose to have 400 MB but I could not have less than 5 GB
“I think this causes it” but again it does not cause any other problems
just probably allocating 4 GB empty space. Since this is a production envir
onment and I had so many si
tes migrated I can not restore from any of my backups because the I do not k
now when that occurred. I did everything you can think of to clear this erro
r but I could not.
Thank you
ktf|||Paul:
Any questions, comments are welcome.
ktf
---
It is a clustered MS SQL 2K (active/passive) with its twin passive site to u
se for disasters using double-take as the real time replication software. I
did restore from the back up several times for different reason but even I r
ecreated a blank database w
ith lookup data only but I every time this error stayed there even doing dbc
c with repair option on. This is a mid size but complex database and I had a
significant improvement in performance. Again I have everything working per
fect for long time except t
his allocation error.
For my blank db I suppose to have 400 MB but I could not have less than 5 GB
“I think this causes it” but again it does not cause any other problems
just probably allocating 4 GB empty space. Since this is a production envir
onment and I had so many si
tes migrated I can not restore from any of my backups because the I do not k
now when that occurred. I did everything you can think of to clear this erro
r but I could not.
Thank you
ktf|||ok - you've unfortunately run up against a corruption state that I know
cannot be repaired (there are many flavors of 8905 but one in particular
cannot be repaired). It also seems that the corruption happened a while ago
and is in your backups. I suggest you call PSS, when you get to the SQL
support engineers tell them that you've been told by Paul Randal in SQL
Development in Redmond (they know who I am) that this seems to be an
unfixable 8905 corruption and they will help you (ask them to confirm with
me over email).
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ktf" <anonymous@.discussions.microsoft.com> wrote in message
news:05E009E5-201E-4D8D-9F2D-F402083A4D37@.microsoft.com...
> Paul:
> Any questions, comments are welcome.
> ktf
> ---
> It is a clustered MS SQL 2K (active/passive) with its twin passive site to
use for disasters using double-take as the real time replication software. I
did restore from the back up several times for different reason but even I
recreated a blank database with lookup data only but I every time this error
stayed there even doing dbcc with repair option on. This is a mid size but
complex database and I had a significant improvement in performance. Again I
have everything working perfect for long time except this allocation error.
> For my blank db I suppose to have 400 MB but I could not have less than 5
GB "I think this causes it" but again it does not cause any other problems
just probably allocating 4 GB empty space. Since this is a production
environment and I had so many sites migrated I can not restore from any of
my backups because the I do not know when that occurred. I did everything
you can think of to clear this error but I could not.
> Thank you
> ktf
>|||Paul,
We do have a Gold support service with Dell.
Should I go through them or Do you have the pss phone number?
Thank you,
ktf
Showing posts with label dbcc. Show all posts
Showing posts with label dbcc. Show all posts
Friday, March 23, 2012
Extent is marked allocated in the GAM (Server: Msg 8905)
After running dbcc check db I am getting
----
Server: Msg 8905, Level 16, State 1, Line
Extent (1:681848) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it
Server: Msg 8905, Level 16, State 1, Line
Extent (1:681856) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated itoh dear
Finding the object for that extent is at best a very difficult task
I would first back up the corrupt database, then reorg it (rebuild the clustered indexes). If it is still not fixed it is very likely that the error is on a heap table. I have had success adding clustered indexes to the heap tables and then dropping the clustered index
You final option may be to bcp all of the data out, empty all of the tables and bcp it back in.|||With all due respect, none of what's below is relevant to this problem
(8905s) at all. The extent is marked allocated in the GAM but nothing is
actually using it. This also means its not available for anything to
allocate it because the allocation code already thinks its allocated.
There are no operations you can do to any of the tables which will affect
this extent - because none of the tables have it allocated and it's not
available for being allocated. The only way to fix this error is to restore
from your backups or to run repair. Ktf, which version of SQL Server are you
running on?
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:86D05800-3256-4674-8610-3A473386D7AE@.microsoft.com...
> oh dear.
> Finding the object for that extent is at best a very difficult task.
> I would first back up the corrupt database, then reorg it (rebuild the
clustered indexes). If it is still not fixed it is very likely that the
error is on a heap table. I have had success adding clustered indexes to
the heap tables and then dropping the clustered index.
> You final option may be to bcp all of the data out, empty all of the
tables and bcp it back in.|||ok - you've unfortunately run up against a corruption state that I know
cannot be repaired (there are many flavors of 8905 but one in particular
cannot be repaired). It also seems that the corruption happened a while ago
and is in your backups. I suggest you call PSS, when you get to the SQL
support engineers tell them that you've been told by Paul Randal in SQL
Development in Redmond (they know who I am) that this seems to be an
unfixable 8905 corruption and they will help you (ask them to confirm with
me over email).
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ktf" <anonymous@.discussions.microsoft.com> wrote in message
news:05E009E5-201E-4D8D-9F2D-F402083A4D37@.microsoft.com...
> Paul:
> Any questions, comments are welcome.
> ktf
> ---
> It is a clustered MS SQL 2K (active/passive) with its twin passive site to
use for disasters using double-take as the real time replication software. I
did restore from the back up several times for different reason but even I
recreated a blank database with lookup data only but I every time this error
stayed there even doing dbcc with repair option on. This is a mid size but
complex database and I had a significant improvement in performance. Again I
have everything working perfect for long time except this allocation error.
> For my blank db I suppose to have 400 MB but I could not have less than 5
GB "I think this causes it" but again it does not cause any other problems
just probably allocating 4 GB empty space. Since this is a production
environment and I had so many sites migrated I can not restore from any of
my backups because the I do not know when that occurred. I did everything
you can think of to clear this error but I could not.
> Thank you
> ktf
>|||Paul
We do have a Gold support service with Dell.
Should I go through them or Do you have the pss phone number
Thank you
ktf
----
Server: Msg 8905, Level 16, State 1, Line
Extent (1:681848) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it
Server: Msg 8905, Level 16, State 1, Line
Extent (1:681856) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated itoh dear
Finding the object for that extent is at best a very difficult task
I would first back up the corrupt database, then reorg it (rebuild the clustered indexes). If it is still not fixed it is very likely that the error is on a heap table. I have had success adding clustered indexes to the heap tables and then dropping the clustered index
You final option may be to bcp all of the data out, empty all of the tables and bcp it back in.|||With all due respect, none of what's below is relevant to this problem
(8905s) at all. The extent is marked allocated in the GAM but nothing is
actually using it. This also means its not available for anything to
allocate it because the allocation code already thinks its allocated.
There are no operations you can do to any of the tables which will affect
this extent - because none of the tables have it allocated and it's not
available for being allocated. The only way to fix this error is to restore
from your backups or to run repair. Ktf, which version of SQL Server are you
running on?
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:86D05800-3256-4674-8610-3A473386D7AE@.microsoft.com...
> oh dear.
> Finding the object for that extent is at best a very difficult task.
> I would first back up the corrupt database, then reorg it (rebuild the
clustered indexes). If it is still not fixed it is very likely that the
error is on a heap table. I have had success adding clustered indexes to
the heap tables and then dropping the clustered index.
> You final option may be to bcp all of the data out, empty all of the
tables and bcp it back in.|||ok - you've unfortunately run up against a corruption state that I know
cannot be repaired (there are many flavors of 8905 but one in particular
cannot be repaired). It also seems that the corruption happened a while ago
and is in your backups. I suggest you call PSS, when you get to the SQL
support engineers tell them that you've been told by Paul Randal in SQL
Development in Redmond (they know who I am) that this seems to be an
unfixable 8905 corruption and they will help you (ask them to confirm with
me over email).
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ktf" <anonymous@.discussions.microsoft.com> wrote in message
news:05E009E5-201E-4D8D-9F2D-F402083A4D37@.microsoft.com...
> Paul:
> Any questions, comments are welcome.
> ktf
> ---
> It is a clustered MS SQL 2K (active/passive) with its twin passive site to
use for disasters using double-take as the real time replication software. I
did restore from the back up several times for different reason but even I
recreated a blank database with lookup data only but I every time this error
stayed there even doing dbcc with repair option on. This is a mid size but
complex database and I had a significant improvement in performance. Again I
have everything working perfect for long time except this allocation error.
> For my blank db I suppose to have 400 MB but I could not have less than 5
GB "I think this causes it" but again it does not cause any other problems
just probably allocating 4 GB empty space. Since this is a production
environment and I had so many sites migrated I can not restore from any of
my backups because the I do not know when that occurred. I did everything
you can think of to clear this error but I could not.
> Thank you
> ktf
>|||Paul
We do have a Gold support service with Dell.
Should I go through them or Do you have the pss phone number
Thank you
ktf
Extent is marked allocated in the GAM (Server: Msg 8905)
After running dbcc check db I am getting:
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:681848) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:681856) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
..
..
..
..
CHECKDB found 2 allocation errors and 0 consistency errors in database.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB.
I also ran dbcc check with repair_allow_data_loss but it did not help me.
Thank you,
ktf
oh dear.
Finding the object for that extent is at best a very difficult task.
I would first back up the corrupt database, then reorg it (rebuild the clustered indexes). If it is still not fixed it is very likely that the error is on a heap table. I have had success adding clustered indexes to the heap tables and then dropping the
clustered index.
You final option may be to bcp all of the data out, empty all of the tables and bcp it back in.
|||With all due respect, none of what's below is relevant to this problem
(8905s) at all. The extent is marked allocated in the GAM but nothing is
actually using it. This also means its not available for anything to
allocate it because the allocation code already thinks its allocated.
There are no operations you can do to any of the tables which will affect
this extent - because none of the tables have it allocated and it's not
available for being allocated. The only way to fix this error is to restore
from your backups or to run repair. Ktf, which version of SQL Server are you
running on?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:86D05800-3256-4674-8610-3A473386D7AE@.microsoft.com...
> oh dear.
> Finding the object for that extent is at best a very difficult task.
> I would first back up the corrupt database, then reorg it (rebuild the
clustered indexes). If it is still not fixed it is very likely that the
error is on a heap table. I have had success adding clustered indexes to
the heap tables and then dropping the clustered index.
> You final option may be to bcp all of the data out, empty all of the
tables and bcp it back in.
|||It is a clustered MS SQL 2K (active/passive) with its twin passive site to use for disasters using double-take as the real time replication software. I did restore from the back up several times for different reason but even I recreated a blank database w
ith lookup data only but I every time this error stayed there even doing dbcc with repair option on. This is a mid size but complex database and I had a significant improvement in performance. Again I have everything working perfect for long time except t
his allocation error.
For my blank db I suppose to have 400 MB but I could not have less than 5 GB “I think this causes it” but again it does not cause any other problems just probably allocating 4 GB empty space. Since this is a production environment and I had so many si
tes migrated I can not restore from any of my backups because the I do not know when that occurred. I did everything you can think of to clear this error but I could not.
Thank you
ktf
|||Paul:
Any questions, comments are welcome.
ktf
It is a clustered MS SQL 2K (active/passive) with its twin passive site to use for disasters using double-take as the real time replication software. I did restore from the back up several times for different reason but even I recreated a blank database w
ith lookup data only but I every time this error stayed there even doing dbcc with repair option on. This is a mid size but complex database and I had a significant improvement in performance. Again I have everything working perfect for long time except t
his allocation error.
For my blank db I suppose to have 400 MB but I could not have less than 5 GB “I think this causes it” but again it does not cause any other problems just probably allocating 4 GB empty space. Since this is a production environment and I had so many si
tes migrated I can not restore from any of my backups because the I do not know when that occurred. I did everything you can think of to clear this error but I could not.
Thank you
ktf
|||ok - you've unfortunately run up against a corruption state that I know
cannot be repaired (there are many flavors of 8905 but one in particular
cannot be repaired). It also seems that the corruption happened a while ago
and is in your backups. I suggest you call PSS, when you get to the SQL
support engineers tell them that you've been told by Paul Randal in SQL
Development in Redmond (they know who I am) that this seems to be an
unfixable 8905 corruption and they will help you (ask them to confirm with
me over email).
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ktf" <anonymous@.discussions.microsoft.com> wrote in message
news:05E009E5-201E-4D8D-9F2D-F402083A4D37@.microsoft.com...
> Paul:
> Any questions, comments are welcome.
> ktf
> It is a clustered MS SQL 2K (active/passive) with its twin passive site to
use for disasters using double-take as the real time replication software. I
did restore from the back up several times for different reason but even I
recreated a blank database with lookup data only but I every time this error
stayed there even doing dbcc with repair option on. This is a mid size but
complex database and I had a significant improvement in performance. Again I
have everything working perfect for long time except this allocation error.
> For my blank db I suppose to have 400 MB but I could not have less than 5
GB "I think this causes it" but again it does not cause any other problems
just probably allocating 4 GB empty space. Since this is a production
environment and I had so many sites migrated I can not restore from any of
my backups because the I do not know when that occurred. I did everything
you can think of to clear this error but I could not.
> Thank you
> ktf
>
|||Paul,
We do have a Gold support service with Dell.
Should I go through them or Do you have the pss phone number?
Thank you,
ktf
sql
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:681848) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
Server: Msg 8905, Level 16, State 1, Line 1
Extent (1:681856) in database ID 7 is marked allocated in the GAM, but no SGAM or IAM has allocated it.
..
..
..
..
CHECKDB found 2 allocation errors and 0 consistency errors in database.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB.
I also ran dbcc check with repair_allow_data_loss but it did not help me.
Thank you,
ktf
oh dear.
Finding the object for that extent is at best a very difficult task.
I would first back up the corrupt database, then reorg it (rebuild the clustered indexes). If it is still not fixed it is very likely that the error is on a heap table. I have had success adding clustered indexes to the heap tables and then dropping the
clustered index.
You final option may be to bcp all of the data out, empty all of the tables and bcp it back in.
|||With all due respect, none of what's below is relevant to this problem
(8905s) at all. The extent is marked allocated in the GAM but nothing is
actually using it. This also means its not available for anything to
allocate it because the allocation code already thinks its allocated.
There are no operations you can do to any of the tables which will affect
this extent - because none of the tables have it allocated and it's not
available for being allocated. The only way to fix this error is to restore
from your backups or to run repair. Ktf, which version of SQL Server are you
running on?
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:86D05800-3256-4674-8610-3A473386D7AE@.microsoft.com...
> oh dear.
> Finding the object for that extent is at best a very difficult task.
> I would first back up the corrupt database, then reorg it (rebuild the
clustered indexes). If it is still not fixed it is very likely that the
error is on a heap table. I have had success adding clustered indexes to
the heap tables and then dropping the clustered index.
> You final option may be to bcp all of the data out, empty all of the
tables and bcp it back in.
|||It is a clustered MS SQL 2K (active/passive) with its twin passive site to use for disasters using double-take as the real time replication software. I did restore from the back up several times for different reason but even I recreated a blank database w
ith lookup data only but I every time this error stayed there even doing dbcc with repair option on. This is a mid size but complex database and I had a significant improvement in performance. Again I have everything working perfect for long time except t
his allocation error.
For my blank db I suppose to have 400 MB but I could not have less than 5 GB “I think this causes it” but again it does not cause any other problems just probably allocating 4 GB empty space. Since this is a production environment and I had so many si
tes migrated I can not restore from any of my backups because the I do not know when that occurred. I did everything you can think of to clear this error but I could not.
Thank you
ktf
|||Paul:
Any questions, comments are welcome.
ktf
It is a clustered MS SQL 2K (active/passive) with its twin passive site to use for disasters using double-take as the real time replication software. I did restore from the back up several times for different reason but even I recreated a blank database w
ith lookup data only but I every time this error stayed there even doing dbcc with repair option on. This is a mid size but complex database and I had a significant improvement in performance. Again I have everything working perfect for long time except t
his allocation error.
For my blank db I suppose to have 400 MB but I could not have less than 5 GB “I think this causes it” but again it does not cause any other problems just probably allocating 4 GB empty space. Since this is a production environment and I had so many si
tes migrated I can not restore from any of my backups because the I do not know when that occurred. I did everything you can think of to clear this error but I could not.
Thank you
ktf
|||ok - you've unfortunately run up against a corruption state that I know
cannot be repaired (there are many flavors of 8905 but one in particular
cannot be repaired). It also seems that the corruption happened a while ago
and is in your backups. I suggest you call PSS, when you get to the SQL
support engineers tell them that you've been told by Paul Randal in SQL
Development in Redmond (they know who I am) that this seems to be an
unfixable 8905 corruption and they will help you (ask them to confirm with
me over email).
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"ktf" <anonymous@.discussions.microsoft.com> wrote in message
news:05E009E5-201E-4D8D-9F2D-F402083A4D37@.microsoft.com...
> Paul:
> Any questions, comments are welcome.
> ktf
> It is a clustered MS SQL 2K (active/passive) with its twin passive site to
use for disasters using double-take as the real time replication software. I
did restore from the back up several times for different reason but even I
recreated a blank database with lookup data only but I every time this error
stayed there even doing dbcc with repair option on. This is a mid size but
complex database and I had a significant improvement in performance. Again I
have everything working perfect for long time except this allocation error.
> For my blank db I suppose to have 400 MB but I could not have less than 5
GB "I think this causes it" but again it does not cause any other problems
just probably allocating 4 GB empty space. Since this is a production
environment and I had so many sites migrated I can not restore from any of
my backups because the I do not know when that occurred. I did everything
you can think of to clear this error but I could not.
> Thank you
> ktf
>
|||Paul,
We do have a Gold support service with Dell.
Should I go through them or Do you have the pss phone number?
Thank you,
ktf
sql
Extent Fragmentation High Just After Reindexing
Hello. When reviewing the DBCC SHOWCONTIG immediately after reindexing all indexes on a database, I see the ExtentFragmentation has values like 50 to 70%... These are SQL 2005 tables with clustered PK's, no large varchars/blobs, and at least 100 pages in the index... The numbers related to PAGE fragmentation are ok after reindexing, but not the EXTENT fragmentation numbers.
I noticed the drive is in need of being defragged at the disk level. Is that a reason why reindexing doesn't fix the Extent frag numbers? ANy other ideas on this? I can try defragging the DISK over the weekend, bringing the database offline then, but any other thougths on why the Extents show these high %'s? Is there any command to reset them and maybe that isn't happening? Like must I do update usage to get valid Extent frag #'s?
If there were MANY autogrows on the files, is that a different level of fragmentation? and how could all those small pieces of files be pulled back together? Thanks, Bruce
Correct me if I'm wrong, but since its not a heap, I'm not sure the old extent fragmentation column is meaningful, in terms of SQL fragmentation. You're in 2005, use sys.dm_db_index_physical_stats instead. The avg_fragmentation_in_pct column shows logical fragmentation for indexes and extent fragmentation for heaps.
Code Snippet
use <database name here>
select * from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'<table name here>'),null, null,null)
This may show you a more application fragmentation percent.
Running your NTFS defrag tool in the OS won't hurt, but you are correct that will have to bring all apps to a halt including SQL.
Physical and logical fragmentation due to autogrow has a lot to do with existing fragmentation, available disk space and activity.
|||
W, thanks. Accoring to the SQL 2005 BOL snip below, ExtentFragmentation is not applicable to heaps and when the index spans multiple files. All of the indexes in question are NOT heaps and have a single MDF file. I'm doing other tests, like using the ALTER INDEX REORGANIZE command, and will try fully dropping and recreating the index, so I see a number closer to 0% Extent Fragmentation. I'd think ExtentFragmentation still matters, but if you'er saying to use the new functions, and the SHOWCONTIG is showing bogus data, hmmm, great, I'll try to find ExtentFragmentation in those functions too... Thanks, Bruce
ExtentFragmentation
Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.
ok, here is a specific example, with SQL I used and results... If anyone has any ideas on WHY the Extent Fragmentation numbers act like this, please let me know. Maybe they ARE just BOGUS completely and meaningless in SQL 2005? Here are the exact steps I did... all on SQL 2005 SP2, database has a single data file, and "MyTable" has a clustered PK and 2 other indexes.
Step 1: Ran a SHOWCONTIG to see the fragmentation level.
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 314
- Extents Scanned..............................: 46
- Extent Switches..............................: 189
- Avg. Pages per Extent........................: 6.8
- Scan Density [Best Count:Actual Count].......: 21.05% [40:190]
- Logical Scan Fragmentation ..................: 54.14%
- Extent Scan Fragmentation ...................: 45.65%
- Avg. Bytes Free per Page.....................: 2890.9
- Avg. Page Density (full).....................: 64.28%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that 2 of teh indexes have 21% and 36% scan density values. So, this is my starting point, and I want to DEFRAG this tabloe, all 3 indexes.
Step 2: Ran an ALTER INDEX on all 3, with the REBUILD option.
Code Snippet
ALTER INDEX XPKtMyTable ON dbo.tMyTableREBUILD
GO
ALTER INDEX XIF2tMyTable ON dbo.tMyTable
REBUILD
GO
ALTER INDEX XIF5tMyTable ON dbo.tMyTable
REBUILD
GO
Step 3: Ran another SHOWCONTIG
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 46.15%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that the Scan Density of teh first 2 indexes changed to 100%, but the 3rd didn't defrag for some reason?!? Also, note that the Extent Scan Fragmentation numbers are still high, up to 72% on the 3rd index.
Step 4: Dropped and recreated the 3 indexes
Code Snippet
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XPKtMyTable')
ALTER TABLE [dbo].[tMyTable] DROP CONSTRAINT [XPKtMyTable]
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF2tMyTable')
DROP INDEX [XIF2tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF5tMyTable')
DROP INDEX [XIF5tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
ALTER TABLE [dbo].[tMyTable] ADD CONSTRAINT [XPKtMyTable] PRIMARY KEY CLUSTERED
( [MyTableID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF2tMyTable] ON [dbo].[tMyTable]
( [coCode] ASC,
[DefID] ASC,
[GroupID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF5tMyTable] ON [dbo].[tMyTable]
( [liabilityID] ASC,
[coCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Step 5: Ran the SHOWCONTIG again
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 38.46%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 45.45%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note the Extent Scan Fragmentation numbers are better, but not good, and that 3rd index is still at 36% Scan Density.
Step 6: Used the new sys.dm_db_index_physical_stats function to compare to the SHOWCONTIG values. (Broke it up into 4 queries for ease in reading here)
Code Snippet
select database_id, object_id, index_id, partition_number, index_type_desc
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
database_id object_id index_id partition_number index_type_desc
-- -- -- -
21 1125175354 1 1 CLUSTERED INDEX
21 1125175354 3 1 NONCLUSTERED INDEX
21 1125175354 6 1 NONCLUSTERED INDEX
alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent
-- -- -
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 25.8064516129032
fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count
-- -- -- --
11 18.6363636363636 205 NULL NULL
3 14 42 NULL NULL
9 3.44444444444444 31 NULL NULL
ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
-- -- -
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
That function doesn't appear to provide as much info as SHOWCONTIG, but maybe there are more functions to get the ExtentFrag % values...
ok, anyone have any ideas on this? Do I even care if Extent Frag numbers are high? and wondering why on some tables that have like 28,000 rows, they get very page-fragged after about only 200 row changes. I did this locally on a laptop also and defragged my drive in between and that had no effect on the SHOWCONTIG numbers. IF the autogrow was very small in the past and expanded many times, how would I see that now? In older versions of SQL Server, there was a table that maintained those "extents" (?), anything like that now, and is that a possible reason for fragmentation also?
Other ideas/comments are appreciated!! Thanks, Bruce
|||Dunno about why index 6 is showing that fragmentation, but your table not only looks pretty tight, but pretty small. 205 pages. Is fragmentation really a concern?I was also under the impression that fragmentation of the clustered index was the chief concern anyway, and that looks tight right now. Pretty sure the stated fragmentation of nonclustered indexes is even meaningful.
Bruce dBA wrote:
IF the autogrow was very small in the past and expanded many times, how would I see that now?"
You should be able to see "Autogrow of file" in the log if you're auditing that.|||
W, yes, I should have done that example on a larger table. The clustered index is the main importance, true, was just wondering why the other indexes get "fragged" so quickly with a small amount of updates (like 200 updates on a 28,000 row table)... The autogrow cauing fragmentation question, was more about before I started checking this out, the autogrow is set better now, but I don't know what it WAS before, and was just wondering if having a lot of small autogrowths in the past added to this, even though reindexing is done, aren't the MDF/NDF files fragged also possibly. In older releases of SQL Server, I remember that data was stored in catalog tables, each new "autogrowth" back then, not sure if you can historically see that, and how files fragging relates to index fragging.... Thanks, Bruce|||
Try using a fill factor of 90%. You are getting fragmentation because you are filling the pages at 100%.
Extent Fragmentation High Just After Reindexing
Hello. When reviewing the DBCC SHOWCONTIG immediately after reindexing all indexes on a database, I see the ExtentFragmentation has values like 50 to 70%... These are SQL 2005 tables with clustered PK's, no large varchars/blobs, and at least 100 pages in the index... The numbers related to PAGE fragmentation are ok after reindexing, but not the EXTENT fragmentation numbers.
I noticed the drive is in need of being defragged at the disk level. Is that a reason why reindexing doesn't fix the Extent frag numbers? ANy other ideas on this? I can try defragging the DISK over the weekend, bringing the database offline then, but any other thougths on why the Extents show these high %'s? Is there any command to reset them and maybe that isn't happening? Like must I do update usage to get valid Extent frag #'s?
If there were MANY autogrows on the files, is that a different level of fragmentation? and how could all those small pieces of files be pulled back together? Thanks, Bruce
Correct me if I'm wrong, but since its not a heap, I'm not sure the old extent fragmentation column is meaningful, in terms of SQL fragmentation. You're in 2005, use sys.dm_db_index_physical_stats instead. The avg_fragmentation_in_pct column shows logical fragmentation for indexes and extent fragmentation for heaps.
Code Snippet
use <database name here>
select * from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'<table name here>'),null, null,null)
This may show you a more application fragmentation percent.
Running your NTFS defrag tool in the OS won't hurt, but you are correct that will have to bring all apps to a halt including SQL.
Physical and logical fragmentation due to autogrow has a lot to do with existing fragmentation, available disk space and activity.
|||
W, thanks. Accoring to the SQL 2005 BOL snip below, ExtentFragmentation is not applicable to heaps and when the index spans multiple files. All of the indexes in question are NOT heaps and have a single MDF file. I'm doing other tests, like using the ALTER INDEX REORGANIZE command, and will try fully dropping and recreating the index, so I see a number closer to 0% Extent Fragmentation. I'd think ExtentFragmentation still matters, but if you'er saying to use the new functions, and the SHOWCONTIG is showing bogus data, hmmm, great, I'll try to find ExtentFragmentation in those functions too... Thanks, Bruce
ExtentFragmentation
Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.
ok, here is a specific example, with SQL I used and results... If anyone has any ideas on WHY the Extent Fragmentation numbers act like this, please let me know. Maybe they ARE just BOGUS completely and meaningless in SQL 2005? Here are the exact steps I did... all on SQL 2005 SP2, database has a single data file, and "MyTable" has a clustered PK and 2 other indexes.
Step 1: Ran a SHOWCONTIG to see the fragmentation level.
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 314
- Extents Scanned..............................: 46
- Extent Switches..............................: 189
- Avg. Pages per Extent........................: 6.8
- Scan Density [Best Count:Actual Count].......: 21.05% [40:190]
- Logical Scan Fragmentation ..................: 54.14%
- Extent Scan Fragmentation ...................: 45.65%
- Avg. Bytes Free per Page.....................: 2890.9
- Avg. Page Density (full).....................: 64.28%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 50.00%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that 2 of teh indexes have 21% and 36% scan density values. So, this is my starting point, and I want to DEFRAG this tabloe, all 3 indexes.
Step 2: Ran an ALTER INDEX on all 3, with the REBUILD option.
Code Snippet
ALTER INDEX XPKtMyTable ON dbo.tMyTableREBUILD
GO
ALTER INDEX XIF2tMyTable ON dbo.tMyTable
REBUILD
GO
ALTER INDEX XIF5tMyTable ON dbo.tMyTable
REBUILD
GO
Step 3: Ran another SHOWCONTIG
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 46.15%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 66.67%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 72.73%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note that the Scan Density of teh first 2 indexes changed to 100%, but the 3rd didn't defrag for some reason?!? Also, note that the Extent Scan Fragmentation numbers are still high, up to 72% on the 3rd index.
Step 4: Dropped and recreated the 3 indexes
Code Snippet
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XPKtMyTable')
ALTER TABLE [dbo].[tMyTable] DROP CONSTRAINT [XPKtMyTable]
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF2tMyTable')
DROP INDEX [XIF2tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
IF EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tMyTable]') AND name = N'XIF5tMyTable')
DROP INDEX [XIF5tMyTable] ON [dbo].[tMyTable] WITH ( ONLINE = OFF )
ALTER TABLE [dbo].[tMyTable] ADD CONSTRAINT [XPKtMyTable] PRIMARY KEY CLUSTERED
( [MyTableID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF2tMyTable] ON [dbo].[tMyTable]
( [coCode] ASC,
[DefID] ASC,
[GroupID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [XIF5tMyTable] ON [dbo].[tMyTable]
( [liabilityID] ASC,
[coCode] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Step 5: Ran the SHOWCONTIG again
Code Snippet
print 'DBCC SHOWCONTIG ([tMyTable])'
DBCC SHOWCONTIG (tMyTable) WITH ALL_INDEXES
go
DBCC SHOWCONTIG ([tMyTable])
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 1, database ID: 21
TABLE level scan performed.
- Pages Scanned................................: 205
- Extents Scanned..............................: 26
- Extent Switches..............................: 25
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 100.00% [26:26]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 38.46%
- Avg. Bytes Free per Page.....................: 123.2
- Avg. Page Density (full).....................: 98.48%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 3, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 42
- Extents Scanned..............................: 6
- Extent Switches..............................: 5
- Avg. Pages per Extent........................: 7.0
- Scan Density [Best Count:Actual Count].......: 100.00% [6:6]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 33.33%
- Avg. Bytes Free per Page.....................: 135.0
- Avg. Page Density (full).....................: 98.33%
DBCC SHOWCONTIG scanning 'tMyTable' table...
Table: 'tMyTable' (1125175354); index ID: 6, database ID: 21
LEAF level scan performed.
- Pages Scanned................................: 31
- Extents Scanned..............................: 11
- Extent Switches..............................: 10
- Avg. Pages per Extent........................: 2.8
- Scan Density [Best Count:Actual Count].......: 36.36% [4:11]
- Logical Scan Fragmentation ..................: 25.81%
- Extent Scan Fragmentation ...................: 45.45%
- Avg. Bytes Free per Page.....................: 214.1
- Avg. Page Density (full).....................: 97.36%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Note the Extent Scan Fragmentation numbers are better, but not good, and that 3rd index is still at 36% Scan Density.
Step 6: Used the new sys.dm_db_index_physical_stats function to compare to the SHOWCONTIG values. (Broke it up into 4 queries for ease in reading here)
Code Snippet
select database_id, object_id, index_id, partition_number, index_type_desc
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select alloc_unit_type_desc, index_depth, index_level, avg_fragmentation_in_percent
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select fragment_count, avg_fragment_size_in_pages, page_count, avg_page_space_used_in_percent, record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
select ghost_record_count, version_ghost_record_count, min_record_size_in_bytes, max_record_size_in_bytes, avg_record_size_in_bytes, forwarded_record_count
from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID(N'tMyTable'),null, null,null)
database_id object_id index_id partition_number index_type_desc
-- -- -- -
21 1125175354 1 1 CLUSTERED INDEX
21 1125175354 3 1 NONCLUSTERED INDEX
21 1125175354 6 1 NONCLUSTERED INDEX
alloc_unit_type_desc index_depth index_level avg_fragmentation_in_percent
-- -- -
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 0
IN_ROW_DATA 2 0 25.8064516129032
fragment_count avg_fragment_size_in_pages page_count avg_page_space_used_in_percent record_count
-- -- -- --
11 18.6363636363636 205 NULL NULL
3 14 42 NULL NULL
9 3.44444444444444 31 NULL NULL
ghost_record_count version_ghost_record_count min_record_size_in_bytes max_record_size_in_bytes avg_record_size_in_bytes forwarded_record_count
-- -- -
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL NULL
That function doesn't appear to provide as much info as SHOWCONTIG, but maybe there are more functions to get the ExtentFrag % values...
ok, anyone have any ideas on this? Do I even care if Extent Frag numbers are high? and wondering why on some tables that have like 28,000 rows, they get very page-fragged after about only 200 row changes. I did this locally on a laptop also and defragged my drive in between and that had no effect on the SHOWCONTIG numbers. IF the autogrow was very small in the past and expanded many times, how would I see that now? In older versions of SQL Server, there was a table that maintained those "extents" (?), anything like that now, and is that a possible reason for fragmentation also?
Other ideas/comments are appreciated!! Thanks, Bruce
|||Dunno about why index 6 is showing that fragmentation, but your table not only looks pretty tight, but pretty small. 205 pages. Is fragmentation really a concern?I was also under the impression that fragmentation of the clustered index was the chief concern anyway, and that looks tight right now. Pretty sure the stated fragmentation of nonclustered indexes is even meaningful.
IF the autogrow was very small in the past and expanded many times, how would I see that now?"
You should be able to see "Autogrow of file" in the log if you're auditing that.|||
W, yes, I should have done that example on a larger table. The clustered index is the main importance, true, was just wondering why the other indexes get "fragged" so quickly with a small amount of updates (like 200 updates on a 28,000 row table)... The autogrow cauing fragmentation question, was more about before I started checking this out, the autogrow is set better now, but I don't know what it WAS before, and was just wondering if having a lot of small autogrowths in the past added to this, even though reindexing is done, aren't the MDF/NDF files fragged also possibly. In older releases of SQL Server, I remember that data was stored in catalog tables, each new "autogrowth" back then, not sure if you can historically see that, and how files fragging relates to index fragging.... Thanks, Bruce|||
Try using a fill factor of 90%. You are getting fragmentation because you are filling the pages at 100%.
Subscribe to:
Posts (Atom)