Friday, August 28, 2015

I got a hunch about SQL Server fragmentation. Part I

I'm about a year behind in blogging, maybe more.  If I split my ideas into smaller bites, and make a series when appropriate, perhaps I'll get more blogging done.  Seems to work for me on Twitter :-)

I had a hunch about achieving maximum possible logical fragmentation in a SQL Server clustered index based on some work I'd done in a different database engine.  Finally decided to put my hunch to the test.

In a brand new database with a single data file, I created by test table.  The table is very simple, constructed so that each row will consume 1 8kb leaf page due primarily to the 4096 character varchar in the middle.

I inserted 4096 rows in the table, starting with clustered index key 4096 and decrementing the clustered index key along the way.

After inserting the rows into the table, I used the index physical stats function to get fragmentation information.

Aha!  You can see above that there are 4096 leaf pages, and 4096 fragments.  The average fragment size in pages is 1.

Merrill Aldritch made a great tool for inspecting physical page placement of small databases.  You can download version 1.2 of this tool at the following link.

Using this tool, its easy to verify that my hunch was correct: Inserting rows in order of decreasing key into an ascending clustered index leads to "perfect" or complete fragmentation from this perspective.

In the screenprint above, notice that the "previous page" for page ID 340 is page 341.  And the
"next page" for page ID 340 is page 339.  This is true throughout the entire clustered index - its in exactly the opposite order from expected.

Here's a teaser for stuff I'll look at in upcoming posts.

In this database, both "auto-create statistics" and "auto-update statistics" are disabled.  For now.

Wednesday, August 5, 2015

SQL Server Workspace Memory - with a Twist

In my recent blog post 'SQL Server Granted/Reserved/Stolen Workspace Memory in Perfmon' I hinted at a twist in the story of perfmon accounting of workspace memory.

Here's the stolen/free/database cache account of memory from perfmon on 'system B'.  Its a big memory system - a terabyte of RAM.


 At times more than 250 GB of RAM is stolen memory.  Wonder how much of that is used workspace memory?  How about inserting an Excel column for the difference between 'SQLServer:Memory Manager\Granted Workspace Memory (KB)' and 'SQLServer:Memory Manager\Reserved Server Memory (KB)'?

Whoa... reserved memory should be a subset of granted memory?!?

The amount of granted memory is so small I can't even see it on the same scale as the reserved memory. Adjust the scale and I can see that 'SQLServer:Memory Manager\Granted Workspace Memory (KB)' rarely has much memory accounted at all.

Well, I know that there are multiple Resource Governor workload groups on this server.  Here's a graph of their CPU activity during this observation time.

WrkGrp_4 is the main source of activity during the observation period. I wonder how these workgroups are distributed among Resource Governor resource pools?  Each resource pool added to the RG results in a new set of memory brokers added to the system, including a new memory broker to account for memory grants within the pool.

Sure - I could show the RG configuration but I'm a charts-n-graphs kinda guy :-)  Comparing this graph of Resource Pool CPU utilization to the previous graph, its clear each of the 4 workload groups is associated with its own resource pool.

With additional resource pools and their brokers in the mix,  the accounting of  'SQLServer:Memory Manager\Granted Workspace Memory (KB)' and 'SQLServer:Memory Manager\Reserved Server Memory (KB)' is expected to change.

We can see that 'SQLServer:Memory Manager\Granted Workspace Memory (KB)' only reports the granted query memory for the default resource pool.

With that being the case,  consistency would predict that 'SQLServer:Memory Manager\Reserved Server Memory (KB)' only report the unused portion of memory grants within the default resource pool.  If that were the case, 'SQLServer:Memory Manager\Reserved Server Memory (KB)' values would always be less than 'SQLServer:Memory Manager\Granted Workspace Memory (KB)' - we've already seen that's not the case.

Below is a transparent red overlay of  'SQLServer:Memory Manager\Reserved Server Memory (KB)' on top of a stacked graph of 'Active memory grant amount (KB)' for each of the resource pools.
That's more like it! 

So there's a fundamental difference between perfmon values for 'SQLServer:Memory Manager\Granted Workspace Memory (KB)' and 'SQLServer:Memory Manager\Reserved Server Memory (KB)': the granted memory values are ONLY for the default resource pool, while the reserved memory values are an aggregate across all resource pools.  Without that important tidbit, memory accounting can get pretty weird if there are active added resource pools to the Resource Governor configuration.

With that piece of knowledge, I add a column to Excel that sums the active memory grant amount for each of the resource pools, then subtract 'SQLServer:Memory Manager\Reserved Server Memory (KB)'.  The result is the total stolen/used workspace memory across all resource pools.  I wish perfmon had a counter for reserved memory per resource pool to accompany its granted per pool counter.  Alas - if you want that you're gonna hafta reap it from sys.dm_exec_query_memory_grants or sys.dm_exec_query_resource_semaphores.

Anyway - with my new computed column I can see that stolen/used query memory is that largest contributor to stolen memory during the observation, and based on the counters for granted per pool I know that the vast majority of the used/stolen query memory during the observation was against grants for ResPool_4.

There are three big reasons I like to track used query memory, especially in the context of Resource Governor:
1. Often query concurrency is limited by the amount of granted memory - but used query memory may be a small fraction of granted throughout.  If there's CPU to spare, using Resource Governor to limit the maximum query grant size can increase system performance by decreasing resource_semaphore waits and increasing query concurrency.
2. Sorting and hashing activity in workspace memory is not counted in the perfmon 'buffer page lookups' counter.  So when there is massive amounts of sorting, it can bend the relationship of CPU utilization to logical IO on the system.  Important to know when modeling system behavior.
3. If there are multiple resource pools active at the same time (other than the pre-existing default and internal pools), the system is vulnerable to out-of-memory exceptions when granted workspace memory becomes overcommitted and non-workspace memory steal/allocations fail.

But those stories will have to wait for yet another day...   

Once you graph the used query memory on a given system, if you see that its just a fraction of granted memory and are experiencing pending memory grants/resource_semaphore waits - you may want to use Resource Governor workload group max query memory grant to increase query concurrency.  An older blog post where I address that.

Resource Governor to restrict Max Query Memory Grant