View memory usage for SQL Server

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.