Improve cache efficiency with T-SQL code to reduce memory consumption

In this article, I will introduce how to write your code to improve the reuse of cache plans. Understand the impact of a cache plan or reusing an existing plan space time and comment, which will help you reduce the number of your application cache plans.

Explore cache plans

Are you using the plan cache? Do you make good use of the cache plan? Have your applications used them? Have they been used multiple times? Do you have multiple cache plans for the same query in the Stored Procedure cache at the same time? How much space does the cache plan use? These are the questions you need to answer to ensure that you Cache during optimization and reduce the number of cache plans your application will create. When you write your T-SQL code, you need to note a little bit, it will make the SQL Server execute additional work for the same T-SQL code to compile and cache the execution plan.

Before SQL Server can process a T-SQL batch, it needs to create an execution plan. In order for SQL Server to create an execution plan, it must first consume valuable resources such as CPU to compile a T-SQL batch. When a program is compiled, it is cached, so it can be reused when your application calls the same T-SQL statement more than once. If you write your T-SQL code to improve the reuse of cache plans for frequently executed T-SQL statements, then you can improve your code performance.

With the launch of SQL Server 2005, Microsoft provides some DMV that you can use to explore cache plans. By using these DMV, you can confirm some things about the cache plan. Below is a brief list of things you can confirm:

• Text related to a cache plan

• The number of times a cache plan is executed

• Cache plan Scale

Later I will show you how to use DM to explore cache plan information.

There are multiple plans due to comments or extra spaces

I believe that you all have the idea of putting code into the stored procedure. This is what we do to reuse code in one or more applications. However, not all code executed by SQL Server is included in the stored procedure. Some applications may be written in an ordered T-SQL code. If you are writing sequential T-SQL code, you need to understand how to annotate your code and place spaces may cause SQL Server to create multiple cache plans for the same T-SQL statement.

The following is an example of a T-SQL script that contains two different T-SQL statements:

 
 

  
  
SELECT * FROM AdventureWorks.Production.Product  
  
  
GO  
  
  
SELECT * FROM AdventureWorks.Production.Product -- return records  
  
  
GO 
 
 

As you can see, I have two similar T-SQL statements. Both return all records in the AdventureWorks. Production. Product table. So how many cache plans do you think SQL Server will create if you run this code? To answer this question, let me use a DMV provided by SQL Server 2005 and SQL Server 2008 to check the cache plan information. To view the plans that these two T-SQL statements generate, I want to run the following code:

 
 

  
  
DBCC FREEPROCCACHE  
  
  
GO  
  
  
SELECT * FROM AdventureWorks.Production.Product  
  
  
GO  
  
  
SELECT * FROM AdventureWorks.Production.Product -- return records  
  
  
GO  
  
  
SELECT stats.execution_count AS exec_count,  
  
  
p.size_in_bytes as [size],  
  
  
[sql].[text] as [plan_text]  
  
  
FROM sys.dm_exec_cached_plans p  
  
  
outer apply sys.dm_exec_sql_text (p.plan_handle) sql  
  
  
join sys.dm_exec_query_stats stats ON stats.plan_handle = p.plan_handle  
  
  
GO 
 
 

In this Code, I first release the cache by running the DBCC FREEPROCCACHE command. This command deletes all compiled execution plans in the memory. I have to give some advice on this command. Do not run the dbcc freeproccache command in a production environment. Doing so in your production environment will delete all your generated cache plans, which may severely affect your production environment because frequently used plans will be re-compiled. After the process cache is released, I execute two different SELECT statements. Finally, I connect the information obtained from a different DMV to return some cached plan information for the two SELECT statements. When I run this command, I obtain the following output from the SELECT statement that references different DMV:

 
 

  
  
exec_count size plan_text  
  
  
-------------------- ----------- --------------------------------------------------------------------------  
  
  
1 40960 SELECT * FROM AdventureWorks.Production.Product -- return records  
  
  
1 40960 SELECT * FROM AdventureWorks.Production.Product 
 
 

As you can see from this output, the two SELECT statements above have created two different cache plans, each executed once (number of exec_count ). This is because these SELECT statements are not exactly the same. A select statement is slightly different because it contains a comment. Also, pay attention to the cache plan size, 40,960 bytes! This occupies a large piece of memory, but is only used for such a trivial T-SQL statement.

Therefore, you must note how you annotate your code. Cutting and pasting is a good way to copy part of your application's statement to another part, but be careful not to place different comments before or between your similar T-SQL statement, this leads to multiple plans.

Another way to generate multiple cache plans for the same T-SQL command is to include some extra space characters in your T-SQL statement. The following are two similar commands, except for spaces:

 
 

  
  
SELECT * FROM AdventureWorks.Production.Product  
  
  
GO  
  
  
SELECT * FROM AdventureWorks.Production.Product  
  
  
GO 
 
 

As you can see, the second statement contains a pair of extra spaces between the FROM clause and the Object Name. This extra space will cause the SQL Server optimizer to think the two statements are different, and thus create different cache plans for the two statements. Here it is obvious that there are extra spaces in the second T-SQL statement. However, if you add some other characters before or after the SELECT clause, the statement may look the same, because you cannot see this space, but SQL Server can see it, therefore, it creates multiple cache plans due to this extra space.

When SQL Server looks at a batch, it compares it with a plan that already exists in the Stored Procedure cache. If the statements to be compiled are identical to an existing cache plan, SQL Server does not need to compile and cache the plan to the memory. SQL Server does this so that it can reuse a plan for similar statements if the plan already exists in the cache. To optimize your code, make sure that you reuse the cache plan as much as possible.

When you develop application code and place T-SQL code in your application, and do not use stored procedures, you need to be careful to ensure that you do your best to achieve the best possible reuse of the program. When writing code, if we want to use the same code in different code blocks in our program, we will use cutting and pasting. As you can see in the above example, you must be careful when doing so. If you add unnecessary spaces in a code block or a different comment, you may get different cache plans.

Maximize performance and minimize memory usage

To optimize your code, you not only need to worry about every command you write and your database design, you also need to worry about whether you have redundant comments and spaces in similar T-SQL statements. If you don't pay attention to the details around similar T-SQL statements, you may cause SQL Server to create multiple cache plans. Having multiple plans for the same T-SQL statement can cause SQL Server to work harder, and memory is wasted by storing these cache plans. It may not seem very important, but as T-SQL statement writers, we need to ensure that we do our best in optimizing performance and minimizing resource utilization. One approach is to ensure that you do not cache multiple plans for the same T-SQL statement.