am a bit confused about something. I am trying to solve a problem. My
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
..obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
Olu Adedeji
Do you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>
Showing posts with label bit. Show all posts
Showing posts with label bit. Show all posts
Sunday, March 11, 2012
DBCC INDEXDEFRAG shows more fragmentation but better performance - Why?
am a bit confused about something. I am trying to solve a problem. My
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
.obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
--
Olu AdedejiDo you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
--
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>
developers wrote a piece of code that inserted 2million rows of data and
then updated the data . .this obviously resulted in significant
fragmentation . . . . scan density reported was quite low . . resulting in
pagiolatch_sh waittypes(this slowed down performance) during reporting
processes (this is partly also to do with the fact that the data and log
files reside on a mirrored array) - pls don't ask, this is a system I have
inherited and as a result I have to live with it for now , I have to work
around these constraints taking into consideration that these export
processes have always completed in less that 5minutes in the past.. .
.obviously something has changed . .I have advised the developers about
re-writing the query so that the inserts are just inserts and not 1)inserts
then 2)updates. . . what I am trying to find an explanation for is
1) I advised for the code to be re-written to limit fragmentation - and it
did 98% scan density
2) logical frag and extent frag was low as well
3) developer runs query and query takes forever!!
a) I advise to run indexdefrag on clustered index
b) extent frag and logical frag still low
c) scan density is now worse
d) BUT query is excuted in 5mins
PS. I am not 'hands-on' just advising so I am waiting for exec plan for the
two scenarios.
I know that the exec plan would be similar as stats has not changed post
index defrag.
any ideas why indxdefrag will report worse scan density?
--
Olu AdedejiDo you have multiple files in the filegroup by any chance? If so the only
figure to trust is the Logical fragmentation. Have a look here for more
details:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Sounds like you are getting a lot of memory pressure and the data on the
mirrored array with the log files does not help at all. It sounds like this
routine probably does table scans but you can verify that by looking at the
query plans. Before you did the defrag what was the total page count? And
after what was it? I am willing to bet the large amount of fragmentation
caused the page count to be extremely large and not very full resulting in a
lot of I/O's. The fact the pages were out of order didn't help any either.
Sounds like you would have been better to do a DBCC DBREINDEX instead due to
the large amount of fragmentation.
--
Andrew J. Kelly SQL MVP
"Olu Adedeji" <anonymous@.email.com> wrote in message
news:uOCXQA2nEHA.4068@.tk2msftngp13.phx.gbl...
> am a bit confused about something. I am trying to solve a problem. My
> developers wrote a piece of code that inserted 2million rows of data and
> then updated the data . .this obviously resulted in significant
> fragmentation . . . . scan density reported was quite low . . resulting in
> pagiolatch_sh waittypes(this slowed down performance) during reporting
> processes (this is partly also to do with the fact that the data and log
> files reside on a mirrored array) - pls don't ask, this is a system I have
> inherited and as a result I have to live with it for now , I have to work
> around these constraints taking into consideration that these export
> processes have always completed in less that 5minutes in the past.. .
> .obviously something has changed . .I have advised the developers about
> re-writing the query so that the inserts are just inserts and not
1)inserts
> then 2)updates. . . what I am trying to find an explanation for is
> 1) I advised for the code to be re-written to limit fragmentation - and it
> did 98% scan density
> 2) logical frag and extent frag was low as well
> 3) developer runs query and query takes forever!!
> a) I advise to run indexdefrag on clustered index
> b) extent frag and logical frag still low
> c) scan density is now worse
> d) BUT query is excuted in 5mins
>
> PS. I am not 'hands-on' just advising so I am waiting for exec plan for
the
> two scenarios.
> I know that the exec plan would be similar as stats has not changed post
> index defrag.
> any ideas why indxdefrag will report worse scan density?
>
> --
> Olu Adedeji
>
dbcc indexdefrag
Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||In addition to the other posts:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment?
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.
|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
|||In addition to the other posts:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment?
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
dbcc indexdefrag
Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||In addition to the other posts:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardless
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||In addition to the other posts:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
Thursday, March 8, 2012
dbcc indexdefrag
Does dbcc indexdefrag cause transaction log growth? I've got a bit database
,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardles
s
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit databa
se,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I
'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||In addition to the other posts:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit databa
se,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I
'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.DBCC INDEXDEFRAG logs everything, since the defrag occurs as a bunch of
mini-transactions. Your log usage will increase significantly, so be sure
to backup the log frequently (even 1-min intervals, if necessary) to manage
this growth. I've done this on DB's up to 200+GB. We had a 36GB log and
seemed to manage with 15-min backups.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
Does dbcc indexdefrag cause transaction log growth? I've got a bit
database,
600GB, and it appears when I run dbcc indexdefrag against all the tables I'm
getting log growth which is what I'm trying to avoid by not running
dbreindex. Can someone out there comment'
Thanks. And please give details on your answers such as the size of the
databases you've done this on and what the size of the log was.
Many thanks.|||DBCC INDEXDEFRAG is fully logged and will cause significant log growth. You
can use/adapt the script found in BOL under DBCC SHOWCONTIG to limit the
particular tables you defrag according to fragementation percentage. You
can also add a section to determine log space consumed so you can pause the
defrag job between tables and fire off a log backup job.
Generally, without these limits, your defrag job will consume about as much
log space as the entire data partition of your database. And yes, there are
some improvements available in SQL 2005.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit
> database,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables
> I'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||From BOL
A very fragmented index might take considerably longer to defragment than to
rebuild. In addition, the defragmentation is always fully logged, regardles
s
of the database recovery model setting (see ALTER DATABASE). The
defragmentation of a very fragmented index can generate more log than even a
fully logged index creation. The defragmentation, however, is performed as a
series of short transactions and thus does not require a large log if log
backups are taken frequently or if the recovery model setting is SIMPLE.
http://sqlservercode.blogspot.com/
"fnguy" wrote:
> Does dbcc indexdefrag cause transaction log growth? I've got a bit databa
se,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I
'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.|||In addition to the other posts:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:6B08F477-C6FD-4DEB-9DEA-B037E65D9C6A@.microsoft.com...
> Does dbcc indexdefrag cause transaction log growth? I've got a bit databa
se,
> 600GB, and it appears when I run dbcc indexdefrag against all the tables I
'm
> getting log growth which is what I'm trying to avoid by not running
> dbreindex. Can someone out there comment'
> Thanks. And please give details on your answers such as the size of the
> databases you've done this on and what the size of the log was.
> Many thanks.
Tuesday, February 14, 2012
DBCC CHECKDB command help...
I need a bit of a hand with coding the DBCC CHECKDB command in QA.
I have been trying to configure my helpdesk database after moving drives in the original box after the old one ran out of space.
Now when i try and start the server daemons, i get a database check error.
can anyone help out with a DBCC CHECKDB command that will only check the database named AHDTEST50?
Thanks!Assuming that you ae using SQL Server 2000, then I'd use:USE AHDTEST50
GO
DBCC CHECKDB ('AHDTEST50') WITH ALL_ERRORMSGS
GO-PatP|||Pat to the rescue again!
Thanks pat.
I have been trying to configure my helpdesk database after moving drives in the original box after the old one ran out of space.
Now when i try and start the server daemons, i get a database check error.
can anyone help out with a DBCC CHECKDB command that will only check the database named AHDTEST50?
Thanks!Assuming that you ae using SQL Server 2000, then I'd use:USE AHDTEST50
GO
DBCC CHECKDB ('AHDTEST50') WITH ALL_ERRORMSGS
GO-PatP|||Pat to the rescue again!
Thanks pat.
Subscribe to:
Posts (Atom)