SQL Server 性能调优(内存)

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:   存储引擎自调整... 1 sql server 是如何分配内存的... 1 32bit地址空间的限制... 2 用户模式vas分配和virtualalloc. 3 非boffer pool 分配内存(保留内存)3 VAS调整.

 

 

存储引擎自调整... 1

sql server 是如何分配内存的... 1

32bit地址空间的限制... 2

用户模式vas分配和virtualalloc. 3

boffer pool 分配内存(保留内存)3

VAS调整... 3

AWE. 3

启动参数-g. 4

诊断内存压力... 4

内存相关计数器... 4

SQL Server :Buffer Manager. 4

buffer cache hit ratio. 4

page life expectancy. 5

Free Pages. 5

Free list stalls/sec. 5

lazy write/sec. 5

SQL Server:memory Manager. 5

total server memory target server memory. 5

memory grants outstanding. 6

memory grants pending. 6

内存相关的DMV. 6

内存相关问题... 6

分页问题... 6

因为lock pages 没有设置服务器最大内存导致系统不稳定... 7

701错误和 FAILED_VIRTUAL_RESERVE. 7

多实例下的内存设置... 7

总结... 7

 

内存的分配和使用在网上一直是讨论的话题。对sql server 来说使用了内存但是不释放是很正常的事情,和其他的应用程序是不一样的,导致一些用户认为sql server确实内存但事实并非如此。

存储引擎自调整

sql server 2005开始,内存的管理就是动态的,与其他关系型数据库不同使用已经调整好的内存空间。如plan cache 是全部的并且自动的,引擎控制根据当前数据库的负载和其他活动信息来控制plan chache内存。sql server 虽然缺少内存的控制方法,但是还有有参数可以设置内存的,如操作系统的版本,内存的大小和处理器的体系结构。

sql server 是如何分配内存的

第一反应就是查看windows任务管理关于sql server 的内存使用情况,一看到sql server 占用了很大的内存就可能会认为sql server 缺少内存,但是缺少内存和占用很大内存其实是没什么关系的。sql server是被设计为大内存使用的,如buffer cache,存放了大量的数据页,为了减少io,提高性能。通常,不管你提供了多少内存sql server 都能使用光,除非你接到一个来自操作系统的memory low通知,sql server 就会自动调节减小内存,通知有2中:

memory high:通知sql server可以增加内存的使用量

memory low:通知sql server 释放内存

如果windows 不通知,那么sql server 就不会增加或减少内存,在windows 2003 sql server 2005 以前的版本是没有的。有一篇关于sqlos中内存的文章介绍了不同类型的内存压力,大致如下:

 

 

文章地址:http://blogs.msdn.com/b/slavao/archive/2005/02/01/364523.aspx

sql server 最大内存使用量由以下几点要素:

1.安装的物理内存数量

2.操作系统的最大内存限制

3.sql server 体系结构,32b64b

4.sql server 配置项

5.sql server 版本



32bit地址空间的限制

对于sql server 32位最大的影响就是32位的地址空间,也就是最大4g而且包含内核模式和用户模式。用户模式和内核模式各2g。主要我们讨论一下几点

1.sql server 用户模式如何分配内存

2.buffer pool的保留空间

3.用户模式使用3g 内存

4.sql server 如何使用大于4g内存,data cache调用awe内存。

接下来主要讲的都是32bit 下内存使用的限制和64bit关系不大。

用户模式vas分配和virtualalloc

sql server 为用户默哀是保留了2g的内存地址,sql server 用户模式需要内存时,通过调用virtualalloc 分配内存并返回32位的指针,因为地址空间的限制,所以sql server 只能使用2g内存。

通过virtualalloc分配的内存并不一定是实际的物理内存,不管安装了多少物理内存,sqlserver都是2g的地址使用空间。windows也保证sql server 和其他应用程序使用的内存,不会超过实际物理内存和页面文件的总容量。如果总共安装的内存少于2g,那么sql server 就会又物理内存的限制,sql server buffer pool的内存也不会超过安装的内存数量。virtualalloc 分配的内存都是页模式的,也就是如果出现内存压力windows 就可以直接把内存写入到磁盘中。

