Home Database Mysql Tutorial SQL Server 数据页缓冲区的内存瓶颈分析

SQL Server 数据页缓冲区的内存瓶颈分析

Jun 07, 2016 pm 06:07 PM
buffer

数据页缓存是SQL Server的内存使用主要的方面,也是占用量最大的部分。在一个稳定的DB Server上,这部分内存使用会相对较稳定

SQL Server会把经常使用到的数据缓存在内存里(就是数据页缓存),用以提高数据访问速度。因为磁盘访问速度远远低于内存,所以减少磁盘访问量同样是数据库优化的重要方面。

当数据页缓存区出现内存不足,则会出现查询慢,磁盘忙等等问题。

分析方法:主要是用到性能计数器。

查看如下性能计数器:

1. SQL SERVER:Buffer Manager-Lazy Writes/sec:内存不足则会频繁调用Lazy Writer把数数据写入磁盘,此值会经常不为0.

2. SQL SERVER:Buffer Manager-Page life expectancy:内存不足时,此计数器表现为下降趋势或者一直停留在较低值。

3. SQL SERVER:Buffer Manager-Page reads/sec:内存不足时,则查询那些经常使用但又没有缓存在内存里的数据时,就不需要读取磁盘,这此值表现为持续上升或者停留在较高值。

4. SQL SERVER:Buffer Manager-Stolen pages:Stolen pages通常用于缓存执行计划,以备重用。内存不足时,SQL Server本身机制会优先清除执行计划缓存,则此值表现为下降或者较低水平。

查询当前用户任务等待:

代码如下:
select * from sys.sysprocesses

如果内存不足则,会看到较多的ASYNC_IO_COMPLETION等待类型。这是因为内存不足时:a.内存和磁盘间会频繁进行交互,磁盘负载增加 b.需要读取磁盘上的数据完成查询,磁盘负载增加。

也就是说这时候磁盘也出现了性能瓶颈,但是这只是“表面”的,我们要结合多个性能指标来认清根本原因是“内存不足”。

确定压力来源及解决办法:

通过前的分析,确定了数据页缓存相关的内存瓶颈。就要分析为什么会这样及解决办法。主要分为如下5个方面:

1. 外部压力

如果OS层面或者其它应用服务需要更多的内存,windows会压缩Database Pages的内存量。这时内存压力来自外部。可以查看如下性能计数器确定是否是外部压力:

1. SQL Server:Memory Manager-Total Server Memory:此计数器值会下降。

2. Memory:Available Mbytes:此值会下降到较低水平。

3. 在没有使用AWE或者Lock page in memory前提下,查看Process:Private Bytes-SqlServer和Process:Working Set-SqlServer,两者值会有显著下降。

解决方法:如果非DB专用服务器,则要权衡各个应用服务之间重要性来分配内存或者加大内存。尽量让服务器只运行SQL Server,成为DB专用服务器。

2. SQL Server自身对Database Page的使用压力

当Total Server Memory已经达到设定的Max Server Memory或者无法从OS获得更多内存,但是经常访问的数据量又远大于物理内存用于数据缓存的容量时,SQL Server被迫将内存的数据移入又移出,用于完成当前查询。

观察如下性能计数器:

1. SQL Server:Memory Manager-Total Server Memory 和 SQL Server:Memory Manager-Target Server Memory两者值将会相等。但是前者不会大于后者。

2. 将会出现“分析方法”所述之情况。

解决方法:既然SQL Server没有足够内存存放Database Page,那就要么增加SQL Server使用的内存量或者减少其使用的内存里。

增加:可以通增加物理内存,启用AWE等方法。

减少:可以通过横向扩展,有两台或者多台服务器分别载部分库;优化相关读取量较大的语句等。

3. Buffer Pool中的Stolen Memory压力

正常情况下Buffer Pool中的Stolen Memory不会给Database Pages造成压力。因为Database Pages有压力,会触发Lazy Writes,同时SQL Server 会清理Stolen Memory中的执行计划缓存。

但是,如果用户申明了过多的对象,而没有登出,并且占用内存过多,就会压缩Database Pages.如:游标,自定义引用的执行计划等。

解决方法:通常是会表现为a)用户提交的请求因内存不足无法完成,701错误;b)需要压缩某些clerk的内存量,来完成用户请求,造成响应延时和缓慢。

通过查询sys.dm_os_memory_clerks的字段Single_pages_kb,找出是哪个clerk使用了过多内存并分析其原因,然后解决之。

4. Multi-Page的压力

multi-page跟Buffer Pool共享OS的虚拟地址空间,如果multi-page使用过多内存,就会压缩Datbase pages。multi-page内存用量一般较小且相对固定,可能发生的情况有:

a. 未开启AWE的32位SQL Server只有2G地址空间,且用-g启动参数扩展的MemToLeave的上限。

b. 64位SQL Server调了内存泄露的第三方代码。

c. 使用带有大量参数或者较长的”IN”语句

d. 调高了Network Packet Size,大于或等于8KB,并且较多这种连接。

e. 大量复杂XML查询,或者第三代码。

解决方法: 通过查询sys.dm_os_memory_clerks的字段multi_pages_kb,找出是哪个clerk使用了过多内存并分析其原因,然后解决之。


作者:Joe.TJ
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
1 months ago By 尊渡假赌尊渡假赌尊渡假赌
Two Point Museum: All Exhibits And Where To Find Them
1 months ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How do you alter a table in MySQL using the ALTER TABLE statement? How do you alter a table in MySQL using the ALTER TABLE statement? Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

How do I configure SSL/TLS encryption for MySQL connections? How do I configure SSL/TLS encryption for MySQL connections? Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

How do you handle large datasets in MySQL? How do you handle large datasets in MySQL? Mar 21, 2025 pm 12:15 PM

Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

How do you drop a table in MySQL using the DROP TABLE statement? How do you drop a table in MySQL using the DROP TABLE statement? Mar 19, 2025 pm 03:52 PM

The article discusses dropping tables in MySQL using the DROP TABLE statement, emphasizing precautions and risks. It highlights that the action is irreversible without backups, detailing recovery methods and potential production environment hazards.

How do you create indexes on JSON columns? How do you create indexes on JSON columns? Mar 21, 2025 pm 12:13 PM

The article discusses creating indexes on JSON columns in various databases like PostgreSQL, MySQL, and MongoDB to enhance query performance. It explains the syntax and benefits of indexing specific JSON paths, and lists supported database systems.

How do you represent relationships using foreign keys? How do you represent relationships using foreign keys? Mar 19, 2025 pm 03:48 PM

Article discusses using foreign keys to represent relationships in databases, focusing on best practices, data integrity, and common pitfalls to avoid.

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)? How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)? Mar 18, 2025 pm 12:00 PM

Article discusses securing MySQL against SQL injection and brute-force attacks using prepared statements, input validation, and strong password policies.(159 characters)

See all articles