Home / server / Questions / 83588
Accepted
Nai
Asked: 2009-11-12 05:04:27 +0800 CST

SQL Server 2005 and Caching

  • 772

Background to question: I'm looking to implement a caching system for my website. Currently we're exploring memcache as a means of doing this. However, I am looking to see if something similar exists for SQL Server. I understand that MySQL has query cache which although is not distributed works as a sort of 'stop gap' measure. Is MySQL query cache equivalent to the buffer cache in SQL Server?

So here are my questions:

  1. Is there a way to know is currently stored in the buffer cache?
  2. Follow up to this, is there a way to force certain tables or result sets into the cache? How much control do I have over what goes on in the buffer and procedure cache? I understand there used to be a DBCC PINTABLE command but that has since been discontinued.
  3. Slightly off topic: Should the caching even exists on the database layer? Or it is more prudent to manage caches using Velocity/Memcache? Is so, why? It seems like cache invalidation is something of a pain when handling many objects with overlapping triggers.

Thanks!

sql-server-2005

4 Answers

  1. I don't think your third point is off topic at all. If you're looking to implement caching for your website, I think it makes sense to use memcache or something similar at the web layer.

    One of the reasons you want to implement caching is to reduce the number of calls to your database in the first place. If you put the cache in the DB, you're not saving yourself much.

    Your performance will also depend a lot on your object model and how you are querying your database. If you are caching objects and all or most of their properties, you will get better cache performance than if you cache specific result sets for each page since each page that uses a particular object can use the same cache record.

    The database is going to have its own internal cache regardless of what method you choose. That is there to help your query performance when your web cache requires you to hit the db by not having to go to disk for every single data page.

    • 1
  2. Best Answer

    1) Yes you can see what is in the Sql Server buffer cache (2005 and 2008) --- DEMO: Buffer counts by object & index.sql --- Memory by Object. --- break down buffers by object (table, index) SELECT b.database_id, database_name = CASE b.database_id WHEN 32767 THEN 'Resource' ELSE db_name(b.database_id) END, p.object_id, Object_Name = object_name(p.object_id, database_id), p.index_id, buffer_count=count(*) FROM sys.allocation_units a JOIN sys.dm_os_buffer_descriptors b ON a.allocation_unit_id = b.allocation_unit_id JOIN sys.partitions p ON a.container_id = p.hobt_id WHERE object_id > 99 GROUP BY b.database_id, p.object_id, p.index_id ORDER BY buffer_count DESC

    2) You can pin tables in the cache in 2005 - this feature is being deprecated in 2008 ...I think that algorithm that SQL Server uses to keep hot tables in cache is better that you deciding that you want pin a table in the cache. Time would be better spent query tuning.

    3) It depends......

    • 1

  3. Apologies as this is about a year past its original posting, but just came across this question.

    In response to Joe E O's 2nd answer, '...You can pin tables in the cache in 2005...' pinning tables no longer works in SQL2005...and here's the culprit:

    http://www.sqlskills.com/BLOGS/PAUL/post/DBCC-PINTABLE.aspx

    and, I believe, has now been fully removed from SQL2008.

    HTH

    humbleDBA

    • 1

  4. A query cache is different from a buffer cache in that while the buffer cache contains actual data blocks the query cache contains results to specific queries. In other words the query cache is only in effect when query is identical or nearly identical to one that was previously run, often restricted to the same user.

    The buffer cache is in effect whenever a query accesses any data that was recently accessed by any other query.

    I'm afraid I don't know of a simple way to see what data is currently in the buffer pool, nor do I know of a supported way of pinning objects to the cache in SQL Server 2005. I might add that I'm primarely an Oracle DBA, so there's a chance someone with better knowledge on MS-SQL may shed some light on this.

    The bufer cache in the database layer is essential to application performance, despite any application level caching. There is arguably some overlap between system file cache the database buffer cache, and in some cases it may be beneficial to let the RDBMS bypass the system cache.

    • 0