SQLServer数据库优化与管理——TempDB

TempDB是一个临时数据库，但是它一点都不“临时”，从SQL Server 2000到SQL
Server 2014，它一直存在，而且，从SQL Server 2005开始，其作用有了很大的提升，但
也恰恰是因为这种提升，使其经常成为性能瓶颈。所有负责任的性能优化书都会包含
TempDB的内容，可见 TempDB的重要性。本章将专门讲述这-重要的系统数据库及一些
相关配置和性能优化方面的知识。
TempDB是-个系统数据库。它只有Simple恢复模式，也就是说，它是自动截断模式
的数据库，事务-旦提交、回滚，就会写人 TempDB 的数据文件中。它的结构和其他数据
库无异，主要用于存放局部临时表或全局临时表、表变量及一些临时用法（如 hash表等)。
但是基于临时特性，每次服务器或者服务重启之后，都会按照Model库的配置重新创建。
这个库是日常使用中很好的一个“验证”场所，你可以把~些测试用的脚本或者代码在这
里运行，只要重启一下服务即可清空，几乎不会对现有系统有任何影响。

用户临时对象

由用户会话显式创建的对象，可以在系统目录中跟踪，主要句含以下内效
在TempDB中创建的实体表和上面的索引。不过注意这些表会在重启后清空。

全局临时表及上面的索引，以#开头的表。
局部临时表及上面的索引，以#开头的表。这些表可能是会话范围内的，也可能是
在存储过程范围内产生的。
表变量，以@开头。同样可能是在会话或者存储过程范围内产生的。"

临时表和表变量的主要区别在于统计信息，统计信息在本书很多地方已
经提及，这里不赘述。表变量不会创建统计信息，或者说，统计信息要么为0，要么为1。
优化器永远会把表变量当作里面只有1条数据或者没有数据的表来对待，本章后面将介绍
这部分对性能的影响。下面演示—下。开启实际执行计划，然后执行下面脚本:

DECLARE @TableVar TABLE ( c1 INT ) ;
INSERT INTO @TableVar 
SELECT TOP 1000000 row_number( ) OVER ( ORDER BY t1.number ) AS N 
FROM   master..spt_values t1  
CROSS JOIN master..spt_values t2 ;
SELECT  COUNT(*)
FROM    @TableVar ;

CREATE TABLE #TempTable ( c1 INT ) ;
INSERT INTO #TempTable 
SELECT TOP 1000000 row_number( ) OVER ( ORDER BY t1.number ) AS N 
FROM   master..spt_values t1  
CROSS JOIN master..spt_values t2 ;
SELECT  COUNT(*)
FROM    #TempTable ;

