Optimize the execution cache for memory usage of SQL Server

Some people often complain on forums, saying:SQL ServerToo much memory. Here I will give a brief introduction to memory-related tuning knowledge based on experience. First, describeSQL ServerWhich parts of memory is used.SQL ServerThe memory occupied is mainly composed of three parts: Data Cache(Data buffer), Execution Cache(Procedure cache), AndSQL ServerEngineProgram.SQL ServerThe cache occupied by 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 performed in anotherArticle.

 

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. For the convenience of the test, we useSQL ServerPerform the following experiments.

The following script executes a simple query in a loop.10000Times.

 

First, let's clear it.SQL ServerOccupied cache:

DBCCFreeproccache

 

Then, execute the script:

Declare@ TDatetime

Set@ T= Getdate()

Set Nocount On

Declare@ IInt,@ CountInt,@ SQLNvarchar(4000)

 

Set@ I=20000

While@ I<=30000

Begin

Set@ SQL= 'Select @ COUNT = count (*) from p_order where else Eno =' + Cast(@ IAs Varchar(10) )

Exec Sp_executesql@ SQL,N'@ Count int output',@ CountOutput

Set@ I=@ I+1

End

Print Datediff(Second,@ T, Current_timestamp )

 

Output:

DBCCThe execution is complete. IfDBCCAn error message is output. Contact the system administrator.

11

 

Used11Completed in seconds10000Query.

Let's take a look.SQL ServerThe query plan used in the cache:

Select Count(*)CNT,Sum(Size_in_bytes)Totalsize

From SYS. dm_exec_cached_plans

 

Query results: Total2628The execution plan is cached inSQL Server. The cache they occupy reaches:

92172288Bytes= 90012kb = 87 MB.

 

We can also useDBCC memorystatusCommand to checkSQL ServerExecution cache and data cache usage.

The execution result is as follows:

 

 

 

 

The execution cache is occupied90088kb, Yes2629Queries are scheduled to be cached.1489Page idle memory (per page8 KBCan be used by data caching and other requests.

 

Now let's modify the previous script and execute it again.DBCC freeproccache. Run the modified script again:

Declare@ TDatetime

Set@ T= Getdate()

Set Nocount On

Declare@ IInt,@ CountInt,@ SQLNvarchar(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',@ CountOutput,@ I

Set@ I=@ I+1

End

Print Datediff(Second,@ T, Current_timestamp )

 

Output:

DBCCThe execution is complete. IfDBCCAn error message is output. Contact the system administrator.

1

That is, this time only1Completed in seconds10000Query.

Let's take a look.SYS. dm_exec_cached_plansQuery plan in:

Select count (*) CNT, sum (size_in_bytes) totalsize from SYS. dm_exec_cached_plans

 

Query results: Total4The execution plan is cached. They occupy a total of memory:172032Bytes= 168kb.

IfDBCC memorystatus, The result is:

 

 

 

There are 12875Page idle memory(Per page8 KB)It can be used by data cache.

 

Here, we have seen a clear contrast. In reality, the former in this example is a type of execution that is often used.SQLScript (for example, concatenate a string in a programSQLStatement, and thenAdo. netOrADOMethodSQL ServerExecution ).

 

Explain the reason:

We know that,SQLThe statement will be compiled and optimized by the Query Optimization engine before execution, so as to obtain the optimized execution plan and then be executed according to the execution plan. For general similarity, only parameters are differentSQLStatement,SQL ServerExecution plans can be reused. But for differentSQLStatement,SQL ServerInstead of reusing the previous execution plan, you need to recompile a new execution plan. At the same time,SQL ServerWhen the memory is sufficient, the previously saved query plan is not cleared (NOTE: For query plans that are no longer used for a long time,SQL ServerWill also be cleared regularly ). In this way, differentSQLStatement execution method will be greatly affectedSQL ServerThe number of query plans stored in. 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 procedure execution as much as possibleSQLStatement (this has becomeSQL Server DBA(2 ).Sp_executesqlMethod to execute a singleSQLStatement (do not use it as in the first example above.Sp_executesql).

 

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 is not only optimizedSQL ServerMemory usage, and because it can reuse the previously compiled execution plan, the subsequent execution does not need to be re-compiled, and the final execution10000The total number of queries is only used1Second.

 

2 , Check and analyze SQL Server Execute the execution plan in the cache

Through the above introduction, we can see thatSQLThe memory used by the cache. You know.SQL ServerThe content in the execution cache is mainly a varietySQLStatement execution plan. 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.

 

QueryDMV: SYS. dm_exec_cached_plans,You can understand the cache status in the database, including the number of times used, cache type, and memory usage.

SelectUsecounts,Cacheobjtype,Objtype,Size_in_bytes,Plan_handle

From SYS. dm_exec_cached_plans

 

 

ThePlan_handleYou can query the execution plan details, including the correspondingSQLStatement:

Select Top100 usecounts,

Objtype,

P.Size_in_bytes,

[SQL].[Text]

From SYS. dm_exec_cached_plansP

Outer ApplySYS. dm_exec_ SQL _text(P.Plan_handle)SQL

Order ByUsecounts

 

 

We can choose to focus on those execution plans that occupy a large amount of memory and are reused a small number of times.SQLStatement. Check whether the call method is reasonable. In addition, the execution plan can be reused for a large number of times.SQLStatement to check whether the execution plan has been optimized. Further, through the analysis of the query plan, you can also find the most occupiedIo,CPUTime, most executionsSQLStatement. The length is limited. I will not introduce this too much here. Readers can refer to the following online books:SYS. dm_exec_query_planContent for help.

 

Appendix:

1:AboutDBCC memory, You can view Microsoft's knowledge base:Http://support.microsoft.com/kb/907877/EN-US

2:AboutSYS. dm_exec_cached_plansAndSYS. dm_exec_ SQL _text, See books online