SQL Server Memory Cost analysis

The first step:

How much memory is used for each type of resource.

Select
Clerks.type,
SUM (clerks.virtual_memory_reserved_kb) as reserve,
SUM (clerks.virtual_memory_committed_kb) as [Commit],
SUM (clerks.awe_allocated_kb) as [awe],
SUM (clerks.shared_memory_reserved_kb) as [Sreserve],
SUM (clerks.shared_memory_committed_kb) as [Scommit]
From Sys.dm_os_memory_clerks as clerks
Group BY Clerks.type;
Go

The first step can give us a general grasp of what kind of diagonal use, how much memory.

Step Two:

Sys.dm_os_buffer_descriptors, returns information about all the current data pages in the SQL Server buffer pool.

Select Dp.database_id,dp.page_type,sum (Dp.row_count) as [RowCount]
From Sys.dm_os_buffer_descriptors as DP

where dp.database_id=db_id (' Studio ')

GROUP BY Dp.database_id,dp.page_type
Order BY dp.database_id;
Go

Step Three:

View the execution plan cache

Select Plans.usecounts,plans.objtype,plans.size_in_bytes,stt.text
From Sys.dm_exec_cached_plans as plans cross apply Sys.dm_exec_sql_text (Plans.plan_handle) as STT;
Go

SQL Server Memory Cost analysis