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

  tempdb作为一个系统数据库，共享在当前实例正在运行的数据库之间，这里不对tempdb的基本概念做过多的介绍了，不过众所周知，tempdb在服务重启时是被重新创建的，drop-create,那么是什么来决定tempdb的data file、log file初始化大小呢？

  1.　　这里先提出一个问题：

　　1.tempdb的GUI界面可以设置初始化大小

　　2.model系统数据库作为模板数据库也决定着新建数据库的初始大小

那么到底是谁来决定tempdb的初始化大小？

 

tempdb tempdev初始化大小设置	model modeldev初始化大小设置	tempdb tempdev真实物理文件大小（重启服务后）
30MB	20MB	30MB
20MB	30MB	30MB
30MB	30MB	30MB

根据测试结果，说明tempdb的tempdev初始化大小的最终真实大小是tempdev和modeldev两者之间的较大值。

另外要注意，tempdb的templog以及新增ndf文件的初始化大小只由自身设置决定，和model没有关系。

2.　　第二个问题：tempdb的tempdev文件初始化的真实大小是否为tempdev的初始化大小？

USE tempdb;
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;

输出结果为：

tempdev初始大小20MB,modeldev初始大小30MB

tempdev初始大小30MB,modeldev初始大小20MB

结果说明

当modeldev初始大小设置30MB大于tempdev的初始大小20MB设置时，tempdb在初始化时起始大小为20MB，后续被扩充至30MB

反之没有发生初始化后续扩大的现象

 

3.　　那么引来最后一个问题：tempdb的文件扩增会有什么影响？如果有该如何避免？

当系统自动调整tempdb大小时，对文件的读写将暂时的阻塞，所以频繁的调整大小会对tempdb的使用产生负面影响，应该避免。

解决方法其实很简单，初始化tempdb大小设置个较合理的值即可，但是不代表随便设个很大的初始大小，应持续观察一段时间的tempdb使用情况，再决定一个合理的值，优化tempdb是个很大的话题，比如还可以通过增加多个ndf文件、优化使用tempdb操作，这里就不对其展开讨论了

 

转载于:https://www.cnblogs.com/SQLServer2012/archive/2012/11/21/2780317.html