Understanding SQL Server Cache

Question

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.

Answer 1

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.

Answer 2

You are probably looking for PLE, or Page Life Expectancy. PLE is the amount of seconds that a data page resides in the cache on average.

SQL Server will only return values to the application or client once they are in the cache/buffer pool. You will see memory pressure or a very low PLE if your instance does not have enough memory/RAM to perform data intensive operations. This means SQL Server is constantly moving data pages in to the cache, then shoveling them out to make room for the other data pages it need and doing physical reads (reading from disk instead of memory, which is I/O intensive).

To prove what you are looking for, start capturing PLE by node on your instance every 15 minutes for like a week. You should see it going up and down based on overall usage and intensive operations. If you want to, you can also look up some scripts to capture reads/writes on expensive operations to find the trouble queries.

In your specific example about daily maintenance tasks, that absolutely could be an issue. A series of index rebuilds could wipe out all useful data pages that your apps use during the day, so the first time those queries/procs/prepared statements run in the morning, they have to flush some data pages to make room for the ones needed that got read in from the disk.

Query stolen from the DBA Stack Exchange:

SELECT [object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters -- https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-performance-counters-transact-sql
WHERE [counter_name] = 'Page life expectancy' --if multiple NUMA on a server should return multiple Nodes, 
OR [counter_name] = 'Free list stalls/sec'  -- Number of requests per second that had to wait for a free page https://learn.microsoft.com/en-us/sql/relational-databases/performance-monitor/sql-server-buffer-manager-object
OR [counter_name] = 'Lazy writes/sec' --Flushes of dirty pages before a checkpoint runs.  
OR [counter_name] = 'Buffer cache hit ratio' --percentage of pages found in the buffer cache without having to read from disk you want this ratio to be high
Order by [counter_name] DESC, [object_name];

Answer 3

sql has 4 steps for executing command and in last step it decided to how to get data. according to your scripts it my decide to get data from mdf or from cache

sql manage its cache by itself, it get data into its cache and whenever new data collected check its cache , if there is a free apace then it put it there if not, it Throw out the query which used The farthest time.

maintenance operations usually execute for update statistic , defrag index or rebuild it, shrink databases etc which it can Throw out your cached data from cache