sql server内存使用情况
转自:https://www.cnblogs.com/wanghao4023030/p/8299478.html
【1】基本查看
该部分转自:https://blog.csdn.net/smartsmile2012/article/details/80950899
(1.1)内存状态查看
DBCC MemoryStatus 查看内存状态
清理缓存、清除缓存
DBCC FREEPROCCACHE 清除存储过程相关的缓存 DBCC FREESESSIONCACHE 会话缓存 DBCC FREESYSTEMCACHE('All') 系统缓存 DBCC DROPCLEANBUFFERS 所有缓存
需要立刻释放数据库占用内存~(如果业务在跑的情况下,可能会导致数据丢失,如果业务在跑应该checkpoint再去做如下操作,或者直接设定最大值等sql server自动调节慢慢降下来)
--清除存储过程缓存 DBCC FREEPROCCACHE --清除会话缓存 DBCC FREESESSIONCACHE --清除系统缓存 DBCC FREESYSTEMCACHE('All') --清除所有缓存 DBCC DROPCLEANBUFFERS GO --打开高级配置 exec sp_configure'show advanced options', 1 GO --设置最大内存MB 2G exec sp_configure 'max server memory', 2048*15 EXEC ('RECONFIGURE' ) --设置执行时间 WAITFOR DELAY '00:00:10' --设置最大内存MB 20G EXEC sp_configure 'max server memory', 20480 * 3 EXEC ('RECONFIGURE' ) GO --关闭高级配置 exec sp_configure'show advanced options',0
2005
select *,CAST(cntr_value/1024.0 as decimal(20,1)) MemoryMB from master.sys.sysperfinfo where counter_name='Total Server Memory (KB)'
2008+
SELECT physical_memory_in_use_kb/1024 AS physical_memory_in_use_MB,* FROM sys.dm_os_process_memory
-- 查询SqlServer总体的内存使用情况 select type , 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、2008 --, sum(multi_pages_kb) --SQL2005、2008 from sys.dm_os_memory_clerks group by type order by type -- 查询当前数据库缓存的所有数据页面,哪些数据表,缓存的数据页面数量 -- 从这些信息可以看出,系统经常要访问的都是哪些表,有多大? select p.object_id, object_name=object_name(p.object_id), p.index_id, buffer_pages=count(*) from sys.allocation_units a, sys.dm_os_buffer_descriptors b, sys.partitions p where a.allocation_unit_id=b.allocation_unit_id and a.container_id=p.hobt_id and b.database_id=db_id() group by p.object_id,p.index_id order by buffer_pages desc -- 查询缓存的各类执行计划,及分别占了多少内存 -- 可以对比动态查询与参数化SQL(预定义语句)的缓存量 select cacheobjtype , objtype , sum(cast(size_in_bytes as bigint))/1024 as size_in_kb , count(bucketid) as cache_count from sys.dm_exec_cached_plans group by cacheobjtype, objtype order by cacheobjtype, objtype -- 查询缓存中具体的执行计划,及对应的SQL -- 将此结果按照数据表或SQL进行统计,可以作为基线,调整索引时考虑 -- 查询结果会很大,注意将结果集输出到表或文件中 SELECT usecounts , refcounts , size_in_bytes , cacheobjtype , objtype , TEXT FROM sys.dm_exec_cached_plans cp CROSS APPLY sys.dm_exec_sql_text(plan_handle) ORDER BY objtype DESC ; GO
原文:https://www.cnblogs.com/zhaoguan_wang/p/4602866.html
其他一些有帮助的语句:
1. 查看SQL语句占用多大内存:
SELECT s2.dbid, s1.sql_handle, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement, execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes FROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid is null ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset;