Is there a means to determine to what extent an existing
SQL table has been populated with the FullText indexing
service .... so to try to assess how long an
incremental population may take ?
Thanks
PhilipPhilip,
Yes, you can use one or more the system metadata, such as
FullTextCatalogProperty('<FT_Catalog_Name>', 'populatestatus') to monitor FT
Populations.
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext
Regards,
John
"Philip" <plippard@.nc.rr.com> wrote in message
news:02b301c34f27$15108970$a301280a@.phx.gbl...
> Is there a means to determine to what extent an existing
> SQL table has been populated with the FullText indexing
> service .... so to try to assess how long an
> incremental population may take ?
> Thanks
> Philip
Showing posts with label extent. Show all posts
Showing posts with label extent. Show all posts
Friday, March 23, 2012
Extent of Dundas functionality
I'm planning to implement MS RS and to take advantage of the charting
supplied by Dundas in my web application. My question is this:
Is the chart control available for use on web pages not created via MS
RS? I will be running MS RS and MS SQL on one machine and IIS on
another. In my web app I have need to make some simple bar charts on
one page.No, the Dundas chart that shipd with Reporting Services is only available in
the context of a report. If you would like programmitic control over the
chart in your application, you would need to license it seperately.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"MarkMurphy" <murphy@.murphysw.com> wrote in message
news:e061eb57.0406180948.687427ca@.posting.google.com...
> I'm planning to implement MS RS and to take advantage of the charting
> supplied by Dundas in my web application. My question is this:
> Is the chart control available for use on web pages not created via MS
> RS? I will be running MS RS and MS SQL on one machine and IIS on
> another. In my web app I have need to make some simple bar charts on
> one page.sql
supplied by Dundas in my web application. My question is this:
Is the chart control available for use on web pages not created via MS
RS? I will be running MS RS and MS SQL on one machine and IIS on
another. In my web app I have need to make some simple bar charts on
one page.No, the Dundas chart that shipd with Reporting Services is only available in
the context of a report. If you would like programmitic control over the
chart in your application, you would need to license it seperately.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"MarkMurphy" <murphy@.murphysw.com> wrote in message
news:e061eb57.0406180948.687427ca@.posting.google.com...
> I'm planning to implement MS RS and to take advantage of the charting
> supplied by Dundas in my web application. My question is this:
> Is the chart control available for use on web pages not created via MS
> RS? I will be running MS RS and MS SQL on one machine and IIS on
> another. In my web app I have need to make some simple bar charts on
> one page.sql
extent locks
hi
Im getting exclusive locks on my table and ext is the resource locked......
can anyone explain me..why do we get ext locks on tables.
regardjust means SQL server has identified a group of 8 pages(extent) for update insert(most likely upadte and insert), create or drop destined for an exclusive lock. More efficient than locking an individual page of a contiguous set of 8 pages|||They are most likely Intent locks and essentially help to prevent things
like deadlocks.
--
Andrew J. Kelly SQL MVP
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:24647640-DE56-4A51-BAE5-74088CD31DE1@.microsoft.com...
> hi,
> Im getting exclusive locks on my table and ext is the resource
locked.......
> can anyone explain me..why do we get ext locks on tables..
> regards
>
Im getting exclusive locks on my table and ext is the resource locked......
can anyone explain me..why do we get ext locks on tables.
regardjust means SQL server has identified a group of 8 pages(extent) for update insert(most likely upadte and insert), create or drop destined for an exclusive lock. More efficient than locking an individual page of a contiguous set of 8 pages|||They are most likely Intent locks and essentially help to prevent things
like deadlocks.
--
Andrew J. Kelly SQL MVP
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:24647640-DE56-4A51-BAE5-74088CD31DE1@.microsoft.com...
> hi,
> Im getting exclusive locks on my table and ext is the resource
locked.......
> can anyone explain me..why do we get ext locks on tables..
> regards
>
extent locks
hi,
Im getting exclusive locks on my table and ext is the resource locked......
.
can anyone explain me..why do we get ext locks on tables..
regardsjust means SQL server has identified a group of 8 pages(extent) for update i
nsert(most likely upadte and insert), create or drop destined for an exclusi
ve lock. More efficient than locking an individual page of a contiguous set
of 8 pages.|||They are most likely Intent locks and essentially help to prevent things
like deadlocks.
Andrew J. Kelly SQL MVP
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:24647640-DE56-4A51-BAE5-74088CD31DE1@.microsoft.com...
> hi,
> Im getting exclusive locks on my table and ext is the resource
locked.......
> can anyone explain me..why do we get ext locks on tables..
> regards
>
Im getting exclusive locks on my table and ext is the resource locked......
.
can anyone explain me..why do we get ext locks on tables..
regardsjust means SQL server has identified a group of 8 pages(extent) for update i
nsert(most likely upadte and insert), create or drop destined for an exclusi
ve lock. More efficient than locking an individual page of a contiguous set
of 8 pages.|||They are most likely Intent locks and essentially help to prevent things
like deadlocks.
Andrew J. Kelly SQL MVP
"san" <anonymous@.discussions.microsoft.com> wrote in message
news:24647640-DE56-4A51-BAE5-74088CD31DE1@.microsoft.com...
> hi,
> Im getting exclusive locks on my table and ext is the resource
locked.......
> can anyone explain me..why do we get ext locks on tables..
> regards
>
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 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
----
--
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
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 still high after index drop/create
hello everyone,
we dropped the clustered & nonclustered indeces on a table, then
rebuilt them. logical fragmentation is near zero, but extent
fragmentation is about 40%. how can this be if the indeces are brand
new?Here's a whitepaper you should read:
http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
Also, what effect is the extent fragmentation having on the performance of
your workload? (i.e. do you have empricial evidence that the workload perf
is decreasing with increasing extent fragmentation - to justify the overhead
of these index operations?)
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
<gary@.dba723.net> wrote in message
news:fb0df63.0310090956.62c752a7@.posting.google.co m...
> hello everyone,
> we dropped the clustered & nonclustered indeces on a table, then
> rebuilt them. logical fragmentation is near zero, but extent
> fragmentation is about 40%. how can this be if the indeces are brand
> new?|||Thanks for the response, Paul!
A particular query on my test server with logical and extent frag near
zero ran in about 12 seconds (very complex query). The same query on
my reporting box took over a minute, with some of the largest tables
included at about 40% extent frag, near zero logical frag.
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message news:<3f86d98b$1@.news.microsoft.com>...
> Here's a whitepaper you should read:
> http://www.microsoft.com/technet/tr...ze/ss2kidbp.asp
> Also, what effect is the extent fragmentation having on the performance of
> your workload? (i.e. do you have empricial evidence that the workload perf
> is decreasing with increasing extent fragmentation - to justify the overhead
> of these index operations?)
> Regards,
> Paul.
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> <gary@.dba723.net> wrote in message
> news:fb0df63.0310090956.62c752a7@.posting.google.co m...
> > hello everyone,
> > we dropped the clustered & nonclustered indeces on a table, then
> > rebuilt them. logical fragmentation is near zero, but extent
> > fragmentation is about 40%. how can this be if the indeces are brand
> > new?
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%.
Extent Fragmentation
I am currently trying to defrag a table and am unable to
reduce Extent fragmentation.
The structure of the table is as follows.
CREATE TABLE [dbo].[PortfolioMember] (
[PortfolioID] [int] NOT NULL ,
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
CLUSTERED
(
[PortfolioID],
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
NONCLUSTERED
(
[ID],
[PortfolioID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [PortfolioMember_FK] ON [dbo].
[PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
FOREIGN KEY
(
[PortfolioID]
) REFERENCES [dbo].[Portfolio] (
[PortfolioID]
)
GO
So it has a clustered index.
The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
DBCC SHOWCONTIG scanning 'PortfolioMember' table...
Table: 'PortfolioMember' (1429580131); index ID: 1,
database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 26048
- Extents Scanned.......................: 3277
- Extent Switches.......................: 3283
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.15%
[3256:3284]
- Logical Scan Fragmentation ..............: 0.03%
- Extent Scan Fragmentation ...............: 90.51%
- Avg. Bytes Free per Page................: 802.9
- Avg. Page Density (full)................: 90.08%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I have tried the following all with no success at
reducing Extent fragmentation:
DBCC DBREINDEX (portfoliomember, '', 90)
DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
PK_PORTFOLIOMEMBER)
I have executed sp_spaceused and there is plenty of room.
I am currently rebuilding the table but don't want to
have to do this to properly defrag the table.
Does anyone have any suggestions?
Thanks in advance.
Jamie
jamie.downs@.risk.sungard.comJamie,
A couple of questions first. Is this the entire table? I assume it has
more columns but were left out for simplicity sake. If so it is usually
best to shoe the actual DDL so there are no assumptions. If [ID] is unique
then why not just make it the PK and drop the PortfolofioID? Why do you
create duplicate indexes? You have a PK constraint and a Unique constraint
on the two columns. This will make an index already to enforce the
constraint. There is no need to add a second index to each one. The last
comment is that I assume you have multiple files in your file group and that
is what is giving you the Extent fragmentation. This value is basically
useless when you have multiple files.
--
Andrew J. Kelly
SQL Server MVP
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> I am currently trying to defrag a table and am unable to
> reduce Extent fragmentation.
> The structure of the table is as follows.
> CREATE TABLE [dbo].[PortfolioMember] (
> [PortfolioID] [int] NOT NULL ,
> [ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> CLUSTERED
> (
> [PortfolioID],
> [ID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> NONCLUSTERED
> (
> [ID],
> [PortfolioID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [PortfolioMember_FK] ON [dbo].
> [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> FOREIGN KEY
> (
> [PortfolioID]
> ) REFERENCES [dbo].[Portfolio] (
> [PortfolioID]
> )
> GO
> So it has a clustered index.
> The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> Table: 'PortfolioMember' (1429580131); index ID: 1,
> database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 26048
> - Extents Scanned.......................: 3277
> - Extent Switches.......................: 3283
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.15%
> [3256:3284]
> - Logical Scan Fragmentation ..............: 0.03%
> - Extent Scan Fragmentation ...............: 90.51%
> - Avg. Bytes Free per Page................: 802.9
> - Avg. Page Density (full)................: 90.08%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> I have tried the following all with no success at
> reducing Extent fragmentation:
> DBCC DBREINDEX (portfoliomember, '', 90)
> DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> PK_PORTFOLIOMEMBER)
> I have executed sp_spaceused and there is plenty of room.
> I am currently rebuilding the table but don't want to
> have to do this to properly defrag the table.
> Does anyone have any suggestions?
> Thanks in advance.
> Jamie
> jamie.downs@.risk.sungard.com
>
>|||As a follow-on to what Andrew said, the extent fragmentation algorithm
inside DBCC SHOWCONTIG specifically does not work when an index/table spans
multiple files and this is stated in BooksOnLine.
You should read the whitepaper on fragmentation and how-to/whether-to
address it at:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$44jy$kDHA.684@.TK2MSFTNGP09.phx.gbl...
> Jamie,
> A couple of questions first. Is this the entire table? I assume it has
> more columns but were left out for simplicity sake. If so it is usually
> best to shoe the actual DDL so there are no assumptions. If [ID] is
unique
> then why not just make it the PK and drop the PortfolofioID? Why do you
> create duplicate indexes? You have a PK constraint and a Unique
constraint
> on the two columns. This will make an index already to enforce the
> constraint. There is no need to add a second index to each one. The last
> comment is that I assume you have multiple files in your file group and
that
> is what is giving you the Extent fragmentation. This value is basically
> useless when you have multiple files.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Jamie" <jamie.downs@.risk.sungard.com> wrote in message
> news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> > I am currently trying to defrag a table and am unable to
> > reduce Extent fragmentation.
> >
> > The structure of the table is as follows.
> >
> > CREATE TABLE [dbo].[PortfolioMember] (
> > [PortfolioID] [int] NOT NULL ,
> > [ID] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> > CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> > CLUSTERED
> > (
> > [PortfolioID],
> > [ID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> > NONCLUSTERED
> > (
> > [ID],
> > [PortfolioID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [PortfolioMember_FK] ON [dbo].
> > [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> > ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> > FOREIGN KEY
> > (
> > [PortfolioID]
> > ) REFERENCES [dbo].[Portfolio] (
> > [PortfolioID]
> > )
> > GO
> >
> > So it has a clustered index.
> >
> > The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> >
> > DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> > Table: 'PortfolioMember' (1429580131); index ID: 1,
> > database ID: 7
> > TABLE level scan performed.
> > - Pages Scanned........................: 26048
> > - Extents Scanned.......................: 3277
> > - Extent Switches.......................: 3283
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 99.15%
> > [3256:3284]
> > - Logical Scan Fragmentation ..............: 0.03%
> > - Extent Scan Fragmentation ...............: 90.51%
> > - Avg. Bytes Free per Page................: 802.9
> > - Avg. Page Density (full)................: 90.08%
> > DBCC execution completed. If DBCC printed error messages,
> > contact your system administrator.
> >
> > I have tried the following all with no success at
> > reducing Extent fragmentation:
> >
> > DBCC DBREINDEX (portfoliomember, '', 90)
> >
> > DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> >
> > DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> > PK_PORTFOLIOMEMBER)
> >
> > I have executed sp_spaceused and there is plenty of room.
> >
> > I am currently rebuilding the table but don't want to
> > have to do this to properly defrag the table.
> >
> > Does anyone have any suggestions?
> >
> > Thanks in advance.
> >
> > Jamie
> > jamie.downs@.risk.sungard.com
> >
> >
> >
> >
>sql
reduce Extent fragmentation.
The structure of the table is as follows.
CREATE TABLE [dbo].[PortfolioMember] (
[PortfolioID] [int] NOT NULL ,
[ID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
CLUSTERED
(
[PortfolioID],
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
NONCLUSTERED
(
[ID],
[PortfolioID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [PortfolioMember_FK] ON [dbo].
[PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[PortfolioMember] ADD
CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
FOREIGN KEY
(
[PortfolioID]
) REFERENCES [dbo].[Portfolio] (
[PortfolioID]
)
GO
So it has a clustered index.
The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
DBCC SHOWCONTIG scanning 'PortfolioMember' table...
Table: 'PortfolioMember' (1429580131); index ID: 1,
database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 26048
- Extents Scanned.......................: 3277
- Extent Switches.......................: 3283
- Avg. Pages per Extent..................: 7.9
- Scan Density [Best Count:Actual Count]......: 99.15%
[3256:3284]
- Logical Scan Fragmentation ..............: 0.03%
- Extent Scan Fragmentation ...............: 90.51%
- Avg. Bytes Free per Page................: 802.9
- Avg. Page Density (full)................: 90.08%
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
I have tried the following all with no success at
reducing Extent fragmentation:
DBCC DBREINDEX (portfoliomember, '', 90)
DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
PK_PORTFOLIOMEMBER)
I have executed sp_spaceused and there is plenty of room.
I am currently rebuilding the table but don't want to
have to do this to properly defrag the table.
Does anyone have any suggestions?
Thanks in advance.
Jamie
jamie.downs@.risk.sungard.comJamie,
A couple of questions first. Is this the entire table? I assume it has
more columns but were left out for simplicity sake. If so it is usually
best to shoe the actual DDL so there are no assumptions. If [ID] is unique
then why not just make it the PK and drop the PortfolofioID? Why do you
create duplicate indexes? You have a PK constraint and a Unique constraint
on the two columns. This will make an index already to enforce the
constraint. There is no need to add a second index to each one. The last
comment is that I assume you have multiple files in your file group and that
is what is giving you the Extent fragmentation. This value is basically
useless when you have multiple files.
--
Andrew J. Kelly
SQL Server MVP
"Jamie" <jamie.downs@.risk.sungard.com> wrote in message
news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> I am currently trying to defrag a table and am unable to
> reduce Extent fragmentation.
> The structure of the table is as follows.
> CREATE TABLE [dbo].[PortfolioMember] (
> [PortfolioID] [int] NOT NULL ,
> [ID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> CLUSTERED
> (
> [PortfolioID],
> [ID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> NONCLUSTERED
> (
> [ID],
> [PortfolioID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [PortfolioMember_FK] ON [dbo].
> [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[PortfolioMember] ADD
> CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> FOREIGN KEY
> (
> [PortfolioID]
> ) REFERENCES [dbo].[Portfolio] (
> [PortfolioID]
> )
> GO
> So it has a clustered index.
> The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> Table: 'PortfolioMember' (1429580131); index ID: 1,
> database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 26048
> - Extents Scanned.......................: 3277
> - Extent Switches.......................: 3283
> - Avg. Pages per Extent..................: 7.9
> - Scan Density [Best Count:Actual Count]......: 99.15%
> [3256:3284]
> - Logical Scan Fragmentation ..............: 0.03%
> - Extent Scan Fragmentation ...............: 90.51%
> - Avg. Bytes Free per Page................: 802.9
> - Avg. Page Density (full)................: 90.08%
> DBCC execution completed. If DBCC printed error messages,
> contact your system administrator.
> I have tried the following all with no success at
> reducing Extent fragmentation:
> DBCC DBREINDEX (portfoliomember, '', 90)
> DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> PK_PORTFOLIOMEMBER)
> I have executed sp_spaceused and there is plenty of room.
> I am currently rebuilding the table but don't want to
> have to do this to properly defrag the table.
> Does anyone have any suggestions?
> Thanks in advance.
> Jamie
> jamie.downs@.risk.sungard.com
>
>|||As a follow-on to what Andrew said, the extent fragmentation algorithm
inside DBCC SHOWCONTIG specifically does not work when an index/table spans
multiple files and this is stated in BooksOnLine.
You should read the whitepaper on fragmentation and how-to/whether-to
address it at:
http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/optimize/ss2kidbp.asp
Regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$44jy$kDHA.684@.TK2MSFTNGP09.phx.gbl...
> Jamie,
> A couple of questions first. Is this the entire table? I assume it has
> more columns but were left out for simplicity sake. If so it is usually
> best to shoe the actual DDL so there are no assumptions. If [ID] is
unique
> then why not just make it the PK and drop the PortfolofioID? Why do you
> create duplicate indexes? You have a PK constraint and a Unique
constraint
> on the two columns. This will make an index already to enforce the
> constraint. There is no need to add a second index to each one. The last
> comment is that I assume you have multiple files in your file group and
that
> is what is giving you the Extent fragmentation. This value is basically
> useless when you have multiple files.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Jamie" <jamie.downs@.risk.sungard.com> wrote in message
> news:0ee801c393e8$463c0460$a301280a@.phx.gbl...
> > I am currently trying to defrag a table and am unable to
> > reduce Extent fragmentation.
> >
> > The structure of the table is as follows.
> >
> > CREATE TABLE [dbo].[PortfolioMember] (
> > [PortfolioID] [int] NOT NULL ,
> > [ID] [int] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] WITH NOCHECK ADD
> > CONSTRAINT [PK_PORTFOLIOMEMBER] PRIMARY KEY
> > CLUSTERED
> > (
> > [PortfolioID],
> > [ID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [AK_IDPFOL_PORTFOLI] UNIQUE
> > NONCLUSTERED
> > (
> > [ID],
> > [PortfolioID]
> > ) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [PortfolioMember_FK] ON [dbo].
> > [PortfolioMember]([ID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > CREATE INDEX [has_members_FK] ON [dbo].[PortfolioMember]
> > ([PortfolioID]) WITH FILLFACTOR = 90 ON [PRIMARY]
> > GO
> >
> > ALTER TABLE [dbo].[PortfolioMember] ADD
> > CONSTRAINT [FK_PORTFOLI_RELATION__PORTFOLI]
> > FOREIGN KEY
> > (
> > [PortfolioID]
> > ) REFERENCES [dbo].[Portfolio] (
> > [PortfolioID]
> > )
> > GO
> >
> > So it has a clustered index.
> >
> > The Best I can get from DBCC SHOWCONTIG (PortfolioMember)
> >
> > DBCC SHOWCONTIG scanning 'PortfolioMember' table...
> > Table: 'PortfolioMember' (1429580131); index ID: 1,
> > database ID: 7
> > TABLE level scan performed.
> > - Pages Scanned........................: 26048
> > - Extents Scanned.......................: 3277
> > - Extent Switches.......................: 3283
> > - Avg. Pages per Extent..................: 7.9
> > - Scan Density [Best Count:Actual Count]......: 99.15%
> > [3256:3284]
> > - Logical Scan Fragmentation ..............: 0.03%
> > - Extent Scan Fragmentation ...............: 90.51%
> > - Avg. Bytes Free per Page................: 802.9
> > - Avg. Page Density (full)................: 90.08%
> > DBCC execution completed. If DBCC printed error messages,
> > contact your system administrator.
> >
> > I have tried the following all with no success at
> > reducing Extent fragmentation:
> >
> > DBCC DBREINDEX (portfoliomember, '', 90)
> >
> > DBCC DBREINDEX (portfoliomember, 'PK_PORTFOLIOMEMBER', 90)
> >
> > DBCC INDEXDEFRAG (CRS_BaseDev, portfoliomember,
> > PK_PORTFOLIOMEMBER)
> >
> > I have executed sp_spaceused and there is plenty of room.
> >
> > I am currently rebuilding the table but don't want to
> > have to do this to properly defrag the table.
> >
> > Does anyone have any suggestions?
> >
> > Thanks in advance.
> >
> > Jamie
> > jamie.downs@.risk.sungard.com
> >
> >
> >
> >
>sql
Subscribe to:
Posts (Atom)