Embedded Files
When you execute a query for the first time, it will be compiled and query plan will be generated for each query. It uses memory to store objects like stored procedure, triggers, adhoc and prepared T-SQL statements.
These two behave exactly the same way,
Ad hoc statements
> possibly auto parameterized statements.
Dynamic String Execution (DSE)
> EXECUTE (@string)
sp_executesql (force statement caching) - behaves like stored procedure.
Prepared queries (forced statement caching though parameter markers).
> Client side from ODBC & OLEDB
> SQLPrepare, SQLExecute and iCommandPrepare
Plan cache pollution is created by "single-use plans" executing and being stored but you might not really benefit.
Each plan takes a multiple 8KB. (Each query statement = 8KB)
The "query_class" is harder to track because each is listed in the cache, but they will have the same query_hash but different query_plan_hash
SQL 2008 (query_hash & query_plan_hash is in sys.dm_exec_query_stats)
> Aggregate by query_hash to find similar queries.
> Aggregate by query_hash & query_plan_hash to find similar queries together with their plan.
CACHESTORE_OBJCP = "Object Plans"
> Stored procedure, function, triggers ...
> Generally desirable
CACHESTORE_SQLCP = "SQL Plans"
> Adhoc SQL Statements (including parameterized)
> Prepared statements
> OK when highly reused but often not
CACHESTORE_PHDR = "Bound Trees"
> Views, constraints and defaults
> Usually OK
CACHESTORE_XPROC = Extended Procs
Plan Cache (a.k.a. "procedure cache"
Uses stolen pages from the buffer pool (data pages)
View cached plans: sys.dm_exec_cached_plans
Memory limits:
> SQL 2008+ and SQL 2005 SP2
75% of visible target memory from 0-4GB
+10% of visible target memory 4GB-64GB
+5% of visible target memory > 64GB
> SQL 2005 RTM and SQL 2005 SP1
75% of visible target memory from 0-8GB
+50% of visible target memory 8GB-64GB
+25% of visible target memory > 64GB
> SQL 2000
4Gb upper cap on the plan cache
Reusing plan cache can be good:
> When different parameters do not change the optimal plan, then saving and reusing is excellent.
> SQL Server saves time in compilation.
Reusing plan can be VERY bad:
> When different parameters wildly change the size of the result set and the optimal plans vary, then reusing the plan can be horribly bad.
> If statistics are added to base tables, existing plans may not leverage them.
When the plan for a given statement within a procedure is not consistent in execution plan, due to parameter changes.
Cost of recompilation might be significantly less than the execution cost of bad plan.
Why?
A much faster execution with a better plan.
Some plans just don't work for a wide variety of your execution cases, some plans should never be saved.
Do we need to this in every procedure?
NO, start with the expensive/highest priority procedures that aren't performing well - TEST before applying in PROD!
CREATE _WITH RECOMPILE = NOT recommended
EXECUTE _ WITH RECOMPILE = Use for testing
sp_recompile objname = Just to re-invalidate
Statement-level recompilation
2005+ OPTION (RECOMPILE)
2008+ OPTION (OPTIMIZE FOR UNKNOWN)
Note: This is a short term fix - You must dig deeper in fixing query bottleneck.
Stored procedure and sp_executesql have the same potential for executing a bad plan BUT stored procedure have more options for centralized control.
Forcing a recompile can be warranted/justified.
Always recompile the smallest amount possible.
Reasons to recompile. See Pinal Dave post.
Sources and good reads:
IEPTO2 - SQLSkills immersion event docs (Recommended Training "https://www.sqlskills.com/sql-server-training/ ")
https://www.sql.kiwi/2011/02/so-is-it-a-seek-or-a-scan.html#more
https://bertwagner.com/2019/08/06/5-things-you-need-to-know-when-reading-sql-server-execution-plans/
Report abuse