这篇介绍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 内存管理