boffer pool 分配内存(保留内存)

如果sql server 请求大于8k的连续内存的时候,会使用多页分配器分配内存。backup buffers 是非buffer pool分配的最大的一个,需要的内存是 maxtransfersize * backupbuffercount 在正常的备份下,需要16backup buffer,每一个buffer4m的内存。所以会吃掉64m的非buffer pool 内存。为了保证有住够非buffer pool32bsql server 在启动的时候会保留一部分内存。一旦有保留内存,那么buffer pool的内存空间会是安装的空间减去保留的空间。对于20052008保留空间为 maxworkerthreads*0.5mb+256mb(默认保留空间大小)maxworkerthreads = processprcount-4+256。在2000 maxworkerthreads = 256,按这个计算保留空间至少是384MB,但是通常少于432mb。保留空间的参数在sql server 启动参数内设置,标记为-g,可是适当在计算出来的结果上增加内存。sql server 一共2g的地址空间减去保留的,大概剩下1.6Gbuffer pool 使用。对于超过4gb的内存可以使用awe来分配内存。

VAS调整

对于4g的内存,系统可以修改内核的地址空间使用率把1:1,变为1:3。这个叫做4g调整,这样就减少了内核模式下的地址空间,导致了PTE减小,pte是虚拟内存和物理内存的映射,一但减小,sql server 总共可使用的内存也就背减少了。所以在调整的时候要谨慎。在windows 2008下可以使用bcdedit /set 命令设置increaseuserva 可以设置从 2048 3072的值。在2000 2003 可以使用/3g的标签来开启。

AWE

如果安装了超过了4个的内存,那么就可以使用awe,当然windows 必须支持才能使用。开启pae,系统最多能够使用64G内存。在内存分配上因为使用virtualalloc分配内存会有限制,那么就改用allocateuserphysicalpage来分配内存,一旦被分配那么内存页被锁住,无法交换到交换文件。当启用awe后所有的datacache使用过awe分配内存还有就是 plancache也是。要启用awe那么pae就必须被设置,还有在sql server awe enabled 参数sql server 的启动用户必须有lock pages 权限。因为awe内存无法被交换出去,所以设置最大内存数量很偶必要,使得sql server 内存使用量得到限制,不会无限期的增长影响其他应用程序和系统内存的使用问题。在系统中如果你的内存少于16g,你可以使用前面提到的awe+4gt的方式,但是不推荐因为如果你一旦设置了4gt,windows 可管理内存从64g下降到了16g yinwei 4gt减少了pte的大小。

启动参数-g

sql server 启动的时候会分配一部分保留内存,保留内存对buffer pool 来说是比较小的。很多的内存是从buffer pool上分配而不是从保留空间中分配,因此基本上不会有问题,但是随着日积月累应用程序变的越来越复杂,默认的保留空间已经无法满足需求那么就通过-g参数配置。因为不合适的空间大小和保留空间的碎片问题,导致无法请求到连续的内存空间。查看sys.dm_os_virual_address_dump 动态性能视图可以查看可用的虚拟地址空间。关于确定可用地址空间可以看相关文章:http://sqlblogcasts.com/blogs/christian/archive/2008/01/07/sql-servermemtoleave-vas-and-64-bit.aspx

保留空间的碎片问题是最难处理的,如果你打电话到微软技术支持,他们给的建议就是升级到64b,因为64b的虚拟地址空间是8t,不会不够如果你不想那么就加大你的保留空间地址。

 

诊断内存压力

sql server 内存不足的时候,那么data cache 存储的数据就少,查询不能在内存中请求到数据,那么就请求io,放入内存,这些数据又很快的被清出内存,需要的时候有继续从io读进来,这个就是buffer pool 滚筒。buffer pool 滚筒会照成io过高,就会误认为是io的问题,其实是内存不足的问题。

内存相关计数器

有一些重要等待和内存使用率相关的性能计数器,但是要记清楚并没有一个计数器就能够表明内存压力的,一个简单的计数器快照并不能说明问题。内存压力的诊断需要一段时间的跟踪。

