Why are certain objects loaded into Buffer?

Question

I have been experimenting on my test environment with clearing buffer cache via

DBCC DROPCLEANBUFFERS

Before any work is done or operational queries run the buffer is instantly starting to repopulate with data from core tables (data warehouse). It is loading one dimension fully (3 years data) whist others are ignored or only partially loaded so for example

• Tab1 1490476 of 1490525 pages loaded (100%) (60m+ row dim)
• Tab2 86446 of 12737347 pages loaded (0.68%) (250m row+ fact)
• Tab3 14 of 18 pages loaded (77.78%) (800 row dim)

Tab1 would rarely have all rows read and updates/inserts would only occur on approximately the last 20% where as Tab3 would be 100% read pretty much all the time but rarely updated/inserted.

Interestingly when I test in a dev environment no data is reloaded in buffer until queries start to be run at which point I see a far different profile

• Tab1 89154 of 512569 pages loaded (17.39%)
• Tab2 105652 of 289851 pages loaded (36.45%)
• Tab3 18 of 18 pages loaded (100%)

These were just random reports and a couple of builds run against dev to get these figures but still fairly true to typical usage. The test environment did not change other than a higher dirty row count.

I am curious as to whether the buffer is being reloaded from something like the plan cache or is there something else i should be looking at?

The reason for this question is that I have a performance problem in production where there are significant IO waits when loading Tab2 and I am perplexed as to why Tab1 is being fully loaded despite limited use and in my simple mind a higher buffer % for Tab2 would make more sense.

2017-02-24
There were a couple of queries being passed down that were highlighted by BlitzCache on read sort that were reading an unnecessary amount of data from Tab1 but it also reads from Tab4 equally unnecessarily (Both joined from another table on Surrogate key in the read).

Tab4 pages are however 0.34% written to buffer which I would assume would have been higher if this was the cause of the high buffer content of Tab1.

There were some other issues highlighed by BlitzCache which I will investigate more fully but still at a bit of a loss here.

Answer 1

First, check the plan cache with queries that hit sys.dm_exec_query_stats. My favorite is the open source sp_BlitzCache (disclaimer: I'm one of the coauthors). Queries could be running outside of your control, like monitoring systems. I've seen third party tools that do things like check table fragmentation.

Next, not everything stays in the plan cache. Unfortunately - especially when you're dealing with monitoring software - some queries are smart enough to do a WITH (RECOMPILE) in order to avoid going into the plan cache. (sp_BlitzCache even does this.) To catch diabolical queries like this, consider doing a Profiler trace or Extended Events capture - although I wouldn't recommend leaving those in place long term. Only do it to satisfy your curiosity.

Answer 2

One possible reason for seeing something like this is that SQL Server may be doing maintenance operations behind the scenes. For example, if you had the AUTO_UPDATE_STATISTICS_ASYNC option set to yes for a database and kicked off a query that encountered stale stats then SQL Server defers the stats update to a background process. If you issued DBCC DROPCLEANBUFFERS after the query plan compilation but before the stats update happened it could appear that SQL Server would be loading data into the buffers without you requesting it.

I don't know how to find a complete list but remember that SQL Server loads data into memory before it can use it. So any maintenance operation that reads data could affect what's in the buffers. To give a few more examples, I expect that ghost record cleanup would have some effect on the buffers. I would also expect that the tuple mover for CCIs would also have some effect on the buffers. There are undoubtedly many other operations that go on behind the scenes.