Question on DBCC DROPCLEANBUFFERS

This is a hastily written post in progress but I'm putting it out there anyway. I've just asked via #sqlhelp on Twitter why that I after I run a DBCC DROPCLEANBUFFERS command I can then query the memory clerk and buffer descriptor DMV's and still see allocations to my database.

Here's what I've been doing:

First, run a checkpoint command to write any dirty pages prior to running DBCC DROPCLEANBUFFERS to remove all the buffers from the buffer pool.

CHECKPOINT

DBCC DROPCLEANBUFFERSAfter that we'll query the memory clerks DMV to see how memory is being allocated in my instance:


SELECT TOP(10) [type] AS [ClerkType],
SUM(pages_kb) / 1024 AS [ClerkMb]
FROM sys.dm_os_memory_clerks WITH (NOLOCK)
GROUP BY [type]
ORDER BY SUM(pages_kb) DESC OPTION (RECOMPILE);

What I can see here is that 2Mb is allocated to the Buffer Pool.

I can drill into this in a bit more detail by querying the buffer descriptors DMV where we see the page allocation detail per database.

SELECT DB_NAME(database_id) AS DatabaseName,

page_type, COUNT(page_id) * 8 As SizeKb

FROM sys.dm_os_buffer_descriptors

GROUP BY page_type, DB_NAME(database_id)

In this case the AdventureWorks2014 database has 360kb of data pages and 168kb of index pages; out of interest the NULL DatabaseName rows refer to the ResourceDb.

So my question is, what is persisted in memory after I have dropped the buffer pool?!

UPDATE:

Spoke to a couple of people via #sqlhelp on twitter. We're none the wiser really and I will come back to this and use DBCC Page to look in a bit more detail but for now its a safe assumption that the pages in memory are there and related to SQL Server "doing things" behind the scenes!

TRACEWRITE and querying sys.traces

This post is about a wait type that I have seen being safely ignored by admins in the past but actually it does highlight an particular that I like to check up on in systems that I’m maintaining.

The wait type I’m referring to is TRACEWRITE and it’s easy to see why it often gets classed as being safe to ignore. SQL Server does run a default trace continuously so naturally this wait will show up in wait stat queries for your instances and be quite prominent (like the one below) but that said; whilst it’s relatively safe for the default trace to be ruled out of any further investigation what about traces that have been started by other users?

I typically see a scenario in non-production systems where a trace has been turned on to monitor a particular process for testing and then forgotten about and not stopped again once the process has finished. Tracing is not particularly cost friendly at the best of times so one running way beyond its expected duration is something you do want to know about.

The following query looks at what traces are currently running on the system:

SELECT

start_time,

stop_time,

[status],

[path],

is_default,

reader_spid

FROM sys.traces

 

This query returns information on all the current traces running on your system. It’s worth adding at this point that tracing to some degree (I still see tracing, a lot! has been replaced by extended events which are much more lightweight in operation, so another reason to be checking. Here's what a default trace looks like:

The columns returned are pretty self-explanatory, a value of 1 in the status column means the trace is currently running (0 = stopped) and you can use the reader_spid column to return information on who is using it, and who to ask about turning the trace off.


To summarise; in most cases trace related waits like TRACEWRITE are safe to ignore but it's good practice to routinely run a check on those systems where people might be running traces or you could even set up jobs that will automatically stop any traces outside of working hours.


One final point, it is very common for monitoring applications to sue some form of trace analysis, these could well be part of routine maintenance so prior to switching anything off ensure you know how the trace has originated and what its purpose is.