内部存款和储蓄器管理,品质调优

2019-08-08 01:39栏目:计算机资讯

一.概述

  这篇介绍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

1. CMEMTHREAD内存 

  cmemthread是指五个用户同期往同一块缓存里申请或自由内部存款和储蓄器时,在叁个时日点上, 独有多少个连连能够做申请或自由内部存款和储蓄器动作, 别的连接必须等待。原因:出现这种等待的因由常常是发出在并发度非常高的sqlserver里,而这几个出现的接连,在大方地应用须要每回都做编写翻译的动态t-sql语句。 消除:修改客户连接行为,尽或然更加多地选用存款和储蓄进程, 或然选拔参数化的t-sql语句,减弱语句编写翻译量扩展推行陈设的选定,幸免大批量总是同有的时候间提请内存做语句编写翻译的景况。

图片 2

    在生育情状下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 语句,手工业清除缓存中的实行铺排,缓存内部存款和储蓄器压力。

图片 3

-- 查看缓存占用空间
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

  图片 4图片 5

3.RESOURCE_SEMAPHORE_QUERY_COMPILE

   resource_semaphore_query_compile是指:当编写翻译的话语供给的内部存款和储蓄器达到了sqlserver的编写翻译内部存款和储蓄器上有效期(sqlserver会为编写翻译内部存款和储蓄器设置叁个上限),其余语句将踏入等待状态,等后面包车型客车言语编写翻译完毕,把内部存款和储蓄器释放出来今后,前面的言辞能力接二连三编译。解决(1)修改客户连接行为,尽恐怕更加多地运用存款和储蓄进程, 大概应用参数化的t-sql语句,减弱语句编写翻译量,扩充试行布置的任用,制止大批量总是同一时候申请内部存款和储蓄器做语句编写翻译的现象.(2)简化每趟需求编写翻译语句的复杂度,减弱编译要求的内部存储器量。(3)当stolen 内部存款和储蓄器使用总数异常的大的时候,也得以定时施行dbcc freeproccache 。

图片 6

计算:以上二种等待类型,当缓存的施行计划更加多,寄存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。 如下图所示:

图片 7  按申请内部存款和储蓄器页大小(<=8kb  >8kb)总计内部存款和储蓄器:
  图片 8

图片 9

 

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 -- 查看内部存储器使用境况

  1. Reserved memory: 

  2. Committed memory: = Physical memory Page file = Shared memory Private Bytes

  3. Shared memory:

  4. Private Bytes:

  5. Working Set: = shared memory Private Bytes - Page file

  6. 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内部存款和储蓄器使用状态深入分析:

  1. 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

deallocate c1

go

 

进行安顿都缓存了些什么? 哪些比较占内部存储器? 

  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

 

  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

 

-----------------华丽的分界线: 上边开端分析 数据页面(database page),  buffer pool 里的stolen, multi-page 那三部分------------------------------------

数据缓冲区压力分析:

  Lazy writes/sec 高

  Page life expectancy 低

  Page reads/sec 高: 符合规律那个值应该临近0, 指从数据文件读取的数据量

  Stolen pages 降低

  sys.sysprocesses 中冒出有的再而三等待I/O达成的风貌: PAGEIOLATCH_SH

 

  使用DMV剖判运营以来做read最多的话语:

  --根据物理读的页面数排序,前50名:

  select top 50

  qs.total_physical_reads, qs.execution_count,

  qs.total_physical_reads / qs.execution_count as [avg IO],

  substring(qt.text, qs.statement_start_offset/2,

    (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 

      else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text,

  qt.dbid, dbname=db_name(qt.dbid),

  qt.objectid, qs.sql_handle, qs.plan_handle

  from sys.dm_exec_query_stats qs

  cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

  order by qs.total_physical_reads desc

 

  --依照逻辑读的页面数排序,前50名

  select top 50

  qs.total_logical_reads, qs.execution_count,

  qs.total_logical_reads / qs.execution_count as [avg IO],

  substring(qt.text, qs.statement_start_offset/2,

    (case when qs.statement_end_offset = -1 then len(convert(nvarchar(max), qt.text)) * 2 

      else qs.statement_end_offset end - qs.statement_start_offset)/2) as query_text,

  qt.dbid, dbname=db_name(qt.dbid),

  qt.objectid, qs.sql_handle, qs.plan_handle

  from sys.dm_exec_query_stats qs

  cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt

  order by qs.total_logical_reads desc

 

  --使用trace 文件分析做read最多的口舌

  select * into sample

  from fn_trace_gettable('c:sample.trc',default)

  where eventclass in (10, 12)  -- 10: RPC completed    12: SQL batch completed

 

  select top 1000 textdata, databaseid, hostname, applicationname, loginname, spid

    starttime, endtime,duration,reads,writes,cpu

  from sample

  order by reads desc

 

  --使用ReadTrace工具分析trace文件,寻找利用大量系统能源的话语

  使用格局见稍后产生。。。

 

Stolen Memory缓存压力剖析:

  Stolen memory 凡是以8K为分配单位的,保存在buffer pool里。 大于8K的,保存在MemToLeave里。 

  缓存: 试行安插,用户安全上下文,连接的数据结商谈输入/输出缓冲区

  未有缓存: 语义分析,优化,排序,Hash,总结

 

  史托尔en memory 在不一致的SQL版本,最大的限量是例外的,见下表:

  图片 10

 

  表征与消除措施:查看 sys.sysprocesses 里面包车型大巴接连等待waittype字段不等于0x0000: (能够手动 DBCC FREEPROCCACHE)

  1. CMEMTHREAD (0x00B9)

  2. SOS_RESERVEDMEMBLOCKLIST (0x007B)

  3. RESOURCE_SEMAPHORE_QUERY_COMPILE(0x011A)

 

版权声明:本文由威尼斯网站发布于计算机资讯,转载请注明出处:内部存款和储蓄器管理,品质调优