High CPU - low IO, low RAM

Hi,
I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server SP4:
- set SQL server buffer cache at 16 MB, restarted;
- ran a read-intensive query (in terms of SQL Profiler data).
While the query was running CPU usage remained at or above 80%. But the disk
activity was not so high. Most of the time it remained under 30% despite the
minimal amount of RAM I allocated to the buffer cache. Disk queues were also
short, that is, way below 1 (by a few magnitudes). I'm not talking about
occasional spikes here.
So why do I get 10s of millions of (logical) reads in SQL Profiler but don't
see 100% load on a hard disk even with the tiny buffer cache?
-- Many thanks, Oskar.
What version of windows are you on Oskar and what disk performance counters
are you using, some of the counters in win 2000+ are a bit iffy to say the
least... ...also are you monitoring all disk arrays or just the array with
sql on?
"Oskar" wrote:

> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the disk
> activity was not so high. Most of the time it remained under 30% despite the
> minimal amount of RAM I allocated to the buffer cache. Disk queues were also
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but don't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>
|||"Ben UK" wrote:

> What version of windows are you on Oskar and what disk performance counters
> are you using, some of the counters in win 2000+ are a bit iffy to say the
> least... ...also are you monitoring all disk arrays or just the array with
> sql on?
1) > > ... Windows 2000 Server SP4
2) Those are single disks and, yes, I looked at the disks with SQL Server
databases. Mind you, the activity was there, but it was less than I expected.
The database files are on a dedicated disk and tempdb is on the OS disk.
[vbcol=seagreen]
> "Oskar" wrote:
|||On Mon, 8 Jan 2007 07:22:00 -0800, Oskar
<Oskar@.discussions.microsoft.com> wrote:
>So why do I get 10s of millions of (logical) reads in SQL Profiler but don't
>see 100% load on a hard disk even with the tiny buffer cache?
If the data is tiny, you can get a zillion logical reads while its
kept in cache, that's why they are LOGICAL reads!
In fact, if the data is well-indexed and just the *index* is cached,
and especially if the data actually fetched is limited, you might get
the pattern you see.
J.
|||If I understood it correctly, your guess is that the data my query is working
on is actually only a little bit bigger than the buffer cache size I
specified. I find this unlikely.
"JXStern" wrote:

> On Mon, 8 Jan 2007 07:22:00 -0800, Oskar
> <Oskar@.discussions.microsoft.com> wrote:
> If the data is tiny, you can get a zillion logical reads while its
> kept in cache, that's why they are LOGICAL reads!
> In fact, if the data is well-indexed and just the *index* is cached,
> and especially if the data actually fetched is limited, you might get
> the pattern you see.
> J.
>
|||On Mon, 8 Jan 2007 09:54:01 -0800, Oskar
<Oskar@.discussions.microsoft.com> wrote:

