SQL Server 内存使用情况

Easter79
• 阅读 450

• 查看设置的最大与最小内存:

SQL Server 内存使用情况 SQL Server 内存使用情况

1 exec sp_configure 'max server memory (MB)'  
2 exec sp_configure 'min server memory (MB)'

View Code

• SqlServer目标内存、当前内存 、数据库内存页数:

SQL Server 内存使用情况 SQL Server 内存使用情况

1 SELECT object_name,counter_name,cntr_value,cntr_value/1024/1024 AS [cntr_value(GB)]  
2 FROM sys.dm_os_performance_counters  
3 WHERE counter_name in('Target Server Memory (KB)','Total Server Memory (KB)','Database pages')

View Code

• 查看设置的最大与最小内存:

SQL Server 内存使用情况 SQL Server 内存使用情况

1 SELECT * FROM sys.dm_os_sys_info  
2 SELECT * FROM sys.dm_os_sys_memory  
3 SELECT * FROM sys.dm_os_process_memory

View Code

•  查看当前是否有等待内存授予的进程:

SQL Server 内存使用情况 SQL Server 内存使用情况

1 Select a.*,b.text, c.query_plan   
2 from sys.dm_exec_query_memory_grants a  
3 CROSS APPLY sys.dm_exec_sql_text(sql_handle) b   
4 CROSS APPLY sys.dm_exec_query_plan (plan_handle) c

View Code

•  SqlServer预留和提交内存情况:

SQL Server 内存使用情况 SQL Server 内存使用情况

SELECT  [type]     
,SUM(virtual_memory_reserved_kb) AS [vm reserved]    
,SUM(virtual_memory_committed_kb) AS [vm commited]    
,SUM(awe_allocated_kb) AS [awe allocated]      
,SUM(shared_memory_reserved_kb) AS [sm reserved]    
,SUM(shared_memory_committed_kb) AS [sm committed]    
,SUM(single_pages_kb) AS [Stolen in Buffer Pool]    
,SUM(multi_pages_kb) AS [MemToLeave]    
,SUM(single_pages_kb) + SUM(multi_pages_kb) AS [Stolen]    
,SUM(virtual_memory_committed_kb) + SUM(multi_pages_kb) AS [Buffer Pool]    
FROM sys.dm_os_memory_clerks   
GROUP BY [type]    
ORDER BY [type] desc

View Code

•  当前各数据库buffer pool的分配情况:

SQL Server 内存使用情况 SQL Server 内存使用情况

SELECT   
CASE database_id   
    WHEN 32767 THEN 'ResourceDb'   
    ELSE db_name(database_id) END AS Database_name  
,count(*) AS cached_pages_count  
,count(*)*8/1024 AS cached_space_in_mb  
,sum(convert(bigint,free_space_in_bytes))/1024/1024 AS free_space_in_mb  
FROM sys.dm_os_buffer_descriptors(nolock)  
GROUP BY db_name(database_id) ,database_id  
ORDER BY cached_pages_count DESC;

View Code

•  当前数据库各表buffer pool的分配情况:

SQL Server 内存使用情况 SQL Server 内存使用情况

