【基本优化实践】【1.4】tempdb优化

【1】tempdb介绍

tempdb全局存储内部对象，用户对象，临时表，临时对象，以及SQL Server操作创建的存储过程。每个数据库实例只有一个tempdb，所以可能存在性能以及磁盘空间瓶颈。

各种形式的可用空间及过度饿DDL/DML操作都会导致tempdb负载过重。这会导致运行在服务器上不相干程序运行缓慢或者运行失败。

　　tempdb的一些常见通病如下：

　　--耗完了tempdb的所有存储空间

　　--读取tempdb时的I/O瓶颈造成的查询运行缓慢。

　　--过度的DDL操作造成在系统表上的瓶颈。

　　--分配竞争

　　在我们开始诊断问题之前，让我们首先看一下tempdb的空间都用在了哪些地方。可以分成四个主要的类比：

类别	描述
用户对象	这些是明确地由用户创建并且在系统类别中进行追踪。他们包括下面的： --表及索引 --全局性质的临时表（##t1）以及索引 --局部临时表（#t1）及索引　　会话范围的　　存储过程范围的 --表变量（@t1）　　会话范围的　　存储过程范围的
内部对象	SQL Server在运行查询的时候会创建或销毁许多语句范围的对象。这些没有在系统类别中被追踪。他们包括以下内容： --工作文件（hash连接） --排序运行 --工作表（游标，池以及临时的大对象数据类型（LOB）存储） --这里有两种情况除外：临时的大对象存储是批处理范围的，另一是游标工作表是会话范围的
版本存储	这个被用来存储行版本。MARS，在线索引，触发器，以及快照隔离级别都是基于行版本的。
空闲空间	这个显示出了可用于tempdb的磁盘空间

　　总的tempdb空间=用户对象+内部对象+存储的版本信息+空闲空间。

　　这个空闲空间大小跟tempdb性能计数器上空闲空间是一样的。

监测tempdb空间

　　提前避免问题的发生总是比出现问题之后再去解决要好的多。你可以使用Free Space in tempdb(kb)性能计数器去监测正在使用的tempdb空间数量。这个计数器以kb为单位追踪空闲空间。管理员可以使用这个计数器去判断tempdb是否因为空闲空间倒置运行缓慢。

　　然而，明确前面提到的四种类别如何使用tempdb磁盘空间的，就显得更有趣也更有效。

　　下面的查询语句返回用户及内部对象使用的tempdb空间。

--查看tempdb的空间使用分布情况
select SUM(user_object_reserved_page_count)*8 as user_objects_kb, 
SUM(internal_object_reserved_page_count)*8 as internal_objects_kb, 
SUM(version_store_reserved_page_count)*8 as version_store_kb, 
SUM(unallocated_extent_page_count)*8 as freespace_kb 
from sys.dm_db_file_space_usage  where database_id=2

--查看数据库初始大小，现在大小，初始与现在的大小差
USE db_tank;
WITH    cte
          AS (
              SELECT    DB_NAME(database_id) AS name,
                        mf.name AS db_filename,
                        mf.physical_name,
                        CAST((mf.size / 128.0) AS DECIMAL(20, 2)) AS initial_size_MB,
                        CAST((df.size / 128.0) AS DECIMAL(20, 2)) AS actual_size_MB,
                        CASE mf.is_percent_growth
                          WHEN 0 THEN STR(CAST((mf.growth / 128.0) AS DECIMAL(10, 2))) + ' MB'
                          WHEN 1 THEN STR(mf.growth) + '%'
                        END AS auto_grow_setting
              FROM      sys.master_files mf
                        JOIN sys.database_files df ON mf.name = df.name
              WHERE     mf.database_id = DB_ID()
             )
    SELECT  *,
            actual_size_MB - initial_size_MB AS change_in_MB_since_restart
    FROM    cte;

【2】如何在SQL Server中优化Tempdb

注意tempdb似乎很明显。但是怎么样？我总结了一些规则：

1.每次重新启动后都会重建tempdb，从而为tempdb提供足够大的初始大小。

参考：调整TEMPDB文件大小

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 64MB, FILEGROWTH = 64MB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 64MB, FILEGROWTH = 64MB )
GO

参考：SQL Server TempDB初始化大小是如何决定的

2.由于经常访问tempdb并将tempdb外包给另一个驱动器是加速访问的一个很好的措施。

　　用于移动tempdb文件的脚本。

--check the file path
select *from master.sys.master_files where database_id=2

ALTER DATABASE tempdb modify file
(NAME =tempdev,FILENAME ='D:dandantangsystemdb	empdb.mdf');

ALTER DATABASE tempdb modify file
(NAME = templog,FILENAME ='D:dandantangsystemdb	emplog.ldf');

--然后重启引起服务即可

最佳实践（迁移到用户数据库相同目录下去）

use tempdb
go
dbcc shrinkfile('tempdev',1000);
dbcc shrinkfile('templog',1000);
use db_tank;
declare @path Nvarchar(500),@path_mdf  Nvarchar(500),@path_ldf  Nvarchar(500),@sql nvarchar(4000);
select @path=physical_name from sys.database_files where type_desc='rows';
set @path=CAST(left(@path,len(@path)-charindex('',reverse(@path))+1) AS NVARCHAR);
set @path_mdf=@path+N'tempdb.mdf';
set @path_ldf=@path+N'templog.ldf';
print @path+','+@path_mdf+','+@path_ldf
set @sql='
alter database tempdb modify file( name =''tempdev'',filename='''+@path_mdf+''',filegrowth=128MB);
alter database tempdb modify file( name =''templog'',filename='''+@path_ldf+''',filegrowth=128MB);
'
exec(@sql)

3. 始终为tempdb提供足够的容量。让它自动增长。

　在1中已经操作过。　参考：SQL Server TempDB初始化大小是如何决定的

4.将恢复模式设置为“简单”。并非所有内容都记录在事务日志中，这意味着当事务完成时它将从事务日志中删除。因此，日志文件不会继续不必要地增长。

alter database tempdb set recovery simple;

5.可以优化创建附加数据文件以访问数据载体，因为可以避免这种存储冲突。有一个pi *拇指规则：CPU数量等于文件数量。这改善了对数据载体的访问。

　　不建议CPU数量等于文件数量，多几个即可，但强烈建议开启7中的文件分配，且应该和1中的大小一致

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( 
    NAME = N'tempdev_1', 
    FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQLOLAPDataMSSQL.1MSSQLDATA	empdev_1.ndf' , 
    SIZE = 8192KB , 
    FILEGROWTH = 20480KB 
) to filegroup primary
GO
-- 参考：文件组与文件操作

6.如果您使用多个指定相同文件大小的文件。这有利于文件的比例填充。

--1117 开启该跟踪标记； 最佳实践 DBCC TRACEON(2203,1117,-1)  
--释义：同一个文件组内的多个文件插入数据的时候会同时增长，而不是只增长第一个数据文件

（参考：跟踪标记）

7.禁用自动更新以提高SQL数据库中临时数据的性能。

--这个操作一般不要做，很影响性能
USE [master]
GO
ALTER DATABASE [DB_Mart] SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT
GO
ALTER DATABASE [DB_Mart] SET AUTO_UPDATE_STATISTICS OFF 
GO

8.开启IFI（即时文件初始化）

　　详情参考：即时文件初始化（IFI）

参考文件

tempdb无法收缩：https://www.cnblogs.com/gered/p/12175290.html