Relative speed of physical and logical I/Os

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
>
>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?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:
>> 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