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

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

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

Registering SQL Cache Dependency in web.config. Why elsewhere, too?

I have a sqlCacheDependency element registered in web.config that I figured would set the database up to deliver all notifications for all its tables:

<

sqlCacheDependencyenabled="true">

<

databases>

<

addname="MyDb"connectionStringName="MyDbConnnectionString"/>

</

databases>

</

sqlCacheDependency>

I've seen docs saying that's all I need to do with SQL Server 2005, but that doesn't appear to be the case. I get the error:

The database 'MyDB' is not enabled for SQL cache notification.

To enable a database for SQL cache notification, please use the System.Web.Caching.SqlCacheDependencyAdmin.EnableNotifications method, or the command line tool aspnet_regsql. To use the tool, please run 'aspnet_regsql.exe -?' for more information.

Are the docs I read wrong or am I missing something?

I beleive the sytax would be:

<

caching>

<

sqlCacheDependencyenabled ="true" >

<

databases>

<

addname="MyDb"connectionStringName="MyDbConnnectionString" />

</

databases>

</

sqlCacheDependency>

</

caching>

And then on your aspx page:

<asp:sqldatasourceid="Whatever"runat="server"connectionstring="<%$ ConnectionStrings:MyDbConnnectionString %>"datasourcemode="DataSet"enablecaching="true"CacheKeyDependency="MyDb"selectcommand="Whatever_SP"selectcommandtype="StoredProcedure">

|||

The web.config file is set up correctly, my problem is that I can't get notification services working. I have to resort to polling although I have SQL Server 2005.

To add more detail, I'm using Windows XP Pro and I programatically add the SqlCacheDependency:

SqlCacheDependency myDependency = new SqlCacheDependency( "MyDb", "MyTable" );

Cache.Insert( blah,
blah,
...
myDependency
... );

Everything executes correctly, but unless I use the SqlCacheDependencyAdmin or aspnet_regsql to set up polling, it throws the error displayed in my first post. If I have polling set up, the error goes away, but I'm not taking full advantage of the performance benefits of SQL Server 2005 notification services.

|||Anyone have any ideas on this? I'm perplexed!|||

I think most of what you need is covered in the links below because it maybe permissions related. Hope this helps.

http://msdn2.microsoft.com/en-us/library/system.web.caching.sqlcachedependencyadmin.aspx

http://msdn2.microsoft.com/en-us/library/xh507fc5.aspx

|||

I wondered if there might be some permission issues, though I haven't discovered what permissions may be required.

What's curious is that I'd expect the SqlCacheDependency to throw some permissions exception in such a case, however, and I wasn't seeing that.

I'll have to keep digging...

|||

Bump! I was hoping the issue would go away when I moved our SQL Server to a development server, but the problem persists.

My progress:

1. I discovered SQL Server can't run under the local machine account for Notification Services to work properly so I switched to Network Service... didn't help.

2. Apparently you need to manually create a Service Broker endpoint and enable the database to use it. Also didn't help...

3. I read a lot of people said they have to call SqlDependency.Start() to get it working... didn't help.

|||When pemissions related problem cannot be traced it usually means the service in this case the Notification service may be SQL Server Agent dependent which means the account that runs the Agent must have Admin permissions. The reason is even to run Replications the Agent needs network access and DTS automation, the list goes on. Hope this helps.|||Okay, the latest in the saga is that a Microsoft support rep is claiming the SQL Server 2005 lost the ability to integrate with SqlCacheDependency through anything other than polling in the release version. This seems fishy, since it contradicts their documentation and a lot of other resources, so I'll see if I can get to the bottom of it.|||As I suspected, query notification-based cache invalidation is alive and well in SQL Server 2005. I did a little digging and ended up finding a couple of issues:

1. You can't use the constructor I was using to set up notification-based dependencies (you need to pass in the SqlCommand).
2. You need to exclude the command SET NOCOUNT ON from your stored procedure.

See here:http://forums.asp.net/1353521/ShowThread.aspx#1353521 for more.