sql server linux性能,SQL Server On Linux（17）—— SQL Server On Linux性能（3）——内置特性（3）——I/O行为...

对于关系数据库而言，CPU、内存和I/O子系统都是不可缺乏的硬件资源，哪个出问题都会直接影响性能，不过要注意不少表象，好比CPU利用率高，有时候仅仅是由于内存不够，CPU忙于把数据从内存和磁盘之间不停调度致使。因此要坚持从总体去思考的原则。web

简介

SQL Server借助read-ahead reading(预读)、write-ahead logging、checkpoint和数据压缩等功能，使其在确保数据一致性和持久性的前提下能最大化读写性能。另外在Linux平台还引入了缓存机制，借用O_DIRECT标记来获取I/O最大化性能。

接下来对这些功能作下介绍。sql

Read-Ahead

SQL Server的数据页是固定8KB的，若是SQL Server每次读取一页时，I/O次数将很是多从而影响性能，所以SQL Server使用了叫read-ahead的机制，把数据页放入buffer pool。SQL Server认为当前正在执行的查询可能会须要这些数据页，所以把数据预先加载到内存的缓存中会更有效。预读用于表或索引中分布在多个页的数据行的扫描操做。

可使用SET STATISTICS IO ON这个T-SQL命令来检查是否有预读，也能够用扩展事件跟踪file_read_completed。还可使用sys.dm_os_performance_counters这个DMV筛选符合object_name = ‘SQLServer:Buffer Manager’ and counter_name = 'Readahead pages/sec’条件的值。

可是每次预读的大小很是依赖于表或索引的组织，由于它其实是把数据连带取出，因此数据应该是要连续的。若是碎片不少，那么预读的效果就很不明显。另外跟SQL Server的版本也有关系，SQL Server 2017企业版最大的单次预读是1MB，而其余版本则更少。接下来简单演示一下。数据库

Read-Ahead 演示

首先建立一个扩展事件跟踪这个行为，这里针对实例数据库WideWorldImporters进行，这个库将会屡次被使用，若是如今还没环境能够参考SQL Server On Linux(3)——SQL Server 2019 For Linux 下载并部署示例数据库。缓存

CREATE EVENT SESSION [tracesqlreads] ON SERVER

ADD EVENT sqlserver.file_read_completed(SET collect_path=(1)

ACTION(sqlserver.database_name,sqlserver.sql_text)

WHERE ([sqlserver].[database_name]=N'WideWorldImporters'))

ADD TARGET package0.event_file(SET filename=N'tracesqlreads')

WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,

MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)

GO

ALTER EVENT SESSION [tracesqlreads] ON SERVER STATE=START

GO

而后执行下面命令，先清空buffer，而后经过SELECT COUNT(*)查询数据，这个查询指定了INDEX=1，默认就是汇集索引，这个简单的操做底层实际上就是把数据从磁盘加载到内存。这里也使用了SET STATISTICS IO 命令获取IO的统计信息。服务器

USE [WideWorldImporters]

GO

DBCC DROPCLEANBUFFERS

GO

SET STATISTICS IO ON

GO

SELECT COUNT(*) FROM Sales.Invoices WITH (INDEX=1)

GO

从下图可见read-ahead 有11388次

session

接下来中止扩展事件并查询结果：运维

--中止扩展事件

ALTER EVENT SESSION [tracesqlreads] ON SERVER STATE=STOP

GO

--查询扩展事件

SELECT [database_name] = xe_file.xml_data.value('(/event/action[@name="database_name"]/value)[1]','[nvarchar](128)'),

[read_size(KB)] = CAST(xe_file.xml_data.value('(/event/data[@name="size"]/value)[1]', '[nvarchar](128)') AS INT)/1024 ,

[file_path] = xe_file.xml_data.value('(/event/data[@name="path"]/value)[1]', '[nvarchar](128)')

--xe_file.xml_data

FROM

(

SELECT [xml_data] = CAST(event_data AS XML)

FROM sys.fn_xe_file_target_read_file('/var/opt/mssql/log/tracesqlreads*.xel', null, null, null)

) AS xe_file

GO

从下图结果能够看到实际取数的文件(file_path)和每次读取的大小(KB)，有些文件上只读取了64KB每次，有些则为128KB，还有256等等。具体细节咱们就不展开讨论，可是这里引出了另一个最佳实践里面的建议，就是对大型数据库分多个数据文件(可能在同一个文件组也可能在不一样文件组)，而且分布在独立的物理驱动，同时让这些文件组尽量初始化大小一致，增加程度一致。目的都是为了尽量经过并行读取接近相同的数据量，一方面加快处理速度，另一方面使操做不至于失衡。

异步

Write-Ahead Logging

当SQL Server由于增删改等操做须要对buffer中的数据页进行修改时，若是必须等待每一个已修改的数据页都写到磁盘才能算结束的话，那么SQL语句的性能将大大下降。可是因为SQL Server(关系数据库都同样)都包含事务日志文件，因此若是SQL Server能确保将全部的变动先写入日志文件，那么就不必立刻写入数据文件。

这种机制就叫作write-ahead logging(WAL，暂时称为预写式日志吧，中文其实不是很重要)，WAL其实不是SQL Server的专利，大量的RDBMS(关系数据库管理系统)都有这个机制，好比Oracle、PG等。SQL Server经过这个机制，确保全部已提交事务的修改都能写进磁盘的事务日志文件，全部事务即便在数据文件未更新前均能保证事务的一致性，哪怕SQL Server On Linux/Windows忽然出现宕机等状况下。

