SQL Server Execution Plan cache

Tags: SQL server/mssql server/database/dba/memory Pool/Buffers

Overview

Understanding the execution plan is important for database performance analysis, which involves statement profiling and storage, which is the purpose of writing this article, before understanding the implementation of the plan to understand some basic knowledge, so the article will say some concepts, learning will be more boring, but these basic knowledge is very important.

Directory

• Overview
• Basic Concepts
  • how to cache execution plans
  • SQL Server automatically deletes execution plans
  • Recompile the execution plan
• Test
  • execution Plan related system views
  • manually emptying the cache execution plan
  • test the impact of index changes on the execution plan
  • test the effect of increasing the field on the execution plan
• Summary

Basic Concepts

SQL Server has a pool of memory that is used to store execution plans and data buffers. The percentage allocated to the execution plan or data buffer within the pool fluctuates dynamically with the system state. The portion of the memory pool used to store execution plans is called the procedure cache.

The SQL Server execution plan contains the following major components:

• Query plan

The principal of the execution plan is a re-entry read-only data structure that can be used by any number of users. This is called a query plan. The user context is not stored in the query plan. In-memory query plan replicas never exceed two: one copy is used for all serial executions, and the other is for all parallel executions. Parallel replicas Overwrite all parallel executions, regardless of the degree of parallelism in parallel execution.

• Execution context

Each user who is executing a query has a data structure that contains its execution-specific data, such as parameter values. This data structure is called the execution context. The execution context data structure can be reused. If a user executes a query and one of the structures is not used, the structure is reinitialized with the context of the new user.

how to cache execution plans

SQL Server has an efficient algorithm to find an existing execution plan for any particular SQL statement. When you execute any SQL statement in SQL Server, the relational engine first looks at whether there is an existing execution plan for the same SQL statement in the procedure cache. SQL Server will reuse any existing plans found, saving the overhead of recompiling the SQL statement. If there is no existing execution plan, SQL Server generates a new execution plan for the query.

SQL Server automatically deletes execution plans

What happens when the execution plan is deleted

In cases where there is no manual purge of the cache, SQL Server automatically clears a portion of the cache plan that is not exploited if there is insufficient memory.

The maximum size of all caches depends on the size of max server memory .

How to determine which execution plan to delete

If there is a low memory condition, the database engine uses a cost-based approach to determine which execution plans are removed from the procedure cache. How to determine the cost of an execution plan SQL Server sets its cost value to 0 for a first execution plan, and the execution plan that was executed multiple times SQL Server set its cost value to the original compilation cost. Therefore, the database engine repeatedly checks the status of each execution plan and deletes the execution plan with a zero current cost. If there is an out-of-memory situation, the execution plan with the current cost of zero is not automatically deleted, and the plan is deleted only if the database engine examines the execution plan and discovers that its current cost is zero. When you examine the execution plan, if no queries are currently using the plan, the database engine lowers the current cost to push it to zero.

The database engine repeatedly checks the execution plan until enough execution plans are removed to meet the memory requirements. If there is a low memory condition, the execution plan can increase or decrease its overhead multiple times. If the out-of-memory situation has disappeared, the database engine will no longer reduce the current cost of using the execution plan, and all execution plans will remain in the procedure cache, even if their cost is zero.

Recompile the execution plan

Depending on the new state of the database, some changes in the database can cause the execution plan to be inefficient or ineffective. SQL Server detects changes that invalidate the execution plan and marks the schedule as invalid. Thereafter, you must recompile the new schedule for the next connection that executes the query. Scenarios that cause the program to be invalid include:

• Make changes to the table or view referenced by the query (ALTER TABLE and ALTER VIEW).
• Make changes to any indexes that are used by the execution plan.
• Updates are made to the statistics used by the execution plan, which may be either explicitly generated from statements (such as update STATISTICS) or generated automatically.
• Deletes the index used by the execution plan.
• Explicitly call sp_recompile.
• A large number of changes to the key (other users use the INSERT or DELETE statement to modify the table referenced by the query).
• For tables with triggers, the number of rows in the inserted or deleted table increases significantly.
• Executes a stored procedure using the WITH RECOMPILE option.

Testexecution Plan related system views

