Understanding and summary of MySql query cache and SQL Server process Cache

I. MySql Query Cache

1. Query Cache

MySQL Query Cache is used to Cache the SELECT statement we execute and the result set of this statement. The specific technical details of MySql in implementing Query Cache are similar to the typical KV storage, it is to map the SELECT statement and the query statement result set to a HASH and save it in a certain memory area. When the client initiates an SQL Query, the Query Cache search logic is to first verify the SQL permission and then Query the results through the Query Cache. It does not need to go through the Optimizer module to analyze and optimize the execution plan, and does not need to interact with any storage engine. It reduces a large amount of disk I/O and CPU operations, so sometimes the efficiency is very high.

2. Query Cache setting parameters

You can enable and set the Query Cache function by adjusting MySQL parameters. The main five parameters are as follows:

(1) query_cache_limit: Maximum capacity of a single query result set that can be cached. The default value is 1 MB. The query result set beyond this parameter will not be cached;
(2) query_cache_min_res_unit: sets the minimum memory size allocated by the Query Cache each time, that is, the minimum memory space occupied by the Cache for each Query;
(3) query_cache_size: Set the memory size used by the Query Cache. The default value is 0. The size must be an integer multiple of 1024. If it is not an integer multiple, mySQL automatically adjusts and reduces the minimum volume to a multiple of 1024;
(4) query_cache_type: the switch that controls the Query Cache function. It can be set to 0, 1, or 2. The meanings are as follows:
A, 0 (OFF): Disable the Query Cache function. Query Cache is not used under any circumstances;
B, 1 (ON): Enable the Query Cache function. However, when the SQL _ no_cache prompt is used in the SELECT statement, the Query Cache is not used;
C, 2 (DEMAND): Enable the Query Cache function, but the Query Cache is used only when the SQL _CACHE prompt is used in the SELECT statement.
(5) query_cache_wlock_invalidate: determines whether to invalidate the Query Cache related to the table when a write lock occurs on the table. If it is set to 1 (TRUE ), when the write lock is performed, all Query Cache related to the table will be invalidated. If it is set to 0 (FALSE), the Query Cache related to the table can still be read at the lock time.

3. Query Cache and Performance

Nothing is too late, especially for systems with frequent writes, enabling the Query Cache function may not improve the system performance, but may sometimes decrease. The reason is that to ensure that the content cached by the Query Cache is absolutely consistent with the actual data, when a data table is updated, deleted, and inserted, mySql forcibly invalidates all Query Cache statements referenced in the table. For intensive write operations, enabling the query cache may cause frequent cache failures, which indirectly leads to memory spikes and CPU spikes. This is a great burden on busy database systems.

4. Others

Query Cache has slightly different implementations because of different MySql storage engines. For example, MyISAM stores Cache result sets in OS Cache, while InnoDB is the most popular in Buffer Pool.

 

Ii. SQL Server Procedure Cache

SQL Server does not have a Query Cache mechanism similar to MySql, but it has its own Cache mechanism. SQL Server does not directly cache the SQL query result set, but caches the queried Data pages (Data Buffer) It has read ), it also caches execution plans (process Cache Procedure Cache). Next we will talk about the well-known process Cache.

1. SQL Execution Process

The SQL statement needs to be compiled before execution. Then, it needs to be optimized through the SQL Server query engine, and then the execution plan after optimization is obtained. Finally, the SQL statement is executed according to the execution plan.

2. Procedure Cache)

When an execution plan is created, the SQL Server query engine automatically caches the execution plan by default.

For SQL statements with different parameters, SQL Server can reuse the cache execution plan.

However, for different SQL statements, SQL Server cannot reuse the previous execution plan, but needs to re-compile a new execution plan because the SQL Server query engine automatically caches the execution plan, each new execution plan occupies the memory of SQL Server.

When SQL Server has enough memory available, the query engine does not actively clear previously saved query plans. Therefore, in some cases, a similar SQL statement only has many execution plans out of thin air because of different writing methods. For similar SQL statements, these redundant execution plans occupy the memory, the number of query plans cached in SQL Server is greatly affected.

In this case, if the maximum available memory of SQL Server is limited, it will reduce the available memory of SQL Server, therefore, more memory pages are exchanged with the disk during query execution, especially for large data queries. If the maximum available memory is not set, the SQL Server caches too many execution plans, so that the memory usage is too large.

3. How to Reduce process Cache
To reduce the Cache Usage During the process, you can use parameterized queries.

The key to parameterized query is that the query optimizer will create a reusable cache plan (the SQL Server Query Optimizer will rewrite the query into a parameterized SQL statement ), this reusable cache plan eliminates the need to create a cache plan for each execution of these SQL statements. By creating a reusable plan, SQL Server reduces the memory usage required to store similar execution plans.

For developers, we can use the following two methods to achieve parameterized query:

(1) Execute SQL statements using stored procedures;

(2) Execute SQL statements using sp_executesql.

Concerning the use of stored procedures to execute SQL statements, another question: there has been a lot of controversy over stored procedures. For example, The ORM team believes that stored procedures are completely process-oriented and difficult to expand and maintain. Based on my personal development experience, I suggest using more simple and almost no logic stored procedures, but complex stored procedures have always been BUG camps, in addition, the maintenance cost is extremely high in the future (as our architect said, the database of an important business system has a storage process of more than eight thousand rows and more than two hundred variables, no one dares to change ), it is best to write the logic through the business logic layer to cope with drastic changes. Now we have a mature ORM and layers. In development, we must avoid writing too long and logically complex stored procedures. Otherwise, we will face changes, it is not impossible to see several eight thousand rows of stored procedures over time.

 

Refer:

<MySql technical insider>

Http://www.sql-server-performance.com/2004/data-cache/

<SQL Server2005 technical insider>