Sql Server Caching

[pacharan]

Good question.

Now, if SQL Server is on the same box as you app/webserver, then the overheard of the database access to some data cached by SQL Server is going to be small, but still larger than a local cache in the due to the opening of a TCP or named pipe connection, the creation of the TDS datastream to SQL Server, SQL Server looking up the query plan and then retrieving the cached data, converting it to TDS protocol for the results and it getting into ADO.NET objects etc and reprocessing the data readers to get the data out.

If the SQL Server instance is on another box on the network, then you can add network latency to this too.

So, the answer is, hitting the database always going to be slower and perhaps much slower than accessing a memory cache in the same address space as the IIS process and for a heavily loaded website, this is going to be siginificant.

Also, a cache can be scaled using a distributed cache, whilst a SQL Server database is a bottleneck that scales up rather than out.

This is why products such as Alachisoft - NCache, Distributed Caching, Distributed .NET Cache, Java Distributed Cache, ASP.NET Session State, JSP Session Cache, StorageEdge, SharePoint Storage Optimization and Performance, Externalize BLOBs exist, otherwise everyone would just hit the database all the time.

Good answer

[pacharan]

Are you saying the results are cached ? or the query execution plan ?

I am not hot on DBA stuff but I recall that SQL server loads a lot of data pages into RAM for quick access and the size of this cache in RAM depends on how big the RAM is.

I don't know. If its only the execution plan that gets cached there's no guarantee that the requested data is in memory even if a load of data pages are loaded into memory.