Paging

What does it mean when your cpu is low (20%ish), you avg
disk queue length is reasonable and your pages/second is
through the roof for about 15 minutes? (50-2000 pages/sec)
Not enough RAM I assume? Or could there be something else
causing it?
Could be that data is being read from disk that wasn't in memory, although
you need a lot of data to keep that up for 15 minutes. More likely that
there is an operation going on that doesn't fit in memory completely and SQL
Server is using tempdb to store intermediate results. ORDER BY or DISTINCT
on a large dataset is a common cause for this.
You can use SQL Profiler and trace Error and Warnings:Hash Warning. If you
get these it's a sign of the above mentioned problem.
Jacco Schalkwijk
SQL Server MVP
"Baffled" <anonymous@.discussions.microsoft.com> wrote in message
news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
> What does it mean when your cpu is low (20%ish), you avg
> disk queue length is reasonable and your pages/second is
> through the roof for about 15 minutes? (50-2000 pages/sec)
> Not enough RAM I assume? Or could there be something else
> causing it?
|||I just did some research and found three things were going
on during that time period (Sql Server 2000 btw):
1. A checkdb of a good sized database (about 95G).
2. Snapshot replication to two servers.
3. Backups of the same database that was 95g above.
I'm pretty sure #3 is low level - we took compression
out. Can #1 or #2 swallow up a lot of RAM?
Btw, it was at the very end of the checkdb that the paging
occurred, i.e. the last 15-20 minutes of a 2+ hour job.
Do you know if checkdb is particulary ram/cpu intensive at
the very end for some reason?

>--Original Message--
>Could be that data is being read from disk that wasn't in
memory, although
>you need a lot of data to keep that up for 15 minutes.
More likely that
>there is an operation going on that doesn't fit in memory
completely and SQL
>Server is using tempdb to store intermediate results.
ORDER BY or DISTINCT
>on a large dataset is a common cause for this.
>You can use SQL Profiler and trace Error and
Warnings:Hash Warning. If you
>get these it's a sign of the above mentioned problem.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Baffled" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
pages/sec)[vbcol=seagreen]
else
>
>.
>
|||I just did some research and found three things were going
on during that time period (Sql Server 2000 btw):
1. A checkdb of a good sized database (about 95G).
2. Snapshot replication to two servers.
3. Backups of the same database that was 95g above.
I'm pretty sure #3 is low level - we took compression
out. Can #1 or #2 swallow up a lot of RAM?
Btw, it was at the very end of the checkdb that the paging
occurred, i.e. the last 15-20 minutes of a 2+ hour job.
Do you know if checkdb is particulary ram/cpu intensive at
the very end for some reason?

>--Original Message--
>Could be that data is being read from disk that wasn't in
memory, although
>you need a lot of data to keep that up for 15 minutes.
More likely that
>there is an operation going on that doesn't fit in memory
completely and SQL
>Server is using tempdb to store intermediate results.
ORDER BY or DISTINCT
>on a large dataset is a common cause for this.
>You can use SQL Profiler and trace Error and
Warnings:Hash Warning. If you
>get these it's a sign of the above mentioned problem.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Baffled" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
pages/sec)[vbcol=seagreen]
else
>
>.
>--Original Message--
>Could be that data is being read from disk that wasn't in
memory, although
>you need a lot of data to keep that up for 15 minutes.
More likely that
>there is an operation going on that doesn't fit in memory
completely and SQL
>Server is using tempdb to store intermediate results.
ORDER BY or DISTINCT
>on a large dataset is a common cause for this.
>You can use SQL Profiler and trace Error and
Warnings:Hash Warning. If you
>get these it's a sign of the above mentioned problem.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Baffled" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:0a6301c4ad5f$191bd7d0$a501280a@.phx.gbl...
pages/sec)[vbcol=seagreen]
else
>
>.
>
|||I don't think there is anything in particular going on towards the end of
DBCC CHECKDB. You might get a lot of activity with DBCC CHECKDB if you have
an error, for example a corrupt index. But that can happen at any time
during DBCC CHECKDB, not specifically at the end.
In the scenario you describe there is of course a lot of activity going on
at the same time. Can you give some more information about your disk set up?
Is everything on the same disk/array or on multiple disks?
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:268f01c4ad66$8102cb90$a401280a@.phx.gbl...[vbcol=seagreen]
>I just did some research and found three things were going
> on during that time period (Sql Server 2000 btw):
> 1. A checkdb of a good sized database (about 95G).
> 2. Snapshot replication to two servers.
> 3. Backups of the same database that was 95g above.
> I'm pretty sure #3 is low level - we took compression
> out. Can #1 or #2 swallow up a lot of RAM?
> Btw, it was at the very end of the checkdb that the paging
> occurred, i.e. the last 15-20 minutes of a 2+ hour job.
> Do you know if checkdb is particulary ram/cpu intensive at
> the very end for some reason?
>
> memory, although
> More likely that
> completely and SQL
> ORDER BY or DISTINCT
> Warnings:Hash Warning. If you
> message
> pages/sec)
> else