SQL SERVER memory allocation and common memory issues (2)--DMV query

Original: SQL SERVER memory allocation and common memory issues (2)--DMV query

Memory dynamic management view (DMV):

Starting from Sys.dm_os_memory_clerks.

SELECT  [Type],        sum (virtual_memory_reserved_kb) as [VM reserved],        sum (virtual_memory_committed_kb) as [ VM Committed],        sum (awe_allocated_kb) as [awe allocated],        sum (shared_memory_reserved_kb) as [SM reserved],        SUM (shared_memory_committed_kb) as [SM committed],        SUM (multi_pages_kb) as [Multipage Allocator],        sum (single_pages_kb) as [Sinlgepage Allocator],        sum (virtual_ MEMORY_RESERVED_KB)/(Case when sum (virtual_memory_committed_kb) =0 then 1 ELSE SUM (virtual_memory_committed_kb) END) as [Reserved/commit],        sum (single_pages_kb) +sum (multi_pages_kb) as Stolen,        sum (virtual_memory_committed_kb) +sum (single_pages_kb) as [Sinlgepage Allocator]        from    sys.dm_os_memory_clerksgroup by [Type]order by [type]

Where type is the name of the memory clerk, you can know the purpose of the RAM.

For the resulting data:

Memoryclerk_sqlbufferpool: In normal terms, the total value is the largest.

CACHESTORE_OBJECP: The execution plan cache for triggers, stored procedures, and functions.

CACHESTORE_SQLCP: Dynamic T-SQL statement, pre-compiled TSQL statement execution plan cache.

CACHESTORE_PHDR: Cached views, user-defined function information to help SQL generate execution plans faster.

Cachestore_xproc: Cache extended Stored procedures, sp_executesql,sp_cursor*,sp_trace*, etc.

Cachestore_temptables: Caches temporary objects. Local temp table, Global temp table, table variable, and more.

CACHESTORE_CLRPROC:SQLCLR the process cache.

Cachestore_events: Stores the time and message for Service Broker.

Cachestore_cursors: Stores all cursors, including localtsqlcursors, Global TSQL cursor, and API CURSORS.

Userstore_tokenperm: Holds the security context of all users and various security-related tokens that are used to check the query's cumulative permissions.

USERSTORE_SXC: The PRC parameter that temporarily holds the statement being executed, and if the parameter is too long, the amount of memory used will be larger.

What are the tables in the in-memory data pages, and what are the respective accounts? Sys.dm_os_buffer_descriptors

DECLARE @name NVARCHAR (+) DECLARE @cmd NVARCHAR (+) DECLARE dbnames cursorfor    SELECT  name from    Master.dbo.sysdatabasesOPEN Dbnamesfetch NEXT from Dbnames to @name while @ @FETCH_STATUS = 0     BEGIN        SET @cmd = ' s Elect B.database_id,db=db_name (b.database_id), P.object_id,p.index_id,buffer_count=count (*) from '            + @name + '. Sys.allocation_units A, ' + @name            + '. Sys.dm_os_buffer_descriptors b, ' + @name            + '. Sys.partitions p where A.alloc ation_unit_id=b.allocation_unit_id and a.container_id=p.hobt_id and b.database_id=db_id ("'            + @name            + ') Group BY b.database_id,p.object_id,p.index_id ORDER by b.database_id,buffer_count Desc '        EXEC (@cmd)        FETCH NEXT from Dbnames to @name     endclose dbnamesdeallocate dbnamesgo

Object that will cache execution plan: proc: Stored procedure prepared: Pre-defined statement adhoc: Dynamic query Replproc: Copy filter process trigger: Trigger View: View Default: Default usrtab: User table Systab: system table Check:check constraint Rule: Rules
You can see how much memory each of the various objects occupies:

SELECT  ObjType,        SUM (size_in_bytes)/1024x768 as sum_size_in_kb,        COUNT (bucketID) as Cache_countsfrom    Sys.dm_exec_cached_plansgroup by ObjType

Analyze which objects are stored specifically:

SELECT  usecounts,        refcounts,        size_in_bytes,        cacheobjtype,        objtype,        textfrom    Sys.dm_exec_cached_plans CP Cross        APPLY sys.dm_exec_sql_text (plan_handle) ORDER by ObjType DESC; GO

--use DMV to parse the most read statements since SQL SERVER startup

--Sort by the number of pages physically read, Top 50 select top 50        qs.total_physical_reads,        Qs.execut Ion_count,        Qs.total_physical_reads/qs.execution_count as [Avg IO],      &NBS P SUBSTRING (Qt.text, qs.statement_start_offset/2,                  (case WHE N Qs.statement_end_offset = -1                         th EN LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2                      &N Bsp  else qs.statement_end_offset                    End-qs.statement _start_offset)/2) as Query_text,        qt.dbid,        dbname = db_name (qt.d Bid),        Qt.objectid,        Qs.sql_handle,        QS.P Lan_handlefrom &NBsp  sys.dm_exec_query_stats qs        cross APPLY sys.dm_exec_sql_text (Qs.sql_handle) as Qtorder by Qs.total_physical_reads desc --According to the number of logically read pages, top 50 select top 50        Qs.total_logical_reads ,        Qs.execution_count,        Qs.total_logical_reads/qs.execution_count A S [AVG IO],        SUBSTRING (Qt.text, qs.statement_start_offset/2,        &NBSP ;         (case qs.statement_end_offset = -1              &NBSP ;          then LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2            & nbsp            else qs.statement_end_offset              &NBS P     End-qs.statement_start_offset)/2) as Query_text,        qt.dbid,    &NB Sp   DBname = db_name (qt.dbid),        Qt.objectid,        Qs.sql_handle,  &nbsp ;     Qs.plan_handlefrom    sys.dm_exec_query_stats qs        Cross APPLY SYS.DM_EX Ec_sql_text (Qs.sql_handle) as Qtorder by Qs.total_logical_reads DESC

--Force a partial SQL SERVER memory cache to be freed with DBCC:

DBCC Freesystemcache

DBCC Freesessioncache

DBCC Freeproccache

--View operating system memory state select  total_physical_memory_kb/1024 as [physical memory (MB)],        available_physical_memory_kb/1024 as [  Available physical memory (MB)],        system_cache_kb/1024 as [total system cache memory (MB)],        (kernel_paged_pool_kb + kernel_nonpaged_pool_kb)/1024 As [total kernel pool memory (MB)],        total_page_file_kb/1024 as [size of submission limit reported by operating system (MB)],        available_page_file_kb/1024 as [unused page file Total (MB)],        system_memory_state_desc as [memory status description]from    sys.dm_os_sys_memory

SQL SERVER memory allocation and common memory issues (2)--DMV query