[Microsoft SQL Server] [最佳实践] SQL SERVER 临时数据库的数据文件按8个进行创建

背景说明

       tempdb是系统数据库,它是一个全局资源,可供连接到 SQL Server 实例或 SQL 数据库的所有用户使用 。它是一个临时数据库,无法永久保存数据,作用是给实例中的各种请求处理中间数据,分为主数据文件(.mdf)、次要数据文件(.ndf)和日志文件(.ldf)。当服务重启的时候,tempdb会被重新创建。

       tempdb数据库如果在设计上存在缺陷,会存在性能上的问题。尤其是tempdb数据库在一些高并发的场景,如果应用频繁地创建和销毁临时表,会导致实例卡顿从而影响业务。

   微软官方建议将临时数据库的文件拆分成多个,一般与逻辑CPU个数相同,超过8个则使用8个数据文件,解决闩锁争用问题每次额外加4个文件。

使用说明

  目前华为云SQL Server各个版本的日志文件均为一个,但数据文件的数目不同。200820122014实例默认1个数据文件,2016实例默认4个数据文件,2017实例默认8个数据文件。

  用户需要根据实例实际规格和具体场景确定数据文件的创建个数。下面将以32USQL Server 2014EE实例为例创建8个tempdb的数据文件。

操作说明

   准备工作

      安装SQL Server客户端,在关系型数据库服务创建一个32U的SQL Server 2014EE实例

   操作步骤

 1.启动SQL Server Management Studio客户端


 2.选择“连接 > 数据库引擎”,在“连接到服务器”弹出框中填选登录信息

图1 连接到服务器 

  服务器名称格式为目标实例的主机IP和数据库端口(IP和数据库端口之间请使用英文半角逗号)。例如:x.x.x.x,1433


    3.查看当前tempdb信息

    选择“数据库 > 系统数据库 > 临时数据库”,右键单击“属性”,在弹出框中选择“文件”,查看当前tempdb信息

    图2 查看当前tempdb信息 

    或通过SQL语句查询

    SELECT name AS FileName,

    size*1.0/128 AS FileSizeInMB,

    CASE max_size

    WHEN 0 THEN 'Autogrowth is off.'

    WHEN -1 THEN 'Autogrowth is on.'

    ELSE 'Log file grows to a maximum size of 2 TB.'

    END,

    growth AS 'GrowthValue',

    'GrowthIncrement' =

    CASE

    WHEN growth = 0 THEN 'Size is fixed.'

    WHEN growth > 0 AND is_percent_growth = 0

    THEN 'Growth value is in 8-KB pages.'

    ELSE 'Growth value is a percentage.'

    END

    FROM tempdb.sys.database_files;

    GO


    4.使用如下语句查看当前实例的tempdb的逻辑文件名称

    SELECT name, physical_name

    FROM sys.master_files

    WHERE database_id = DB_ID('tempdb');

图3 查看tempdb 

 

 5.在“文件”页签下查看tempdb在磁盘上的当前位置

图4 查看当前位置 


 6.使用如下语句将tempdb文件迁移到D盘RDSDBDATA文件夹的DATA目录下,根据实际需要指定初始大小和增长速度

    USE master;

    GO

    ALTER DATABASE [tempdb] MODIFY FILE (NAME = tempdev, FILENAME = 'D:\RDSDBDATA\DATA\tempdb.mdf', SIZE = 8MB, FILEGROWTH = 64MB);

    GO

    ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME = 'D:\RDSDBDATA\DATA\templog.ldf', SIZE = 8MB, FILEGROWTH = 64MB);

    GO

图5 迁移文件 

 

 7.在“实例管理”页面,选择指定的主实例,单击更多 > 重启实例”,也可以在“实例管理”页面,单击目标实例名称,在页面右上角,单击“重启实例”


 8.使用SQL语句查询文件是否迁移成功

    SELECT name, physical_name

    FROM sys.master_files

    WHERE database_id = DB_ID('tempdb');

图6 查看tempdb 


 9.根据实际需要配置文件名、初始大小、增长速度等信息,通过如下SQL语句或界面增加tempdb的文件数

    -- 根据CPU的数量增加tempdb文件数、初始大小和增长速度分别为8MB和64MB

    USE [master]

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp2', FILENAME = N'D:\RDSDBDATA\DATA\tempdb2.ndf', SIZE = 8MB, FILEGROWTH = 64MB)

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp3', FILENAME = N'D:\RDSDBDATA\DATA\tempdb3.ndf', SIZE = 8MB, FILEGROWTH = 64MB)

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp4', FILENAME = N'D:\RDSDBDATA\DATA\tempdb4.ndf', SIZE = 8MB, FILEGROWTH = 64MB)

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp5', FILENAME = N'D:\RDSDBDATA\DATA\tempdb5.ndf', SIZE = 8MB, FILEGROWTH = 64MB)

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp6', FILENAME = N'D:\RDSDBDATA\DATA\tempdb6.ndf', SIZE = 8MB, FILEGROWTH = 64MB)

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp7', FILENAME = N'D:\RDSDBDATA\DATA\tempdb7.ndf', SIZE = 8MB, FILEGROWTH = 64MB)

    GO

    ALTER DATABASE [tempdb] ADD FILE ( NAME = N'temp8', FILENAME = N'D:\RDSDBDATA\DATA\tempdb8.ndf', SIZE = 8MB, FILEGROWTH = 64MB)

    GO


    或在“文件”页签,单击“添加”,增加tempdb的文件数

    • 图7 添加tempdb文件 


    10.配置完成后,再次重启实例


 11.验证文件增加成功

图8 查看tempdb信息