Because all pages are hashed in an in-memory storage cache, tasks requiring a small number of private buffers for storing intermediate pages, for example during sorting, use the default data cache. Use the buf_imdb_privatebuffer_grab monitor counter to determine if Adaptive Server is temporarily using the default data cache for sort buffers:
buf_imdb_privatebuffer_grab buffer_0 244 510525
This script collects monitor counters from all monitoring groups. It first clears all the monitor counters, samples the monitor counters for one minute, and reports values for all monitor counters updates for that monitoring interval.
To determine the current value for buf_imdb_privatebuffer_grab, run:
dbcc monitor ('clear','all','on')
go
dbcc monitor ('sample','all','on')
go
waitfor delay "00:01:00"
go
dbcc monitor ('sample','all','off')
go
dbcc monitor ('select','all','on')
go
select * from master.dbo.sysmonitors
where field_name = 'buf_imdb_privatebuffer_grab'
go
The value for buf_imdb_privatebuffer_grab indicates the default data cache’s buffer usage from queries that require temporary buffers for intermediate sorting that are run against tables in an in-memory database. Evaluate the value for buf_imdb_privatebuffer_grab according to the size of the default data cache:
If the number of buffers grabbed is very low compared to the size of the default data cache, queries run against tables in in-memory databases are not heavily using temporary buffers.
If the number of buffers grabbed is a significant portion of the number of buffers in the default data cache, it indicates a heavy load on the default data cache for buffers used for queries running against in-memory databases. This generally occurs only when the default data cache is very small, for example, if it is using the default size of 8MB.
Using a small default data cache may affect the performance of other applications that rely on the default data cache. Increase the size of the default data cache to accommodate both requests for temporary buffer usage that come from in-memory databases, and to accommodate other concurrent applications using the same cache.
See Chapter 2, “Monitoring Performance with sp_sysmon,” in Performance and Tuning Series: Monitoring Adaptive Server with sp_sysmon.
