Hi ALL,
Somehow one of tables in my database with clustered index got tempered and the pageid are not in proper order. I tried the checkdb command with repair_rebuild option but it is giving the following error.
Table error: Object ID 1984530649, index ID 1. Page (1:254682) is missing a reference from previous page (1:254681). Possible chain linkage problem.
Server: Msg 8936, Level 16, State 1, Line 1
Table error: Object ID 1984530649, index ID 1. B-tree chain linkage mismatch. (1:254680)->next = (1:256198), but (1:256198)->Prev = (1:256197).
Not able to reindex the index of table. I can not even export the data to any other table with same structure.
Please help me.........
Thanks,
PracheerPls take a look at
http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=6046
:)|||Thanks for the help. I will try all the scenarios. Hope that will solve my problem.
:)
Showing posts with label index. Show all posts
Showing posts with label index. Show all posts
Wednesday, March 7, 2012
Reorganizing/Rebuilding an index seems to have no effect
We have multiple database, one per customer with the same schema, and we
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database Architect
Sounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>
|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM
|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect
|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>
|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM
|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database Architect
Sounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>
|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM
|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect
|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>
|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM
|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM
Reorganizing/Rebuilding an index seems to have no effect
We have multiple database, one per customer with the same schema, and we
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database ArchitectSounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM|||Why are the queries so sensitive to fragmentation? Are they scanning the
table? If so, have you done anything about tuning them?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23WOqawZzHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Nope, it has a clustered index. The reorganize/rebuild works fine on the
> table in one database, but does nothing in another database.
> MTM
>|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database ArchitectSounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM|||Why are the queries so sensitive to fragmentation? Are they scanning the
table? If so, have you done anything about tuning them?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23WOqawZzHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Nope, it has a clustered index. The reorganize/rebuild works fine on the
> table in one database, but does nothing in another database.
> MTM
>|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM
Reorganizing/Rebuilding an index seems to have no effect
We have multiple database, one per customer with the same schema, and we
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database ArchitectSounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM|||Why are the queries so sensitive to fragmentation? Are they scanning the
table? If so, have you done anything about tuning them?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23WOqawZzHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Nope, it has a clustered index. The reorganize/rebuild works fine on the
> table in one database, but does nothing in another database.
> MTM
>|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM|||If you're doing a defrag , don't forget the statistics update
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OJSaclJ1HHA.1204@.TK2MSFTNGP03.phx.gbl...
> I'm going to re-examine this particular issue after we have updated the
> schema which will be sometime in October.
> Thanks.
> MTM
>
have one particular table that gets fragmented very quickly and which has a
huge impact on performance. So we are having to reorganize/rebuld the
indexes on a regular basis, however in one database the fragmentation of the
indexes doesn't seem to change after attempting to reorganize/rebuild, they
remain high. I even dropped one of the offending indexes, and checked the
fragmentation for all the other indexes, all very low, and then recreated
the index that had been dropped and suddenly the fragmentation went back up
again.
In all other databases, the reorganize/rebuild seems to work fine and will
reduce the fragmentation of the indexes as expected.
Anyone ever come across something like this or who can explain the behaviour
we are seeing?
TIA
Michael MacGregor
Database ArchitectSounds like that particular table does not have a clustered index and the
others do. Check to see if that is a heap.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23Wa52nVzHHA.3448@.TK2MSFTNGP03.phx.gbl...
> We have multiple database, one per customer with the same schema, and we
> have one particular table that gets fragmented very quickly and which has
> a huge impact on performance. So we are having to reorganize/rebuld the
> indexes on a regular basis, however in one database the fragmentation of
> the indexes doesn't seem to change after attempting to reorganize/rebuild,
> they remain high. I even dropped one of the offending indexes, and checked
> the fragmentation for all the other indexes, all very low, and then
> recreated the index that had been dropped and suddenly the fragmentation
> went back up again.
> In all other databases, the reorganize/rebuild seems to work fine and will
> reduce the fragmentation of the indexes as expected.
> Anyone ever come across something like this or who can explain the
> behaviour we are seeing?
> TIA
> Michael MacGregor
> Database Architect
>|||Nope, it has a clustered index. The reorganize/rebuild works fine on the
table in one database, but does nothing in another database.
MTM|||Why are the queries so sensitive to fragmentation? Are they scanning the
table? If so, have you done anything about tuning them?
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
Benchmark your query performance
http://www.SQLBenchmarkPro.com
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:%23WOqawZzHHA.4824@.TK2MSFTNGP02.phx.gbl...
> Nope, it has a clustered index. The reorganize/rebuild works fine on the
> table in one database, but does nothing in another database.
> MTM
>|||The schema leaves a lot to be desired to be honest. The PKs on every table
are GUIDs so they become fragmented very quickly. In addition very few of
the queries, stored procs, are not optimized, but we have limited time and
resources to get these sorted out.
Anyway, that's kind of beside the point. We don't understand why, when an
index is reported as being highly fragmented, >50%, that ALTER INDEX, or
DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't a
showstopper by any means but we find it odd and would love to know why,
unfortunately, again due to limited time and resources, we can't investigate
this further other than to simply put the question out there to see if
anyone has experienced this and/or knows anything about it.
Regards,
Michael MacGregor
Database Architect|||Any chance this index has less than 8 pages in it? Can we see the showcontig
output? Is the clustered index on the Guid? If so you may want to place it
on better suited column.
--
Andrew J. Kelly SQL MVP
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:uTqFvNgzHHA.4712@.TK2MSFTNGP04.phx.gbl...
> The schema leaves a lot to be desired to be honest. The PKs on every table
> are GUIDs so they become fragmented very quickly. In addition very few of
> the queries, stored procs, are not optimized, but we have limited time and
> resources to get these sorted out.
> Anyway, that's kind of beside the point. We don't understand why, when an
> index is reported as being highly fragmented, >50%, that ALTER INDEX, or
> DBCC INDEXDEFRAG has absolutely no impact on the fragmentation. This isn't
> a showstopper by any means but we find it odd and would love to know why,
> unfortunately, again due to limited time and resources, we can't
> investigate this further other than to simply put the question out there
> to see if anyone has experienced this and/or knows anything about it.
> Regards,
> Michael MacGregor
> Database Architect
>|||I'll post the showcontig info later, I have to go to a meeting so I just
have time to say yes the clustered index is on the GUID and yes that is
something that has to be addressed and will be in the very near future.
However, there is another table that isn't clustered on the GUID and we get
the same behaviour. Anyway, I will post more later.
MTM|||I'm going to re-examine this particular issue after we have updated the
schema which will be sometime in October.
Thanks.
MTM|||If you're doing a defrag , don't forget the statistics update
--
Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com/SQL
"Michael MacGregor" <nospam@.nospam.com> wrote in message
news:OJSaclJ1HHA.1204@.TK2MSFTNGP03.phx.gbl...
> I'm going to re-examine this particular issue after we have updated the
> schema which will be sometime in October.
> Thanks.
> MTM
>
Reorganize vs. REbuild Index Question
OK, I am assuming that by reorganizing my indexes it will do just that to
optimize the indexes vs. rebuild will do the same but it will also let me
adjust the amount of free space for the index. If I continue to use the
default free space % would these tasks not have the exact same result? Also,
the time it takes to do a reorganize vs. a rebuild would be the same? Is
there anything I am misunderstanding about this maintainenace plan task that
I want to select one or the other?DBCC DBREINDEX does a somewhat better job, but locks the table.
DBCC INDEXDEFRAG does almost as well, but can run while the users accesses
the table/index
However, I beleive you're looking at the SQL Server 2000 maintenance Plan
Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
DROP/CREATE INDEX to rebuild.
Note that the amount of benifite you'll get depends on how the indexes are
being used. OLTP, not so much, DSS potentially a lot.
I suppose they would both take the same amount of time, however, you would
not change the free space % for that reason. Change free space % to a larger
value if the table is insert heavy and to a smaller value the more static
the table is. It goes to performance when using the system.
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
> OK, I am assuming that by reorganizing my indexes it will do just that to
> optimize the indexes vs. rebuild will do the same but it will also let me
> adjust the amount of free space for the index. If I continue to use the
> default free space % would these tasks not have the exact same result?
> Also,
> the time it takes to do a reorganize vs. a rebuild would be the same? Is
> there anything I am misunderstanding about this maintainenace plan task
> that
> I want to select one or the other?
>|||> However, I beleive you're looking at the SQL Server 2000 maintenance Plan Wizard which uses
> DBREINDEX to reorg and (I'll be corrected if I'm wrong) DROP/CREATE INDEX to rebuild.
DBREINDEX *is* CREATE followed by DROP (internally). There's no choice in 2000 MP regarding how you
want the "optimization" to be done. MP does DBREINDEX for all indexes in the database.
May I also suggest for Richard below reading:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:%23XBqVax6HHA.5752@.TK2MSFTNGP04.phx.gbl...
> DBCC DBREINDEX does a somewhat better job, but locks the table.
> DBCC INDEXDEFRAG does almost as well, but can run while the users accesses the table/index
> However, I beleive you're looking at the SQL Server 2000 maintenance Plan Wizard which uses
> DBREINDEX to reorg and (I'll be corrected if I'm wrong) DROP/CREATE INDEX to rebuild.
> Note that the amount of benifite you'll get depends on how the indexes are being used. OLTP, not
> so much, DSS potentially a lot.
> I suppose they would both take the same amount of time, however, you would not change the free
> space % for that reason. Change free space % to a larger value if the table is insert heavy and to
> a smaller value the more static the table is. It goes to performance when using the system.
> "Richard K" <RichardK@.discussions.microsoft.com> wrote in message
> news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
>> OK, I am assuming that by reorganizing my indexes it will do just that to
>> optimize the indexes vs. rebuild will do the same but it will also let me
>> adjust the amount of free space for the index. If I continue to use the
>> default free space % would these tasks not have the exact same result? Also,
>> the time it takes to do a reorganize vs. a rebuild would be the same? Is
>> there anything I am misunderstanding about this maintainenace plan task that
>> I want to select one or the other?
>|||Thank you Tibor, I was close. Still wrong, but close.
I read the link a while ago and found it to be excellent reading, well worth
your time.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:D4C5F3BF-ABFC-4A96-B47F-CD5467322302@.microsoft.com...
>> However, I beleive you're looking at the SQL Server 2000 maintenance Plan
>> Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
>> DROP/CREATE INDEX to rebuild.
> DBREINDEX *is* CREATE followed by DROP (internally). There's no choice in
> 2000 MP regarding how you want the "optimization" to be done. MP does
> DBREINDEX for all indexes in the database.
> May I also suggest for Richard below reading:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23XBqVax6HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> DBCC DBREINDEX does a somewhat better job, but locks the table.
>> DBCC INDEXDEFRAG does almost as well, but can run while the users
>> accesses the table/index
>> However, I beleive you're looking at the SQL Server 2000 maintenance Plan
>> Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
>> DROP/CREATE INDEX to rebuild.
>> Note that the amount of benifite you'll get depends on how the indexes
>> are being used. OLTP, not so much, DSS potentially a lot.
>> I suppose they would both take the same amount of time, however, you
>> would not change the free space % for that reason. Change free space % to
>> a larger value if the table is insert heavy and to a smaller value the
>> more static the table is. It goes to performance when using the system.
>> "Richard K" <RichardK@.discussions.microsoft.com> wrote in message
>> news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
>> OK, I am assuming that by reorganizing my indexes it will do just that
>> to
>> optimize the indexes vs. rebuild will do the same but it will also let
>> me
>> adjust the amount of free space for the index. If I continue to use the
>> default free space % would these tasks not have the exact same result?
>> Also,
>> the time it takes to do a reorganize vs. a rebuild would be the same?
>> Is
>> there anything I am misunderstanding about this maintainenace plan task
>> that
>> I want to select one or the other?
>>
>
optimize the indexes vs. rebuild will do the same but it will also let me
adjust the amount of free space for the index. If I continue to use the
default free space % would these tasks not have the exact same result? Also,
the time it takes to do a reorganize vs. a rebuild would be the same? Is
there anything I am misunderstanding about this maintainenace plan task that
I want to select one or the other?DBCC DBREINDEX does a somewhat better job, but locks the table.
DBCC INDEXDEFRAG does almost as well, but can run while the users accesses
the table/index
However, I beleive you're looking at the SQL Server 2000 maintenance Plan
Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
DROP/CREATE INDEX to rebuild.
Note that the amount of benifite you'll get depends on how the indexes are
being used. OLTP, not so much, DSS potentially a lot.
I suppose they would both take the same amount of time, however, you would
not change the free space % for that reason. Change free space % to a larger
value if the table is insert heavy and to a smaller value the more static
the table is. It goes to performance when using the system.
"Richard K" <RichardK@.discussions.microsoft.com> wrote in message
news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
> OK, I am assuming that by reorganizing my indexes it will do just that to
> optimize the indexes vs. rebuild will do the same but it will also let me
> adjust the amount of free space for the index. If I continue to use the
> default free space % would these tasks not have the exact same result?
> Also,
> the time it takes to do a reorganize vs. a rebuild would be the same? Is
> there anything I am misunderstanding about this maintainenace plan task
> that
> I want to select one or the other?
>|||> However, I beleive you're looking at the SQL Server 2000 maintenance Plan Wizard which uses
> DBREINDEX to reorg and (I'll be corrected if I'm wrong) DROP/CREATE INDEX to rebuild.
DBREINDEX *is* CREATE followed by DROP (internally). There's no choice in 2000 MP regarding how you
want the "optimization" to be done. MP does DBREINDEX for all indexes in the database.
May I also suggest for Richard below reading:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <spam@.nospam.org> wrote in message news:%23XBqVax6HHA.5752@.TK2MSFTNGP04.phx.gbl...
> DBCC DBREINDEX does a somewhat better job, but locks the table.
> DBCC INDEXDEFRAG does almost as well, but can run while the users accesses the table/index
> However, I beleive you're looking at the SQL Server 2000 maintenance Plan Wizard which uses
> DBREINDEX to reorg and (I'll be corrected if I'm wrong) DROP/CREATE INDEX to rebuild.
> Note that the amount of benifite you'll get depends on how the indexes are being used. OLTP, not
> so much, DSS potentially a lot.
> I suppose they would both take the same amount of time, however, you would not change the free
> space % for that reason. Change free space % to a larger value if the table is insert heavy and to
> a smaller value the more static the table is. It goes to performance when using the system.
> "Richard K" <RichardK@.discussions.microsoft.com> wrote in message
> news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
>> OK, I am assuming that by reorganizing my indexes it will do just that to
>> optimize the indexes vs. rebuild will do the same but it will also let me
>> adjust the amount of free space for the index. If I continue to use the
>> default free space % would these tasks not have the exact same result? Also,
>> the time it takes to do a reorganize vs. a rebuild would be the same? Is
>> there anything I am misunderstanding about this maintainenace plan task that
>> I want to select one or the other?
>|||Thank you Tibor, I was close. Still wrong, but close.
I read the link a while ago and found it to be excellent reading, well worth
your time.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:D4C5F3BF-ABFC-4A96-B47F-CD5467322302@.microsoft.com...
>> However, I beleive you're looking at the SQL Server 2000 maintenance Plan
>> Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
>> DROP/CREATE INDEX to rebuild.
> DBREINDEX *is* CREATE followed by DROP (internally). There's no choice in
> 2000 MP regarding how you want the "optimization" to be done. MP does
> DBREINDEX for all indexes in the database.
> May I also suggest for Richard below reading:
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <spam@.nospam.org> wrote in message
> news:%23XBqVax6HHA.5752@.TK2MSFTNGP04.phx.gbl...
>> DBCC DBREINDEX does a somewhat better job, but locks the table.
>> DBCC INDEXDEFRAG does almost as well, but can run while the users
>> accesses the table/index
>> However, I beleive you're looking at the SQL Server 2000 maintenance Plan
>> Wizard which uses DBREINDEX to reorg and (I'll be corrected if I'm wrong)
>> DROP/CREATE INDEX to rebuild.
>> Note that the amount of benifite you'll get depends on how the indexes
>> are being used. OLTP, not so much, DSS potentially a lot.
>> I suppose they would both take the same amount of time, however, you
>> would not change the free space % for that reason. Change free space % to
>> a larger value if the table is insert heavy and to a smaller value the
>> more static the table is. It goes to performance when using the system.
>> "Richard K" <RichardK@.discussions.microsoft.com> wrote in message
>> news:BD5711A0-F263-4294-8728-01FC94A02E53@.microsoft.com...
>> OK, I am assuming that by reorganizing my indexes it will do just that
>> to
>> optimize the indexes vs. rebuild will do the same but it will also let
>> me
>> adjust the amount of free space for the index. If I continue to use the
>> default free space % would these tasks not have the exact same result?
>> Also,
>> the time it takes to do a reorganize vs. a rebuild would be the same?
>> Is
>> there anything I am misunderstanding about this maintainenace plan task
>> that
>> I want to select one or the other?
>>
>
Reorganize Index Task - SelectedDatabases expression format
Hi
I am trying to configure the SelectedDatabases property of the Reorganize Index Task using an expression.
The Expressions property of the task provides the ability to configure the SelectedDatabases property of the task using an expression. The properties pane shows that the type of the SelectedDatabases property should be a "(Collection)" (which is edited using the 'Object Collection Editor').
How do I create an expression to configure the SelectedDatabases property? Can I build the collection in text? Or do I need to provide a variable of type System.Object that contains a collection type (and if so exactly what type should it contain)?
TIA . . . Ed
No, you cannot execute an expression successfully for the SelectedDatabases property of the Reorganize Index Task, and to generalize, any "(Collection)" property, and even more generally, any non value-type task property (excepting strings).The workaround for these types of properties (like the StringCollection
being referred to in this question) is to use the self-modifying
package technique as outlined here:
http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/12/31/17731.aspx
Or course, one could roll a custom task with an expression-able property of type string, which depending on your familiarity with custom tasks, may be a reasonable choice.
Reorganize index pages never completes
There are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and t
he
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury"Tracy McKibben" wrote:
> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.
> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.
> - is your database configured to auto-grow when needed?
Yes.
> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.
> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||I used to have this function turned on in our maint plan, running every
Sunday night. For the last two months or so it would never complete, and the
server would be non-responsive Monday morning.
Does anyone know what causes this to happen?
Maury|||There are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and t
he
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury|||"Tracy McKibben" wrote:
> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.
> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.
> - is your database configured to auto-grow when needed?
Yes.
> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.
> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||"Tracy McKibben" wrote:
> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.
> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury|||"Tracy McKibben" wrote:
> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.
> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and t
he
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury"Tracy McKibben" wrote:
> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.
> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.
> - is your database configured to auto-grow when needed?
Yes.
> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.
> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||I used to have this function turned on in our maint plan, running every
Sunday night. For the last two months or so it would never complete, and the
server would be non-responsive Monday morning.
Does anyone know what causes this to happen?
Maury|||There are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and t
he
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury|||"Tracy McKibben" wrote:
> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.
> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.
> - is your database configured to auto-grow when needed?
Yes.
> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.
> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||"Tracy McKibben" wrote:
> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.
> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury|||"Tracy McKibben" wrote:
> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.
> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury
Reorganize index pages never completes
I used to have this function turned on in our maint plan, running every
Sunday night. For the last two months or so it would never complete, and the
server would be non-responsive Monday morning.
Does anyone know what causes this to happen?
MauryThere are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and the
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury|||"Tracy McKibben" wrote:
> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.
> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.
> - is your database configured to auto-grow when needed?
Yes.
> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.
> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||"Tracy McKibben" wrote:
> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.
> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury
Sunday night. For the last two months or so it would never complete, and the
server would be non-responsive Monday morning.
Does anyone know what causes this to happen?
MauryThere are, unfortunately, MANY things that can cause poor indexing
performance. You're going to have to do some analysis. You don't tell
us how large your database is, but obviously the more data you have,
the larger the indexes will be, thus the more work this reindexing
process will have to do. Things to check:
- physical disk fragmentation of your database and log files
- I/O utilization of your disk hardware
- other processes running on the server causing additional load?
- other processes blocking the reindex?
- is your database configured to auto-grow when needed?
- are you shrinking your databases, either with a scheduled job or the
"autoshrink" DB option? You should not shrink a production database
unless you have concerns about disk space. The combination of
shrinking, then growing, then shrinking, then growing, will lead to
severe fragmentation of your database file, causing increasingly poor
performance.
There are other factors that will affect indexing speed, but these are
the common ones.
Maury Markowitz wrote:
> I used to have this function turned on in our maint plan, running every
> Sunday night. For the last two months or so it would never complete, and the
> server would be non-responsive Monday morning.
> Does anyone know what causes this to happen?
> Maury|||"Tracy McKibben" wrote:
> There are, unfortunately, MANY things that can cause poor indexing
> performance.
This isn't a performance issue, although I should have pointed this out the
first time.
If I run a complete reindex by hand it takes perhaps 3 minutes at the worst,
and averages about 1.5 minutes. The delay in question is eight hours. It is
not that it is proceeding slowly, it simply isn't proceeding at all.
However, the system offers no indication whatsoever that there is a problem,
let alone what the problem is.
> - other processes blocking the reindex?
This is the only thing that I think could be the issue. However, it is not
clear to me how one would debug this. There is no indication in any of the
logs I have found that a lock is causing a problem, even when I return to
work Monday and see the system is "frozen".
Is there some way I can guarentee that locks are released? My users often
leave their Access apps open "forever", so it is entirely possible (and not
that uncommon) for there to be locking issues.
> - is your database configured to auto-grow when needed?
Yes.
> - are you shrinking your databases, either with a scheduled job or the
> "autoshrink" DB option? You should not shrink a production database
> unless you have concerns about disk space.
OK, but like I said, this isn't a performance issue. I will turn this off
anyway.
> shrinking, then growing, then shrinking, then growing, will lead to
> severe fragmentation of your database file
The fact that NT allows this has always made me scratch my head.
Maury|||Maury Markowitz wrote:
> This is the only thing that I think could be the issue. However, it is not
> clear to me how one would debug this. There is no indication in any of the
> logs I have found that a lock is causing a problem, even when I return to
> work Monday and see the system is "frozen".
When you leave on Friday, open a Query Analyzer connection to the
server, and leave it open. When you come in on Monday, run "sp_who2"
in that session to see if there is blocking. The reason for leaving
the QA session open on Friday is so that you aren't locked out of SQL.
Also, if you connect to the server with RDP, or can physically access
the console, take a look at Perfmon, look at things like CPU %, Disk
Queue Length, etc., these will tell you if the machine is doing
anything, and what parts of it are "bound up".
Now that you've described things a little more, I really think blocking
is your problem. It could have been the auto-shrink setting, if SQL
decides to shrink the database while you're reindexing, well that would
be bad. Access is also a common thorn in the side for causing DB
problems.|||"Tracy McKibben" wrote:
> When you leave on Friday, open a Query Analyzer connection to the
> server, and leave it open. When you come in on Monday, run "sp_who2"
> in that session to see if there is blocking. The reason for leaving
> the QA session open on Friday is so that you aren't locked out of SQL.
Ok, I'll give this a try.
> Now that you've described things a little more, I really think blocking
> is your problem.
Me too. I also have "random" locks on what appear to be read-only requests,
so this certainly could be the issue here too.
Maury
Saturday, February 25, 2012
Reorganize data and index pages option
We have a 40GB database and we, on a weekly basis, run a maintainance plan
with the option "Reorganize data and index pages" I think it is creating
a 20GB log file. The sql docs say that that option "Cause table indexes in
the database to be dropped and re-created with a new fill factor". I guess
that would explain it.
Is there a less expensive operation that can be performed? Like a DBCC
dbreindex but for all tables? I guess I could write a script to do that.
Any thoughts?
-alan>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables?
Same thing. See dbcc indexdefrag.
>--Original Message--
>We have a 40GB database and we, on a weekly basis, run a
maintainance plan
>with the option "Reorganize data and index pages" I
think it is creating
>a 20GB log file. The sql docs say that that
option "Cause table indexes in
>the database to be dropped and re-created with a new fill
factor". I guess
>that would explain it.
>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables? I guess I could write a
script to do that.
>Any thoughts?
>-alan
>
>.
>|||Sounds like you need the DBCC INDEXDEFRAG I put in SQL Server 2000. Look in
BOL for DBCC SHOWCONTIG and I included a script that will defrag all the
indexes in your database based on a fragmentation threshold you set.
Also see our new whitepaper on this topic 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.
"Alan Berezin" <aberezin@.drillinginfo.com> wrote in message
news:eRunmZ7TDHA.2312@.TK2MSFTNGP12.phx.gbl...
> We have a 40GB database and we, on a weekly basis, run a maintainance plan
> with the option "Reorganize data and index pages" I think it is
creating
> a 20GB log file. The sql docs say that that option "Cause table indexes
in
> the database to be dropped and re-created with a new fill factor". I
guess
> that would explain it.
> Is there a less expensive operation that can be performed? Like a DBCC
> dbreindex but for all tables? I guess I could write a script to do that.
> Any thoughts?
> -alan
>
with the option "Reorganize data and index pages" I think it is creating
a 20GB log file. The sql docs say that that option "Cause table indexes in
the database to be dropped and re-created with a new fill factor". I guess
that would explain it.
Is there a less expensive operation that can be performed? Like a DBCC
dbreindex but for all tables? I guess I could write a script to do that.
Any thoughts?
-alan>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables?
Same thing. See dbcc indexdefrag.
>--Original Message--
>We have a 40GB database and we, on a weekly basis, run a
maintainance plan
>with the option "Reorganize data and index pages" I
think it is creating
>a 20GB log file. The sql docs say that that
option "Cause table indexes in
>the database to be dropped and re-created with a new fill
factor". I guess
>that would explain it.
>Is there a less expensive operation that can be
performed? Like a DBCC
>dbreindex but for all tables? I guess I could write a
script to do that.
>Any thoughts?
>-alan
>
>.
>|||Sounds like you need the DBCC INDEXDEFRAG I put in SQL Server 2000. Look in
BOL for DBCC SHOWCONTIG and I included a script that will defrag all the
indexes in your database based on a fragmentation threshold you set.
Also see our new whitepaper on this topic 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.
"Alan Berezin" <aberezin@.drillinginfo.com> wrote in message
news:eRunmZ7TDHA.2312@.TK2MSFTNGP12.phx.gbl...
> We have a 40GB database and we, on a weekly basis, run a maintainance plan
> with the option "Reorganize data and index pages" I think it is
creating
> a 20GB log file. The sql docs say that that option "Cause table indexes
in
> the database to be dropped and re-created with a new fill factor". I
guess
> that would explain it.
> Is there a less expensive operation that can be performed? Like a DBCC
> dbreindex but for all tables? I guess I could write a script to do that.
> Any thoughts?
> -alan
>
Reorganize data and index pages
Hello all,
SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it
gives me an option to reorganize data and index pages. When I check on
it, it populates the line "change free space per page percentage to" and
puts in 10 in there. Is this the default for free space? Is it the
data pages that will have 10% free space or just the index pages? Are
data and index on the same pages?
Thanks,
Raziq.
*** Sent via Developersdex http://www.developersdex.com ***Hi
If you profile the maintainance plan is seems that choosing causes a call to
DBCC DBREINDEX which is documented in books online. The 10% free space value
is reversed to be a 90% fillfactor as described by:
fillfactor
Is the percentage of space on each index page to be used for storing data
when the index is created. fillfactor replaces the original fillfactor as
the new default for the index and for any other nonclustered indexes rebuilt
because a clustered index is rebuilt. When fillfactor is 0, DBCC DBREINDEX
uses the original fillfactor specified when the index was created.
If you look at the Table and Index Architecture section of books online you
will see how clustered and non-clustered indexes are composed.
or at:
http://msdn.microsoft.com/library/d...ar_da2_8sit.asp
http://msdn.microsoft.com/library/d...ar_da2_1tbn.asp
http://msdn.microsoft.com/library/d...ar_da2_75mb.asp
You may want to get "Inside SQL Server 2000" by Kalen Delany ISBN
0-7356-0998-5 which talks about this sort of thing in detail.
John
"Raziq Shekha" <raziq_shekha@.anadarko.com> wrote in message
news:rF97e.5$Nk2.347@.news.uswest.net...
> Hello all,
> SQL 2000 on Windows 2000. If I go into all tasks, maintenance plan, it
> gives me an option to reorganize data and index pages. When I check on
> it, it populates the line "change free space per page percentage to" and
> puts in 10 in there. Is this the default for free space? Is it the
> data pages that will have 10% free space or just the index pages? Are
> data and index on the same pages?
> Thanks,
> Raziq.
>
> *** Sent via Developersdex http://www.developersdex.com ***
Reorganize data and index pages
I have a couple of questions here so I will break them out.
1. I have used the Database Maintenance plan to schedule a Reorganization of
the data and index pages that runs every weekend. I lef the free space per
page percentage to 10%.
-A: Do I need to do anything special to make sure the statistics are
updated such as sp_updatestats? Can I trust that by doing that alone my
indexes are in the best shape and that my data is defragmented the best way?
Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
-B: It seems that from what I've read that the 10% varies on what your
database is used for. Is that correct?
2. If I need to defrag databases that aren't on that plan, I'll normally
just do the following when I see fragmentation above 20 percent:
USE Database
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
USE Database
DBCC INDEXDEFRAG (Database, table, index)
GO
3. I notice that the sysindexes table has an index called tsysindexes that
has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
concern? I don't ever manually defrag the system tables as I believe I'm not
supposed to, but I'm not sure if the weekend job gets it.
Thanks
JasonHi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
to worry about updating the statistics as it's done right after indexes are
rebuilt. however you may have to update the statistics if you are using DBCC
INDEXDEFRAG.
You can find some good tips and scripts related to this topic @.
<WWW.SQLCOMMUNITY.COM>
<http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
<http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
<http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
Hope this helps.
--
Thank you,
<WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
Forums, etc
"jason7655" wrote:
> I have a couple of questions here so I will break them out.
> 1. I have used the Database Maintenance plan to schedule a Reorganization of
> the data and index pages that runs every weekend. I lef the free space per
> page percentage to 10%.
> -A: Do I need to do anything special to make sure the statistics are
> updated such as sp_updatestats? Can I trust that by doing that alone my
> indexes are in the best shape and that my data is defragmented the best way?
> Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> -B: It seems that from what I've read that the 10% varies on what your
> database is used for. Is that correct?
> 2. If I need to defrag databases that aren't on that plan, I'll normally
> just do the following when I see fragmentation above 20 percent:
> USE Database
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> USE Database
> DBCC INDEXDEFRAG (Database, table, index)
> GO
> 3. I notice that the sysindexes table has an index called tsysindexes that
> has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> concern? I don't ever manually defrag the system tables as I believe I'm not
> supposed to, but I'm not sure if the weekend job gets it.
> Thanks
> Jason|||Thanks.
I probably should have mentioned that we are on SQL Server 2000.
"SQLGurus" wrote:
> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
> to worry about updating the statistics as it's done right after indexes are
> rebuilt. however you may have to update the statistics if you are using DBCC
> INDEXDEFRAG.
> You can find some good tips and scripts related to this topic @.
> <WWW.SQLCOMMUNITY.COM>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> Hope this helps.
> --
> Thank you,
> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> Forums, etc
>
> "jason7655" wrote:
> > I have a couple of questions here so I will break them out.
> >
> > 1. I have used the Database Maintenance plan to schedule a Reorganization of
> > the data and index pages that runs every weekend. I lef the free space per
> > page percentage to 10%.
> > -A: Do I need to do anything special to make sure the statistics are
> > updated such as sp_updatestats? Can I trust that by doing that alone my
> > indexes are in the best shape and that my data is defragmented the best way?
> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> > -B: It seems that from what I've read that the 10% varies on what your
> > database is used for. Is that correct?
> >
> > 2. If I need to defrag databases that aren't on that plan, I'll normally
> > just do the following when I see fragmentation above 20 percent:
> > USE Database
> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >
> > USE Database
> > DBCC INDEXDEFRAG (Database, table, index)
> > GO
> >
> > 3. I notice that the sysindexes table has an index called tsysindexes that
> > has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> > concern? I don't ever manually defrag the system tables as I believe I'm not
> > supposed to, but I'm not sure if the weekend job gets it.
> >
> > Thanks
> > Jason|||The commands Kevin gave were for SQL2000.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> Thanks.
> I probably should have mentioned that we are on SQL Server 2000.
> "SQLGurus" wrote:
>> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
>> have
>> to worry about updating the statistics as it's done right after indexes
>> are
>> rebuilt. however you may have to update the statistics if you are using
>> DBCC
>> INDEXDEFRAG.
>> You can find some good tips and scripts related to this topic @.
>> <WWW.SQLCOMMUNITY.COM>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> Hope this helps.
>> --
>> Thank you,
>> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> Professionals)
>> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
>> Forums, etc
>>
>> "jason7655" wrote:
>> > I have a couple of questions here so I will break them out.
>> >
>> > 1. I have used the Database Maintenance plan to schedule a
>> > Reorganization of
>> > the data and index pages that runs every weekend. I lef the free space
>> > per
>> > page percentage to 10%.
>> > -A: Do I need to do anything special to make sure the statistics are
>> > updated such as sp_updatestats? Can I trust that by doing that alone my
>> > indexes are in the best shape and that my data is defragmented the best
>> > way?
>> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> > indexes?
>> > -B: It seems that from what I've read that the 10% varies on what your
>> > database is used for. Is that correct?
>> >
>> > 2. If I need to defrag databases that aren't on that plan, I'll
>> > normally
>> > just do the following when I see fragmentation above 20 percent:
>> > USE Database
>> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >
>> > USE Database
>> > DBCC INDEXDEFRAG (Database, table, index)
>> > GO
>> >
>> > 3. I notice that the sysindexes table has an index called tsysindexes
>> > that
>> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
>> > that a
>> > concern? I don't ever manually defrag the system tables as I believe
>> > I'm not
>> > supposed to, but I'm not sure if the weekend job gets it.
>> >
>> > Thanks
>> > Jason|||The links were specific to 2005, and that's what I was referring to.
Although one of them mentions a script for 2000.
Wasn't taking anything away from it...just mentioning that I left off an
important piece of information b/c 2005 goes a long way in helping one
monitor that sort of thing.
"Andrew J. Kelly" wrote:
> The commands Kevin gave were for SQL2000.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> > Thanks.
> >
> > I probably should have mentioned that we are on SQL Server 2000.
> >
> > "SQLGurus" wrote:
> >
> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
> >> have
> >> to worry about updating the statistics as it's done right after indexes
> >> are
> >> rebuilt. however you may have to update the statistics if you are using
> >> DBCC
> >> INDEXDEFRAG.
> >>
> >> You can find some good tips and scripts related to this topic @.
> >> <WWW.SQLCOMMUNITY.COM>
> >>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> >>
> >> Hope this helps.
> >> --
> >> Thank you,
> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
> >> Professionals)
> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> >> Forums, etc
> >>
> >>
> >> "jason7655" wrote:
> >>
> >> > I have a couple of questions here so I will break them out.
> >> >
> >> > 1. I have used the Database Maintenance plan to schedule a
> >> > Reorganization of
> >> > the data and index pages that runs every weekend. I lef the free space
> >> > per
> >> > page percentage to 10%.
> >> > -A: Do I need to do anything special to make sure the statistics are
> >> > updated such as sp_updatestats? Can I trust that by doing that alone my
> >> > indexes are in the best shape and that my data is defragmented the best
> >> > way?
> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
> >> > indexes?
> >> > -B: It seems that from what I've read that the 10% varies on what your
> >> > database is used for. Is that correct?
> >> >
> >> > 2. If I need to defrag databases that aren't on that plan, I'll
> >> > normally
> >> > just do the following when I see fragmentation above 20 percent:
> >> > USE Database
> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >> >
> >> > USE Database
> >> > DBCC INDEXDEFRAG (Database, table, index)
> >> > GO
> >> >
> >> > 3. I notice that the sysindexes table has an index called tsysindexes
> >> > that
> >> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
> >> > that a
> >> > concern? I don't ever manually defrag the system tables as I believe
> >> > I'm not
> >> > supposed to, but I'm not sure if the weekend job gets it.
> >> >
> >> > Thanks
> >> > Jason
>|||Sorry I didn't click on the links.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:B87939FC-6CAB-48DC-8ECB-129B76AB5486@.microsoft.com...
> The links were specific to 2005, and that's what I was referring to.
> Although one of them mentions a script for 2000.
> Wasn't taking anything away from it...just mentioning that I left off an
> important piece of information b/c 2005 goes a long way in helping one
> monitor that sort of thing.
> "Andrew J. Kelly" wrote:
>> The commands Kevin gave were for SQL2000.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
>> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
>> > Thanks.
>> >
>> > I probably should have mentioned that we are on SQL Server 2000.
>> >
>> > "SQLGurus" wrote:
>> >
>> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you
>> >> don't
>> >> have
>> >> to worry about updating the statistics as it's done right after
>> >> indexes
>> >> are
>> >> rebuilt. however you may have to update the statistics if you are
>> >> using
>> >> DBCC
>> >> INDEXDEFRAG.
>> >>
>> >> You can find some good tips and scripts related to this topic @.
>> >> <WWW.SQLCOMMUNITY.COM>
>> >>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> >>
>> >> Hope this helps.
>> >> --
>> >> Thank you,
>> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> >> Professionals)
>> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events,
>> >> SQL
>> >> Forums, etc
>> >>
>> >>
>> >> "jason7655" wrote:
>> >>
>> >> > I have a couple of questions here so I will break them out.
>> >> >
>> >> > 1. I have used the Database Maintenance plan to schedule a
>> >> > Reorganization of
>> >> > the data and index pages that runs every weekend. I lef the free
>> >> > space
>> >> > per
>> >> > page percentage to 10%.
>> >> > -A: Do I need to do anything special to make sure the statistics
>> >> > are
>> >> > updated such as sp_updatestats? Can I trust that by doing that alone
>> >> > my
>> >> > indexes are in the best shape and that my data is defragmented the
>> >> > best
>> >> > way?
>> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> >> > indexes?
>> >> > -B: It seems that from what I've read that the 10% varies on what
>> >> > your
>> >> > database is used for. Is that correct?
>> >> >
>> >> > 2. If I need to defrag databases that aren't on that plan, I'll
>> >> > normally
>> >> > just do the following when I see fragmentation above 20 percent:
>> >> > USE Database
>> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >> >
>> >> > USE Database
>> >> > DBCC INDEXDEFRAG (Database, table, index)
>> >> > GO
>> >> >
>> >> > 3. I notice that the sysindexes table has an index called
>> >> > tsysindexes
>> >> > that
>> >> > has logical scan fragmentation at 99, but it's small at 119 pages.
>> >> > Is
>> >> > that a
>> >> > concern? I don't ever manually defrag the system tables as I believe
>> >> > I'm not
>> >> > supposed to, but I'm not sure if the weekend job gets it.
>> >> >
>> >> > Thanks
>> >> > Jason
>>
1. I have used the Database Maintenance plan to schedule a Reorganization of
the data and index pages that runs every weekend. I lef the free space per
page percentage to 10%.
-A: Do I need to do anything special to make sure the statistics are
updated such as sp_updatestats? Can I trust that by doing that alone my
indexes are in the best shape and that my data is defragmented the best way?
Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
-B: It seems that from what I've read that the 10% varies on what your
database is used for. Is that correct?
2. If I need to defrag databases that aren't on that plan, I'll normally
just do the following when I see fragmentation above 20 percent:
USE Database
DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
USE Database
DBCC INDEXDEFRAG (Database, table, index)
GO
3. I notice that the sysindexes table has an index called tsysindexes that
has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
concern? I don't ever manually defrag the system tables as I believe I'm not
supposed to, but I'm not sure if the weekend job gets it.
Thanks
JasonHi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
to worry about updating the statistics as it's done right after indexes are
rebuilt. however you may have to update the statistics if you are using DBCC
INDEXDEFRAG.
You can find some good tips and scripts related to this topic @.
<WWW.SQLCOMMUNITY.COM>
<http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
<http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
<http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
<http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
Hope this helps.
--
Thank you,
<WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
Forums, etc
"jason7655" wrote:
> I have a couple of questions here so I will break them out.
> 1. I have used the Database Maintenance plan to schedule a Reorganization of
> the data and index pages that runs every weekend. I lef the free space per
> page percentage to 10%.
> -A: Do I need to do anything special to make sure the statistics are
> updated such as sp_updatestats? Can I trust that by doing that alone my
> indexes are in the best shape and that my data is defragmented the best way?
> Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> -B: It seems that from what I've read that the 10% varies on what your
> database is used for. Is that correct?
> 2. If I need to defrag databases that aren't on that plan, I'll normally
> just do the following when I see fragmentation above 20 percent:
> USE Database
> DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> USE Database
> DBCC INDEXDEFRAG (Database, table, index)
> GO
> 3. I notice that the sysindexes table has an index called tsysindexes that
> has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> concern? I don't ever manually defrag the system tables as I believe I'm not
> supposed to, but I'm not sure if the weekend job gets it.
> Thanks
> Jason|||Thanks.
I probably should have mentioned that we are on SQL Server 2000.
"SQLGurus" wrote:
> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't have
> to worry about updating the statistics as it's done right after indexes are
> rebuilt. however you may have to update the statistics if you are using DBCC
> INDEXDEFRAG.
> You can find some good tips and scripts related to this topic @.
> <WWW.SQLCOMMUNITY.COM>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> Hope this helps.
> --
> Thank you,
> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server Professionals)
> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> Forums, etc
>
> "jason7655" wrote:
> > I have a couple of questions here so I will break them out.
> >
> > 1. I have used the Database Maintenance plan to schedule a Reorganization of
> > the data and index pages that runs every weekend. I lef the free space per
> > page percentage to 10%.
> > -A: Do I need to do anything special to make sure the statistics are
> > updated such as sp_updatestats? Can I trust that by doing that alone my
> > indexes are in the best shape and that my data is defragmented the best way?
> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the indexes?
> > -B: It seems that from what I've read that the 10% varies on what your
> > database is used for. Is that correct?
> >
> > 2. If I need to defrag databases that aren't on that plan, I'll normally
> > just do the following when I see fragmentation above 20 percent:
> > USE Database
> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >
> > USE Database
> > DBCC INDEXDEFRAG (Database, table, index)
> > GO
> >
> > 3. I notice that the sysindexes table has an index called tsysindexes that
> > has logical scan fragmentation at 99, but it's small at 119 pages. Is that a
> > concern? I don't ever manually defrag the system tables as I believe I'm not
> > supposed to, but I'm not sure if the weekend job gets it.
> >
> > Thanks
> > Jason|||The commands Kevin gave were for SQL2000.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> Thanks.
> I probably should have mentioned that we are on SQL Server 2000.
> "SQLGurus" wrote:
>> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
>> have
>> to worry about updating the statistics as it's done right after indexes
>> are
>> rebuilt. however you may have to update the statistics if you are using
>> DBCC
>> INDEXDEFRAG.
>> You can find some good tips and scripts related to this topic @.
>> <WWW.SQLCOMMUNITY.COM>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> Hope this helps.
>> --
>> Thank you,
>> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> Professionals)
>> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
>> Forums, etc
>>
>> "jason7655" wrote:
>> > I have a couple of questions here so I will break them out.
>> >
>> > 1. I have used the Database Maintenance plan to schedule a
>> > Reorganization of
>> > the data and index pages that runs every weekend. I lef the free space
>> > per
>> > page percentage to 10%.
>> > -A: Do I need to do anything special to make sure the statistics are
>> > updated such as sp_updatestats? Can I trust that by doing that alone my
>> > indexes are in the best shape and that my data is defragmented the best
>> > way?
>> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> > indexes?
>> > -B: It seems that from what I've read that the 10% varies on what your
>> > database is used for. Is that correct?
>> >
>> > 2. If I need to defrag databases that aren't on that plan, I'll
>> > normally
>> > just do the following when I see fragmentation above 20 percent:
>> > USE Database
>> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >
>> > USE Database
>> > DBCC INDEXDEFRAG (Database, table, index)
>> > GO
>> >
>> > 3. I notice that the sysindexes table has an index called tsysindexes
>> > that
>> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
>> > that a
>> > concern? I don't ever manually defrag the system tables as I believe
>> > I'm not
>> > supposed to, but I'm not sure if the weekend job gets it.
>> >
>> > Thanks
>> > Jason|||The links were specific to 2005, and that's what I was referring to.
Although one of them mentions a script for 2000.
Wasn't taking anything away from it...just mentioning that I left off an
important piece of information b/c 2005 goes a long way in helping one
monitor that sort of thing.
"Andrew J. Kelly" wrote:
> The commands Kevin gave were for SQL2000.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
> > Thanks.
> >
> > I probably should have mentioned that we are on SQL Server 2000.
> >
> > "SQLGurus" wrote:
> >
> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you don't
> >> have
> >> to worry about updating the statistics as it's done right after indexes
> >> are
> >> rebuilt. however you may have to update the statistics if you are using
> >> DBCC
> >> INDEXDEFRAG.
> >>
> >> You can find some good tips and scripts related to this topic @.
> >> <WWW.SQLCOMMUNITY.COM>
> >>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
> >>
> >> Hope this helps.
> >> --
> >> Thank you,
> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
> >> Professionals)
> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events, SQL
> >> Forums, etc
> >>
> >>
> >> "jason7655" wrote:
> >>
> >> > I have a couple of questions here so I will break them out.
> >> >
> >> > 1. I have used the Database Maintenance plan to schedule a
> >> > Reorganization of
> >> > the data and index pages that runs every weekend. I lef the free space
> >> > per
> >> > page percentage to 10%.
> >> > -A: Do I need to do anything special to make sure the statistics are
> >> > updated such as sp_updatestats? Can I trust that by doing that alone my
> >> > indexes are in the best shape and that my data is defragmented the best
> >> > way?
> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
> >> > indexes?
> >> > -B: It seems that from what I've read that the 10% varies on what your
> >> > database is used for. Is that correct?
> >> >
> >> > 2. If I need to defrag databases that aren't on that plan, I'll
> >> > normally
> >> > just do the following when I see fragmentation above 20 percent:
> >> > USE Database
> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
> >> >
> >> > USE Database
> >> > DBCC INDEXDEFRAG (Database, table, index)
> >> > GO
> >> >
> >> > 3. I notice that the sysindexes table has an index called tsysindexes
> >> > that
> >> > has logical scan fragmentation at 99, but it's small at 119 pages. Is
> >> > that a
> >> > concern? I don't ever manually defrag the system tables as I believe
> >> > I'm not
> >> > supposed to, but I'm not sure if the weekend job gets it.
> >> >
> >> > Thanks
> >> > Jason
>|||Sorry I didn't click on the links.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"jason7655" <jason7655@.discussions.microsoft.com> wrote in message
news:B87939FC-6CAB-48DC-8ECB-129B76AB5486@.microsoft.com...
> The links were specific to 2005, and that's what I was referring to.
> Although one of them mentions a script for 2000.
> Wasn't taking anything away from it...just mentioning that I left off an
> important piece of information b/c 2005 goes a long way in helping one
> monitor that sort of thing.
> "Andrew J. Kelly" wrote:
>> The commands Kevin gave were for SQL2000.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "jason7655" <jason7655@.discussions.microsoft.com> wrote in message
>> news:FE53F490-A4CF-4345-B974-4D9B411F9943@.microsoft.com...
>> > Thanks.
>> >
>> > I probably should have mentioned that we are on SQL Server 2000.
>> >
>> > "SQLGurus" wrote:
>> >
>> >> Hi Jason, If you are rebuing indexes using DBCC DBREINDEX then you
>> >> don't
>> >> have
>> >> to worry about updating the statistics as it's done right after
>> >> indexes
>> >> are
>> >> rebuilt. however you may have to update the statistics if you are
>> >> using
>> >> DBCC
>> >> INDEXDEFRAG.
>> >>
>> >> You can find some good tips and scripts related to this topic @.
>> >> <WWW.SQLCOMMUNITY.COM>
>> >>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=14&tabid=56>
>> >> <http://www.sqlcommunity.com/SQLTips/tabid/77/grm2id/28/Default.aspx>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=12&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=7&tabid=56>
>> >> <http://www.sqlcommunity.com/Default.aspx?grm2id=9&tabid=56>
>> >>
>> >> Hope this helps.
>> >> --
>> >> Thank you,
>> >> <WWW.SQLCOMMUNITY.COM> (World Wide Community for SQL Server
>> >> Professionals)
>> >> SQLTips, SQL Automation Scripts, SQL Articles, SQL Blogs, SQL Events,
>> >> SQL
>> >> Forums, etc
>> >>
>> >>
>> >> "jason7655" wrote:
>> >>
>> >> > I have a couple of questions here so I will break them out.
>> >> >
>> >> > 1. I have used the Database Maintenance plan to schedule a
>> >> > Reorganization of
>> >> > the data and index pages that runs every weekend. I lef the free
>> >> > space
>> >> > per
>> >> > page percentage to 10%.
>> >> > -A: Do I need to do anything special to make sure the statistics
>> >> > are
>> >> > updated such as sp_updatestats? Can I trust that by doing that alone
>> >> > my
>> >> > indexes are in the best shape and that my data is defragmented the
>> >> > best
>> >> > way?
>> >> > Isn't this like a DBCC REINDEX or maybe a complete rebuild of the
>> >> > indexes?
>> >> > -B: It seems that from what I've read that the 10% varies on what
>> >> > your
>> >> > database is used for. Is that correct?
>> >> >
>> >> > 2. If I need to defrag databases that aren't on that plan, I'll
>> >> > normally
>> >> > just do the following when I see fragmentation above 20 percent:
>> >> > USE Database
>> >> > DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES
>> >> >
>> >> > USE Database
>> >> > DBCC INDEXDEFRAG (Database, table, index)
>> >> > GO
>> >> >
>> >> > 3. I notice that the sysindexes table has an index called
>> >> > tsysindexes
>> >> > that
>> >> > has logical scan fragmentation at 99, but it's small at 119 pages.
>> >> > Is
>> >> > that a
>> >> > concern? I don't ever manually defrag the system tables as I believe
>> >> > I'm not
>> >> > supposed to, but I'm not sure if the weekend job gets it.
>> >> >
>> >> > Thanks
>> >> > Jason
>>
reorg PK clustered index in VLDB
My data warehouse is now 4 years old and the size is about
600 GB od data.
We had only data insert no update in this duration. PKs
are all clustered indexes and in chronological order.
Some of tables are as large as 100 GB.
I wanted to check if the PKs are in good figure and ran
DBCC showcontig against many of large tables and 80 % had
bad rate for scan density , such as 50 %, 30 %.
I may need to reorganise PK.
I BOL it says comparing the values of Extent Switches and
Extents Scanned is a way to know how much fragmented. But
it says this method does not work if the index spans
multiple files. I presume all VLDB exploit multiple files
for one table in order to gain physical disk I/O.
My question: how can I check fragmentation rate of my
large tables which span multiple files (up to 4 to 6
files)?
What is the best way to reorganise clustered index which
are PK ? I have to drop all FK in order to reorganise PK,
don't I !
I hope to hear your idea!!!When you say you presume your db spans multiple files, does the database use
more than one file other than the MDF? DBCC DBreindex on the clustered key
should reindex your data tables and automatically reindex your other
nonclustered indexes.
Some links:
http://www.microsoft.com/technet/co...ql/sql0326.mspx
http://www.microsoft.com/technet/co...ql/sql1014.mspx
http://www.sqlservercentral.com/scr...butions/721.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:140c01c40b37$dd2c95d0$3501280a@.phx.gbl...
> My data warehouse is now 4 years old and the size is about
> 600 GB od data.
> We had only data insert no update in this duration. PKs
> are all clustered indexes and in chronological order.
> Some of tables are as large as 100 GB.
> I wanted to check if the PKs are in good figure and ran
> DBCC showcontig against many of large tables and 80 % had
> bad rate for scan density , such as 50 %, 30 %.
> I may need to reorganise PK.
> I BOL it says comparing the values of Extent Switches and
> Extents Scanned is a way to know how much fragmented. But
> it says this method does not work if the index spans
> multiple files. I presume all VLDB exploit multiple files
> for one table in order to gain physical disk I/O.
> My question: how can I check fragmentation rate of my
> large tables which span multiple files (up to 4 to 6
> files)?
> What is the best way to reorganise clustered index which
> are PK ? I have to drop all FK in order to reorganise PK,
> don't I !
> I hope to hear your idea!!!|||MDF file is used only for system table in all of my
databases. (especially when dealing with VLDB).
The database is over 600GB, and each table could be nearly
100GB,
Would DBreindex a good solution ?
This will copy the whole table into different location
without asking !
>--Original Message--
>When you say you presume your db spans multiple files,
does the database use
>more than one file other than the MDF? DBCC DBreindex on
the clustered key
>should reindex your data tables and automatically reindex
your other
>nonclustered indexes.
>Some links:
>http://www.microsoft.com/technet/co...chats/trans/sql
/sql0326.mspx
>http://www.microsoft.com/technet/co...chats/trans/sql
/sql1014.mspx
>http://www.sqlservercentral.com/scr...tributions/721.
asp
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"didi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:140c01c40b37$dd2c95d0$3501280a@.phx.gbl...
about
had
and
But
files
PK,
>
>.
>|||Did those links help?
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:148601c40b4b$a0c711b0$3a01280a@.phx.gbl...
> MDF file is used only for system table in all of my
> databases. (especially when dealing with VLDB).
> The database is over 600GB, and each table could be nearly
> 100GB,
> Would DBreindex a good solution ?
> This will copy the whole table into different location
> without asking !
>
> does the database use
> the clustered key
> your other
> /sql0326.mspx
> /sql1014.mspx
> asp
> message
> about
> had
> and
> But
> files
> PK,|||Links were very good! Thank you very much!
Especially Index Defrag Best Practices.
So, according to the article I should use fragmentation
level by logical scan fragmentation.
Still I am not very sure about using DBCC INDEXDEFRAG.
Because when a table is 100GB, and do this operation, how
large the log should be allocated ? 200 GB, 300 GB ?
Usually for copying data it takes about 2.5 times of data
size consumed in log before the data is inserted into.
Would DBCC INDEXDEFRAG be a best way in VLDB environment ?
>--Original Message--
>Did those links help?
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"didi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:148601c40b4b$a0c711b0$3a01280a@.phx.gbl...
nearly
on
reindex
>http://www.microsoft.com/technet/co...chats/trans/sql
>http://www.microsoft.com/technet/co...chats/trans/sql
>http://www.sqlservercentral.com/scr...tributions/721.
PKs
ran
which
>
>.
>|||Depends on the needed uptime of your DB, you can write scripts to defrag in
chunks. Here is an example of using dbreindex (you can alter to use index
defrag) and backing up the log when needed, think I got this from MVP Andrew
Kelly but not 100% sure:
-- Reindexing the tables --
SET NOCOUNT ON
DECLARE @.TableName VARCHAR(100), @.Counter INT
SET @.Counter = 1
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @.TableName
DBCC DBREINDEX (@.TableName)
SET @.Counter = @.Counter + 1
-- Backup the Log every so often so as not to fill the log
IF @.Counter % 10 = 0
BEGIN
BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT , NOUNLOAD
,
NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
NOFORMAT
END
FETCH NEXT FROM curTables INTO @.TableName
END
CLOSE curTables
DEALLOCATE curTables
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:159401c40c21$2fc43b60$3a01280a@.phx.gbl...
> Links were very good! Thank you very much!
> Especially Index Defrag Best Practices.
> So, according to the article I should use fragmentation
> level by logical scan fragmentation.
> Still I am not very sure about using DBCC INDEXDEFRAG.
> Because when a table is 100GB, and do this operation, how
> large the log should be allocated ? 200 GB, 300 GB ?
> Usually for copying data it takes about 2.5 times of data
> size consumed in log before the data is inserted into.
> Would DBCC INDEXDEFRAG be a best way in VLDB environment ?
>
> message
> nearly
> on
> reindex
> PKs
> ran
> which
600 GB od data.
We had only data insert no update in this duration. PKs
are all clustered indexes and in chronological order.
Some of tables are as large as 100 GB.
I wanted to check if the PKs are in good figure and ran
DBCC showcontig against many of large tables and 80 % had
bad rate for scan density , such as 50 %, 30 %.
I may need to reorganise PK.
I BOL it says comparing the values of Extent Switches and
Extents Scanned is a way to know how much fragmented. But
it says this method does not work if the index spans
multiple files. I presume all VLDB exploit multiple files
for one table in order to gain physical disk I/O.
My question: how can I check fragmentation rate of my
large tables which span multiple files (up to 4 to 6
files)?
What is the best way to reorganise clustered index which
are PK ? I have to drop all FK in order to reorganise PK,
don't I !
I hope to hear your idea!!!When you say you presume your db spans multiple files, does the database use
more than one file other than the MDF? DBCC DBreindex on the clustered key
should reindex your data tables and automatically reindex your other
nonclustered indexes.
Some links:
http://www.microsoft.com/technet/co...ql/sql0326.mspx
http://www.microsoft.com/technet/co...ql/sql1014.mspx
http://www.sqlservercentral.com/scr...butions/721.asp
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:140c01c40b37$dd2c95d0$3501280a@.phx.gbl...
> My data warehouse is now 4 years old and the size is about
> 600 GB od data.
> We had only data insert no update in this duration. PKs
> are all clustered indexes and in chronological order.
> Some of tables are as large as 100 GB.
> I wanted to check if the PKs are in good figure and ran
> DBCC showcontig against many of large tables and 80 % had
> bad rate for scan density , such as 50 %, 30 %.
> I may need to reorganise PK.
> I BOL it says comparing the values of Extent Switches and
> Extents Scanned is a way to know how much fragmented. But
> it says this method does not work if the index spans
> multiple files. I presume all VLDB exploit multiple files
> for one table in order to gain physical disk I/O.
> My question: how can I check fragmentation rate of my
> large tables which span multiple files (up to 4 to 6
> files)?
> What is the best way to reorganise clustered index which
> are PK ? I have to drop all FK in order to reorganise PK,
> don't I !
> I hope to hear your idea!!!|||MDF file is used only for system table in all of my
databases. (especially when dealing with VLDB).
The database is over 600GB, and each table could be nearly
100GB,
Would DBreindex a good solution ?
This will copy the whole table into different location
without asking !
>--Original Message--
>When you say you presume your db spans multiple files,
does the database use
>more than one file other than the MDF? DBCC DBreindex on
the clustered key
>should reindex your data tables and automatically reindex
your other
>nonclustered indexes.
>Some links:
>http://www.microsoft.com/technet/co...chats/trans/sql
/sql0326.mspx
>http://www.microsoft.com/technet/co...chats/trans/sql
/sql1014.mspx
>http://www.sqlservercentral.com/scr...tributions/721.
asp
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"didi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:140c01c40b37$dd2c95d0$3501280a@.phx.gbl...
about
had
and
But
files
PK,
>
>.
>|||Did those links help?
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:148601c40b4b$a0c711b0$3a01280a@.phx.gbl...
> MDF file is used only for system table in all of my
> databases. (especially when dealing with VLDB).
> The database is over 600GB, and each table could be nearly
> 100GB,
> Would DBreindex a good solution ?
> This will copy the whole table into different location
> without asking !
>
> does the database use
> the clustered key
> your other
> /sql0326.mspx
> /sql1014.mspx
> asp
> message
> about
> had
> and
> But
> files
> PK,|||Links were very good! Thank you very much!
Especially Index Defrag Best Practices.
So, according to the article I should use fragmentation
level by logical scan fragmentation.
Still I am not very sure about using DBCC INDEXDEFRAG.
Because when a table is 100GB, and do this operation, how
large the log should be allocated ? 200 GB, 300 GB ?
Usually for copying data it takes about 2.5 times of data
size consumed in log before the data is inserted into.
Would DBCC INDEXDEFRAG be a best way in VLDB environment ?
>--Original Message--
>Did those links help?
>--
>Ray Higdon MCSE, MCDBA, CCNA
>--
>"didi" <anonymous@.discussions.microsoft.com> wrote in
message
>news:148601c40b4b$a0c711b0$3a01280a@.phx.gbl...
nearly
on
reindex
>http://www.microsoft.com/technet/co...chats/trans/sql
>http://www.microsoft.com/technet/co...chats/trans/sql
>http://www.sqlservercentral.com/scr...tributions/721.
PKs
ran
which
>
>.
>|||Depends on the needed uptime of your DB, you can write scripts to defrag in
chunks. Here is an example of using dbreindex (you can alter to use index
defrag) and backing up the log when needed, think I got this from MVP Andrew
Kelly but not 100% sure:
-- Reindexing the tables --
SET NOCOUNT ON
DECLARE @.TableName VARCHAR(100), @.Counter INT
SET @.Counter = 1
DECLARE curTables CURSOR STATIC LOCAL
FOR
SELECT Table_Name
FROM Information_Schema.Tables
WHERE Table_Type = 'BASE TABLE'
OPEN curTables
FETCH NEXT FROM curTables INTO @.TableName
SET @.TableName = RTRIM(@.TableName)
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT 'Reindexing ' + @.TableName
DBCC DBREINDEX (@.TableName)
SET @.Counter = @.Counter + 1
-- Backup the Log every so often so as not to fill the log
IF @.Counter % 10 = 0
BEGIN
BACKUP LOG [Presents] TO [DD_Presents_Log] WITH NOINIT , NOUNLOAD
,
NAME = N'Presents Log Backup', NOSKIP , STATS = 10,
NOFORMAT
END
FETCH NEXT FROM curTables INTO @.TableName
END
CLOSE curTables
DEALLOCATE curTables
Ray Higdon MCSE, MCDBA, CCNA
--
"didi" <anonymous@.discussions.microsoft.com> wrote in message
news:159401c40c21$2fc43b60$3a01280a@.phx.gbl...
> Links were very good! Thank you very much!
> Especially Index Defrag Best Practices.
> So, according to the article I should use fragmentation
> level by logical scan fragmentation.
> Still I am not very sure about using DBCC INDEXDEFRAG.
> Because when a table is 100GB, and do this operation, how
> large the log should be allocated ? 200 GB, 300 GB ?
> Usually for copying data it takes about 2.5 times of data
> size consumed in log before the data is inserted into.
> Would DBCC INDEXDEFRAG be a best way in VLDB environment ?
>
> message
> nearly
> on
> reindex
> PKs
> ran
> which
Subscribe to:
Posts (Atom)