Relative speed of physical and logical I/Os

How much faster is a database access that is serviced from cache compared to
retrieved from disk. Obviously this is dependent on the specifics of disk
configurations, processor speed, memory speed, etc. but in terms of order of
magnitude, is cache access 100, 1000, 10000 times quicker or more?
Hi
Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
In theory. 250x.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Robin East" <robin_east@.hotmail.com> wrote in message
news:eg9vatBcFHA.228@.TK2MSFTNGP12.phx.gbl...
> How much faster is a database access that is serviced from cache compared
> to retrieved from disk. Obviously this is dependent on the specifics of
> disk configurations, processor speed, memory speed, etc. but in terms of
> order of magnitude, is cache access 100, 1000, 10000 times quicker or
> more?
>
|||Robin East wrote:
> How much faster is a database access that is serviced from cache
> compared to retrieved from disk. Obviously this is dependent on the
> specifics of disk configurations, processor speed, memory speed, etc.
> but in terms of order of magnitude, is cache access 100, 1000, 10000
> times quicker or more?
Memory access is measured in nanoseconds 10^-9. Disk access is measured
in milliseconds. 10^-3. This difference is extreme and measurable in
production systems.
The thing to remember when thinking of disk access in a RDBMS
environment is that the access does not occur in isolation. That is, a
single access to data on disk may take 3ms, which could return data in
an acceptable amount of time, but if 100 users all want data from disk,
that 3ms can look more like 30ms. And a 1,000 users can make it look
like 3 seconds. Contention in memory is much less noticeable because of
the increased speed.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Mike Epprecht (SQL MVP) wrote:
> Hi
> Most RAM runs at 60ns. Disks run at 15ms on good days (15000 ns)
> In theory. 250x.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
I think it's worse than that. Isn't 15ms = 15,000,000ns
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:et9x2$BcFHA.3620@.TK2MSFTNGP09.phx.gbl...
> The thing to remember when thinking of disk access in a RDBMS
> environment is that the access does not occur in isolation. That is, a
Just to add to this, I'd like to point out for the OP that this can be
monitored to some degree by watching the PhysicalDisk:Avg. Disk Read Queue
Length counter in perfmon. You can learn a lot about why you're having
performance problems by watching that counter... Also check out the
SqlServer:CacheManager:Cache Hit Ratio counter at the same time. What you'd
like to see is the queue length drop as the cache hit ratio goes up (meaning
that data in cache is being hit instead of the disk.)
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
|||As a rule of thumb: 100 to 1000 times faster.
To give you an idea: I ran a big query on one of my older servers (with
2 700Mhz CPU's and 4 10K RPM drives).
With a hot cache the results were: Scan count 2, logical reads 46536,
physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
= 842 ms.
With a cold cache, the results were: Scan count 2, logical reads 46541,
physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
time = 89005 ms.
As you can see, with a hot cache, the query took 0.8 seconds, with a
cold cache it took 89.0 seconds. With small reads, sequential read is
not possible (read-ahead count will usually be 0). Random reads are
slower than sequential reads, so what you are seeing in the example
above is really the best case for a cold cache.
HTH,
Gert-Jan
Robin East wrote:
> How much faster is a database access that is serviced from cache compared to
> retrieved from disk. Obviously this is dependent on the specifics of disk
> configurations, processor speed, memory speed, etc. but in terms of order of
> magnitude, is cache access 100, 1000, 10000 times quicker or more?
|||Thanks everyone who replied. I think Gerts answer was closest to what I was
looking for and expecting. I have greater experience with Oracle where, as a
general rule, the figure is closer to 100 than a 1000.
As a matter of interest, Gert, how come the hot cache results gave CPU
1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed time
before but usually only about 10-15ms.
Robin
>Re: Relative speed of physical and logical I/Os
>From: Gert-Jan Strik
>Date Posted: 6/13/2005 11:45:00 AM
>
[vbcol=seagreen]
>As a rule of thumb: 100 to 1000 times faster.
>To give you an idea: I ran a big query on one of my older servers (with
>2 700Mhz CPU's and 4 10K RPM drives).
>With a hot cache the results were: Scan count 2, logical reads 46536,
>physical reads 0, read-ahead reads 0, CPU time = 1531 ms, elapsed time
>= 842 ms.
>With a cold cache, the results were: Scan count 2, logical reads 46541,
>physical reads 65, read-ahead reads 40672, CPU time = 2203 ms, elapsed
>time = 89005 ms.
>As you can see, with a hot cache, the query took 0.8 seconds, with a
>cold cache it took 89.0 seconds. With small reads, sequential read is
>not possible (read-ahead count will usually be 0). Random reads are
>slower than sequential reads, so what you are seeing in the example
>above is really the best case for a cold cache.
>HTH,
>Gert-Jan
>
>Robin East wrote:
|||Robin East wrote:
> Thanks everyone who replied. I think Gerts answer was closest to what
> I was looking for and expecting. I have greater experience with
> Oracle where, as a general rule, the figure is closer to 100 than a
> 1000.
> As a matter of interest, Gert, how come the hot cache results gave CPU
> 1531ms and elapsed time only 842ms? I've seen CPU exceeding elapsed
> time before but usually only about 10-15ms.
>
That's because of a parallel plan on more than one processor. Parallel
plans are more expensive that their single-cpu plans, but with the
additional available CPUs, they can complete faster.
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||Of course, obvious really
regards
Robin
"David Gugick" <david.gugick-nospam@.quest.com> wrote in message
news:exPusPPcFHA.612@.TK2MSFTNGP12.phx.gbl...
> Robin East wrote:
> That's because of a parallel plan on more than one processor. Parallel
> plans are more expensive that their single-cpu plans, but with the
> additional available CPUs, they can complete faster.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com