关于tempdb的一些注意事项

    由于数据库的文件的位置对于I/O性能如此重要，以至于在创建主数据文件的文职时，需要考虑tempdb性能对系统性的影响，因为它是最动态的数据库，速度还需要最快。

  组成：有主数据文件和日志文件组成。从sql server2005开始添加了许多功能集的支持来创建用户对象、内部对象或版本。如：触发器、查询、快照隔离、多个活动结果集、联机索引创建、游标、lob参数、临时表、表变量和表-值函数等等。

   重启SQL Server,tempdb是唯一恢复为原始默认大小的数据库（8M），并且将根据使用率的要求继续增长。增长率一般应该被设为允许文件在两分钟内完成增长的数字。

   实时监控tempdb，可用下面的语句查询到5项执行任务，它们最大限度的使用tempdb:

select top 5 * from sys.dm_db_session_space_usage
order by (user_objects_alloc_page_count+internal_objects_alloc_page_count) desc

   推荐使用下面的查询来监控tempdb的大小，一般每周运行一次来查询使用情况，主要显示：内部对象、空闲空间、版本存储区和用户对象使用的空间：

select sum(user_object_reserved_page_count)*8 as 用户对象,sum(internal_object_reserved_page_count)*8 as 内部对象,
sum(version_store_reserved_page_count)*8 as 版本存储, sum(unallocated_extent_page_count)*8 as 空闲空间
from sys.dm_db_file_space_usage

查询结果如图：

 

配置步骤：

使用之前的查询和监控，根据我们的使用情况我们可以简要分成一下几个步骤来配置tempdb。

1.根据上面的语句来为tempdb来分配预先的空间，同时启动自增长功能来防止耗尽空间引起SQL Server 停止运行。

2.经验法则，每个cpu为每个实例创建tempdb数据文件的个数大小都相同，最多8个。

3.确保将tempdb设置为简单恢复模式。

4.自增长的值一般为初始大小的10%。

5.将tempdb 放在快速、专用的I/O子系统上。

6.使用SQL Server 代理或带有SQL Knowledge Pack 的MSCOM来创建监控环境的警报，以确保跟踪1101或1105（数据已满）。

其中主要监控这几计数器：SQLServer:Databases:LogFile(s)Size(KB)、SQLServer:Databases:DataFile(s)Size(KB)、SQLServer:Databases:Log File(s)Used(KB)。

7.使用即时数据库文件初始化。确保将SE_MANAGE_VOLUME_NAME 权限授予服务账户。或者使用管理员权限的账户运行。

另一个很好用的工具是 DMV sys.dm_db_task_space_usage.

总结：

     本篇我们简单介绍了如何规划设计和预警配置tempdb ，主要理解如何才能不至tempdb空间耗尽引起SQL Server 停止运行，同时如何配置使的性能得到更大提高。最后需要注意的是，警报任务完成后计数器重新定为0，另外还应该监控磁盘计数器Avg.Sec/Read和Avg.Sec/Write，小于10ms为很好，10-20ms为及格，20-50ms是缓慢，需要注意，大于50ms处于严重的IO瓶颈。