SQL Server :Buffer Manager

又很多有用的计数器都是这 buffer manager 对象下面,可以帮助发现buffer pool滚筒的问题。

buffer cache hit ratio

buffer cache hit ratio一般情况下在oltp中要高于95%,在olap中要高于90%。可惜的是没有关于这个性能指标相关的解释,和这个值是如何影响预读机制的。如果这个指标的值有巨大的下降那么就说明有问题。这个不能说明内存压力和sql server 健康指数。

page life expectancy

page life expectancy是页生命周期,也就是一个数据页在内存中的时间。在以前sql server 2000 4g的内存已经很大了,sql server buffer pool的大小是1.6g,如果sql server 从磁盘上读取1.6g的数据也只要5分钟,但是今天64g的内存是主流,如果从磁盘一下子读取50g的内存,会严重的冲击io。当存在大量的查询扫描表,读入新的数据页,导致生命周期值下降也不是不正常的。这个值必须长期的监视来分析问题。

Free Pages

free pages是内存中空页的数量,不要接近于0。这个值说明查询能否在其他查询不是放内存的情况下,快速的分配内存的主要依据。如果free pages 很少,页生命周期很短,并且伴随着空页争用(free list stalls/sec)的情况那么很有可能导致内存压力。

Free list stalls/sec

Free list stalls/sec每秒空页等待的数量,如果一段时间内都在0以上那么说明可能存在内存压力。

lazy write/sec

lazy write/sec 就是每秒写入磁盘的次数。如果发生量很大并且生命周期很短,free page 很少,但是 free list stall/sec 量很大,那么就是发生内存压力了。



SQL Server:memory Manager

SQL Server:memory Manager对象内对内存的消费和内存管理的问题提供了很重要参考

total server memory target server memory

2个计数器代表了当前sql server 使用的总共内存和sql server 想要用的内存。如果 target server memory超过了total server memory,也是内存压力的重要标志。sql server 会减少内存的需求来接近服务的可用内存,或者通过最大服务器内存配置,所以当内存出现压力问题的时候不应该第一时间去查看这2个计数器

memory grants outstanding

该值是现实多少进程已经成功的获取了内存的授权。在一段时间内,业务高峰期,如果该值过低,那么标志可能存在内存压力,特别是 memory grants pending 也比较高的情况下。

 memory grants pending

该值是有过少进程正在等待内存的授权。如果为非0,那么说明需要调整或者优化负载或者增加内存。

 

内存相关的DMV

和内存相关的等待和非buffer pool 内存分配的信息,从dmv中获取。

sys.dm_exec_query_memory_grants 可以查看正在等待授权的查询,特别是大内存的授权

sys.dm_os_memory_cache_counter multi_pages_kb 显示了多页分配的内存分配

sys.dm_os_sys_memory 合计了系统当前内存,缓冲,cache,多页分配分配的内存。

sys.dm_os_memory_clerks 显示相关管理内存的书记进程,如 buffer pool 大内存的使用并且结合 MEMORYCLERK_SQLQERESERVATIONS 可以发现buffer pool 内存不住

 

内存相关问题

通常的一些问题可以被分为3种:错觉,错误配置,正在的问题。大量的疑似内存问题的最后其实只有一小部分才是真正的问题。

分页问题

sql server 重要的组件被page out了,会在error log 中出现一个信息a significant part of SQL Server process memory has been paged out对于 worksettrim通常是下列的情况:

1.当没启用lock pages的时候,不正确的最大服务器内存的设置

2.windows 中系统缓冲,被用来处理非缓存的io操作,如复制文件。

3.硬件驱动问题导出使用过多的内存。

最有效的阻止方法是,开启lock pages,文章:http://support.microsoft.com/kb/918483讲述了64b sql server 发生workset trim的根本原因。

 

因为lock pages 没有设置服务器最大内存导致系统不稳定

如果sql server开启了 lock pages 但是 最大服务服务内存又没设置,sql server 会吃光所有的服务器的可用内存。当windows 内存紧张会向通知sql server 内存压力,但是buffer pool working set 都不会被交换页面文件。这样会导致windows crash。如果最大内存数设置的过大也会造成同样的情况。

 

