Sql-server – Understanding SQL Server Cache

sql serversql server 2014

I have a tricky question and I do not exactly know where to find relevant information to this.

I have SQL Server 2014 Enterprise installed on a database server. On this server there exist multiple databases, some belong to us and some to other companies.

My concern is with cache memory. I have a basic understanding of how SQL Server uses cache memory to retrieve data pages fast. You run a query, and usually, data pages get loaded to the cache and with repeatable calls, it gets results from this.

But… our application is highly used in usual working hours by our customers but at night there are always maintenance tasks going on. I am assuming (please tell me if I am wrong) that during the day, the cache gets loaded with useful data pages, but at night it is deleted because of the maintenance tasks, and so the next day, when customers start doing transactions, data pages have to be again loaded into the cache first, which takes time and slows a lot of things down.

So, can I somehow prove if that happens?

If yes, can I manage it? Such as somehow telling SQL Server to not load into cache?

I would be grateful for some specific articles discussing this topic. I could not find any, or better say, I do not know how to name this problem.

Best Answer

but at night it is deleted because of the maintenance task

I would not say deleted as more "overwritten" or "evicted". Maintenance may simply use the memory for other things. Not a big issue normally.

and so a next day, when customers do transactions, data pages have to be again loaded to Cache, which takes time and slows a lot of things down.

If that is really that brutal the case, you have either an edge case or a hardware problem.

Generally you can preseed the cache by running a number of "maintenance queries" in the early morning (to reload the data into the cache at 0600 in the morning for example) as part of maintenance. I.e. you end it by querying for the data you want loaded into the cache. Problem solved.

But generally this should not result in an EXTREME slowdown - yes, initial requests will be slower, but that should fix itself quite fast as the cache is going to fill up with every query. Is it possible that your experienced slowdown is not "cache" but "morning spike"? There are a lot of systems where at specific times (one of them the morning when people log in and check their work) the load is a lot higher than later during the day. In this case, you need to scale the hardware up to handle this. WOuld be among the first times I would have seen maintenance as a very significant issue.

Related Solutions

Sql-server – Columns count in SELECT query and SQL Server buffer cache

SQL Server stores all the columns for one row together in a single disk page. (It's more complex than this but for int and char etc. this is more-or-less true.) To retrieve any column's value the whole page is read into RAM. So once you have any column available for a given row, all of the columns for that row are available.

There are several buffers / caches involved in responding to a query. One is the great big page bufferpool where pages read from disk are held in memory so execution plans can act on them. And yes, if one query causes a page to be loaded into the bufferpool all subsequent queries will use the same page from the bufferpool and not have to suffer the IO overhead. This is an incidental performance gain, however, and shouldn't be relied upon because SQL Server may choose to evict any page from the bufferpool at any time it needs the space.

Another set of buffering occurs when the output from your query gets sent over your connection to your application. This is (mostly) on a per-connetion basis so duplicating the work duplicates the resources required (more or less).

Another cost is the optimisations you miss out on when you return all columns. Covering indexes and "INCLUDES" columns are meaningless. Every read will have to retrieve the underlying clustered index (or heap) page. It's a fair bet that SQL Server will not be using specific indexes it might otherwise have chosen because a scan-the-cluster plan is cheaper than an index-and-cluster plan.

Sql-server – Help understanding discrepency between sys.dm_exec_cached_plans and dm_os_memory_clerks

FWIW - and I'm only posting this as a temporary answer because there's no way I could make this a digestible comment - I ran these queries and saw some discrepancies as well:

select cp.cacheobjtype, cp.objtype, 
  sum(cast(cp.size_in_bytes as DECIMAL(19,4)))/1024/1024 as sizeMB
from sys.dm_exec_cached_plans as cp
group by cp.cacheobjtype, cp.objtype
ORDER BY sizeMB DESC;

select mc.type, mc.pages_kb/1024 as pagesMB
from sys.dm_os_memory_clerks as mc
where mc.type LIKE N'CACHESTORE[_]%'
ORDER BY pagesMB DESC;

Most notably, the CACHESTORE_SQLCP total in the second query is less than the four Compiled Plan results in the first query, and there was substantial memory clerk usage under other cachestores as well. This is a lowly VM that doesn't have 300 GB of RAM but still demonstrates that these numbers do not always correlate and add up nicely.

Best Answer

Related Solutions

Sql-server – Columns count in SELECT query and SQL Server buffer cache

Sql-server – Help understanding discrepency between sys.dm_exec_cached_plans and dm_os_memory_clerks

Related Question