您的位置:澳门新葡8455最新网站 > 数据库管理 > 天性调优,内部存款和储蓄器管理

天性调优,内部存款和储蓄器管理

发布时间:2019-11-14 16:55编辑:数据库管理浏览(167)

    一.概述

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

     

      Stolen memory 在不同的SQL版本,最大的限制是不同的,见下表:

      图片 10

     

      表征与解决方法:查看 sys.sysprocesses 里面的连接等待waittype字段不等于0x0000: (可以手动 DBCC FREEPROCCACHE)

      1. CMEMTHREAD (0x00B9)

      2. SOS_RESERVEDMEMBLOCKLIST (0x007B)

      3. RESOURCE_SEMAPHORE_QUERY_COMPILE(0x011A)

     

    本文由澳门新葡8455最新网站发布于数据库管理,转载请注明出处:天性调优,内部存款和储蓄器管理

    关键词: