全文索引性能优化

本文涉及的产品
云数据库 RDS SQL Server,独享型 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
日志服务 SLS,月写入数据量 50GB 1个月
简介: 硬件资源(例如内存、磁盘速度、CPU 速度和计算机体系结构)会影响全文索引和全文查询的性能。导致全文索引性能降低的主要原因是硬件资源的限制: 如果筛选器后台程序宿主进程 (fdhost.exe) 或 SQL Server 进程 (sqlservr.exe) 的 CPU 使用率接近 100%,则 CPU 会成为瓶颈。

硬件资源(例如内存、磁盘速度、CPU 速度和计算机体系结构)会影响全文索引和全文查询的性能。导致全文索引性能降低的主要原因是硬件资源的限制:

  • 如果筛选器后台程序宿主进程 (fdhost.exe) 或 SQL Server 进程 (sqlservr.exe) 的 CPU 使用率接近 100%,则 CPU 会成为瓶颈。

  • 如果平均磁盘等待队列长度是磁盘头数量的两倍以上,则磁盘将成为瓶颈。主要的解决方法是创建独立于 SQL Server 数据库文件和日志的全文目录。将日志、数据库文件和全文目录分别放在不同的磁盘上。购买运行速度更快的磁盘和使用 RAID 也能帮助改善索引性能。

  • 如果物理内存不足(3GB 限制),则内存可能是瓶颈。物理内存限制可能存在于所有系统上,而在 32 位系统上,虚拟内存压力可导致全文索引速度降低。

    注意:
    从 SQL Server 2008 开始,全文引擎可以使用 AWE 内存,因为全文引擎是 sqlservr.exe 的一部分。

如果系统没有硬件瓶颈,则全文搜索的索引性能主要取决于以下因素:

  • SQL Server 创建全文批次花费的时间。

  • 筛选器后台程序能以多快的速度处理这些批次。

注意:
与完全填充不同,增量、手动和自动更改跟踪填充的设计目的并不是为了最大程度地利用硬件资源以获得更高速度。因此,这些优化建议可能不会增强全文索引的性能。

填充完成后,将触发最终的合并过程,以便将索引碎片合并为一个主全文索引。由于只需要查询主索引而不需要查询大量索引碎片,因此这将提高查询性能,并且可以使用更好的计分统计信息来得出相关性排名。请注意,由于合并索引碎片时必须读取和写入大量数据,所以主合并可能会耗费大量 I/O,但它不会阻塞传入的查询。

重要提示:
对大量数据进行主合并会创建一个长时间运行的事务,在检查点期间延迟事务日志的截断。在这种情况下,事务日志可能会在完整恢复模式下显著增长。作为最佳做法,在使用完整恢复模式的数据库中重新组织较大的全文索引之前,应确保事务日志中包含足够的空间用于长时间运行的事务。有关详细信息,请参阅。

优化全文索引的性能

若要最大限度地提高全文索引的性能,请实施下列最佳做法:

  • 若要最大程度地使用所有处理器或内核,请将 ‘max full-text crawl ranges' 设置为系统的 CPU 数。有关该配置选项的信息,请参阅 。

  • 请确保基表具有聚集索引。对聚集索引的第一列使用整数数据类型。避免在聚集索引的第一列使用 GUID。对聚集索引执行多范围填充可以产生最高的填充速度。我们建议充当全文键的列采用整数数据类型。

  • 使用 语句更新基表的统计信息。更重要的是,更新聚集索引或全文键的统计信息以进行完全填充。这有助于多范围填充在表上生成良好的分区。

  • 如果要提高增量填充的性能,请对 timestamp 列生成辅助索引。

  • 在大型多 CPU 计算机上执行完全填充之前,建议您通过设置 max server memory 值来暂时限制缓冲池的大小,从而留出足够的内存供 fdhost.exe 进程及操作系统使用。有关详细信息,请参阅本主题后面的“估计筛选器后台程序宿主进程 (fdhost.exe) 的内存需求量”。

解决完全填充性能问题

若要诊断性能问题,请查看全文爬网日志。有关爬网日志的信息,请参阅。

如果对完全填充的性能不满意,则建议按顺序执行以下故障排除步骤。

物理内存使用量

在全文填充期间,fdhost.exe 或 sqlservr.exe 的内存有可能不足。如果全文爬网日志显示 fdhost.exe 正在反复重新启动,或系统返回错误代码 8007008,则意味着这些进程中的某一个进程内存不足。如果 fdhost.exe 在生成转储(特别是在大型多 CPU 计算机上),则该进程的内存可能不足。

