#SQLServer: Imagine I were much better at creating graphics :-)

SQLInjun has something pretty interesting...
http://www.eraofdata.com/sql-server-flushcache-messages-might-actual-io-stall/

At first I couldn't figure out what was bugging me about the graph above.  Red for %Processor Time, Blue for Target Server Memory, Green for total server memory.

When SQL Server starts up, unless large pages are being used for the bpool, total server memory should (usually) grow steadily until it reaches target (max server memory or less if server conditions require).  If min server memory memory is set, total server memory should not dip below it after the first time it achieves min server memory.

The dip at 14:09 is understood - it was a SQL Server restart.  But there are other big dips - right around 12:30 and around 3:30.

The big question is actually not "what caused the big dips in total server memory", but what is responsible for the preceding growth which is out of line with the (I assume) bpool-driven trajectory preceding it?  I've crudely inserted parallelograms to illustrate what I mean... SQL Server something (optimizer? plan cache? column store prep?) consumed a lot of memory before the CPU spikes, then let it go.

Depending on what that memory consumer was, there are a number of spinlock scenarios that could have contributed to the high CPU.  It could also be that much of that memory was either large pages and there was a lot of work to scrounge up the contig memory for the large pages, or maybe a lot of that memory was cross-NUMA node (if SQL Server was using its default NUMA support).