As the following figure illustrates, below functions are executed in SQL Server (Overview):
1- TDS goes to the SNI component in SQL Server to be decoded into ‘Language Event’;
2- Command Parser parses the TSQL Query to validate the syntax, and uses Algebrizer component to generate ‘Query Tree’.
3- Query Tree been sent to ‘Optimizer’ to generate proper and good enough execution plan.
4- The ‘Query Plan’ sent to ‘Query Executor’ to queue up the sessions and execute the plan.
5- Access Methods component from Storage Engine gets the query plan and asks Buffer Manager to check whether the data pages are already loaded into memory, if it is not loaded yet, Buffer manager reads the data pages from data file(s) and load them into memory and changes the data, then mark the data page as dirty.
6- Access Methods sends the Transaction Records to the Transaction Manager to make the Transaction durable by writing it into the LDF file.
7- Then the result goes all the way back to SNI component to be encoded into TDS format and send to the client.
SQL Server’s plan cache is made up of four separate memory areas, called cache stores. There are actually other stores in SQL Server’s memory. The names in parentheses below are the values that can be seen in the type column of sys.dm_os_memory_cache_counters:
- Object Plans (CACHESTORE_OBJCP): Object Plans include plans for stored procedures, functions, and triggers
- SQL Plans (CACHESTORE_SQLCP): SQL Plans include the plans for adhoc cached plans, autoparameterized plans, and prepared plans.
- Bound Trees (CACHESTORE_PHDR): Bound Trees are the structures produced by SQL Server’s algebrizer for views, constraints, and defaults.
- Extended Stored Procedures (CACHESTORE_XPROC): Extended Procs (Xprocs) are predefined system procedures, like sp_executeSql and sp_tracecreate, that are defined using a DLL.
Each plan cache store contains a hash table to keep track of all the plans in that particular store. Each bucket in the hash table contains zero, one, or more cached plans. When determining which bucket to use, SQL Server uses a very straightforward hash algorithm.
The hash key is computed as (object_id * database_id) mod (hash table size). For plans that are associated with adhoc or prepared plans, the object_id is an internal hash of the batch text.
There are two main types of plans in the Object and SQL plan cache stores: compiled plans and execution plans. Compiled plans are the type of object we have been looking at up to this point. When SQL Server experiences heavy memory pressure, the policies used to remove cache objects ensure that our compiled plans are not the first objects to be removed.
execution contexts, are considered to be dependent on compiled plans and do not show up in the sys.dm_exec_cached_plans view. Executable plans are runtime objects created when a compiled plan is executed.
Plan Cache Size Management
Local Memory Pressure
If any single cache store grows too big, it indicates local memory pressure and SQL Server will start removing entries from that store only. This behavior prevents one store from using too much of the total system memory. If a cache store reaches 75 percent of the cache plan pressure limit, in single-page allocations or 50 percent of the cache plan pressure limit in multipage allocations, internal memory pressure is triggered and plans will be removed from cache.
In addition to memory pressure occurring when the total amount of memory reaches a particular limit, SQL Server also indicates memory pressure when the number of plans in a store exceeds four times the hash table size for that store, regardless of the actual size of the plans. The queries below can be used to determine the number of buckets in the hash tables for the object store and the SQL store, and the number of entries in each of those stores.
Global Memory Pressure
Global memory pressure applies to memory used by all the cache stores together, and can be either external or internal. External global pressure occurs when the operating system determines that the SQL Server process needs to reduce its physical memory consumption because of competing needs from other processes on the server. All cache stores will be reduced in size when this occurs.
Internal global memory pressure can occur when virtual address space is low. Internal global memory pressure can also occur when the memory broker predicts that all cache stores combined will use more than 80 percent of the cache plan pressure limit. Again, all cache stores will have entries removed when this occurs.
Plan Cache Cost
Evicting plans from cache is based on their cost. For adhoc plans, the cost is considered to be zero, but it is increased by one every time the plan is reused. For other types of plans, the cost is a measure of the resources required to produce the plan. When one of these plans is reused, the cost is reset to the original cost. For non–adhoc queries, the cost is measured in units called ticks, with a maximum of 31. The cost is based on three factors: I/O, context switches, and memory. Each has its own maximum within the 31-tick total.
- I/O: each I/O costs 1 tick, with a maximum of 19.
- Context switches: 1 tick each with a maximum of 8.
- Memory: 1 tick per 16 pages, with a maximum of 4.
When not under memory pressure, costs are not decreased until the total size of all plans cached reaches 50 percent of the buffer pool size. At that point, the next plan access will decrement the cost in ticks of all plans by 1. Once memory pressure is encountered, then SQL Server will start a dedicated resource monitor thread to decrement the cost of either plan objects in one particular cache (for local pressure) or all plan cache objects (for global pressure).
- Local Memory Pressure Can Increase The Respond Time Of Query Execution.
- Global Memory Pressure Can Reduce The Overall System Performance And Increases The I/O Operations.
- Both Local And Global Memory Pressure Can Increase The Processor Usage.
- Plan Cache Stores Are Out Of Buffer Pool Sections, Configure SQL Server Memory To Provide Enough Space For Plan Caches.