事务日志的写操做由后台任务LOG WRITER(能够经过sys.dm_exec_requests筛选LOG WRITER来查看)完成，为了获得更好的扩展性，从SQL Server 2016开始引入了基于NUMA节点的多个LOG WRITER任务。详见SQL 2016 – It Just Runs Faster: Multiple Log Writer Workers，好比本系列演示环境：ide

select session_id, status, command, scheduler_id, task_address, wait_type

from sys.dm_exec_requests

where command = 'LOG WRITER'

从SQL Server 2014也引入了一个叫delayed durability(延迟持久性)的功能，能够容许在事务未写入磁盘以前就标志事务已提交，从而加快事务的持续时间，提升响应速度，可是这种操做明显会带来数据丢失的风险。这部分的说明在“控制事务持续性”中能够查看。

Checkpoint，LazyWriters，Eager Writes

Checkpoints

加载到内存的数据，一旦被修改，就会标志为“脏页(dirty page)”，若是这些页一直都不移到磁盘作最终保存，那么事务日志就一直要记录这些信息，从而无限制地增加(单纯地限制增加也会致使数据库变成只读)，日志的增加速度能够很是快，本人见过在几分钟以内就会增长几百G，很容易就致使服务器出现没法预估的空间增加从而最终中止服务。另外，超大的LDF文件也会在数据库备份和还原过程花费很是多的时间。

所以，SQL Server提供了一些技术来应对这类问题。默认状况下，SQL Server 2017使用“indirect checkpoint，间接检查点”来写入数据库页。它使用目标恢复时间(target recovery time)来决定何时获取脏页信息并写到对应的数据库数据文件中。这个目标恢复时间可使用ALTER DATABASE TARGET_RECOVERY_TIME命令来配置，默认值为1分钟。

SQL Server还有一个automatic checkpoints的选项，这是SQL 2016以前的默认功能，使用sp_configure配置recovery interval来实现。可是在运行时可能会致使I/O写行为的密集出现。自动检查点使用后台任务CHECKPOINT来实现，(从sys.dm_exec_requests where command =‘RECOVERY WRITER’查看)。间接检查点的出现就是为了尽量减小这种峰值的影响，它使用后台任务“RECOVERY WRITER”(从sys.dm_exec_requests where command =‘RECOVERY WRITER’查看)来进行操做。

除了上面两种主要的ckeckpoint类型以外，SQL Server还会针对某些特定的操做和事件触发checkpoint，完整的内容能够查阅官方文档：数据库检查点 (SQL Server)。若是要监控checkpoint，能够查看sys.dm_os_performance_counter 中object_name=’ SQLServer:Buffer Manager’ and counter_name=‘Background writer pages/sec’ (针对间接检查点)和查看sys.dm_os_performance_counter 中object_name=’ SQLServer:Buffer Manager’ and counter_name=‘Checkpoint pages/sec’ (针对自动检查点)。

Lazywrite

因为绝大部分服务器上的数据库体积都远大于物理内存的数量，因此内存没法真正缓存全部的数据。在系统运行的过程当中，常常会出现所需的数据没有在缓存中，须要从硬盘上加载，可是缓存自己已经没有足够的空间，这个时候就须要吧缓存中的某些数据清空回去硬盘，首当其冲的就是那些在缓存中已被修改过的数据页(脏页)，SQL Server使用WAL来进行这种操做以便保证数据的一致性，可是有些脏页还在未提交的事务中，它们可能在后续会被回滚，为了保证这种逻辑，在脏页写入以前，事务日志须要先记录。在全部buffer pool管理动做中写入脏页的操做统称lazywrite。一般由称为LAZY WRITER的后台进程执行(sys.dm_exec_requests where command=‘LAZY WRITER’)。

可是若是出现短时大容量数据变动，如SELECT …INTO/INSERT…SELECT等，使用lazywrite效率很低。这个时候SQL Server会使用Eager write(官网翻译为勤奋写入)来实现脏页写入。这种操做适合上面所述的属于大容量日志操做关联的脏页。以并行方式建立和写入新页，不须要等待整个操做完成就能够将页写入磁盘。

小结

检查点(checkpoint)、惰性写入(lazywrite)和勤奋写入(eager write)是三种脏页写入磁盘的方式。它们均不须要等待I/O操做完成，始终使用异步I/O实现。能够充分利用服务器的CPU和I/O资源。

数据压缩

对于B-tree索引而言，索引的层级直接影响索引效率，而层级的大小又跟数据页的数量有关系，若是每一个数据页能容纳更多的数据，那么相同的数据就只须要更少的数据页从而提高索引性能，另外在数据库常规维护过程当中，数据体积越小越好，这个就不必多说了。SQL Server 从2008版开始引入了数据压缩技术，包含页压缩和行压缩，使得更少的数据页须要加载并驻留在buffer中，这个已是很成熟的功能，在后续章节会提到另一种压缩，就是使用列存储索引。可是注意压缩和解压因为须要额外的CPU进行计算，因此并非全部地方都通用的。读者能够先行阅读Data Compression。这里先很少说。

总结

本文从SQL Server内部介绍了SQL Server关于I/O方面的特性。有些是SQL Server 2016开始引入的新特性，有些则是出现时间较久可是依旧有效的特性。下一篇将从配置层面去介绍如何最大化SQL Server特别是Linux平台的性能。