SQL 2005 Cache Plan Summary

A cache plan can be divided into two classes: the compiled plan and the execution context. The former is shared with all users, and the latter is for a specific user, and it contains information about the specific parameters of a user executing the plan. There are four types of compiled plans, which can be viewed through the following statements:

SELECT *
FROM sys.dm_os_memory_cache_counters
WHERE name IN ('Object Plans', 'SQL Plans',
'Bound Trees', 'Extended Stored Procedures');

These four classes correspond to object plans (stored procedures, functions, triggers), SQL plans (ad hoc queries, automatic parameterization, sp_executesql executed statements), Bound trees (the structure generated by the algebraic phase of the plan generation), Extended Stored procedures (extended stored procedure).

The execution context is stored in a place called SQL Manager Cache (sqlmgr). If the buffer reaches the upper limit, SQL Server releases the memory of the Sqlmgr section first, because their compilation cost is 0. The planned cost of compilation is halved, and will be released if the cost reaches 0.

The SQL2005 process cache is dynamically allocated, and its maximum size is calculated from the following formula:

The visible target memory referred to here is the size returned by the visible row in the result set of the buffer counts when the DBCC MEMORYSTATUS command is used. As shown in the following illustration:

If the 64-bit operating system target and visible are both equal. Target is the smaller of both the system's visible memory and the SQL Server's maximum available memory. If it is a 32-bit system, the maximum is 3G when the/3GB boot parameter is set, otherwise the maximum is 2G.

If more than 4G of memory is used in a 32-bit system, suppose you have an awe configured for SQL Server. Target and visible are no longer equal. The procedure cache is not stored in AWE memory.

Let's say you run sqlserver2005sp2 in a 32-bit operating system with 16G of RAM in it. You've configured AWE for SQL Server, and given the SQL services such as SSRS, SSIS, and Fulltext, you set up the SQL Server engine's maximum available memory of 12G. Using the DBCC MEMORYSTATUS command, you should see that target size should be 1572864. Because the value of the buffers column represents the number of buffer pages, the size of each page is 8K. Therefore 12G words should be 12*1024*1024/8=1572864 a buffer page. At this point, the visible is not equal to target, assuming that the current visible value is 2G. The maximum size of the process cache is 2*0.75=1.5g.

In the case of a 64-bit system, the above calculation formula is 4*0.75+8*0.1=3.8g. We see that SQL2005 's SP2 is more restrictive than the SP1 size of the process buffer, in order to prevent a scramble for memory with the data cache because of a large cache schedule.

Will a batch of commands have multiple execution plans? Another execution plan is generated only if the environment in which the execution is changed. View Sys.dm_exec_plan_attributes contains some of the property information for a plan, let's try to generate multiple execution plans for the same batch of commands after changing the execution environment, and execute the following statement:

I get the following results: