笔记31-徐 一些SQL内存使用的错误理解以及内存使用状况分析

笔记31-徐 一些SQL内存使用的错误理解以及内存使用状况分析

--一些SQL内存使用的错误理解以及内存使用状况分析

--1、Windows上还有很多物理内存没有被使用，就意味着SQL不缺内存
--SQL很可能设置了max server memory，约束了SQL继续申请内存的能力
--32位机器上，由于虚拟地址空间的限制，SQL可能已经无法继续申请内存了

--例如，一台4GB的32位服务器上，SQL最多只能使用2GB物理内存。一般Windows
--会使用500MB左右，这台机器可能就有1GB多的空闲物理内存。这部分是SQL不开AWE
--就用不到的

--2、SQL进程的private bytesH或working set在不停地向上涨，说明SQL有内存泄漏的问题
--对于申请内存，SQL有严格限制，在32位服务器上，不管是buffer pool还是memtoleave，都有一个
--使用最大上限。当Windows感觉到有内存压力的时候，SQL会自动地释放内存，所以作为一个进程
--SQL发生内存泄漏的机会是非常小的
--因为刚开始SQL只会commit他启动所需要的那部分内存，当使用越多数据缓存，会不断申请内存，
--Windows感到有内存压力。如果想限制SQL内存申请，设置max server memory就可以了

--3、max server memory的值，就是SQL内存使用量的最大值，超过这个值就不正常
--max server memory只是buffer pool的上限，不是SQL所有内存的上限。由于memtoleave一般
--只有384MB，远远小于buffer pool，所以控制好buffer pool就基本控制住了SQL的整体内存使用量

--4、SQL内存使用总量就是性能监视器里的SQLSERVER：memory manager-total server memory的值
--性能监视器里收集的数据都是SQL自己收集的。从SQL2005以后，SQL整合了所有的内存申请，让他们
--使用同一接口。所以SQL对自己申请的内存数量是了如指掌的。但是问题是SQL进程里运行的代码不都是
--SQL自己的代码。对第三方代码，SQL是不知道他们申请了多少内存的
--如果SQL开启了AWE，Windows就没有办法正确判断出一个使用了AWE内存的进程究竟用了多少内存




--5、当系统有内存压力的时候，SQL总是会自动释放内存
--如果使用了lock page in memory ,就不会释放内存，这时候最好设置一下max server memory


--6、SQL有办法将自己的内存绑定在物理内存里
--企业版的SQL能通过lock page in memory锁定内存，但是用户态应用程序受限于核心态，如果核心态发出
--内存请求，比如Windows和一些驱动程序，SQL就会被逼把内存释放出来



--7、增加memtoleave的大小可以提高SQL的性能
--在32位SQLSERVER上，memtoleave的大小是384MB，如果memtoleave使用完了，那么一些重要功能不能使用
--甚至连接都建立不起来。例如linked server分布式查询，CLR 扩展存储过程，要增加memtoleave使用
---g 启动参数，如果想把memtoleave设成512MB，可以这样

--   /g 512


--  -g  memory_to_reserve
--指定内存的兆字节整数，该内存被保留下来用于SQLServer2000内部（进程内）运行的其它应用程序。


--但是要注意，SQL只有2GB虚拟地址空间，memtoleave多，那么buffer pool就会变少


--8、增加物理内存一定会提高SQL的性能
--（1）检查内存瓶颈：database cache  \stolen  \buffer pool    \memtoleave 
--（2）SQL是否用得上，最好升级到64位操作系统
--（3）如果database cache没有压力，SQL会缓存太多的TSQL执行计划，反而会增加SQL的维护成本
--只有在database cache缺内存的时候，增加内存对性能才有帮助。
--如果是小型数据库，常用数据页面已经缓存在内存里，增加内存对性能不会有太大帮助





-----------------------SQL内存使用状况分析------------------------------------
--DBA可以从三个方面分析SQL内存：
--（1）性能监视器
--（2）SQL内存相关计数器
--（3）sys.dm_os_memory_clerks动态管理视图  clerk：店员
--在SQL2005以后，SQL内存管理作出了调整，使用memory clerk的方式统一管理SQL内存的分配和回收

