Memory Utilization

Memory utilization is an important area considering for performance tuning of an environment.

When we executes a query, there would 3 consumers for memory:
1. Compile
Compilation is a process of generating a compiled plan for each execution. It requires a significant amount of memory to
find out the optimal plan for the query.
2. Cache
Once we got the optimal plan, the plan has to be cached in athe server memory.
3. Memory grant
When we have sorting and hashing, there would be a consumption of memory while executing the query.
Grant can be again devided into "Required Memory" and "Additional Memory".
The required memory to execute a query can be called as "Required". This is vital one as lack of this memory, the query never executes.
However additional memory is for sorting and hashing.Depends on the data to be sorted, the size of the memory may increase depends on the cardinatlity of the data.

Apart from the above, there would be some consumption of memory for Degree of Parallelism(CXPACKET).
When a query executes in parallel, it uses memory to split the process and for each worker thread separate memory to do the sort operation.

Resource semaphore distributes the memory to different processes depends on the availability.

SQL server 2005 ownwards there is no upper limit for the caching.
Both data and procedure cache allocate as per the request clearing the old/obsolete entries(its a huge area; shortened).
SQL Server has divided its memory for two major categories:

1. Memory for Data caching
- All data has been cached in this particular area.
2. Memory for Procedure cache
- Procedure cache contains all cached plans for procedures, funtions, triggers, adhoc etc...


Major elements in Procedure Cache objects are as below:
1. CACHESTORE_OBJCP
2. CACHESTORE_SQLCP

The below script will show you how your memory been divided(took only top 6 most consuming area)

SELECT  TOP 6
 LEFT([name], 20) as [name],
 LEFT([type], 20) as [type],
 [single_pages_kb] + [multi_pages_kb] AS cache_kb,
 [entries_count]
FROM sys.dm_os_memory_cache_counters
order by single_pages_kb + multi_pages_kb DESC

CACHESTORE_OBJCP
These are compiled plans for stored procedures, functions and triggers.

CACHESTORE_SQLCP
These are cached SQL statements or batches that aren't in stored procedures, functions and triggers. This includes any dynamic SQL or raw SELECT statements sent to the server.

CACHESTORE_PHDR
These are algebrizer trees for views, constraints and defaults. An algebrizer tree is the parsed SQL text that resolves the table and column names.

There are few things that we need to look for memory pressure.