Shulou(Shulou.com)05/31 Report--

It is believed that many inexperienced people don't know what to do about how to implement the plan cache in SQL Server. Therefore, this article summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

Basic concept

SQL Server has a memory pool for storing execution plans and data buffers. The percentage allocated to execution plans or data buffers in the pool fluctuates dynamically depending on the state of the system. The part of the memory pool that stores the execution plan is called the process cache.

The SQL Server execution plan consists of the following main components:

Inquiry plan

The main body of the execution plan is a reentrant read-only data structure that can be used by any number of users. This is called a query plan. User context is not stored in the query plan. There are never more than two copies of the query plan in memory: one for all serial execution and the other for all parallel execution. Parallel copies cover all parallel execution regardless of the degree of parallelism of parallel execution.

Execution context

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

How to cache the execution plan

SQL Server has an efficient algorithm to find existing execution plans for any particular SQL statement. When executing any SQL statement in SQL Server, the relational engine first checks the procedure cache to see if there is an existing execution plan for the same SQL statement. SQL Server reuses any existing plans found, saving the overhead of recompiling SQL statements. If there is no existing execution plan, SQL Server generates a new execution plan for the query.

SQL Server automatically deletes execution plan

Under what circumstances will the execution plan be deleted

Without manually clearing the cache, SQL Server automatically clears some unutilized cache plans if there is insufficient memory.

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

How to determine the execution plan that needs to be deleted

If there is an out-of-memory situation, the database engine uses a cost-based approach to determine which execution plans are removed from the process cache. How to determine the cost of an execution plan? for an execution plan that has been executed many times, SQL Server sets its cost value to 0, and the execution plan SQL Server that has been executed many times sets its cost value to the original compilation cost, so the database engine will double check the status of each execution plan and delete the current execution plan with zero overhead. If there is an out-of-memory condition, the execution plan with the current cost of zero is not automatically deleted, but only if the Database engine checks the execution plan and finds that its current cost is 00:00. When checking the execution plan, if no query currently uses the plan, the database engine reduces the current overhead to push it to zero.

The database engine repeatedly checks the execution plan until enough execution plans are deleted to meet the memory requirements. If there is a lack of memory, the execution plan can increase or decrease its overhead multiple times. If the lack of memory has disappeared, the database engine will no longer reduce the current overhead of unused execution plans, and all execution plans will remain in the process cache, even if the overhead is zero.

Recompile the execution plan

Depending on the new state of the database, some changes in the database may cause the execution plan to be inefficient or invalid. SQL Server detects changes that invalidate the execution plan and marks the plan as invalid. After that, the new plan must be recompiled for the next connection that executes the query. Situations that invalidate the plan include:

Make changes to the table or view referenced by the query (ALTER TABLE and ALTER VIEW).

Make changes to any indexes used in the execution plan.

Updates the statistics used in the execution plan, which may be explicitly or automatically generated from statements such as UPDATE STATISTICS.

Delete the index used by the execution plan.

Explicitly call sp_recompile.

A large number of changes to keys (modifications made by other users using INSERT or DELETE statements on tables referenced by queries).

For tables with triggers, the number of rows in the inserted or deleted table increases significantly.

Use the WITH RECOMPILE option to execute the stored procedure.

test

-1. Each cached object returns a row, including SELECT * FROM sys.syscacheobjects;-2, including the type of cache plan, the object referenced by the cache, the space occupied by the cache plan, the number of times it is used, and the creation time. Each cached query plan returns a row, including the number of times the execution plan is used, the size of the execution plan, the memory address, the type of execution plan, statements and other SELECT * FROM sys.dm_exec_cached_plans; GO-- 3. Returns the text of the SQL batch identified by the specified sql_handle / * where sql_handle comes from: sys.dm_exec_query_stats sys.dm_exec_requests sys.dm_exec_cursors sys.dm_exec_xml_handles sys.dm_exec_query_memory_grants sys.dm_exec_connections plan_handle from: sys.dm_exec_cached_plans * / SELECT * FROM sys.dm_exec_sql_text (sql_handle | plan_handle); GO-- 4. Returns the display plan of the batch query specified by the plan handle in XML format, mainly accepting plan_handle handle SELECT * FROM sys.dm_exec_query_plan (plan_handle) from sys.dm_exec_cached_plans; GO-- 5. Each plan property returns a row, mainly accepting plan_handle handle SELECT * FROM sys.dm_exec_plan_attributes (plan_handle) from sys.dm_exec_cached_plans; GO-- 6. Returns a row for each Transact-SQL execution plan, common language runtime (CLR) execution plan, and cursors associated with the plan, mainly accepting the plan_handle handle SELECT * FROM sys.dm_exec_cached_plan_dependent_objects (plan_handle) from sys.dm_exec_cached_plans;-- 7. Returns aggregate performance statistics for cached query plans. Each query statement in the cache plan corresponds to a row in this 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 deleted from the view. * /-- this system view calculates the execution time, physical, logical operation and other information of the execution plan in each cache SELECT * FROM sys.dm_exec_query_stats

Manually clear the cache execution plan

-emptying the database execution plan DECLARE @ DBID INT SET @ DBID=DB_ID () DBCC FLUSHPROCINDB (@ DBID) GO-create test databases CREATE TABLE TPlan (ID INT PRIMARY KEY IDENTITY (1)), Name NVARCHAR (20) NOT NULL, Istate INT NOT NULL, Idate DATETIME DEFAULT (GETDATE ()) GO-create indexes CREATE INDEX IX_TPlan_NAME ON TPlan (Name) GO INSERT INTO TPlan (Name,Istate) VALUES ('1), ('2)), (3) GO SELECT NAME FROM TPlan GO SELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjects WHERE DBID=DB_ID ()

Monitoring using Profiler

Use SQL:StmtRecompile monitoring, if it is monitoring stored procedures, use: SP:Recompile

Modify the index

Add a field to the index

DROP INDEX [IX_TPlan_NAME] ON [dbo]. [TPlan] WITH (ONLINE = OFF) GO USE [Study] GO CREATE 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 = 90) FILLFACTOR [FILLFACTOR]

Execute the query again

SELECT NAME FROM TPlan

Test the impact of added fields on the execution plan

Add query irrelevant fields

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

Deleting the index related to the query will also cause the execution plan to be recompiled, so no screenshots will be posted here.

View the execution plan

SELECT Cacheobjtype,objtype,dbid,objid,usecounts,pagesused,sql FROM sys.syscacheobjects WHERE DBID=DB_ID ()

The execution plan shows that the execution plan has been called twice, and it is written in the random series that the new execution plan will be recompiled. If so, the value here 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 of * queries is still used in the new query.

After reading the above, have you mastered how to perform plan caching in SQL Server? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.