--所有SQL代码要申请或释放内存都需要通过他们的clerk。SQL通过这些clerk之间的协调，满足不同
--形式的需求。某一些clerk使用得多，SQL会通知其他clerk，请他们释放一部分内存，让出空间来
--给新内存的clerk。通过这种机制，SQL可以知道每个clerk使用了多少内存，从而能够知道自己
--总共用了多少内存。这些信息保存在动态管理视图里。

--有些信息是以前SQL版本无法知道的，所以在SQL2005以后，定期查询动态管理视图是一种比较好
--的监视手段

--动态管理视图为监视SQL的运行打开了一个新的大门


--sys.dm_os_memory_clerks：他返回实例中当前处于活动状态的全部内存clerk的集合
--从这个视图可以看到内存是怎麽被SQL使用掉的
--但是SQL进程里的第三方代码所申请的内存是不能被这个视图跟踪的。也就是说，从这个视图
--可以看到所有buffer pool（或者叫single page）的使用，以及memtoleave（或者叫multi page）
--里被SQL代码使用掉的内存。memtoleave里的另一部分内存（第三方代码）将不会被包含


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 [singlepage allocator],
        SUM(multi_pages_kb) AS [multi page allocated]
FROM    sys.dm_os_memory_clerks
GROUP BY type
ORDER BY type

--字段含义如下：
--type:memory clerk的名称，可以根据clerk的名字大致知道内存的用途

-- SUM(virtual_memory_reserved_kb)：内存clerk reserve的虚拟内存量。这是使用此clerk
--的组件直接保留的内存量。在多数情况下，只有使用buffer pool的memory clerk才会有这种机制

--  SUM(virtual_memory_committed_kb) ：相对于reserve内存，commit的虚拟内存量。
--这是clerk提交的内存量。提交的内存量应始终小于保留的内存量。这部分内存主要存储数据页

-- SUM(awe_allocated_kb)：内存clerk使用地址窗口扩展插件AWE分配的内存量。在SQL中，只有缓冲池
--clerk memoryclerk_sqlbufferpool使用此机制，而且仅在启用AWE后使用

-- SUM(shared_memory_reserved_kb)：内存clerk保留的共享内存量。保留以供共享内存和文件映射
--使用的内存量

-- SUM(shared_memory_committed_kb) ：内存clerk提交的内存量。这两个字段可以跟踪shared memory
--的大小。一般shared memory的值都很小

--SUM(multi_pages_kb)：分配的多页内存量（KB），此内存在缓冲池外面分配，也就是我们传统说的
--SQL自己的代码使用的memtoleave的大小


--SUM(single_pages_kb)：通过stolen分配的单页内存量（KB）。也就是说，是buffer pool里的stolen memory的大小


--reserved/Commit:SUM(virtual_memory_reserved_kb)+SUM(virtual_memory_committed_kb)

--stolen:SUM(single_pages_kb)+SUM(multi_pages_kb)

--buffer pool(single page):SUM(shared_memory_committed_kb)+SUM(single_pages_kb)

--memtoleave(multi page):SUM(multi_pages_kb)

--通过type字段从而知道各个memory clerk申请内存的数量从而知道什么样的行为用掉了内存



--type:MEMORYCLERK_SQLBUFFERPOOL:正常来讲这个clerk会是最大的，他的值基本就是buffer pool里
--database page的大小。很有意思的是这个clerk的multi page常不为0，意味着他会用一些multi-page内存
--所以说SQL内存管理是非常复杂的，内存分析的时候要抓住最大的内存使用者，对于一些小量的内存使用
--可以不用花太大注意力

--cachestore开头：对象缓存

--type:cachestore_objcp: 触发器、存储过程、函数的执行计划缓存


--type:cachestore_sqlcp:动态TSQL语句adhoc query、预编译TSQLprepared语句的执行计划缓存
--sqlcp:sys.dm_exec_cached_plans cp的缩写！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

--type:cachestore_phdr:视图缓存、用户自定义函数，帮助SQL更快生成执行计划

--type:cachestore_xproc:扩展存储过程缓存，例如：sp_executesql,sp_cursor*,sp_trace*等等

--type:cachestore_temptables:临时表对象，例如local temp table,global temp table,table variable等等

--type:cachestore_clrporc:sqlcrl过程缓存

--type:cachestore_events:存储service broker的事件和消息

--type:cachestore_cursors:存储所有的游标，包括local tsql cursors,global tsql cursor,api cursors

--USERSTORE开头：信息缓存 用户信息 数据库对象信息 运行上下文

--type:USERSTORE_TOKENPERM:保存所有用户的安全上下文以及各种安全相关的令牌token，当SQL要运行一句
--tsql语句的时候，他需要检查当前的用户是否能够访问这句话所涉及的所有对象，表格，视图等。有了这些
--令牌缓存，反复执行同一句话的时候，部分检查工作就不需要反复进行


--type:USERSTORE_SXC:暂时存放正在执行中的语句的参数。例如，客户调用存储过程，或者用sp_executesql
--调用动态TSQL语句时，需要带入过程参数。如果参数过长，这部分内存的使用量就会比较大


--memoryclerk开头  SQLSERVER功能组件consumer
--memoryclerk_sqlgeneral
--memoryclerk_sqlstoreng
--memoryclerk_sqlutilities
--memoryclerk_sqlclr
--memoryclerk_sqlmgr

--正常情况下，这些功能组件内存不会使用很多。在32位系统下，内存主要还是会用在数据页面缓存上

--在SQL2005之前，我们很难看到SQL到底缓存了哪些对象，例如，数据页面缓存，缓存了哪个数据库，
--哪个表，对于执行计划，是什么语句，随着DMV的功能开放，现在可以基本看到了


--内存中的数据页面由哪些表组成，各占多少
--sys.dm_os_buffer_descriptors,记录了SQL缓冲池中当前所有数据页的信息。根据数据库，对象，类型
--来确定缓冲池里数据库页的分布

--打印出当前内存里缓存的所有页面的统计信息
DECLARE @name NVARCHAR(100)
DECLARE @cmd  NVARCHAR(1000)
DECLARE dbnames CURSOR FOR SELECT name FROM  sys.databases
OPEN dbnames
FETCH NEXT FROM dbnames INTO @name
WHILE @@FETCH_STATUS=0
BEGIN
SET @cmd='select b.database_id,db=db_name(b.database_id),p.object_id,p.index_id,buffer_count=count(*) 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 desc'
EXEC(@cmd)
FETCH NEXT FROM dbnames INTO @name
END
CLOSE dbnames
DEALLOCATE dbnames
GO

--and b.database_id=db_id('''+@name+''') 如果你写成这样db_id('+@name+')
--那么编译成db_id(gposdb)
--db_id('''+@name+''') 编译成db_id('gposdb')
--第一个单引号引住前面哪些字符串，第二个单引号是转义


--例子中表明 对象2077305481的堆上(index_id=0)和1774629365的聚集索引上(index_id=1)分别
--缓存了15个页面和11个页面

--在sys.partition里，堆的index_id=0 聚集所以的index_id=1  非聚集索引的index_id>1
--hobt_id:包含此分区的行的数据堆或 B 树的 ID。

--运行上面的sql语句就知道3个问题
--1、一个应用经常要访问的数据到底有哪些表，有多大

--2、如果以间隔很短的时间运行上面的sql脚本，前后返回的结果有很大的差异，说明SQL刚刚
--为新的数据作了paging,SQL的缓存区有压力。而在后面那次运行出现的新数据，就是刚刚paging in
--进来的数据


--3、一条语句第一次执行前后各运行一次上面的脚本，就知道这句话要读入多少数据到内存里

--例如：
DBCC DROPCLEANBUFFERS
GO

--上面的SQL脚本

USE AdventureWorks
GO
SELECT * FROM Person.Address
go
--上面的脚本
GO




--执行计划
--SQL会为下面的对象缓存执行计划
-- proc             存储过程
--prepared          预定义语句
--adhoc             动态查询
--replproc          复杂筛选过程
--trigger           触发器
--view              视图
--default            默认值
--usrtab            用户表
--systab             系统表
--check              check约束
--rule               规则


--查询各种对象占了多少内存
SELECT objtype ,SUM(size_in_bytes) AS sum_size_in_bytes,COUNT(bucketid) AS cache_counts
FROM sys.dm_exec_cached_plans
GROUP BY objtype




--要分析具体存储了哪些对象,用下面语句,但是要把结果输出到文件里,不要输出到SSMS里,不然会引起资源争用
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

--SQL的内存按分配大小和申请方式主要分3块:数据页面(database page) \bufferpool里的stolen部分\multi-page部分