SELECT top(20) name ,index_id ,count(*)AS cached_pages_count   
,count(*)*8/1024 AS cached_space_in_mb   
FROM sys.dm_os_buffer_descriptors AS bd   
INNER JOIN (  
    SELECT object_name(object_id) AS name ,index_id ,allocation_unit_id  
    FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p   
    ON au.container_id = p.hobt_id  AND (au.type = 1 OR au.type = 3 )   
    UNION ALL  
    SELECT object_name(object_id) AS name,index_id, allocation_unit_id  
    FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p   
    ON au.container_id = p.hobt_id  AND au.type = 2  
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id  
WHERE database_id = db_id() AND obj.name NOT LIKE 'sys%'  
GROUP BY name, index_id   
ORDER BY cached_pages_count DESC

View Code

•  当前各表或索引在缓冲池中的页数量及大小:

SQL Server 内存使用情况 SQL Server 内存使用情况

SELECT obj.name ,obj.index_id,i.name ,count(*)AS cached_pages_count ,count(*)*8/1024 AS cached_space_in_mb  
FROM sys.dm_os_buffer_descriptors AS bd   
INNER JOIN (  
    SELECT object_name(object_id) AS name,object_id,index_id ,allocation_unit_id  
    FROM sys.allocation_units AS au  
    INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)  
    UNION ALL  
    SELECT object_name(object_id) AS name,object_id,index_id, allocation_unit_id  
    FROM sys.allocation_units AS au  
    INNER JOIN sys.partitions AS p   ON au.container_id = p.partition_id AND au.type = 2  
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id  
LEFT JOIN sys.indexes i(nolock) on obj.object_id = i.object_id AND obj.index_id = i.index_id    
WHERE database_id = db_id() AND obj.name NOT LIKE 'sys%'  
GROUP BY obj.name ,obj.index_id,i.name  
ORDER BY cached_pages_count DESC;

View Code

•  统计当前内存分配情况 :

SQL Server 内存使用情况 SQL Server 内存使用情况

select   
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,  
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,  
bpool_committed * 8 / 1024 as bpool_committed_mb,  
bpool_commit_target * 8 / 1024 as bpool_target_mb,  
bpool_visible * 8 / 1024 as bpool_visible_mb  
from sys.dm_os_sys_info

View Code

•  当前内存脏页数量及大小 :

SQL Server 内存使用情况 SQL Server 内存使用情况

SELECT db_name(database_id) AS 'Database'  
,count(page_id) AS 'Dirty Pages'  
,count(page_id)*8/1024 AS 'Dirty Pages(MB)'  
FROM sys.dm_os_buffer_descriptors(nolock)  
WHERE is_modified =1  
GROUP BY db_name(database_id)  
ORDER BY 'Dirty Pages' DESC

View Code

•  缓存类型数量大小 :

SQL Server 内存使用情况 SQL Server 内存使用情况

select cacheobjtype as [Cached Type]  
,COUNT(*) [Number of Plans]   
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]  
from sys.dm_exec_cached_plans   
group by cacheobjtype   
order by [Plan Cache Size(MB)] desc

View Code

•  缓存对象数量大小 :

SQL Server 内存使用情况 SQL Server 内存使用情况

select objtype as [Cached Object Type]  
,COUNT(*) as [Number of Plans]   
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]  
,avg(usecounts) [Avg Use Count]   
from sys.dm_exec_cached_plans   
group by objtype   
order by [Plan Cache Size(MB)] desc

View Code

•  内存对象 :

SQL Server 内存使用情况 SQL Server 内存使用情况

SELECT top 10 * FROM sys.dm_os_memory_cache_counters(nolock)  
ORDER BY entries_count DESC;  
  
SELECT * FROM sys.dm_os_memory_clerks a  
WHERE name='TokenAndPermUserStore'  
  
SELECT * FROM sys.dm_os_memory_cache_entries(nolock)  
WHERE [type]='USERSTORE_TOKENPERM' and name='TokenAndPermUserStore'

View Code

•  缓存分析对象 :

SQL Server 内存使用情况 SQL Server 内存使用情况

SELECT * FROM sys.syscacheobjects   
SELECT * FROM sys.dm_exec_cached_plans

View Code

•  即时查询优化(运行即生效) :

SQL Server 内存使用情况 SQL Server 内存使用情况

EXEC sp_configure 'show advanced options',1  
RECONFIGURE  
EXEC sp_configure 'optimize for ad hoc workloads',1  
RECONFIGURE

View Code

all code :

SQL Server 内存使用情况 SQL Server 内存使用情况

--查看设置的最大最小每次  
exec sp_configure 'max server memory (MB)'  
exec sp_configure 'min server memory (MB)'  
  
  
--SqlServer目标内存、当前内存 、数据库内存页数  
SELECT object_name,counter_name,cntr_value,cntr_value/1024/1024 AS [cntr_value(GB)]  
FROM sys.dm_os_performance_counters  
WHERE counter_name in('Target Server Memory (KB)','Total Server Memory (KB)','Database pages')  
  
  
--系统物理内存使用情况  
SELECT * FROM sys.dm_os_sys_info  
SELECT * FROM sys.dm_os_sys_memory  
SELECT * FROM sys.dm_os_process_memory  
  
