![Page 1: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/1.jpg)
Managing SQL Server’s Plan Cache
Richard Douglas
![Page 2: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/2.jpg)
2 Global MarketingPlan Cache
Agenda
• Introductions
• What is the plan Cache?
• Plan Cache Memory Usage
• Finding problem plans in the cache
• Events near you
• Q&A
![Page 3: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/3.jpg)
3 Global MarketingPlan Cache
Your host
• Richard Douglas
• Systems Consultant
• SQL Server MCITPro
• Maidenhead SQL User Group Leader
• Blog: http://SQL.RichardDouglas.co.uk
• Twitter: @SQLRich
• Email: [email protected]
![Page 4: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/4.jpg)
4 Global MarketingPlan Cache
What is the plan cache?
![Page 5: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/5.jpg)
5 Global MarketingPlan Cache
Query optimization explained simply
1. Query submitted
2. Magic happens
3. Shedload of data returned
![Page 6: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/6.jpg)
6 Global MarketingPlan Cache
Plan cache simplified
Query submitted
Run with planCompile
Check for plan in cache
Doesn’t exist Exists
CPU Intensive
Stored in memory
The more plans, the longer this takes. Be mindful of “cache bloating”
![Page 7: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/7.jpg)
7 Global MarketingPlan Cache
Recompilation Process
From “Plan Caching in SQL Server 2008” by Greg Low
![Page 8: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/8.jpg)
8 Global MarketingPlan Cache
Finding Compiles / Recompiles
![Page 9: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/9.jpg)
9 Global MarketingPlan Cache
(Re)Compile Tip
![Page 10: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/10.jpg)
10 Global MarketingPlan Cache
Call & Hit Rates in SoSSE
![Page 11: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/11.jpg)
11 Global MarketingPlan Cache
Recompilations• If schema changes are made to any dependencies of code then a
recompilation will occur. This includes:– Tables– Views– Procedures– Indices
• Reaching statistics thresholds:
• An explicit call to sp_recompile.
• Executing a stored procedure using the WITH RECOMPILE option.
![Page 12: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/12.jpg)
12 Global MarketingPlan Cache
Plan Cache Memory Usage
![Page 13: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/13.jpg)
13 Global MarketingPlan Cache
Plan Cache Memory Usage
SQL Server Version Cache Pressure Limit
SQL Server 2012, SQL Server 2008 and SQL Server 2005 SP2
75% of visible target memory from 0-4GB + 10% of visible target memory from 4Gb-64GB + 5% of visible target memory > 64GB
SQL Server 2005 RTM and SQL Server 2005 SP1
75% of visible target memory from 0-8GB + 50% of visible target memory from 8Gb-64GB + 25% of visible target memory > 64GB
SQL Server 2000 SQL Server 2000 4GB upper cap on the plan cache
32gb = 5.8gb cache64gb = 9gb cache128gb = 12.2gb cache
![Page 14: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/14.jpg)
14 Global MarketingPlan Cache
Ageing out plans
Cost = I/O cost + context switch cost (a measure of CPU cost) + memory cost
The individual parts of the cost are calculated as follows.
• Two I/O’s cost 1 tick, maximum of 19 ticks.
• Two context switches cost 1 tick, maximum of 8 ticks.
• Sixteen memory pages (128 KB) cost 1 tick, maximum of 4 ticks.
![Page 15: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/15.jpg)
15 Global MarketingPlan Cache
Finding problem plans in the cache
![Page 16: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/16.jpg)
16 Global MarketingPlan Cache
Finding Problem Plans in the Cache
In this section:
• Examples of cache bloat
• Finding IO intensive queries
• Finding CPU Intensive queries
• Looking for certain plan operations
• Looking at Individual plans
![Page 17: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/17.jpg)
17 Global MarketingPlan Cache
Summary
• The reason for the plan cache.
• Monitoring the plan cache.
• Plan cache memory utilisation.
• Demonstrated how to find:– Cache bloat– Particular operators– High resource queries
![Page 18: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/18.jpg)
Pg. 18© 2012 Quest Software Inc. All rights reserved.
Solution Area Product DescriptionFast, flexible backup and recovery with industry-leading compression technology
Discover and resolve performance issues in production before they impact end users and service levels
Deepest possible understanding of database performance and norms
Plan and develop applications that deliver both functionality and
optimal performance
Project Lucy
Backup and Recovery
Performance & Operations
Performance Tuning
Development
Comprehensive schema, object, security and change management
Administration
Community crowdsourcing for SQL Server tracing, health checks and performance information!
Community, Knowledge, Training
Product Offerings
SpotlightEssentials.com
Free !!!
![Page 19: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/19.jpg)
SQLRelay Dates 11/11/13 – Reading
12/11/13 – Southampton
13/11/13 – Cardiff 14/11/13 – Birmingham 15/11/13 – Hemel
Hempstead
25/11/13 – Newcastle 26/11/13 – Manchester 27/11/13 – Norwich 28/11/13 – Bristol 29/11/13 – London
Register at
sqlrelay.co.uk
@SQLRelay2013
![Page 20: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/20.jpg)
20 Global MarketingPlan Cache
SQL Rally
Amsterdam (Holland): 6 – 8th Nov
http://www.sqlpass.org/sqlrally/2013/amsterdam/Home.aspx
Stockholm (Sweden): 4-6th Nov
http://www.sqlpass.org/sqlrally/2013/nordic/Home.aspx
![Page 21: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/21.jpg)
21 Global MarketingPlan Cache
What Do Developers Need to Know About SQL Server?
Join Microsoft Certified Master Brent Ozar & Richard Douglas:
The database comes with a manual, but let's be honest: nobody's got time for that. Database administrators - let's distill the most important concepts and rules for developers down to 140-character chunks on Twitter on Thursday, October 24th from 10AM to 11AM Eastern.
Microsoft Certified Master Brent Ozar will lead the discussion at #dellsql.
Register now - http://bit.ly/H6XeDU
![Page 22: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/22.jpg)
22 Global MarketingPlan Cache
What would you like to see?
• Best Practices– DBA– Developer
• Indexing
• Baselining
• SSAS
• SSIS
• SSRS
• Always On Availability Groups
• Backups
• Clustering
• Database Mirroring
• Performance Monitoring
• Performance Tuning
• Installation & Configuration
Next Webcast: 19th November 2013
![Page 23: Cache issues from T-SQL-generated Plans and How to Manage Them](https://reader033.vdocuments.us/reader033/viewer/2022051817/5492a8a0ac79592f2e8b469d/html5/thumbnails/23.jpg)
23 Global MarketingUnderstanding Query Execution Plans
Any questions?