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

【1】tempdb介绍

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

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

　　tempdb的一些常见通病如下：

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

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

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

　　--分配竞争

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

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

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

监测tempdb空间

　　提前避免问题的发生总是比出现问题之后再去解决要好的多。你可以使用Free Space in tempdb(kb)性能计数器去监测正在使用的tempdb空间数量。这个计数器以kb为单位追踪空闲空间。管理员可以使用这个计数器去判断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

【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文件的脚本。

ALTER DATABASE tempdb

修改文件（NAME = tempdev，FILENAME =，E：DATAtempdb.mdf‘）;

ALTER DATABASE tempdb

修改文件（NAME = templog，FILENAME =，E：Datatemplog.ldf‘）;

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

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

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

alter database tempdb set recovery simple;

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

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）

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

原文：https://www.cnblogs.com/gered/p/11092248.html