对于表变量和临时表的使用选择都应该进行测试。根据国外专家的经验，对于大数据
量，偏向使用临时表，小数据量(一-般来说小于100行）则可以使用表变量。

TempDB 上的常见问题及监控

TempDB经常是性能及磁盘空间问题的根源。常见的TempDB问题有:
耗尽TempDB空间。
因为TempDB的I/O瓶颈导致运行缓慢。
过多的DDL操作导致系统表上的瓶颈。

对于问题，预防胜于治疗。可以使用Free Space in TempDB(KB)这个性能计数器监
控TempDB的使用情况。下面使用sys.dn_db_file_space_usage来检查空间(检查的是
TempDB）

USE tempdb
GO
SELECT  SUM(total_page_count) * 8 / 1024 AS 'tempdb size (MB)' ,
        SUM(total_page_count) AS 'tempdb pages' ,
        SUM(allocated_extent_page_count) AS 'in use pages' ,
        SUM(user_object_reserved_page_count) AS 'user object pages' ,
        SUM(internal_object_reserved_page_count) AS 'internal object pages' ,
        SUM(mixed_extent_page_count) AS 'Total Mixed Extent Pages'
FROM    sys.dm_db_file_space_usage;

除此之外，还有sys.dm_db_task_space_usage可用，它提供 TempDB 中当前运行任务
的信息。但是这个值只存在于任务运行过程中，任务运行结束就会消失。不过它对于探究
当前执行的任务情况还是很有帮助的。

USE tempdb
GO
SELECT TOP 5
        *
FROM    sys.dm_db_task_space_usage
WHERE   session_id > 50
ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count

在任务完成之后，上面 DMV中的值会按会话汇总，然后可以通过sys.dm_db_session_
space_usage来查询，如下所示:

SELECT  *
FROM    sys.dm_db_session_space_usage
WHERE   session_id > 50
ORDER BY user_objects_alloc_page_count + internal_objects_alloc_page_count DESC ;

可以用上面的语句来查看会话的空间分配情况，但是这个不包含当前活动的任务。
上面只是进行了总体观察，下面来细分的监控。

1用户对象

由于用户对象不属于特定的会话，所以如果要确定TempDB 的空间中某对象使用了多
少空间，可以借助以下脚本来实现:

DECLARE userobj_cursor CURSOR FOR 
select 
     sys.schemas.name + '.' + sys.objects.name 
from sys.objects, sys.schemas
where object_id > 100 and 
      type_desc = 'USER_TABLE'and 
      sys.objects.schema_id = sys.schemas.schema_id
go

open userobj_cursor
go

declare @name varchar(256)
fetch userobj_cursor into @name
while (@@FETCH_STATUS = 0) 
begin
    exec sp_spaceused @objname = @name
        fetch userobj_cursor into @name	
end
close userobj_cursor

2.版本存储

行版本是可以跨会话的，行版本的创建者无法控制其空间的回收，如果想回收这部分
的空间，需要找到并停止运行时间最长的事务。下面的脚本是查找基于版本存储最长的两
个运行事务。

select top 2 
    transaction_id, 
    transaction_sequence_num, 
    elapsed_time_seconds 
from sys.dm_tran_active_snapshot_database_transactions
order by elapsed_time_seconds DESC

然后使用下面脚本来查看相应的会话ID，如果可以杀掉，就使用KILL SPID来终止会话。

SELECT session_id.
FROM
sys.dm_tran_session_transactions
WHERE
transaction_id =查询得到的SPID

3.内部对象

内部对象在每个语句内产生和销毁，如果发现TempDB有大量的空间分配，需要查找
哪个会话或者任务消耗了这些空间。SQL Server 2008提供了两个DMV来实现查找:sys.
dm_db_session_space_usage和sys.dm_db_task_space_usage。下面的脚本用于查找包含当前
任务的分配内部对象最多的用户会话。

SELECT 
    t1.session_id,
    (t1.internal_objects_alloc_page_count + task_alloc) as allocated,
    (t1.internal_objects_dealloc_page_count + task_dealloc) as	
    deallocated 
from sys.dm_db_session_space_usage as t1, 
    (select session_id, 
        sum(internal_objects_alloc_page_count)
            as task_alloc,
    sum (internal_objects_dealloc_page_count) as 
        task_dealloc 
      from sys.dm_db_task_space_usage group by session_id) as t2
where t1.session_id = t2.session_id and t1.session_id >50
order by allocated DESC

可使用下面的脚本来查看这些会话的信息:.

select 
    t1.session_id, 
    t1.request_id, 
    t1.task_alloc,
    t1.task_dealloc,
    t2.sql_handle, 
    t2.statement_start_offset, 
    t2.statement_end_offset, 
    t2.plan_handle
from (Select session_id, 
             request_id,
             sum(internal_objects_alloc_page_count) as task_alloc,
             sum (internal_objects_dealloc_page_count) as task_dealloc 
      from sys.dm_db_task_space_usage 
      group by session_id, request_id) as t1, 
      sys.dm_exec_requests as t2
where t1.session_id = t2.session_id and 
     (t1.request_id = t2.request_id)
order by t1.task_alloc DESC

最后可以使用以下脚本来查看:

select text from :sys.dmi_exec_sql_text(@sq1_handle)
select* from sys.dm__exec_query_plan(@plan_handle)

TempDB的I/O瓶颈

由于TempDB被广泛使用，加上其对磁盘的消耗较大，所以经常会成为I/O瓶颈。我
们需要做好前期的监控，以免出问题的时候无法联机处理(解决问题的很多操作需要脱机才
能进行，这在生产环境中基本上是不可能的)。监控TempDB的首选是性能监视器，除此之
外还有DMV 可以用。

SELECT  DB_NAME(database_id) AS 'Database Name',
        file_id,
        io_stall_read_ms / num_of_reads AS 'Avg Read Transfer/ms',
        io_stall_write_ms / num_of_writes AS 'Avg Write Transfer/ms'
FROM    sys.dm_io_virtual_file_stats(null, null)
WHERE   num_of_reads > 0
        AND num_of_writes > 0 ;