注意:
若要获得有关全文爬网所用的内存缓冲区的信息,请参阅 。

可能的原因如下:

  • 如果在完全填充期间可用的物理内存数量是零,则 SQL Server 缓冲池可能正占用系统的大部分物理内存。

    sqlservr.exe 进程试图侵占缓冲池的所有可用内存(最大为配置的最大服务器内存)。如果分配的“最大服务器内存” 过大,fdhost.exe 进程可能会面临内存不足的状况及共享内存分配失败。

    注意:
    在多 CPU 计算机(如 64 路 IA64 计算机)上进行全文填充期间,fdhost.exe 或 sqlservr.exe 之间可能出现缓冲池内存争用。由此造成的共享内存不足会导致批次重试、内存抖动并让 fdhost.exe 进程进行转储。
    可以通过适当设置 SQL Server 缓冲池的“最大服务器内存” 值来解决此问题。有关详细信息,请参阅本主题后面的“估计筛选器后台程序宿主进程 (fdhost.exe) 的内存需求量”。减小用于全文索引的批次大小可能也会有用。

  • 分页问题

    页文件大小不足也会导致 fdhost.exe 或 sqlservr.exe 的内存不足,例如在具有增长受限的较小页文件的系统上。

    如果爬网日志未指示存在任何与内存相关的故障,则很可能是因为过度分页导致性能下降。

估计筛选器后台程序宿主进程 (fdhost.exe) 的内存需求量

进行填充时 fdhost.exe 进程需要的内存量主要取决于它使用的全文爬网范围数、入站共享内存 (ISM) 的大小以及最大 ISM 实例数。

可以使用下面的公式粗略估算筛选器后台程序宿主占用的内存量(以字节为单位):

number_of_crawl_ranges * ism_size * max_outstanding_isms * 2

上面公式中的变量的默认值如下所示:

变量 默认值

number_of_crawl_ranges

CPU 的数目

ism_size

x86 计算机为 1 MB

x64 计算机为 4 MB、8 MB 或 16MB,具体取决于物理内存总量

max_outstanding_isms

x86 计算机为 25

x64 计算机为 5

下表列出了有关如何估算 fdhost.exe 的内存需求量的准则。此表中的公式使用以下值:

  • F,它是 fdhost.exe 所需内存的估计值 (MB)。

  • T,它是系统中可用物理内存的总量 (MB)。

  • M,它是最佳“最大服务器内存” 设置。

重要提示:
有关公式的基本信息,请参阅下面的 123

平台 估计 fdhost.exe 的内存需求量 (MB) - F1 用于计算最大服务器内存的公式 - M2

禁用 AWE 的 x86

F = Number of crawl ranges * 50

M = minimum(T, 2000) F 500

启用 AWE 的 x86

F = Number of crawl ranges * 50

M = T F 500

x64 或 IA643

F = Number of crawl ranges * 10 * 8

M = T F 500

1 如果正在进行多个完全填充,则分别计算每个完全填充的 fdhost.exe 内存需求量,如 F1F2 等等。然后按照 T sigma(Fi) 计算得到 M

2 500 MB 是系统中其他进程所需内存的估计值。如果系统正在执行其他工作,请相应地增加此值。

3 .ism_size 对于 x64 平台假定为 8 MB。

示例:估计 fdhost.exe 的内存需求量

此示例针对具有 8GM RAM 和 4 个双核处理器的 AMD64 计算机。首先计算出 fdhost.exe 所需内存的估计值 F。爬网范围数是 8

F = 8*10*8=640

然后计算出“最大服务器内存” 的最佳值 M 该系统的可用物理内存总量 (MB) T8192

M = 8192-640-500=7052

示例:设置最大服务器内存

此示例使用 和 Transact-SQL 语句将“最大服务器内存” 设置为上一个示例中计算得到的 M 值,即 7052

复制代码
USE master;
GO
EXEC sp_configure 'max server memory', 7052;
GO
RECONFIGURE;
GO

设置最大服务器内存配置选项







可以降低 CPU 占用率的因素

