Optimize the execution cache for memory usage of SQL Server

First, describe the memory used by SQL Server. The memory occupied by SQL Server is mainly composed of three parts: Data Buffer, Procedure Cache, and SQL Server engine program. The cache occupied by SQL Server engine programs is generally relatively small, so the main focus of memory optimization is data cache and execution Cache control. This article describes how to optimize the execution cache. Data Cache Optimization will be introduced in another article.

To reduce the Cache Usage, you can use parameterized queries to reduce memory usage.
1. Use parameterized query to reduce Cache Usage
The following example shows the impact of parameterized query on Cache Usage. To facilitate the experiment, we used an SQL Server without other loads to perform the following experiment.
The following script executes a simple query cyclically, 10000 times in total.

First, clear the cache occupied by SQL Server:
Dbcc freeproccache

Then, execute the script:Copy codeThe Code is as follows: DECLARE @ t datetime
SET @ t = getdate ()
SET NOCOUNT ON
DECLARE @ I INT, @ count INT, @ SQL nvarchar (4000)

SET @ I = 20000
WHILE I <= 30000
BEGIN
SET @ SQL = 'select @ count = count (*) FROM P_Order WHERE else Eno = '+ cast (@ I as varchar (10 ))
EXEC sp_executesql @ SQL, n' @ count INT output', @ count OUTPUT
SET @ I = @ I + 1
END
Print datediff (second, @ t, current_timestamp)

Output:
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
11

10000 queries were completed in 11 seconds.
Let's take a look at the query plan occupied by the SQL Server cache:
Select Count (*) CNT, sum (size_in_bytes) TotalSize
From sys. dm_exec_cached_plans

Query results: a total of 2628 execution plans are cached in SQL Server. The cache they occupy reaches:
92172288 bytes = 90012KB = 87 MB.

You can also use the dbcc memorystatus command to check the SQL Server execution cache and data cache usage.
The execution result is as follows:

The execution cache occupies 2629 kb, 1489 query plans are in the cache, and pages of free memory (8 KB per page) can be used by data cache and other requests.

Modify the preceding script and run dbcc freeproccache again. Run the modified script again:Copy codeThe Code is as follows: DECLARE @ t datetime
SET @ t = getdate ()
SET NOCOUNT ON
DECLARE @ I INT, @ count INT, @ SQL nvarchar (4000)

SET @ I = 20000
WHILE I <= 30000
BEGIN
SET @ SQL = 'select @ count = count (*) FROM P_Order WHERE else Eno = @ I'
EXEC sp_executesql @ SQL, n' @ count int output, @ I int ', @ count OUTPUT, @ I
SET @ I = @ I + 1
END
Print datediff (second, @ t, current_timestamp)

Output:
DBCC execution is complete. If DBCC outputs an error message, contact the system administrator.
1
That is, 10000 queries are completed in only one second.
Let's take a look at the query plan in sys. dm_exec_cached_plans:
Select Count (*) CNT, sum (size_in_bytes) TotalSize From sys. dm_exec_cached_plans

Query results: a total of four execution plans are cached. Memory usage: 172032 bytes = kb.
If dbcc memorystatus is executed, the result is as follows:

12875 pages of free memory (8 KB per page) can be used by the data cache.

Here, we have seen a clear contrast. In reality, the former in this example is a commonly used method for executing SQL scripts (for example, concatenating strings in a program into an SQL statement, and then using ADO.. NET or ADO ).

Explain the reason:
We know that SQL statements will be compiled and optimized by the Query Optimization engine before execution to obtain the optimized execution plan and then be executed according to the execution plan. SQL Server can reuse execution plans for SQL statements with similar parameters. However, for different SQL statements, SQL Server cannot reuse the previous execution plan, but needs to re-compile a new execution plan. At the same time, when the memory of SQL Server is sufficient, it does not actively clear the previously saved query plan (NOTE: For query plans that are not used for a long time, SQL Server will also be cleaned regularly ). In this way, different SQL statement execution methods will greatly affect the number of query plans stored in SQL Server. If the maximum available memory of SQL Server is limited, too many useless execution plans will be occupied, which will reduce the available memory of SQL Server, in this way, more memory pages are exchanged with the disk when the query is executed, especially when the query is large. If the maximum available memory is not limited, the SQL Server will occupy more memory because the available memory is reduced.

In this regard, we can generally achieve parameterized query in two ways: first, use stored procedures to execute SQL statements as much as possible (which has become a principle of SQL Server DBA in reality ), second, execute a single SQL statement using sp_executesql (do not use sp_executesql as in the first example above ).

In reality, in the same software system, a large number of load types are often similar. The difference is that the specific parameter values passed in each time are different. Therefore, it is necessary and possible to use parameterized queries. In addition, we can see from this example that the parameterized query not only optimizes the memory usage of SQL Server, but also enables repeated use of the previously compiled execution plan, so that the subsequent execution does not need to be re-compiled. It takes only one second to execute 10000 queries.

2. Check and analyze the execution plans in the SQL Server execution Cache
Through the above introduction, we can see the memory size occupied by the SQL cache. It is also known that the content in the SQL Server execution cache is mainly the execution plan of various SQL statements. To optimize the cache, you can analyze the execution plans in the cache to find out which are useful and which are useless execution plans to analyze and locate the problems.

By querying DMV: sys. dm_exec_cached_plans, you can learn about the cache conditions in the database, including the number of times used, cache type, and occupied memory.
SELECT usecounts, cacheobjtype, objtype, size_in_bytes, plan_handle
FROM sys. dm_exec_cached_plans

You can query the details of the execution plan through plan_handle of the cache plan, including the corresponding SQL statement:

Select top 100 usecounts,

Objtype,

P. size_in_bytes,

[SQL]. [text]

FROM sys. dm_exec_cached_plans p

Outer apply sys. dm_exec_ SQL _text (p. plan_handle) SQL

Order by usecounts

We can choose to focus on the analysis of SQL statements that occupy a large amount of memory and are reused a small number of times. Check whether the call method is reasonable. In addition, you can analyze the SQL statements that have been repeatedly used in the execution plan to check whether the execution plan has been optimized. Further, through the analysis of the query plan, you can also find some SQL statements that occupy the most I/O, CPU time, and execution times in the system as needed, and then perform corresponding optimization analysis. The length is limited. I will not introduce this too much here. For more information, see sys. dm_exec_query_plan in books online.

Appendix:

1: About dbcc memory, you can view the Microsoft Knowledge Base: http://support.microsoft.com/kb/907877/EN-US

2: For information about sys. dm_exec_cached_plans and sys. dm_exec_ SQL _text, see books online.