Please Advice.
I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
Active/Passive Cluster configuration.
I notice when I ran DBCC CheckDB on one of my Production
databases the available RAM Memory decreases and fails to
return on completion of the command.
Please help.Hi
BOL says:
DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that
requires checking must first be read from disk into memory. In addition,
DBCC CHECKDB uses tempdb to do sorting.
If actively performing transactions while DBCC CHECKDB is running, the
transaction log continues to grow because the DBCC command blocks log
truncation until it has finished reading the log.
It is recommended that DBCC CHECKDB be run during hours when the load is
light on the server. If DBCC CHECKDB is run during heavy peak usage time,
expect a performance hit on the transaction throughput as well as DBCC
CHECKDB completion time.
"Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
> Please Advice.
> I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
> Active/Passive Cluster configuration.
> I notice when I ran DBCC CheckDB on one of my Production
> databases the available RAM Memory decreases and fails to
> return on completion of the command.
> Please help.
>|||Given that CHECKDB reads every allocated page in the database, I'm guessing
the transaction load was such that the buffer pool grew while CHECKDB was
running (i.e. decreasing available memory) and has not shrunk again since.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eng$cC6zDHA.4064@.tk2msftngp13.phx.gbl...
> Hi
> BOL says:
> DBCC CHECKDB is a CPU- and disk-intensive operation. Each data page that
> requires checking must first be read from disk into memory. In addition,
> DBCC CHECKDB uses tempdb to do sorting.
> If actively performing transactions while DBCC CHECKDB is running, the
> transaction log continues to grow because the DBCC command blocks log
> truncation until it has finished reading the log.
> It is recommended that DBCC CHECKDB be run during hours when the load is
> light on the server. If DBCC CHECKDB is run during heavy peak usage time,
> expect a performance hit on the transaction throughput as well as DBCC
> CHECKDB completion time.
>
> "Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
> news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
> > Please Advice.
> >
> > I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
> > Active/Passive Cluster configuration.
> > I notice when I ran DBCC CheckDB on one of my Production
> > databases the available RAM Memory decreases and fails to
> > return on completion of the command.
> >
> > Please help.
> >
>|||Thanks Paul for your advice.
When running the DBCC CHECKDB statement there were no
open transaction. I ran the same statement on other
production databses one reported a similar behaviour i.e
the amount of memory available decreased but not much and
the others had little or no impact. What could be
responsible for this.
Your kind help appreciated.
>--Original Message--
>Given that CHECKDB reads every allocated page in the
database, I'm guessing
>the transaction load was such that the buffer pool grew
while CHECKDB was
>running (i.e. decreasing available memory) and has not
shrunk again since.
>--
>Paul Randal
>Dev Lead, Microsoft SQL Server Storage Engine
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:eng$cC6zDHA.4064@.tk2msftngp13.phx.gbl...
>> Hi
>> BOL says:
>> DBCC CHECKDB is a CPU- and disk-intensive operation.
Each data page that
>> requires checking must first be read from disk into
memory. In addition,
>> DBCC CHECKDB uses tempdb to do sorting.
>> If actively performing transactions while DBCC CHECKDB
is running, the
>> transaction log continues to grow because the DBCC
command blocks log
>> truncation until it has finished reading the log.
>> It is recommended that DBCC CHECKDB be run during
hours when the load is
>> light on the server. If DBCC CHECKDB is run during
heavy peak usage time,
>> expect a performance hit on the transaction throughput
as well as DBCC
>> CHECKDB completion time.
>>
>> "Olutimi" <anonymous@.discussions.microsoft.com> wrote
in message
>> news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
>> > Please Advice.
>> >
>> > I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
>> > Active/Passive Cluster configuration.
>> > I notice when I ran DBCC CheckDB on one of my
Production
>> > databases the available RAM Memory decreases and
fails to
>> > return on completion of the command.
>> >
>> > Please help.
>> >
>>
>
>.
>|||I think its still the buffer pool growing in response to CHECKDB reading all
the pages. The algorithms involved are beyond the scope of this forum to
explain. Try looking at Kalen's Inside SQL Server 2000 for details.
What's the actual difference in mem used by SQL Server before and after
running CHECKDB?
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Olutimi" <anonymous@.discussions.microsoft.com> wrote in message
news:047901c3d11c$dcdf0a40$a501280a@.phx.gbl...
> Thanks Paul for your advice.
> When running the DBCC CHECKDB statement there were no
> open transaction. I ran the same statement on other
> production databses one reported a similar behaviour i.e
> the amount of memory available decreased but not much and
> the others had little or no impact. What could be
> responsible for this.
> Your kind help appreciated.
> >--Original Message--
> >Given that CHECKDB reads every allocated page in the
> database, I'm guessing
> >the transaction load was such that the buffer pool grew
> while CHECKDB was
> >running (i.e. decreasing available memory) and has not
> shrunk again since.
> >
> >--
> >Paul Randal
> >Dev Lead, Microsoft SQL Server Storage Engine
> >
> >This posting is provided "AS IS" with no warranties, and
> confers no rights.
> >"Uri Dimant" <urid@.iscar.co.il> wrote in message
> >news:eng$cC6zDHA.4064@.tk2msftngp13.phx.gbl...
> >> Hi
> >> BOL says:
> >> DBCC CHECKDB is a CPU- and disk-intensive operation.
> Each data page that
> >> requires checking must first be read from disk into
> memory. In addition,
> >> DBCC CHECKDB uses tempdb to do sorting.
> >>
> >> If actively performing transactions while DBCC CHECKDB
> is running, the
> >> transaction log continues to grow because the DBCC
> command blocks log
> >> truncation until it has finished reading the log.
> >>
> >> It is recommended that DBCC CHECKDB be run during
> hours when the load is
> >> light on the server. If DBCC CHECKDB is run during
> heavy peak usage time,
> >> expect a performance hit on the transaction throughput
> as well as DBCC
> >> CHECKDB completion time.
> >>
> >>
> >>
> >> "Olutimi" <anonymous@.discussions.microsoft.com> wrote
> in message
> >> news:082901c3cf9f$7f0883c0$a401280a@.phx.gbl...
> >> > Please Advice.
> >> >
> >> > I run a SQL 2K SP3 on WIN 2K SP3 Advanced in an
> >> > Active/Passive Cluster configuration.
> >> > I notice when I ran DBCC CheckDB on one of my
> Production
> >> > databases the available RAM Memory decreases and
> fails to
> >> > return on completion of the command.
> >> >
> >> > Please help.
> >> >
> >>
> >>
> >
> >
> >.
> >
