cache

SQL will use as much RAM on the server as you allow it.  Right after you start, less memory is used, but as the database starts doing queries, SQL Server will use all available memory.  In addition to whatever a database application does, it will use the RAM for

  • caching database objects (data pages and indexes which are also data pages) which has the object ID and database ID in the datapage
  • sorting (isnt’t that in tempdb?)
  • caching execution plans (perfmon counters SQL Server: SQL Statistics – Batch Requests/sec and Compilations/sec – recompiles should be low)
  • system tasks like anti-virus or remote desktop (or SSIS, SSRS and SSAS – or SSMS)

Brent Ozar has a query that will look at the objects and determine which database they are from.  If a large database is not often queried or only via a few tables, the cached data will be small.  The page life expectancy (PLE) Permon counter will tell how fast the cached data changes (in seconds and should be over 300 seconds) or is flushed due to non-use.  Low PLE and other problems can often be addressed with more RAM.  Poorly written applications might use a lot of memory or cause memory churn because they are badly written and therefore should be on their own machine, not the DB server.  Cached execution plans that are not reused take up cache, Optimize for Ad Hoc queries can help here.

Cached data is good data and will never change – unless SQLserver.exe changes the data (files are not shared with anything else).  In that case, the exe will know that the data in cache needs to be refreshed, or that it can’t be trusted and needs to be reloaded.  also recall that the data on the disk might be ‘bad’ but the same as data in cache – if the buffer hasn’t written changes out to the disk.

If we have efficient covering non-clustered indexes, maybe we don’t have to cache the actual database data for a wide table.  Use sys.dm_os_buffer_descriptors to determine which database tables are taking a lot of space in cache and which might be helped with a good NCI with enough covering fields.

Advertisements