Shulou(Shulou.com)06/01 Report--

When executing any query, SQL Server will read the data into memory. After the data is used, it will not be released immediately, but will be cached in memory Buffer. When the same query is executed again, if all the required data is cached in memory, SQL Server will not generate Disk IO operations and immediately return the query results. This is the performance optimization mechanism of SQL Server.

The main memory consumer

1, Data Cache

Data Cache is a buffer that stores data pages. When SQL Server needs to read data pages in a data file, SQL Server will transfer the entire Page into memory (a Page in memory is called buffer). Page is the smallest unit of data access.

When a user modifies data on a Page, SQL Server modifies the Buffer in memory first, but does not immediately write the data leaf back to disk, but waits for the CheckPoint or lazy Writer process to run. When a user reads a Page, if SQL Server has no memory pressure, it will not delete the Page in memory, because the data page in memory always stores the latest state of the data. If other users use this Page, SQL Server does not need to read it from the hard disk once, saving statement execution time. Ideally, SQL Server caches all the data that users need to access in memory. SQL Server never needs to go to the hard disk to read the data. It only needs to write the modified pages back to the hard disk when CheckPoint or lazy Write is running.

2. Query Plan Cache

Store query statements and execution plans for stored procedures for reuse without the need for recompilation, because compiling query statements to produce execution plans is a very resource-intensive process.

Second, check the memory consumption

In SQL Server, only Memory Clerk can allocate memory, Memory Clerk records the amount of memory allocated, any object that needs to use memory must create its own Memory Clerk, and use this Memory Clerk to allocate memory.

1. Check the amount of memory allocated by Memory Clerk

View Code

For memory node 64, only used in DAC.

2. Count the total amount of memory allocated by Memory Clerk

View Code

The memory-consuming Clerk is:

MEMORYCLERK_SQLBUFFERPOOL: basically the size of the page in the Buffer Pool

OBJECTSTORE_LOCK_MANAGER: Memory used by lock structure. When serious lock blocking occurs, this indicates that a large number of locks are stored in the system, causing lock management to consume a large amount of memory;

CACHESTORE_OBJCP: Cache space occupied by execution plans of modules such as triggers and stored procedures;

CACHESTORE_SQLCP: cache of execution plans for dynamic TSQL statements, ad hoc queries and prepared TSQL;

CACHESTORE_COLUMNSTOREOBJECTPOOL: ColumnStore Index occupied cache

3. View data pages in cache

When a data page is read from disk to memory, it is copied to a Buffer Pool for reuse by SQL Server. Each cached data page has a Buffer Descriptor, which uniquely identifies the data page in memory. For each data page cached in the SQL Server instance, you can view the cache description information from sys.dm_os_buffer_descriptors.

View Code

4. View Plan Cache

Generating execution plans consumes CPU resources. SQL Server stores each query plan, its occupied memory space, reuse times and other information in the Plan Cache in memory.

View Code

Third, empty the cache

When tuning the performance of stored procedures, it is necessary to empty the cache. Buffer Pool is the cache manager of SQL Server, which contains most of the cached data of SQL Server, such as Plan cache and Data cache.

There are three common commands for emptying the cache:

FREEPROCCACHE

Checkpoint and DBCC DROPCLEANBUFFERS are used to clean dirty and clean pages in the Data Cache, while DBCC FREEPROCCACHE is used to empty all Plan Cache.

1. Empty data cache

Checkpoint is used to write Dirty Pages to the hard disk. Dirty Pages refer to data pages read into the cache and modified, resulting in different contents between data pages in memory and data pages in the hard disk. Clean Pages refer to data pages read into the cache and not modified, so the contents of data pages in memory and data pages in the hard disk are the same. Both Dirty pages and Clean pages are Data Cache, which must be cleaned from memory during performance tuning. Otherwise, query performance will ignore IO consumption of loading data from hard disk to memory, affecting the execution of query statements.

CHECKPOINT is used to generate a cold buffer Cache, which writes all dirty pages generated by the current database to the hard disk and cleans the memory buffer; DBCC DROPCLEANBUFFERS is executed after CHECKPOINT to empty all clean pages from the buffer pool.

During performance testing, use DBCC DROPCLEANBUFFERS to clear all clean cache data from SQLSERVER's data cache pool. Note that this command only removes clean cache, not dirty cache. Therefore, before executing this command, you should execute CheckPoint to write all dirty pages to disk, so that when DBCC RROPCLEANBUFFERS is run, you can ensure that all data caches are cleaned, not some of them.

2. Empty the plan cache

Plan Cache is used to cache the execution plan of query statements. After each query statement is executed, its query plan will be cached in Plan Cache. In production environments, do not easily clean Plan Cache. If you detect parameter sniffing problems with a Plan Cache that are causing poor performance, it is recommended that you modify the query statement and recompile the stored procedure to flush the Plan Cache for that SP alone.

DBCC FREEPROCCACHE [ ( { plan_handle | sql_handle} ) ]

The plan cache, formerly known as the procedure cache, executes the DBCC FREEPROCCACHE command to free all plan caches, which causes stored procedures, Ad Hoc queries, etc. to have to be recompiled to create a new plan cache.

IV. Forced recompilation of the execution plan

Module modification stored procedures, triggers, etc. can make its execution plan recompile, but there are other methods that can force recompilation of execution plans

1, mark, recompile next time

Using this stored procedure, mark an execution module (SP, Trigger, User-Defined Function) and recompile the execution plan at the next execution

sys.sp_recompile [ @objname = ] 'object'

2. Do not reuse the execution plan

When creating stored procedures, use the WITH RECOMPILE option to recompile each SP execution, using the new execution plan.

CREATE PROCEDURE dbo.usp_procname @Parameter_Name varchar(30) = 'Parameter_default_value'WITH RECOMPILE

3, recompile at execution time

Recompile the execution plan of a stored procedure when it is executed

exec dbo.usp_procname @Parameter_name='Parameter_value' WITH RECOMPILE

4. Recompilation at the statement level

In SP, use the query option (recompile) to recompile only execution plans at that statement level

select column_name_listfrom dbo.tablenameoption(recompile)

After SQL Server executes the query, RECOMPILE instructs the storage engine to discard the plan cache and force the query optimizer to recompile to generate a new execution plan the next time the stored procedure is executed. On recompilation, the SQL Server optimizer generates a new plan cache using the current variable values.

Attached:

Cold cache, hot cache, dirty cache and clean cache

Clean Buffer refers to the unmodified data pages in memory. DBCC DROPCLEANBUFFERS is used to remove clean pages from the Buffer Pool and free the Buffer.

Dirty Buffer refers to the data page is modified in memory, but not written to the hard disk, resulting in data in the hard disk is different from memory, usually, dirty pages through CHECKPOINT process to automatically synchronize, CHECKPOINT dirty page data written to the hard disk, so that the data in memory and hard disk files to maintain consistency, can reduce the time to restore data.

Cold buffer pages are cache pages that have not been used recently during data processing.

A hot buffer page is a cache page that has been used most recently during data processing.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.