patternsqlMinor
Why are certain objects loaded into Buffer?
Viewed 0 times
whyobjectsareintoloadedcertainbuffer
Problem
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 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
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
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
Solution
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.
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.
Context
StackExchange Database Administrators Q#165378, answer score: 4
Revisions (0)
No revisions yet.