701错误和 FAILED_VIRTUAL_RESERVE

sql server 申请一个连续的vas失败,就会返回701错误和答应出需求大小的信息。这个错误只会发生在32bsql server32b sql server vas十分有限。这个错误和buffer pool 没有什么关系主要是大于8k内存分配的时候出现。解决办法就是使用-g启动参数,修改sql server保留空间。

 

多实例下的内存设置

sql server 如果多实例安装在单个机器上或者一个故障转移能减少license的购买。当一台服务器上有多个实例,那么设置min_server_memory max_server_memory 很重要,根据每个实例的负载,避免出现内存冲突的情况。根据先前提到过的性能指标和dmv 对内存使用情况监测,设置一个合理的最大内存和最小内存数。在多实例情况下,建议把最小内存数也设置上,因为如果有最小内存数,那么sql server 申请内存的时间会减少。如果最小内存数没有设置,sql server 可能会自愿减少内存的使用率而导致性能下降。

 

总结

在内存上面 32b 64b 又很大的不同,如果负载较高那么就是用64b6号那天sql server 2012发布,还没去看联机文档,但是听朋友说201232b不在支持awe,也就是说如果大内存那么就用64b64b内存方面很有优势。这篇主要讲了内存的状况,总结到这里我已经明显的感觉到,常见问题远没有原理来的重要。troubleshooting只是原理的一种应用而已。关于资源的都讲完了,cpu,内存,io,下一篇会讲讲miss indexmiss index 也是会引起 cpu,内存,io 的异常状况。

 

 

 

 

 

 

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
12天前
|
SQL 数据库 开发者
MSSQL性能调优实战技巧:索引优化、SQL语句微调与并发控制策略
在Microsoft SQL Server(MSSQL)的管理与优化中,性能调优是一项复杂但至关重要的任务
|
12天前
|
SQL 监控 数据库
MSSQL性能调优实战技巧:索引优化策略、SQL查询重构与并发控制详解
在Microsoft SQL Server(MSSQL)的管理与优化过程中,性能调优是确保数据库高效运行的关键环节
|
12天前
|
SQL 监控 数据库
MSSQL性能调优实战指南:精准索引策略、SQL查询优化与高效并发控制
在Microsoft SQL Server(MSSQL)的性能调优过程中,精准索引策略、SQL查询优化以及高效并发控制是三大核心要素
|
3天前
|
SQL Oracle 关系型数据库
MySQL、SQL Server和Oracle数据库安装部署教程
数据库的安装部署教程因不同的数据库管理系统(DBMS)而异,以下将以MySQL、SQL Server和Oracle为例,分别概述其安装部署的基本步骤。请注意,由于软件版本和操作系统的不同,具体步骤可能会有所变化。
21 3
|
2天前
|
存储 缓存 监控
Flink内存管理机制及其参数调优
Flink内存管理机制及其参数调优
|
9天前
|
缓存 Prometheus 监控
Java面试题:如何监控和优化JVM的内存使用?详细讲解内存调优的几种方法
Java面试题:如何监控和优化JVM的内存使用?详细讲解内存调优的几种方法
30 3
|
12天前
|
SQL 运维 监控
MSSQL性能调优实战技巧:索引优化、SQL查询优化与并发控制策略
在Microsoft SQL Server(MSSQL)的运维过程中,性能调优是确保数据库高效运行、满足业务需求的关键环节
|
9天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
3天前
|
SQL 资源调度 关系型数据库
实时计算 Flink版产品使用问题之在使用Flink on yarn模式进行内存资源调优时,如何进行优化
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
9天前
|
存储 设计模式 监控
Java面试题:如何在不牺牲性能的前提下,实现一个线程安全的单例模式?如何在生产者-消费者模式中平衡生产和消费的速度?Java内存模型规定了变量在内存中的存储和线程间的交互规则
Java面试题:如何在不牺牲性能的前提下,实现一个线程安全的单例模式?如何在生产者-消费者模式中平衡生产和消费的速度?Java内存模型规定了变量在内存中的存储和线程间的交互规则
19 0