SQL Server memory-intensive understanding

The memory that SQL Server occupies consists primarily of three parts: Data buffer, execution cache (Procedure cache), and SQL Server engine program. While the cache used by SQL Server engine programs is generally relatively small, the main focus of our memory tuning is on the data cache and the execution cache control.
A). Data cache: Executes a query statement, SQL Server loads the relevant data page (the data for SQL Server operations is in pages) into memory, and the next time you request the data for this page, you do not have to read the disk, greatly increasing the speed.
b). Execute the command cache: When executing a stored procedure, a custom function, SQL Server needs to be binary compiled and then run, and the compiled results will be cached again, without having to compile again.
To perform a cache tuning:
To reduce the footprint of the execution cache, you can reduce memory consumption primarily by using parameterized queries.
1. Use parameterized queries to reduce cache consumption
Analysis:
SQL statements are first compiled and optimized through the query optimization engine before they are executed, resulting in an optimized execution plan that is then executed according to the execution plan. SQL Server can reuse execution plans for SQL statements that are similar in their entirety and are just different parameters. However, for different SQL statements, SQL Server does not reuse the previous execution plan, but it needs to recompile a new execution plan. At the same time, SQL Server does not actively purge previously saved query plans when memory is sufficiently used (note: SQL Server cleans up periodically for query plans that are no longer used for a long time). This way, different SQL statement execution will significantly affect the number of query plans stored in SQL Server. If the maximum available memory for SQL Server is limited, too much useless execution plan consumption will result in less available memory for SQL Server, resulting in more memory paging from the disk when executing queries, especially when large queries occur. If the maximum available memory is not qualified, SQL Server consumes more memory because of the reduced available memory.
Resolution:
We can generally implement parameterized queries in two ways: the first is to use stored procedures to execute SQL statements (which in reality has become a principle of SQL Server DBA), and the second is to use sp_executesql To execute a single SQL statement (take care not to use sp_executesql as the first example above).
2. Examine and analyze the execution plan in SQL Server execution cache
To optimize caching, you can analyze and locate problems by specifically analyzing the execution plan in the cache to see what is useful and what is useless.
By querying DMV:sys.dm_exec_cached_plans, you can understand the cache situation in the database, including the number of times used, the type of cache, the amount of memory occupied, and so on:

SELECT from

The plan_handle of the cache plan can query the execution plan details, including the corresponding SQL statement:

SELECT  TOP  - usecounts,    objtype,    p.size_in_bytes,    [sql].  [text] from sys.dm_exec_cached_plans pOUTER  APPLY sys.dm_exec_sql_text (p.plan_handle) SQLORDER by usecounts

We can choose to focus on the SQL statements that execute a plan that consumes large amounts of memory and that are reused less frequently. See if it is reasonable to call the method. In addition, you can analyze the execution plan by using more frequently repeated SQL statements to see if the execution plan has been optimized. Further, through the analysis of the query plan, we can find some SQL statements which occupy the most Io, CPU time and the most number of executions in the system, and then perform the corresponding tuning analysis.

Here are some common ways to clean up your cache:

DBCC Dropcleanbuffers        -- Clear data cache (data)DBCC freeproccache        -- clear the stored procedure cache (Execution plan)  DBCC freesessioncache         -- Clear Session cache   DBCC Freesystemcache ('all')      -- clears the system  cache

Here are some script statements that provide memory viewing capabilities:

  -- Memory Usage SELECT *  from sys.dm_os_performance_counters    WHERE inch ('Target Server Memory (KB)','totalServer Memory (KB)'  )     --  memory status    DBCC memorystatus

SQL Server memory-intensive understanding