A quick look at: sys.dm_os_buffer_descriptors

SQL Server places data into cache as it reads it from disk so as to speed up future queries. This dmv lets you see how much data is cached at any given time and knowing how this changes over time can help you ensure your servers run smoothly and are adequately resourced to run your systems.

This dmv gives the number of cached pages in the buffer pool along with the database id that they relate to:

This gives you results which are quite useful, but if you add a new column with the code to convert the pages value to show a MB value then they become more relevant and meaningful.

To see how your server reacts to queries, start up SSMS and connect to a test server and database – mine is called AdventureWorks2008. Make sure you start from a know position by running:

Now we can run a query that would normally turn a DBA’s hair white:

…and then check our cache situation: A nice low figure – not! Almost 2000 pages of data in cache equating to approximately 15MB. Luckily these tables are quite narrow; if this had been on a table with more columns then this could be even more dramatic.

So, let’s make our query more efficient. After resetting the cache with the DROPCLEANBUFFERS and FREEPROCCACHE code above, we’ll only select the columns we want and implement a WHERE predicate to limit the rows to a specific customer.

…and check our effect cache we have a result that is more sympathetic to our server and the other systems sharing its resources.

I can hear you asking: “What has this got to do with logging, Jonathan?”

Well, a smart DBA will keep an eye on this metric on their servers so they know how their hardware is coping and be ready to investigate anomalies so that no ‘disruptive’ code starts to unsettle things.

Capturing this information over a period of time can lead you to build a picture of how a database relies on the cache and how it interacts with other databases. This might allow you to decide on appropriate schedules for over night jobs or otherwise balance the work of your server.

You could schedule this job to run with a SQL Agent job and store the data in your DBA’s database by creating a table with:

…and then filling it with:

After this has been left logging your system metrics for a while you can easily see how your databases use the cache over time and may see some spikes that warrant your attention.

This sort of logging can be applied to all sorts of server statistics so that you can gather information that will give you baseline data on how your servers are performing. This means that when you get a problem you can see what statistics are out of their normal range and target you efforts to resolve the issue more rapidly.