Perfmon And SQL Server Memory

Lonny Niederstadt looks at using Perfmon to understand what’s happening with memory allocations on your SQL Server instance:

Lets look at stolen memory a bit.  The relationship between memory grants and stolen memory is probably the least intuitive relationship.  Remember – if a query gets a memory grant the grant happens at the beginning of query execution.  Its just a promise of sort/hash memory to be made available when the query needs it.  The grant memory isn’t stolen immediately – rather its stolen in small allocations over time as needed by the query.

In the graph immediately below, the outstanding grants are shown over time.  There are no pending grants during the observation period.  Granted memory and reserved memory are both shown as areas, with reserved memory in front of granted memory.  Granted memory is consistently greater than reserved memory (in this case, no resource pools have been added beyond the pre-existing default and internal pools).  This is how we can determine that the reserved memory is granted memory which hasn’t been stolen yet.

This is a great explanation of what stolen memory is and why it’s important.

Related Posts

Service Broker External Activator And .NET Framework

Allen White walks us through a problem he experienced recently: My test environment is running SQL Server 2017 on Windows Server 2016, a pretty vanilla environment. After downloading the appropriate installer for the server where the service was to run, I installed it, made the necessary changes to the config file per the documentation provided […]

Read More

Using Powershell As Your Default Prompt On Windows Server Core

Patrick Gruenauer shows us how to make Powershell the shell of choice when running on Windows Server Core: Well, if you’re running a Server Core (I hope so, for domain controllers, dns, file services and more there’s no good reason to do not so), then it’s an ease to change that. The corresponding setting has […]

Read More

Categories

November 2016
MTWTFSS
« Oct Dec »
 123456
78910111213
14151617181920
21222324252627
282930