--1. Each cached object returns one row, including the type of the cache plan, the object to cache the reference, the amount of space the cache plan occupies, the number of times it was used, and the creation time.SELECT *  fromsys.syscacheobjects;--2. Each query plan for the cache returns one row, including the number of times the execution plan was used, the size of the execution plan, the memory address, the type of execution plan, the statement, and so onSELECT *  fromSys.dm_exec_cached_plans;GO---3. Returns the text of the SQL batch identified by the specified sql_handle/*of which sql_handle from: Sys.dm_exec_query_statssys.dm_exec_requestssys.dm_exec_cursorssys.dm_exec_xml_ Handlessys.dm_exec_query_memory_grantssys.dm_exec_connectionsplan_handle from: Sys.dm_exec_cached_plans*/ SELECT *  fromSys.dm_exec_sql_text (sql_handle|plan_handle);GO--4. Returns the display plan for the batch query specified by the plan handle in XML format, mainly accepting the plan_handle handle from Sys.dm_exec_cached_plansSELECT *  fromsys.dm_exec_query_plan (plan_handle);GO--5. Each schedule attribute returns one row, primarily accepting plan_handle handles from Sys.dm_exec_cached_plansSELECT *  fromsys.dm_exec_plan_attributes (plan_handle);GO--6. Returns a row for each Transact-SQL execution plan, common language Runtime (CLR) execution plan, and cursors associated with the plan, primarily accepting plan_handle handles from Sys.dm_exec_cached_plansSELECT *  fromsys.dm_exec_cached_plan_dependent_objects (plan_handle);--7. Returns aggregate performance statistics for the cached query plan. Each query statement in the cache plan corresponds to a row in that view, and the lifetime of the row is associated with the plan itself. When you delete a plan from the cache, the corresponding row is also removed from the view. */--The system view counts the execution time, physical, logical operations, and so on for each execution plan in a cacheSELECT *  fromSys.dm_exec_query_stats

manually emptying the cache execution plan

--empty the execution plan in the cacheDBCCFreeproccache;--(plan_handle | sql_handle | pool_name)GO--clear the execution plan for the development databaseDBCCFLUSHPROCINDB (<dbid>);GO---Empty the data in the cacheDBCCdropcleanbuffers;---Empty the execution plan in a specific cache storage areaDBCCFreesystemcache (<Cachestore>)--' All ', Pool_name, ' Object plans ', ' SQL plans ', ' Bound Trees 'GO

test the impact of index changes on the execution plan

---Clear the database execution planDECLARE @DBID INTSET @DBID=db_id()DBCCFLUSHPROCINDB (@DBID);GO---Create a test databaseCREATE TABLETplan (IDINT PRIMARY KEY IDENTITY(1,1), NameNVARCHAR( -) not NULL, IstateINT  not NULL, IdateDATETIME DEFAULT(GETDATE()))GO---Create an indexCREATE INDEXIx_tplan_name onTplan (Name)GOINSERT  intoTplan (name,istate)VALUES('1',1),('2',2),('3',3)GOSELECTNAME fromTplanGOSELECTCacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql fromsys.syscacheobjectsWHEREDBID=db_id()

Monitor with Profiler

Use sql:stmtrecompile monitoring, if the monitoring stored procedure is used: Sp:recompile

modifying indexes

Add a field to an index

DROP INDEX [Ix_tplan_name]  on [dbo].[Tplan]  with(ONLINE= OFF )GO Use [Study]GOCREATE nonclustered INDEX [Ix_tplan_name]  on [dbo].[Tplan] (    [Name] ASC) INCLUDE ([istate]) with(Pad_index= OFF, Statistics_norecompute= OFF, sort_in_tempdb= OFF, Ignore_dup_key= OFF, drop_existing= OFF, ONLINE= OFF, Allow_row_locks=  on, Allow_page_locks=  on,FILLFACTOR =  -) on [PRIMARY]GO

Execute Query again

SELECT  from Tplan

test the effect of increasing the field on the execution plan

Increase query non-related fields

ALTER TABLE [dbo]. [tplan] ADD  Number INT

Deleting a query about the index also causes the execution plan to recompile, which is not posted here.

View execution plans

SELECT  from sys.syscacheobjects WHERE DBID=db_id()

The execution plan shows that the execution plan was called two times, and in the random books it was written to recompile the new execution plan, and if so, the value should be 1.

GUESS: SQL Server has compiled the original execution plan by detecting the execution plan when the schema changes, so the execution plan for the first query is used in the new query.

If anyone knows the result, trouble telling.

Summary

if the article is helpful to everyone, I hope you can give a recommendation, thank you!!!

Note: pursuer.chen Blog:http://www.cnblogs.com/chenmh This site all the essays are original, welcome to reprint, but reprint must indicate the source of the article, and at the beginning of the article clearly to the link, otherwise reserves the right to hold responsibility. Welcome to the exchange of discussions

SQL Server Execution Plan cache