Transcript Document
A deep dive into SQL Server
Plan Cache Management
Decrease in throughput or an increase in query response time
Out-of-memory errors or connection timeout errors
Query executions and/or query compiles timeout errors
WAITs
CMEMTHREAD wait type
RESOURCE_SEMAPHORE_QUERY_COMPILE
High CPU usage in SQL Server 2012 or 2014 due to plan cache
contention
This is a result of
Large number of memory pressure events
Memory contention
Large number of compilations
The plan cache is used to save all the
execution plans in case they can be reused
Plan caching enables SQL Server to reuse
plans to avoid recompilation
The total size of memory is dynamic by
default, and the space used for query plans
is also very fluid
Plan Cache
The content of the plan cache, cache object types and caching
mechanisms
4 stores contain query plans:
Object Plans (CACHESTORE_OBJCP)
SQL Plans (CACHESTORE_SQLCP)
Bound Trees (CACHESTORE_PHDR)
Extended Stored Procedures (CACHESTORE_XPROC)
Stores and their sizes
sys.dm_os_memory_objects
sys.dm_exec_cached_plans
sys.dm_os_memory_cache_counters
6
Determining the Cache Store Pressure Limit value
SQL Server 2005 SP1 on a 64-bit SQL Server 28 GB TM
The single cache store pressure limit: (75% of 8 GB) + (50% of 20GB) = 6GB + 10GB =16 GB
SQL Server 2012 RTM 64-bit with 28 GB TM
The single cache store pressure limit : (75% of 4 GB) + (10% of 24 GB)= 3GB + 2.4GB = 5.4 GB
Local
When any single cache store grows too big
A cache store reaches 62.5 percent of it’s cache store pressure limit value
SQL Server 2012 RTM 64-bit with 28 GB TM
The single cache store pressure limit :
5.4 GB
Internal memory pressure is triggered at: 62.5*5.4GB=3.375GB
Global
External global pressure (from OS)
Internal global memory pressure can occur
when virtual address space is low
when the memory broker predicts that all cache stores combined will use more than
80 percent of the plan cache pressure limit
SQL Server 2012 RTM 64-bit with 28 GB TM
Internal Memory Pressure Event triggers at:
62.5% of The plan cache pressure limit =62.5%*5.4 GB=3.375GB.
Eviction policy Algorithm
zero-cost plans are removed from cache
the cost of all other plans is reduced by half
One cycle updates the cost of at most 16 entries for every cache store
Costing of cache entries
Adhoc plans
cost is considered to be zero, increased by 1 every time the plan is reused
Non-adhoc queries
based on three factors: I/O, context switches, and memory
On plan re-use the cost is reset to the original cost
Detecting memory pressure
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 160,036 plans in a specific cache store
Heavy contention for the SOS_CACHESTORE spinlock and high CPU
usage on SQL Server 2012/2014
Resolution
Apply hotfix http://support.microsoft.com/kb/3026083/EN-US
Fixed in SQL Server 2012 SP1 CU 14
Fixed in SQL Server 2014 CU 6
To apply the change: -T174
Optimize for AdHoc=True
Also: -T8032 (be careful) - Reverts the cache limit parameters to the SQL Server 2005 RTM
setting
SQL Server: Plan Cache/Cache Pages(_Total)
SQLServer: BufferManager/Database pages
Detecting excessive compiles and recompiles:
SQL Statistics\SQL Compilations/sec
SQL Statistics\SQL Re-Compilations/sec
Memory Manager\Target Server Memory (KB)
Memory Manager\Total Server Memory (KB)
Always specify Optimize for AdHoc=true
Always configure MAX SERVER MEMORY Setting
Monitor the size and usage of your plan cache
Know your thresholds
Check for wasted space amount on a regular basis
Be aware of some situations that cause multiple query plans for the
same procedure to be saved in cache
A difference in certain SET options
A need to go trough the implicit name resolution process
Apply CU and fixes for SQL Server 2012/2014 if you encounter high
CPU due to spinlock contention problems
Apply –T8032 only after careful analysis, monitor the buffer pool
after that
Sometimes periodically cleaning the Adhoc plan cache could work
better if the problem is located in SQLPlans cache store
DBCC FREESYSTEMCACHE('SQL Plans') for usecount=1 and objtype IN ('Adhoc',
'Prepared')
Change the application (Yes, I know )
Questions?
[email protected]
[email protected]
www.maginaumova.com
Explore Everything PASS Has to Offer
Free SQL Server and BI Web Events
Free 1-day Training Events
Regional Event
This is Community
Business Analytics Training
Local User Groups Around
the World
Session Recordings
PASS Newsletter
Free Online Technical Training