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.