>If I understood it correctly, your guess is that the data my query is working
>on is actually only a little bit bigger than the buffer cache size I
>specified. I find this unlikely.
I'm just guessing, you should be able to tell how much data you're
fetching.
J.
[vbcol=seagreen]
>"JXStern" wrote:
|||This is actually what I would expect in this case. SQL Server does a lot of
things to optimize disk io. When you are doing pretty sequential reads the
IO subsystem will do read-ahead so a lot of data can be pulled into memory
with a single IO. The issue with low memory then becomes one of
continuously moving data around in the buffer pool to make room for new data
arriving from the disk and building the result set. This requires a lot of
processor cycles. I think what you're missing here (if your description is
correct) is that a lot of buffer space doesn't help you the first time you
run a query. When you scan a table the first time you have to read all the
sectors from disk no matter how much buffer memory you have. Where the
difference comes in is that if you have a lot of memory, the SECOND time you
scan a table, most of the data will be in buffer memory so it won't have to
be read from the disk.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Oskar" <Oskar@.discussions.microsoft.com> wrote in message
news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
> Hi,
> I did this experiment with an MS SQL Server 2000 SP4, Windows 2000 Server
> SP4:
> - set SQL server buffer cache at 16 MB, restarted;
> - ran a read-intensive query (in terms of SQL Profiler data).
> While the query was running CPU usage remained at or above 80%. But the
> disk
> activity was not so high. Most of the time it remained under 30% despite
> the
> minimal amount of RAM I allocated to the buffer cache. Disk queues were
> also
> short, that is, way below 1 (by a few magnitudes). I'm not talking about
> occasional spikes here.
> So why do I get 10s of millions of (logical) reads in SQL Profiler but
> don't
> see 100% load on a hard disk even with the tiny buffer cache?
> -- Many thanks, Oskar.
>
|||For Physical Disk monitoring use Disk Reads/ Sec & Disk Writes/ Sec coupled
with % Idle (100- %Idle = % Busy) for seeing how busy the disks are.. ..you
can then use Avg Disk Sec/ Read & Avg Disk Sec/ Write to measure the latency
and see if the disk is keeping up, and Avg Read Bytes/Sec & Avg Write
Bytes/Sec to understand the throughput.
You could also monitor Pages/Sec, Pages Input/Sec, Page Reads/Sec, Page
Output/Sec, Page Writes/Sec to see what is happening with regards to memory
and it's relation to the disk..
Check sql counters like Buffer Cache Hit Ratio, Lazy Writes/Sec, Avg Page
Life Expectency, Latch Waits/Sec, Avg Latch Wait Time to see how what your
doing is impacting sql.
Hope this helps
Ben
"Oskar" wrote:
[vbcol=seagreen]
>
> "Ben UK" wrote:
>
> 1) > > ... Windows 2000 Server SP4
> 2) Those are single disks and, yes, I looked at the disks with SQL Server
> databases. Mind you, the activity was there, but it was less than I expected.
> The database files are on a dedicated disk and tempdb is on the OS disk.
>
|||This is an interesting point. Because, if I'm right, it implies that it's
better to minimize the amount of the buffer cache allocated to a query that
does a large scan. The query would just waste precious CPU resources for
bringing data into the cache just to toss it out in a short while due to RAM
shortage.
As to the low disk activity: maybe this explains it all:
FROM
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlops6.mspx
<<
Note on Disk Queue Length
This counter no longer yields meaningful results in interpreting disk I/O
problems with SQL Server 2000. This is because the SQL Server engines
dynamically manage disk I/O, which is why the Max Async I/O setting no longer
exists. A simplified explanation is that SQL Server issues an order for disk
I/O, but does not wait for that process to complete before issuing another
request. When the I/O request has completed its work, the database engine is
notified and processes the results. SQL Server monitors the throughput on
these requests, and will manage the amount of I/O that the disks can
effectively handle.[vbcol=seagreen]
-- Many thanks, Oskar.
"Roger Wolter[MSFT]" wrote:

> This is actually what I would expect in this case. SQL Server does a lot of
> things to optimize disk io. When you are doing pretty sequential reads the
> IO subsystem will do read-ahead so a lot of data can be pulled into memory
> with a single IO. The issue with low memory then becomes one of
> continuously moving data around in the buffer pool to make room for new data
> arriving from the disk and building the result set. This requires a lot of
> processor cycles. I think what you're missing here (if your description is
> correct) is that a lot of buffer space doesn't help you the first time you
> run a query. When you scan a table the first time you have to read all the
> sectors from disk no matter how much buffer memory you have. Where the
> difference comes in is that if you have a lot of memory, the SECOND time you
> scan a table, most of the data will be in buffer memory so it won't have to
> be read from the disk.
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
>
>
|||"Greg Linwood" wrote:

> That disk queues are short only indicates that the disks are keeping up with
> requests.
> This shouldn't be surprising if either:
> (a) Disk requests are being managed by SQL's UMS
Not sure what do you mean...

> or
> (b) The disk/s are simply keeping up with the requests..
> Also, for a "read intensive query", do you mean a large table scan? If so,
> it's possible that fewer disk requests might be performed than you're
> expecting due to disk IO heuristics such as extent reads..
I didn't look at the execution plan - just the Reads column for
SQL:BatchCompleted and RPC:Completed events, so probably you're right that
some of the reads were read-ahead reads (maybe even a majority of them).
-- Thanks, Oskar.

> Regards,
> Greg Linwood
> SQL Server MVP
> http://blogs.sqlserver.org.au/blogs/greg_linwood
> "Oskar" <Oskar@.discussions.microsoft.com> wrote in message
> news:06F4686F-A738-49E5-8DEB-A7EBDA156843@.microsoft.com...
>
>