--查看当前是否有等待内存授予的进程  
Select a.*,b.text, c.query_plan   
from sys.dm_exec_query_memory_grants a  
CROSS APPLY sys.dm_exec_sql_text(sql_handle) b   
CROSS APPLY sys.dm_exec_query_plan (plan_handle) c  
  
  
--SqlServer预留和提交内存情况  
DBCC MEMORYSTATUS; --旧的方法  
  
SELECT  [type]     
,SUM(virtual_memory_reserved_kb) AS [vm reserved]    
,SUM(virtual_memory_committed_kb) AS [vm commited]    
,SUM(awe_allocated_kb) AS [awe allocated]      
,SUM(shared_memory_reserved_kb) AS [sm reserved]    
,SUM(shared_memory_committed_kb) AS [sm committed]    
,SUM(single_pages_kb) AS [Stolen in Buffer Pool]    
,SUM(multi_pages_kb) AS [MemToLeave]    
,SUM(single_pages_kb) + SUM(multi_pages_kb) AS [Stolen]    
,SUM(virtual_memory_committed_kb) + SUM(multi_pages_kb) AS [Buffer Pool]    
FROM sys.dm_os_memory_clerks   
GROUP BY [type]    
ORDER BY [type] desc  
  
  
--当前各数据库buffer pool的分配情况  
SELECT   
CASE database_id   
    WHEN 32767 THEN 'ResourceDb'   
    ELSE db_name(database_id) END AS Database_name  
,count(*) AS cached_pages_count  
,count(*)*8/1024 AS cached_space_in_mb  
,sum(convert(bigint,free_space_in_bytes))/1024/1024 AS free_space_in_mb  
FROM sys.dm_os_buffer_descriptors(nolock)  
GROUP BY db_name(database_id) ,database_id  
ORDER BY cached_pages_count DESC;  
  
  
--当前数据库各表buffer pool的分配情况  
SELECT top(20) name ,index_id ,count(*)AS cached_pages_count   
,count(*)*8/1024 AS cached_space_in_mb   
FROM sys.dm_os_buffer_descriptors AS bd   
INNER JOIN (  
    SELECT object_name(object_id) AS name ,index_id ,allocation_unit_id  
    FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p   
    ON au.container_id = p.hobt_id  AND (au.type = 1 OR au.type = 3 )   
    UNION ALL  
    SELECT object_name(object_id) AS name,index_id, allocation_unit_id  
    FROM sys.allocation_units AS au INNER JOIN sys.partitions AS p   
    ON au.container_id = p.hobt_id  AND au.type = 2  
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id  
WHERE database_id = db_id() AND obj.name NOT LIKE 'sys%'  
GROUP BY name, index_id   
ORDER BY cached_pages_count DESC  
  
  
-- 当前各表或索引在缓冲池中的页数量及大小  
SELECT obj.name ,obj.index_id,i.name ,count(*)AS cached_pages_count ,count(*)*8/1024 AS cached_space_in_mb  
FROM sys.dm_os_buffer_descriptors AS bd   
INNER JOIN (  
    SELECT object_name(object_id) AS name,object_id,index_id ,allocation_unit_id  
    FROM sys.allocation_units AS au  
    INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id AND (au.type = 1 OR au.type = 3)  
    UNION ALL  
    SELECT object_name(object_id) AS name,object_id,index_id, allocation_unit_id  
    FROM sys.allocation_units AS au  
    INNER JOIN sys.partitions AS p   ON au.container_id = p.partition_id AND au.type = 2  
) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id  
LEFT JOIN sys.indexes i(nolock) on obj.object_id = i.object_id AND obj.index_id = i.index_id    
WHERE database_id = db_id() AND obj.name NOT LIKE 'sys%'  
GROUP BY obj.name ,obj.index_id,i.name  
ORDER BY cached_pages_count DESC;  
  
-- 统计当前内存分配情况  
select   
physical_memory_in_bytes / 1024 / 1024 as physical_memory_mb,  
virtual_memory_in_bytes / 1024 / 1024 as virtual_memory_mb,  
bpool_committed * 8 / 1024 as bpool_committed_mb,  
bpool_commit_target * 8 / 1024 as bpool_target_mb,  
bpool_visible * 8 / 1024 as bpool_visible_mb  
from sys.dm_os_sys_info  
  
  
--当前内存脏页数量及大小  
SELECT db_name(database_id) AS 'Database'  
,count(page_id) AS 'Dirty Pages'  
,count(page_id)*8/1024 AS 'Dirty Pages(MB)'  
FROM sys.dm_os_buffer_descriptors(nolock)  
WHERE is_modified =1  
GROUP BY db_name(database_id)  
ORDER BY 'Dirty Pages' DESC  
  
  
--缓存类型数量大小  
select cacheobjtype as [Cached Type]  
,COUNT(*) [Number of Plans]   
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]  
from sys.dm_exec_cached_plans   
group by cacheobjtype   
order by [Plan Cache Size(MB)] desc  
  
  
--缓存对象数量大小  
select objtype as [Cached Object Type]  
,COUNT(*) as [Number of Plans]   
,SUM(CONVERT(BIGINT,size_in_bytes))/1024/1024 [Plan Cache Size(MB)]  
,avg(usecounts) [Avg Use Count]   
from sys.dm_exec_cached_plans   
group by objtype   
order by [Plan Cache Size(MB)] desc  
  
