SQL ServerSQL Server内部的内存管理

  • @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
    — 因地方拿到下的@object_SQL Server,id找来是孰数据库的哪位表
    SELECT s.name AS table_schema, o.name as table_name
    –以的即便是table_schema.table_name表
    FROM sys.sysobjects AS o INNER JOIN
    sys.schemas AS s ON o.uid = s.schema_id
    WHERE (o.id = @object_id)
    — 因地方拿到下的@object_id和@index_id找有索引的名
    SELECT id, indid, name as index_name — index_name就是索引的号
    FROM sys.sysindexes

    WHERE (id = @object_id) AND (indid = @index_id)

    因地点拿到下的表名table_schema.table_name和目录的名号index_name,还好找寻来该索引是建以如何字段上的
    EXEC sp_helpindex ‘table_schema.table_name’
    翻内存中缓存的实践计划,以及实施计划对应之口舌:
    — 输出可能比生,请小心用
    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

1、内存以分类

以用途分类
1)Database cache(数据页面)。SQL
Server中之页面都是以8KB为一个页面存储的。当SQL
Server需要接纳某个页面时,它汇合以拖欠页面读到外存中,使用完后谋面休息存在内存中。在内存没有压力之情下,SQL
Server不会将页面从外存中删除。若是SQL
Server感觉到内存的下压力时,会将最丰盛日子没使的页面从内存中删除来空来内存。
2)各类Consumer(功能组件
Connection的连年音讯
General:一组大杂烩。语句的编译、范式化、每个锁数据结构、事务上下文、表格和目录的头版数据等
Query
Plan:语句和储存过程的履行计划。和Database
cache类似,SQL Server也会用尽计划缓存以供应未来接纳,缩小编译时间。
Optimizer:生成执行计划之经过中吃的内存。
Utilities:像BCP、Log Manager、Backup等相比较相当的操作消耗的内存。
3)线程内存:存放进程内每个线程的数据结构和相关信息消耗的内存,每个线程需0.5MB的内存。
4)第三正代码消耗的内存:SQL Server的进程里,会运作有非SQL
Server自身的代码。例如:用户定义之CLR或Extended Stored
PRocedure代码。
听从申请法分类
1)预先Reserve一片很的内存,然后于行使的时刻同块一样块的Commit。Database
Page是据这种方法报名之。
2)直接用Commit形式报名之内存,成为Stolen格局。除了Database
Page之外其他内存基本仍旧比照那种方法报名之。
依据申请内存的大小分类
1)申请小于等于8KB为一个单位之内存,这多少个内存称为Buffer Pool

