Good habits when writing T-SQL statements (Part 3)

TipsMake.com - In this third part, the article will show you how to write T-SQL statements to promote the reuse of cache storage (cache). Understanding whitespace issues and taking notes on how to create a new cache map or reusing existing diagrams will help you minimize the number of diagrams your application must cache.

Explore caching diagrams

Have you taken advantage of saving the diagram on the cache? How much cache have you exploited? Does your application only use them once or take advantage of it many times? Do you have multiple cached plans for the same query in the procedure cache at the same time? How much space does the cache scheme use? Here are some questions you need to answer to make sure that you have optimized the procedure cache and minimized the number of cache diagrams that the application creates. There are a few minor problems in writing your T-SQL statement that cause the SQL server to do more to compile and cache executable diagrams for the same code.

Before the SQL server can process the T-SQL code, it needs to create an execution plan. To create an execution map, the SQL server must first consume valuable resources such as the CPU to compile T-SQL code. When the diagram is created, it will be cached to be reusable when the application calls the same T-SQL statement more than once. You can improve SQL server performance if you write T-SQL statements to enhance cache reuse with frequently executed T-SQL fragments.

With the emergence of SQL Server 2005, Microsoft provides DMVs (Dynamic Management Views) that allow you to explore saved diagrams. By using DMVs, you can learn a lot about cache diagrams. Here is a summary list of things you can identify:

1. The text is related to the cache diagram
2. The number of cache caching times to be executed
3. Size of cached diagram

In the following part of the article, I will show you how to use the DMV to learn cache information.

Create multiple diagrams for notes or extra spaces

I'm sure you all support the idea of ​​putting code into Stored Procedure (SP) procedures. We do this to increase code reuse within a single application or across multiple applications. However, not all code executed by SQL server is in SPs. Some applications can be written in in-line T-SQL commands (raw commands). If you are writing raw T-SQL code, you need to be careful when writing notes or placing spaces because it might cause the SQL server to create multiple cached diagrams for the same T-SQL code. .

The following is an example of 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 the same two T-SQL statements. Both return all records from the AdventureWorks.Production.Product table. So how many cache maps do you think the SQL server will create when running the above code? To answer this question, I will learn the cache diagram information using the DMV in SQL Server 2005 and SQL Server 2008. To see the diagrams generated by the above two T-SQL statements, I will 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 the above code, I first release the procedure cache by running the DBCC FREEPROCCACHE command. This command removes all executable diagrams in memory. However, I would also like to note that you should not use this command when working in the enterprise because it will delete the entire cache scheme. This can have a huge impact on your work because the regular diagrams are recompiled. After releasing the procedure cache, I run two different SELECT statements. Finally, I link the information from the DMV to return the cached schema information of the two SELECT statements. The following is the result obtained when running the above code:

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, the two SELECT statements create two different caching schemes and each one is executed (exec_count number). The reason this happens is because the two SELECT statements are not exactly the same. The second SELECT statement is slightly different because there are additional notes. Also, notice the size of the diagram: 40960 bytes - the memory size is too large for a very simple T-SQL statement. Therefore, you should be careful when adding notes to the code, avoiding the server from creating more redundant diagrams.

Another reason for creating multiple cache diagrams for the same T-SQL statements is the white space. The following are the two statements that are the same except for spaces:

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

As you can see, the second statement contains some extra space between the FROM and the object name. These extra spaces cause the SQL server to think that these are two different statements, thereby leading to the creation of two different cache schemes for the two statements. In this case, it is obvious that you can easily tell the difference between the two statements because the spaces are in the middle of the statement. But if you accidentally add a space in front of the SELECT or the end of the statement, you won't be able to recognize whitespace and the statement will look exactly the same. However, the SQL server is visible, and so it creates many caching schemes because of the extra spaces.

When the SQL server looks at the code, it compares with the available diagrams in the procedure cache. If the code is identical to the existing cache scheme, the SQL server does not need to compile and save the diagram to memory. The SQL server will reuse the schema contained in the cache for the same code. To optimize the source code, you need to make sure to reuse the cached diagram whenever possible.

When you are building application source code that uses T-SQL statements without using SP, you need to be careful to ensure that you get the highest possible reusable diagram. We often use copy-paste method when we want to use the same code in different parts of the application. However, as you can see in the examples above, you need to be careful when doing this. Just a few extra spaces or a small note also makes the SQL server create many different cache diagrams.

Maximize performance and reduce memory

To optimize the source code, it is not enough to care only about database design, you need to pay attention to every little detail such as spaces and notes. If you don't pay attention to the details around the same T-SQL statements, you can make the SQL server create multiple cache diagrams. Maybe having some redundant cache in memory is not so important, but as a programmer, we need to do our best to improve server performance and minimize usage resources. And one of the ways to accomplish this goal is to avoid creating multiple cache diagrams for the same T-SQL statements.