对于关系数据库而言,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平台的性能。