这篇介绍Stolen内存相关的主要三种等待类型以及对应的waittype编号,CMEMTHREAD(0x00B9),SOS_RESERVEDMEMBLOCKLIST(0x007B),RESOURCE_SEMAPHORE_QUERY_COMPILE(0x011A)。也可以通过sysprocesses里查看连接处于某个等待状态, waittype!=0x0000。
select * from sys.sysprocesses where waittype!=0x0000 and spid>50
再次看下Stolen内存的分配场景:
1. CMEMTHREAD内存
cmemthread是指多个用户同时往同一块缓存里申请或释放内存时,在一个时间点上, 只有一个连接可以做申请或释放内存动作, 其他连接必须等待。原因:出现这种等待的原因通常是发生在并发度非常高的sqlserver里,而这些并发的连接,在大量地使用需要每次都做编译的动态t-sql语句。 解决:修改客户连接行为,尽可能更多地使用存储过程, 或者使用参数化的t-sql语句,减少语句编译量增加执行计划的重用,避免大量连接同时申请内存做语句编译的现象。
在生产环境下cmemthread平均每次请求时间为0.20ms(1570876.0/7825922.0=0.20)
2.SOS_RESERVEDMEMBLOCKLIST
sos_reservedmemblocklist是指当用户要申请MemtoLeave这块内存时而暂时不能满足就会出现等待。原因:当用户发过来的语句内含有大量参数,或者有一个in 子句,它的执行计划在8kb的singlepage里可能放不下,需要用multi-page来存储。当缓存的执行计划越来越多,multi-page里的内存也会越来越多。 解决:(1)避免使用带有大量参数或者长in子句的语句,这种语句需要消耗比正常语句更多的内存及cpu资源, 改变的方法是可以把参数值存储到临时表,用join来连接。(2)定期运行dbcc freeproccache 语句,手工清除缓存中的执行计划,缓存内存压力。
-- 查看缓存占用空间
SELECT SUM(CONVERT(DECIMAL(18,4),size_in_bytes))/1024.0/1024.0 AS 'sizeMB'
FROM sys.dm_exec_cached_plans
--查看缓存中的对象类型,重用次数,sql语句,缓存空间大小,可以根据几个维度来统计
SELECT usecounts,size_in_bytes/1024.0 AS 'sizeKB',cacheobjtype,objtype,[text]
FROM sys.dm_exec_cached_plans
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
WHERE usecounts > 1
ORDER BY usecounts DESC
3.RESOURCE_SEMAPHORE_QUERY_COMPILE
resource_semaphore_query_compile是指:当编译的语句需要的内存达到了sqlserver的编译内存上限时(sqlserver会为编译内存设置一个上限),其它语句将进入等待状态,等前面的语句编译完成,把内存释放出来以后,后面的语句才能继续编译。解决(1)修改客户连接行为,尽可能更多地使用存储过程, 或者使用参数化的t-sql语句,减少语句编译量,增加执行计划的重用,避免大量连接同时申请内存做语句编译的现象.(2)简化每次需要编译语句的复杂度,降低编译需要的内存量。(3)当stolen 内存使用总量比较大的时候,也可以定期执行dbcc freeproccache 。
总结:以上三种等待类型,当缓存的执行计划越来越多,存放buffer pool里的stolen内存在不断增长,当需要的内存超过8kb时,multi-page里的存储执行计划stolen内存也会越来越多 。能过sys.sysprocess.waittype字段,可以检查stolen内存上是否有瓶颈。通过sql server 内存初探 知道 sql server里的Consumer下的功能组件,第三方代码,线程都是能过stolen方式直接提交,并不需要先申请内存。
查看内存使用情况
-- 按申请方式统计内存 (Reserve 再commit)(直接commit叫Stolen)
SELECT
SUM(virtual_memory_reserved_kb)/1024.0 AS 'reserved(MB)',
SUM(virtual_memory_committed_kb)/1024.0 AS 'committed(MB)',
(SUM(single_pages_kb)+SUM(multi_pages_kb))/1024.0 AS 'Stolen(MB)'
FROM sys.dm_os_memory_clerks
-- 按申请内存页大小统计内存
SELECT
(SUM(virtual_memory_committed_kb)+SUM(single_pages_kb))/1024.0 AS 'Buffer Pool(MB)',
SUM(multi_pages_kb)/1024.0 AS 'MemToLeave(MB)'
FROM sys.dm_os_memory_clerks
按申请方式统计内存,共申请了92576MB,提交了83621MB, 在Stolen中有9244MB。 如下图所示:
按申请内存页大小(<=8kb
>8kb)统计内存:
windows memory: Memory: Cache Bytes 是系统的working set, 也就是系统使用的物理内存数目。 可以观察Windows用了多少物理内存。
1. System Cache Resident Bytes
2. System Driver Resident Bytes
3. System Code Resident Bytes
4. Pool Paged Resident Bytes
SQL Server 动态管理内存:
SQL Server 是通过以下的API去感知windows是否有内存压力:
APIQueryMemoryResourceNotification -》 windows memory -》 decrease target server memory.
Total server memory : SQL Server启动账户拥有 Lock pages in memory 权限, 锁定内存,避免windows抢夺内存
Target server memory: SQL在启动时候, 比较AWE, max server memory, physical memory 三者选一个最小的值作为Target server memory.
comparison |
Result |
Remark |
Total < target |
Windows has enough memory, SQL can allocate new memory for new data |
Total is increasing |
Total = target |
SQL used all of memory, SQL don’t allocate new memory for new data |
SQL clean up memory for new data, such as Lazy writer |
Total > target |
Windows has memory pressure, SQL decrease Target |
SQL clean up memory for new data, such as Lazy writer to release buffer pool and cached plan |
SQL server 内存管理概念: DBCC memorystatus -- 查看内存使用情况
Reserved memory:
Committed memory: = Physical memory + Page file = Shared memory + Private Bytes
Shared memory:
Private Bytes:
Working Set: = shared memory + Private Bytes - Page file
Page Fault(soft/hard)
32位SQL:
memToLeave: 256MB+256thread*521KB=384MB (SQL 启动的时候就计算好了,不能变大): extended stored procedure, third party dirver, and linked server : 启动参数 -g512 = 512MB+256thread*521KB
BufferPool = 2G-384MB=1664MB (包括 database cache + stolen memory)
AWE开启以后, 只能给先reserve再commit的部分使用,即只能给database cache来扩展使用(Physical memory-2GB就是AWE扩展后database cache所能用的内存数量),stolen memory只能用1664MB里的内存。
SQL内存使用情况分析:
memory manager:
Total server memory,
target server memory,
Optimizer memory
SQL cache memory
Lock memory
Connection memory
Granted workspace memory: hash, sort, bulk-insert, create index...
Memory grants pending: 等待工作空间内存授权的进程总数, 不等于0, 意味着比较严重的内存瓶颈
Buffer manager:
Buffer Cache Hit Ratio: 应该>99%, 如果<95%, 通常就是内存不足的问题
Checkpoint pages/sec: 这个和内存压力没有关系,和用户的行为有关。用户做很多insert/update/delete, 这个值就会很大,脏数据多。
database pages: 就是 database cache
free pages:
Lazy writes/sec: 如果SQL 内存压力不大,不会经常触发lazy writer。 如果经常触发, 那么就应该是有内存的瓶颈
page life expectancy: 页不被引用,将在缓冲池中停留的秒数。只有Lazy writer 被触发, Page life expectancy 才会突然下降。如果总是高高低低的, 应该是有内存压力
page reads/sec: 这个值正常情况下应该很低。 如果比较高, 一般page life expectancy 会下降, Lazy writes/sec 会上升。
stolen pages: 所有非database pages, 包括执行计划缓存。
内存DMV:sys.dm_os_memory_clerk
select type,
sum(virtual_memory_reserved_kb) as [vm reserved],
sum(virtual_memory_committed_kb) as [vm committed],
sum(awe_allocated_kb) as [AWE allocated],
sum(shared_memory_reserved_kb) as [SM reserved],
sum(shared_memory_committed_kb) as [SM committed],
sum(multi_pages_kb) as [Multipage allocator],
sum(single_pages_kb) as [Singlepage allocator]
from sys.dm_os_memory_clerks
group by type
order by type
内存中的数据页面由哪些表格组成,各占多少: sys.dm_os_buffer_descriptors
dbcc dropcleanbuffers --clean up data buffer
go
declare @name varchar(100)
declare @cmd varchar(5000)
declare c1 cursor for select name from sys.sysdatabases
open c1
fetch next from c1 into @name
while @@FETCH_STATUS=0
begin
print @name
set @cmd= 'select b.database_id, db=db_name(b.database_id), object_name(p.object_id), p.index_id, buffer_count_kb=count(*)*8 from ' +@name+'.sys.allocation_units a, '+@name+'.sys.dm_os_buffer_descriptors b,'+@name +'.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('''+@name+''')' +' group by b.database_id, p.object_id, p.index_id ' +' order by b.database_id, buffer_count_kb desc '
print @cmd
exec(@cmd)
fetch next from c1 into @name
end
close c1
本文由美高梅官方网站发布于数据统计,转载请注明出处:美高梅官方网站SQL Server 内存管理