Transcript Maciej Pilecki Consultant Project Botticelli Ltd. DAT404 SELECT About FROM Speakers WHERE Name = ’Maciej Pilecki’ Over 10 years of SQL Server experience SQL Server MVP.

Maciej Pilecki
Consultant
Project Botticelli Ltd.
DAT404
SELECT About FROM Speakers
WHERE Name = ’Maciej Pilecki’
Over 10 years of SQL Server experience
SQL Server MVP since Jan 2006
Specializing in SQL Server database development,
administration, performance tuning and
troubleshooting
Delivering consulting services around the world
(special discounted rate for TechEd attendees)
Frequent speaker at many international conferences
Living in Wroclaw, Poland
Agenda
Query optimization and execution
Plan caching and reuse
What is the Procedure Cache?
Managing the Procedure Cache
Controlling plan reuse
Query Execution
You type your query in SSMS, hit F5 and get the
results...
...well, it is more complicated than that...
Query Execution
Every query goes through a numer of steps:
Parsing
Algebraization
Optimization
Execution
Query Optimization
Process of selecting one execution plan from
many possible plans
Cost-based – aimed at finding execution plan
with the lowest (or close to lowest) estimated
execution cost
Source for graphics: SQL Server BOL
Can optimization go wrong?
Optimizer ESTIMATES the cost for each potential
plan
That estimate can be wrong (lack of stats,
outdated stats, query structure etc.)
A WRONG (sub-optimal) plan can be selected
(but it seems best)
You can outsmart the optimizer with hints! (if
you have to)
Query Optimization
Query Optimization is good - improves query
performance
But:
It is expensive (memory, CPU, time)
It is throttled at the server level
(see Optimization section in KB 907877)
It can timeout
We need plan caching...
Plan caching
Attempt to save the optimization effort by
caching and reusing execution plans
Execution plan consists of:
Query plan (one for many users, read-only)
Execution context (per-user but cached and reused
as well)
Plan cache is also called „Procedure Cache”
Query Execution Phases
Query
Cache
lookup
Found
Not found
Compile query
Schema
Statistics
Check plan
No
Plan
valid?
Yes
Execute query
How cache lookup works
Finding an existing plan is based on:
ID of the object (for SPs, triggers, functions etc.)
Hash of the query text (for ad-hoc queries)
Also, the cache-key attributes must match:
SET options
DBID
Language and date settings
user_id for non-qualified object names
And other...
Inside the Procedure Cache
Stores compiled execution plans
There is not one ProcCache but 4:
CACHESTORE_OBJCP – object plans
CACHESTORE_SQLCP – SQL plans
CACHESTORE_PHDR – „bound trees”
CACHESTORE_XPROC – XProcs
ProcCache „steals” pages from the Buffer Pool
Entries aged-out based on cost of compilation and
usage frequency
Inside the Procedure Cache - DMVs
sys.dm_exec_cached_plans
sys.dm_exec_sql_text(plan_handle)
sys.dm_exec_query_plan(plan_handle)
sys.dm_exec_plan_attributes(plan_handle)
sys.dm_exec_query_stats
You can combine and aggregate those in a
number of interesting ways...
Procedure Cache Size
Procedure cache size limits
SQL 2000: max. 4GB
SQL 2005 RTM and SP1:
75% 0-8GB + 50% 8-64GB + 25% >64GB
SQL 2005 SP2 and SQL 2008:
75% 0-4GB + 10% 4-64GB + 5% >64GB
Example: with 16GB on RTM limit is 10GB, on SP2: 4.2 GB
Or number of items:
Up to 10007 per cache store (SQLCP, OBJCP) on 32-bit
Up to 40009 per cache store on 64-bit
No direct control over the size of cache
Workload Governor helps, if you can use it (EE only)
How is Procedure Cache Cleared?
Automatically:
Entries aged-out due to memory pressure
Due to certain database operations:
Database restore, changing DB state, etc.
See KB917828 for SQL Server 2005 list
(or http://blogs.msdn.com/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx)
Logs to ERRORLOG: "SQL Server has encountered %d
occurrence(s) of cachestore flush for the '%s' cachestore
(part of plan cache) due to some database maintenance
or reconfigure operations".
Fixed in SQL 2008 (in most cases clears plans for one
DB only)
How is Procedure Cache Cleared?
Manually:
DBCC FREEPROCCACHE
Clears everything – everything needs to be recompiled
Usually an overkill – serious performance effects
In SQL 2008 you can be more selective:
DBCC FREEPROCCACHE ( { plan_handle | sql_handle | pool_name } )
plan_handle – single plan
sql_handle – all plans for particular SQL text
pool_name – Resorce Governor pool
How is Procedure Cache Cleared?
Manually (cont’d):
DBCC FLUSHPROCINDB(dbid)
Clears all plans for a single database
Better but still an overkill
Undocumented and unsupported
DBCC FREESYSTEMCACHE('SQL Plans')
Clears a particular cache store
In this case, the ad-hoc plans cache store
Keeps other plans intact
Undocumented and unsupported
Managing plan reuse
Generally, plan reuse is a good thing
Less plans in cache (less memory consumed)
Less time spend optimizing and compiling plans
Our goal is to improve plan reuse as much as
possible (but beware of drawbacks!)
Depending on application, can be very easy
or very hard to achieve
Controlling plan caching
Application-side parameterization
Stored Procedures
Forced parameterization
Database-level option
More aggressive in parameterizing ad-hoc SQL
Optimize for ad hoc workloads
New server option in SQL Server 2008
Only a stub is cached on first execution
Full plan cached after second execution
When is plan reuse NOT a good thing?
The main downside of plan caching:
Cached plan is based on the parameters used during
optimization
Known as „parameter sniffing”
Not always the best plan for subsequent executions
with a different parameter values
Sometimes difficult to spot – look for queries with
greatly varying execution stats
Estimated number of rows << Actual number of rows
Working around the „bad plan” issue
Recompiling for given execution:
OPTION (RECOMPILE)
EXECUTE ... WITH RECOMPILE
Always recompiling (for SPs)
Masking parameter values through local variables
Specifying value for the optimizer:
OPTIMIZE FOR (@var = val)
OPTIMIZE FOR UNKNOWN (new in 2008)
Summary
Query optimization and execution
Plan caching and reuse
What is the Procedure Cache?
Managing the Procedure Cache
Controlling plan reuse
Additional reading:
http://msdn.microsoft.com/en-us/library/ee343986.aspx
Email: [email protected]
Resources
www.microsoft.com/teched
www.microsoft.com/learning
Sessions On-Demand & Community
Microsoft Certification & Training Resources
http://microsoft.com/technet
http://microsoft.com/msdn
Resources for IT Professionals
Resources for Developers
Complete an evaluation
on CommNet and enter to
win an Xbox 360 Elite!
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should
not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.