View memory usage for SQL Server

View the memory usage of SQL Server

The previous article mentions that dynamic T-SQL produces more execution plans, and how much memory is consumed by these execution plans? I found the answer in Xu Haiwei's book today. Dynamic view not only can find the cache of execution plan, the page cache of data table can also be found, and SQL will be collated and marked.

--querying SQL Server overall memory usageSelecttype,sum(virtual_memory_reserved_kb) vm_reserved,sum(virtual_memory_committed_kb) vm_commited,sum(awe_allocated_kb) awe_allocated,sum(shared_memory_reserved_kb) shared_reserved,sum(shared_memory_committed_kb) shared_commited--, sum (single_pages_kb)--sql2005,        --, sum (multi_pages_kb)--sql2005, fromsys.dm_os_memory_clerksGroup  bytypeOrder  bytype--Query the current database cache of all data pages, which data tables, the number of cached data pages--from this information can be seen, the system often access to what tables, how big? SelectP.object_id,object_name=object_name (p.object_id), p.index_id, buffer_pages=Count(*)  fromsys.allocation_units A, sys.dm_os_buffer_descriptors B, sys.partitions pwherea.allocation_unit_id=b.allocation_unit_id anda.container_id=p.hobt_id andb.database_id=db_id()Group  byP.object_id, p.index_idOrder  byBuffer_pagesdesc --query cache for various execution plans, and how much memory is accounted for separately--You can compare the amount of memory for dynamic queries with parameterized SQL (pre-defined statements)SelectCacheobjtype, ObjType,sum(cast(size_in_bytes as bigint))/1024x768  assize_in_kb,Count(bucketID) asCache_count fromSys.dm_exec_cached_plansGroup  byCacheobjtype, ObjTypeOrder  byCacheobjtype, ObjType--The specific execution plan in the query cache, and the corresponding SQL--This result is counted as a data table or SQL and can be used as a baseline to consider when adjusting the index--query results are large, note that the result set is output to a table or fileSELECTusecounts, Refcounts, Size_in_bytes, Cacheobjtype, ObjType,TEXT fromSys.dm_exec_cached_plans CP CrossAPPLY sys.dm_exec_sql_text (plan_handle)ORDER  byObjTypeDESC ;GO

View memory usage for SQL Server