--内存对象  
SELECT top 10 * FROM sys.dm_os_memory_cache_counters(nolock)  
ORDER BY entries_count DESC;  
  
SELECT * FROM sys.dm_os_memory_clerks a  
WHERE name='TokenAndPermUserStore'  
  
SELECT * FROM sys.dm_os_memory_cache_entries(nolock)  
WHERE [type]='USERSTORE_TOKENPERM' and name='TokenAndPermUserStore'  
  
  
--缓存分析对象  
SELECT * FROM sys.syscacheobjects   
SELECT * FROM sys.dm_exec_cached_plans  
  
  
--即时查询优化(运行即生效)  
EXEC sp_configure 'show advanced options',1  
RECONFIGURE  
EXEC sp_configure 'optimize for ad hoc workloads',1  
RECONFIGURE

View Code

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Easter79 Easter79
3年前
swap空间的增减方法
(1)增大swap空间去激活swap交换区:swapoff v /dev/vg00/lvswap扩展交换lv:lvextend L 10G /dev/vg00/lvswap重新生成swap交换区:mkswap /dev/vg00/lvswap激活新生成的交换区:swapon v /dev/vg00/lvswap
待兔 待兔
4个月前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Stella981 Stella981
3年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
3年前
Java获得今日零时零分零秒的时间(Date型)
publicDatezeroTime()throwsParseException{    DatetimenewDate();    SimpleDateFormatsimpnewSimpleDateFormat("yyyyMMdd00:00:00");    SimpleDateFormatsimp2newS
Stella981 Stella981
3年前
SpringBoot整合Redis乱码原因及解决方案
问题描述:springboot使用springdataredis存储数据时乱码rediskey/value出现\\xAC\\xED\\x00\\x05t\\x00\\x05问题分析:查看RedisTemplate类!(https://oscimg.oschina.net/oscnet/0a85565fa
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Easter79 Easter79
3年前
SpringBoot整合Redis乱码原因及解决方案
问题描述:springboot使用springdataredis存储数据时乱码rediskey/value出现\\xAC\\xED\\x00\\x05t\\x00\\x05问题分析:查看RedisTemplate类!(https://oscimg.oschina.net/oscnet/0a85565fa
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这
Easter79
Easter79
Lv1
今生可爱与温柔,每一样都不能少。
文章
2.8k
粉丝
5
获赞
1.2k