我们希望当平均 CPU 占用率低于大约 30% 时完全填充的性能不是最佳的。本节讨论影响 CPU 占用率的一些因素。

  • 长时间等待页面

    若要了解等待页面的时间是否太长,请执行下面的 Transact-SQL 语句:

    复制代码
    Execute SELECT TOP 10 * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC;
    下表描述了这里需要了解的等待类型。

    等待类型 说明 可能的解决方法

    PAGEIO_LATCH_SH(_EX 或 _UP)

    这可能表明存在 IO 瓶颈,在此情况下通常还会发现平均磁盘队列长度很高。

    将全文索引移动到其他磁盘上的其他文件组可能有助于减少 IO 瓶颈。

    PAGELATCH_EX(或 _UP)

    这可能表明多个正在试图写入相同数据库文件的线程之间存在大量争用现象。

    将文件添加到全文索引所在的文件组可能有助于减轻此类争用。

    有关详细信息,请参阅 。

  • 扫描基表的效率很低

    完全填充将扫描基表,以生成批次。在下列情况下,这样的表扫描可能很低效:

    • 如果基表有很高百分比的行外列正在建立全文索引,则扫描基表以生成批次可能成为瓶颈。在这种情况下,使用 varchar(max)nvarchar(max) 对较小的数据进行行内移动可能有用。

    • 如果基表非常零碎,扫描可能很低效。有关计算行外数据和索引碎片的信息,请参阅 和 。

      若要减少碎片,可以重新组织或重新生成聚集索引。有关详细信息,请参阅。

相关实践学习
日志服务之数据清洗与入湖
本教程介绍如何使用日志服务接入NGINX模拟数据,通过数据加工对数据进行清洗并归档至OSS中进行存储。
目录
相关文章
|
2月前
|
存储 关系型数据库 MySQL
【性能优化】MySql查询性能优化必知必会
【性能优化】MySql查询性能优化必知必会
95 0
【性能优化】MySql查询性能优化必知必会
|
6天前
|
SQL 运维 监控
MSSQL性能调优实战:索引优化、SQL查询效率提升与并发控制策略
在Microsoft SQL Server(MSSQL)的日常运维与性能优化中,精准的策略与技巧是实现高效数据库管理的关键
|
6天前
|
SQL 运维 监控
MSSQL性能调优实战:索引深度优化、SQL查询技巧与高效并发控制
在Microsoft SQL Server(MSSQL)的运维环境中,性能调优是确保数据库高效、稳定运行的核心任务
|
19天前
|
SQL 关系型数据库 MySQL
MySQL性能优化实战:从索引策略到查询优化
MySQL性能优化聚焦索引策略和查询优化。创建索引如`CREATE INDEX idx_user_id ON users(user_id)`可加速检索;复合索引考虑字段顺序,如`idx_name ON users(last_name, first_name)`。使用`EXPLAIN`分析查询效率,避免全表扫描和大量`OFFSET`。通过子查询优化分页,如LIMIT配合内部排序。定期审查和调整策略以提升响应速度和降低资源消耗。【6月更文挑战第22天】
134 2
|
6天前
|
SQL 存储 监控
MSSQL性能调优实战:索引策略、查询优化与并发控制的深度剖析
在Microsoft SQL Server(MSSQL)的性能调优实践中,索引策略、查询优化以及并发控制是三个至关重要的方面
|
10月前
|
数据库 索引
数据库性能优化中的索引优化
数据库性能优化中的索引优化
|
SQL 缓存 搜索推荐
第⼋章 查询性能优化
第⼋章 查询性能优化
|
存储 SQL JSON
MySql查询性能优化必知必会
作为一个写业务代码的 "JAVA CURD BOY" ,具备写出高效率SQL让应用高性能访问数据库的能力非常重要。获得这个能力的过程我收获了点知识和经验,今天在这里分享出来,希望大家多多交流指点。 本文内容主要包括以下几个方面:分析查询SQL,MySQL查询优化器、数据库存储结构、索引,索引维护,索引设计,SQL优化,表结构设计,分库分表,查询功能架构设计。
495 0
|
关系型数据库 PostgreSQL 索引
PostgreSQL 性能优化: 执行计划
PostgreSQL为每个收到的查询产生一个查询计划。查询计划的结构是一个计划结点的树。最底层的结点是扫描结点:它们从表中返回未经处理的行。不同的表访问模式有不同的扫描结点类型:顺序扫描、索引扫描、位图索引扫描。也还有不是表的行来源,例如VALUES子句和FROM中返回集合的函数,它们有自己的结点类型。如果查询需要连接、聚集、排序、或者在未经处理的行上的其它操作,那么就会在扫描结点之上有其它额外的结点来执行这些操作。并且,做这些操作通常都有多种方法,因此在这些位置也有可能出现不同的结点类型。
93 0
|
存储 SQL 缓存
MySQL查询性能优化(上)
MySQL查询性能优化(上)
248 0
MySQL查询性能优化(上)