SQL SERVER 2016 内存 与 CPU 最佳配置 sql server多大内存

SQL SERVER内存按存放数据的类型，大概可以分为三类：

1、buffer pool，存放数据页面的缓冲区，sql server数据都是存放在一个个8K的页面里，当用户需要使用这个页面上的数据时，都是把整个页面加载到内存的buffer pool区缓存起来。

2、各类consumer:

      connect:SQL SERVER为每一个客户端连接分配一块内存，用来存储连接的信息，以及发过来的指令和缓存指令结果待待客户端取走

      无数据：表、存储过程、索引等的元数据

      锁：SQL SERVER中锁是稀有资源，会占用大量内存

      Query plan:缓存SQL的执行计划

      Optimizer:生成执行 计划过程中需要使用内存

3、线程内存：sql server会为每个线程分配0.5M的内存，用来存放线程的数据结构和相关信息

 

 

 下面的语句可以统计服务器实例的内存使用情况：

select type,                           

• SUM(single_pages_kb)/1024 single_pages_mb,                           
• 
  
• SUM(multi_pages_kb)/1024 multi_pages_mb,                            
• 
  
• SUM(virtual_memory_reserved_kb)/1024 virtual_memory_reserved_mb,                            
• 
  
• SUM(virtual_memory_committed_kb)/1024 virtual_memory_committed_mb,                            
• 
  
• sum(shared_memory_committed_kb)/1024 shared_memory_committed_mb                            
• 
  
• from sys.dm_os_memory_clerks                            
• 
  
• group by type                                               
• 
  
• order by 1;

 

结果如下：

 

CACHESTORE_OBJCP：存储过程、函数等的执行计划

CACHESTORE_SQLCP：SQL语句的执行计划(这里SQL语句的执行计划占用了多达6G的内存，是因为程序存在大量没有绑定变量的SQL语句，下次再做介绍)

MEMORYCLERK_SQLBUFFERPOOL：Buffer pool

OBJECTSTORE_LOCK_MANAGER：锁

 

下面再来看下BUFFER POOL中，都缓存了哪些表(当前数据库)的数据：

  select OBJECT_NAME(object_id) 表名,COUNT(*) 页数,COUNT(*)*8/1024.0 Mb     

from   sys.dm_os_buffer_descriptors a,sys.allocation_units b,sys.partitions c                              
where  a.allocation_unit_id=b.allocation_unit_id   
and b.container_id=c.hobt_id             
 and database_id=DB_ID()                              
group by OBJECT_NAME(object_id)                           
order by 2 desc  

 

方法二：使用以下语句查找出什么语句读 内存很高，查处查询语句或过程在进行优化

SELECT  SS.SUM_EXECUTION_COUNT,
        T.TEXT,
        SS.SUM_TOTAL_ELAPSED_TIME,
        SS.SUM_TOTAL_WORKER_TIME,
        SS.SUM_TOTAL_LOGICAL_READS,
        SS.SUM_TOTAL_LOGICAL_WRITES
FROM  (SELECTS.PLAN_HANDLE,
              SUM (S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,
              SUM (S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,
              SUM (S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,
              SUM (S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,
              SUM (S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES
        FROM  SYS.DM_EXEC_QUERY_STATSS
        GROUP  BY  S.PLAN_HANDLE
       )  AS  SS
       CROSS  APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
ORDER  BY  SUM_TOTAL_LOGICAL_READS  DESC

SELECT  SS.SUM_EXECUTION_COUNT,
        T.TEXT,
        SS.SUM_TOTAL_ELAPSED_TIME,
        SS.SUM_TOTAL_WORKER_TIME,
        SS.SUM_TOTAL_LOGICAL_READS,
        SS.SUM_TOTAL_LOGICAL_WRITES
FROM  (SELECTS.PLAN_HANDLE,
              SUM (S.EXECUTION_COUNT)SUM_EXECUTION_COUNT,
              SUM (S.TOTAL_ELAPSED_TIME)SUM_TOTAL_ELAPSED_TIME,
              SUM (S.TOTAL_WORKER_TIME)SUM_TOTAL_WORKER_TIME,
              SUM (S.TOTAL_LOGICAL_READS)SUM_TOTAL_LOGICAL_READS,
              SUM (S.TOTAL_LOGICAL_WRITES)SUM_TOTAL_LOGICAL_WRITES
        FROM  SYS.DM_EXEC_QUERY_STATSS
        GROUP  BY  S.PLAN_HANDLE
       )  AS  SS
       CROSS  APPLY SYS.dm_exec_sql_text(SS.PLAN_HANDLE)T
ORDER  BY  SUM_TOTAL_LOGICAL_READS  DESC