SQL Server对于Database
cache都是使先Reserved后Commit的主意报名之,而数据页都是因8KB为单位展开申请的。
对于Consumer中的内存申请,一般都是按照Stolen情势报名的,且多数底实践计划之轻重都是自愧不如8KB的,少数专门复杂的仓储过程的行计划会超过8KB,默认的连年的数据包是4KB,除非客户端特别设置了跨8KB(不指出)
老三正值代码的内存申请一般是依据Stolen模式报名的,个别比如CLR中或者会面用Reserved/Commit的方报名。
线程的内存每个都坐0.5MB的计报名,自然是身处MemToLeave中。
于是花了这么可怜篇幅来讲SQL Server的内存分类,是为SQL
Server尤其是32位之SQL
Server对两样门类之内存的申请大小是休相同的,对Commit、Stolen和MemTOLeave等类的内存是有限定的。因而会合起系统中还有空闲内存,可是SQL
Server不会申请采取的景观。
2、SQL Server内存使用情形的解析
貌似的话出半点种植办法,第一种就是用来分析连串内存情形常采取的之所以性计数器来分析,第二栽是应用动态管理视图(DMV,只适用于SQL
Server2005和2008)
1)SQL Server性能计数器
SQLServer:Memory Manager:Total Server Memory(KB):SQL
Server缓冲区提交的内存。不是SQL Server总的施用内存,只是Buffer
Pool中之大小。
SQLServer:Memory Manager:Target Server Memory(KB):服务器可供应SQL
Server使用的外存量。一般是由SQL Server能访问到的内存量及SQL
Server的sp_Configure配置中的马克斯 Server Memory值中之较小值算得。
SQLServer:Memory Manger:Memory 格兰特(Grant)s
Pending:等待内存授权的进程总数。固然该值未为0,表明当前发出用户的内存申请由内存压力为延缓,这意味着相比严重的内存瓶颈。
SQLServer:Buffer Manager:Buffer Cache Hit
Ratio:数据从缓冲区中找到而休需打硬盘中去取的比重。SQL
Server在运转一段时间后,该比率的变动应该特别有些,而且还该在98%上述,固然以95%之下,表达有内存不足的题材。
SQLServer:Buffer Manager:Lazy Writes/Sec:每分钟为惰性编辑器(Lazy
writer)写副的休养冲数。当SQL
Server感觉到内存压力的时候,会以最久没有应用的多少页面及履行计划由缓冲池中清理掉,做此动作之即便是Lazy
Writer。
SQLServer:Buffer Manager:Page Life
Expectancy:页面不吃引用后,在缓冲池中滞留的秒数。在内存没有压力的景象下,页面会一直用在缓冲池中,Page
Life Expectancy会维持在一个于高的价,倘若暴发内存压力时,Page Life
Expectancy会下降。所以只要Page Life
Expectancy不可能保障在一个价值达到,就意味着SQLServer有外存瓶颈。
SQLServer:Buffer Manager:Database Pages :就是Database Cache的大小。
SQLServer:Buffer Manager:Free Pages:SQL Server中空闲可用的轻重缓急。
SQLServer:Buffer Manager:Stolen Pages:Buffer Pool中Stolen的大小。
SQLServer:Buffer Manager:Total Pages: Buffer
Pool的究竟大小(等于Database Pages+Free Pages+Stolen
Pages)。该值乘以8KB,应该等于Memory Manager:Total Server
Memory的价值。
从今地点这么些计数器中我们便会了解SQL
Server的内存以意况,结合前边说之系统层的计数器大概会望是否是内存瓶颈。
2)内存动态管理视图
每当SQL Server 2005过后,SQL Server的内存管理是使Memory
Clerk的方法统一管理。所有的SQL
Server的内存的报名仍然放,都要经她的Clerk,SQL
Server也透过这些Clerk的调和来满意不同需求。通过查询这个DMV,可以收获比用性能计数器更加详实的内存以境况。
咱俩可因而下的查询语句来检测SQL Server的Clerk的内存以情状。
使用sys.dm_os_memory_clerks查看内存以情状
SELECT type, –Clerk的类型
sum(virtual_memory_reserved_kb) as vm_Reserved_kb, — 保留的内存
sum(virtual_memory_committed_kb) as vm_Committed_kb, –提交的内存
sum(awe_allocated_kb) as awe_Allocated_kb, — 开启AWE后使的内存
sum(shared_memory_reserved_kb) as sm_Reserved_kb, —
共享的保留内存
sum(shared_memory_committed_kb) as sm_Committed_kb, —
共享的交由内存
sum(single_pages_kb) as SinlgePage_kb, –Buffer
Pool中的Stolen的内存
sum(multi_pages_kb) as Multipage_kb —
MemToLeave的内存
FROM sys.dm_os_memory_clerks
GROUP BY type
ORDER BY type
打上边的查询语句,大家得算出前边提到的内存大小
Reserved/Commit = sum(virtual_memory_reserved_kb) /
sum(virtual_memory_committed_kb)
Stolen = sum(single_pages_kb) + sum(multi_pages_kb)
Buffer Pool = sum(virtual_memory_committed_kb) +
sum(single_pages_kb)
MemToLeave = sum(multi_pages_kb)
通过地点的介绍大家可以领会SQL
Server总体与各有内存的使用情形,假诺本身怀想清楚数据页的苏醒存着到底缓存了怎么着数据,这多少个数据是属哪个数据库的哪位表中的也罢?执行计划而是缓存了哪些话的行计划也?这为足以通过DMV查看的及。
翻开内存中的数额页面缓存的是何许人也数据库的哪位表格的多寡
declare @name nvarchar(100)
declare @cmd nvarchar(1000)
declare dbnames cursor for
select name from master.dbo.sysdatabases
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 ‘
–这里的object_id代表是SQL
Server中的靶子号,index_id代表是索引号,buffer_count代表的是页面数

2)申请过8KB为一个单位之内存,那些内存称为Multi-Page(或MemToLeave)

相关文章