tempdb数据库简介及优化

                                                             ps:msdn摘
 tempdb数据库是一个特殊的数据库,它和其他数据库最大的不同之处在于SQL SERVER每次重启的时候它会被重建(不是恢复).

 

tempdb的特点:
 1.总是使用简单恢复模式.日志不断的被截断,因为需不要备份.它记录的信息只能用来回滚事务(只有最初数据,不包含更新后的新行),担不是恢复事务.

 2.某些数据库选项无法设置,比如OFFLINE READONLY CHECKSUM

 3.无法被删除

 4.无法为其创建快照,因为不能恢复TEMPDB

 

tempdb的对象：
 1.用户对象：局部和全局的临时表,表变量,表值函数中返回的表等;

 

 2.内部对象：元数据存储在内存中.
  a.工作表：用于存储假脱机中间结果的工作表；运行DBCC CHECKDB 等；使用XML及varchar(max)；处理SQL SERVER Broker对象;使用静态或者键集游标
  b.工作文件:SQL SERVER处理一个使用哈希操作符的查询时
  c.排序单元:在使用ORDER BY 子句；建立索引;某些类型的链接;排序单元在数据排序时被创建在tempdb中保持那些数据.

 

 3.版本库：在以下情况下,更新的数据行的老版本将保存在tempdb中.
  a.当AFTER 触发器触发的时候;
  b.当一个DML命令在一个允许快照级别的食物的数据库中执行时；
  c.当客户端调用多个活动结果集；
  d.新建或者重建一个索引期间,在该索引上进行DML操作.

 下面列出了 SQL Server 中创建 tempdb 的用户对象、内部对象或行版本的功能。只要有可能，便会提供估计磁盘空间使用量的方法
   ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_1devconc/html/3065e567-dbeb-4770-9835-c6e1b44595b8.htm（2008SQL MSDN本地）
   
tempdb的优化:
 1.将 tempdb 的恢复模式设置为 SIMPLE。此模式自动回收日志空间以保持较小的空间要求。

 2.使 tempdb 文件的大小可以根据需要自动增大。这可以使文件的大小增大到磁盘变满为止
 3.将文件增量设置为合理的大小以避免 tempdb 数据库文件的增量过小。如果文件的增量与写入 tempdb 的数据量相比过小，则 tempdb 可能需要不断扩大。这将影响性能
  建议为 tempdb 文件设置 FILEGROWTH 增量时遵循以下通用原则:
  tempdb 文件大小  FILEGROWTH 增量  
      0 至 100 MB        10 MB
     100 至 200 MB       20 MB
   200 MB 或更多       10%

 4.通过将文件大小设置为足够容纳环境中典型工作负荷的值来预分配所有 tempdb 文件的空间。这可以避免 tempdb 因扩展得过于频繁而影响性能。

 5.根据需要创建足够多的文件以使磁盘宽度最大化。使用多个文件可以减少 tempdb 存储争用并获得更大的可伸缩性。
  但是，请勿创建过多的文件，因为此操作可能降低性能并增加管理开销。
  作为通用原则，为服务器中的每一个 CPU 创建一个数据文件，然后根据需要上下调整文件的数量。

 6.使每个数据文件的大小相同，这样可以优化比例填充的性能。

 7.将 tempdb 数据库放置在快速 I/O 子系统中。如果有许多直接连接的磁盘，则请使用磁盘条带化。

 8.将 tempdb 数据库放置在用户数据库使用的磁盘以外的磁盘中。
 
查看 tempdb 大小和增长参数:

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 will grow to a maximum size of 2 TB.'

    END,

    growth AS 'GrowthValue',

    'GrowthIncrement' = 

        CASE

            WHEN growth = 0 THEN 'Size is fixed and will not grow.'

            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

--A. 将tempdb数据库的主数据文件大小设置为10MB。

ALTER DATABASE tempdb

MODIFY FILE(

    name=tempdev,

    size=100 MB)

GO

 

--B. 将tempdb数据库的主数据文件移动到指定的磁盘分区上，并且为其添加一个数据文件。

--移动主数据文件

ALTER DATABASE tempdb MODIFY FILE

  ( NAME='tempdev',

   FILENAME ='d:\tempdb.mdf')