数据库优化 ——关于tempdb系统库影响IO和数据库性能的一些问题

现在的一些公司一天天光想着降本，根本不考虑系统的实际情况，好端端运行好的系统说降配就降配，然后系统卡了超时了慢了，一句话自己解决，从来不会想这个日积月累的项目堆了多少💩，好似一句经典名言：你去把唐僧师徒干掉， 真是苦了码农了【T__T】（发文前先吐槽一下哈哈）

前两天公司做SQLServer数据库优化，添加、删除、重建了很多索引，系统发布之后数据库CPU也降下来了很多，但是奇怪的是阿里云RDS实例监控与报警sqlserver.db_io 却非常的高，下图二十七号发布之后sqlserver.db_io_read和sqlserver.db_io_write都是突增，起初是觉得系统流畅了，查询和写的频率可能就会增多，但是后来觉得有点不对劲，增高的跨度有点大嗷（如下图）。

于是我们使用动态管理视图（DMV）监控 I/O 活动和空间使用情况。SQLServer查询语句如下：

SELECT 
    DB_NAME(mf.database_id) AS DatabaseName,
    mf.physical_name AS FilePath,
    vfs.num_of_reads,
    vfs.num_of_writes,
    vfs.io_stall_read_ms,
    vfs.io_stall_write_ms,
    vfs.io_stall,
    vfs.size_on_disk_bytes / 1024 / 1024 AS SizeOnDiskMB
FROM 
    sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN 
    sys.master_files AS mf ON vfs.database_id = mf.database_id AND vfs.file_id = mf.file_id
ORDER BY 
    vfs.io_stall DESC;
    

一.首先了解一下tempdb库

tempdb 是 SQL Server 的一个系统数据库，它在 SQL Server 实例启动时自动创建，并在实例运行期间始终存在。tempdb 的主要作用是存储临时数据和内部对象，类似于一个“工作区”，用于处理各种临时存储需求。

tempdb 的产生和初始化
自动创建：
tempdb 是 SQL Server 的系统数据库之一，与 master、model 和 msdb 一起，在 SQL Server 实例安装和启动时自动创建。
它的初始配置（如文件路径、大小和增长设置）由 SQL Server 安装程序决定，但可以在运行时
通过 SQL 命令修改。

初始化过程：
每次 SQL Server 实例启动时，tempdb 会被清空并重新初始化。
所有用户创建的临时表、表变量和内部对象在实例启动时都会被清除。
tempdb 的文件大小和配置会根据安装时的设置或后续的修改命令进行初始化。

查询结果如下，可以看到tempdb 库的数值都非常的高：

二.分析高IO的根本原因：

1.高写入 I/O 等待时间：
io_stall_write_ms 的值非常高（例如 412252064 毫秒），表明写入操作是 I/O 等待的主要来源。
这可能是因为 tempdb 中的临时表或内部对象频繁进行写操作，导致磁盘 I/O 成为瓶颈。

2.高读取 I/O 次数：
num_of_reads 和 num_of_writes 的值都非常高（例如 1122013 和 2493752），表明 tempdb 的读写操作非常频繁。
这可能是因为系统中频繁使用临时表、表变量或复杂的查询操作。

3.文件大小一致性：
所有 tempdb 文件的大小（SizeOnDiskMB）均为 250MB，这表明文件大小配置是合理的，
然而，由于 I/O 活动非常高，可能需要进一步调整文件大小或数量。

4.文件数量：
已经为 tempdb 配置了多个数据文件（8 个），这有助于分散 I/O 负载但是 I/O 活动仍然很高，可以寻求别的解决方案。

再分析一下tempdb 跟前期优化的增加、删除、重建索引的关系（优化后才增高的原因）

主要可能有以下几个方面原因：
1. 索引重建与 tempdb 的关系
排序操作：在重建索引时，SQL Server 需要对数据进行排序。如果指定了 SORT_IN_TEMPDB = ON 选项，排序过程中产生的中间数据将存储在 tempdb 中，而不是目标文件组中。
这会显著增加 tempdb 的写入操作，因为数据需要先写入 tempdb，然后再写入目标文件。
如果 tempdb 的 I/O 性能较低（如磁盘速度慢），这可能会导致 I/O 等待时间增加。
空间需求：重建索引需要额外的空间来存储中间结果。即使不使用 SORT_IN_TEMPDB，tempdb 仍然会用于存储事务日志信息，尤其是在在线重建索引时。
2. 添加和删除索引对 tempdb 的影响
添加索引：创建新索引时，SQL Server 需要对数据进行排序和组织，这些操作可能会使用 tempdb 来存储中间结果。
如果创建多个索引，尤其是对大型表操作时，tempdb 的 I/O 活动会显著增加。
删除索引：删除索引时，SQL Server 需要清理相关数据和日志信息，这也会产生一定的 I/O 操作。
3. 索引操作对 tempdb I/O 的具体影响
写入操作增加：无论是重建、添加还是删除索引，都会产生大量的写入操作。这些操作需要 tempdb 提供足够的空间和 I/O 带宽。
事务日志压力：索引操作会生成大量的事务日志记录，这些记录也需要存储在 tempdb 中，进一步增加了 I/O 负载。

三.解决方案

1.文件数量的合理性：
根据 CPU 核心数配置多个数据文件，以分散 I/O 负载。通常建议配置 1 到 8 个数据文件，具体数量取决于实际工作负载（我的数据库已经配置了8个数据文件了所以ps掉）。

2.文件路径的性能：
确保 tempdb 文件路径位于高性能存储（如 SSD）上，以提高 I/O 性能

3. 增加 tempdb 文件大小

（按照自己数据库的实际情况）虽然文件大小已经配置为 250MB，但根据 I/O 活动的强度，可能需要进一步增加文件大小以减少自动增长的频率和避免频繁的文件扩展操作，我们将文件大小调整到合适并设置加大自增长值。例如：
sql

 ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 512MB, FILEGROWTH = 128MB);

这段 SQL 查询的作用是检索 tempdb 数据库中所有文件的逻辑名称、物理路径和状态描述。我们可以通过这段代码查找到文件名称和位置：

   SELECT 
    name AS LogicalName,
    physical_name AS FilePath,
    state_desc AS Status
FROM 
    sys.master_files
WHERE 
    database_id = DB_ID('tempdb');

找到逻辑文件名称后修改文件大小并加大自增长值，要注意的是一定要设计合理（根据自己实际情况）

   ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', SIZE = 512MB, FILEGROWTH = 128MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', SIZE = 512MB, FILEGROWTH = 128MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev1', SIZE = 512MB, FILEGROWTH = 128MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev2', SIZE = 512MB, FILEGROWTH = 128MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev3', SIZE = 512MB, FILEGROWTH = 128MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev4', SIZE = 512MB, FILEGROWTH = 128MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev5', SIZE = 512MB, FILEGROWTH = 128MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev6', SIZE = 512MB, FILEGROWTH = 128MB);
   ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev7', SIZE = 512MB, FILEGROWTH = 128MB);