认识Tempdb----排除日常问题

23ee

tempdb作为临时对象的共享资源，这一特性使得它比其他数据库更容易出现特定的性能问题。这里将介绍tempdb容易遭受的最常见的问题，以及如何进行故障排除，甚至避免它们。

Latch 竞争

与一般的数据库相比，tempdb作为临时存储区的使用使得工作负载模式可能包含数量不成比例的很多小对象的创建及摧毁。这种类型的工作负载能够导致一个数据库中需要分配对象的页上的Latch竞争。Latch是一个短期同步锁，用于SQL Server保护物理页。你不能像普通的锁一样通过改变隔离级别或使用提示来影响Latch，Latch是幕后自动被使用的，当内容或结构正在被改变或从磁盘读取时，它保护内存中的页免遭其他任务的修改。

分配页

当你在tempdb创建一个对象，如临时表的时候，它需分配空间，这和在正常数据库中创建表完全一样。你需要注意分配过程中的3种页：Page Free Space, Global Allocation Map, and Shared Global Allocation Map。

PFS (Page Free Space)

PFS页为每个页存储一个字节的信息，表明上面有多少可用空间，以及用于什么，这意味着单个PFS页能够存储64MB页的信息。因此，你可以在大约64MB间隔的空间里发现一个新PFS页，它贯穿一个数据库的数据文件。任何数据库数据文件的第一个页总是一个PFS页，因此，很容易认出一个错误信息中的页。如果你在什么地方看到“2:1:1”，它就表示database_id 2（tempdb）中第一个数据文件的第一个页；“5:3:1”表示database_id 5中file_id 3的第一个PFS页。

GAM（Global Allocation Map）

GAM页每个范围（一个范围是8个页）存储1比特，表明哪些范围在使用，哪些是空的。SQL Server通过读取GAM页找到可用空间，并把一整个范围分配给一个对象。一个范围仅存储一个比特（而不是像PFS页，PFS是一个页一个字节），意味着单个GAM页能够追踪更多空间，在一个数据文件中，你可以在大约4GB间隔的空间找到一个新GAM页。然而，数据文件中的第一个GAM页的页码总是2，因此“2:1:2”就表示tempdb中的第一个GAM页。

SGAM (Shared Global Allocation Map)

SGAM页也是一个范围存储一个比特，但是该值表示该范围是不是与可用空间或一个完整范围混合的范围。SQL Server通过读取SGAM页来找到与可用空间混合的范围来把空间分配给小对象。单个SGAM页能够追踪4GB的空间，因此，你可以在4GB的间隔中找到它们，就像GAM页一样。在数据文件中，第一个SGAM页是页3，所以“2:1:3”就表示tempdb的第一个SGAM页。

分配页竞争（Allocation Page Contention）

设想你操作应用程式创建一个临时表，要确定在tempdb什么地方创建表，SQL Server会读取SGAM页（2:1:3）找到一个和可用空间混合的范围分配给该表。SQL Server在更新SGAM页时会在其上面加了一个EX Latch，然后去读取PFS页，在范围里找可用的页去分配给对象。EX Latch也会加到PFS页上，以确保没其他对象分配到同一数据页。当更新完成后，Latch会释放掉。这是一个非常简单的过程，运行很好，直到tempdb因为分配请求变得超负载。阈值很难预测，下一节会讲述一些实践，以便主动避免它。

问题本身显示的是一个PAGELATCH等待，资源描述是2:1:1或2:1:3。图8-11显示了因为多个用户同时试图分配多个对象而引起的分配页的竞争。


下面的脚本过滤出所有的GAGELATCH等待，并显示每个页是不是PFS、GAM或SGAM。如果你发现多个任务在等待PAGELATCH，就说明存在分配页竞争。


    WITH TASKS  
    AS (SELECT session_id,  
    wait_type,  
    wait_duration_ms,  
    blocking_session_id,  
    resource_description,  
    PageID = Cast(Right(resource_description, Len(resource_description)-  
    Charindex(':', resource_description, 3)) As Int)  
    From sys.dm_os_waiting_tasks  
    Where wait_type Like 'PAGE%LATCH_%'  
    And resource_description Like '2:%')  
    SELECT session_id,  
    wait_type,  
    wait_duration_ms,  
    blocking_session_id,  
    resource_description,  
    ResourceType = Case  
    When PageID = 1 Or PageID % 8088 = 0 Then 'Is PFS Page'  
    When PageID = 2 Or PageID % 511232 = 0 Then 'Is GAM Page'  
    When PageID = 3 Or (PageID - 1) % 511232 = 0 Then 'Is SGAM Page'  
    Else 'Is Not PFS, GAM, or SGAM page'  
    End  
    From Tasks ;  

解决或避免分配页竞争的问题

一旦你确定tempdb正在遭受分配页竞争，你就可以用下面几种不同的方法降低发生的可能性：

    Multiple Tempdb Data Files------如果你是DBA，你或许首先倾向于这个解决方案。每个数据文的开头都有一个分配页的集合，所以，如果你有不只一个文件，那么就能平衡负载。不管怎样，对于你的实例而言，有多个tempdb文件是一个不错的最佳实践，因为很容易做到这点，无风险地降低了竞争发生的可能性。有多个数据文件时，tempdb通过使用成比例填充算法（proportional fill algorithm）来平衡贯穿所有文件的可用空间量。这种做法的效果是选择有最多可用空间的文件，直到该文件和所有其他文件的空间相当。
    Temporary Object Reuse------如果你是开发者，你首先要做的可能是优化代码，而不是回顾服务器最佳实践。从SQL Server 2005开始，就可以缓存临时对象的定义以便在需要再次创建的时候复用。再具体一点，就是缓存IAM页（Index Allocation Map）和 一个范围。被复用的对象不需要分配新空间，因此不会有助于任何分配问题。优化代码，确保临时表被缓存，将有助于降低任何潜在的问题。SQL Server默认缓存临时表，所以你首先要检查SQL Server是否缓存了你的临时表。要做到这点，你可以循环跑你的代码，并监视性能监视器计数器“temp table creation rate”在循环开始和结束时的不同。你可以通过如下的脚本来监视：

        SET NOCOUNT ON ;  
        GO  
        DECLARE @table_counter_before_test BIGINT ;  
        SELECT @table_counter_before_test = cntr_value  
        FROM sys.dm_os_performance_counters  
        WHERE counter_name = 'Temp Tables Creation Rate' ;  
        DECLARE @i INT = 0 ;  
        WHILE ( @i < 10 )  
        BEGIN  
        EXEC tempdbdemo.dbo.usp_loop_temp_table ;  
        SELECT @i += 1 ;  
        END ;  
        DECLARE @table_counter_after_test BIGINT ;  
        SELECT @table_counter_after_test = cntr_value  
        FROM sys.dm_os_performance_counters  
        WHERE counter_name = 'Temp Tables Creation Rate' ;  
        PRINT 'Temp tables created during the test: '  
        + CONVERT(VARCHAR(100), @table_counter_after_test  
        - @table_counter_before_test) ;  
    只要下面某项成立，临时对象就会被缓存：
    *指定的约束没有创建；
    *DDL语句影响了表，例如CREATE INDEX或CREATE STATISTICS，在表被创建之后没有运行
    *对象不是使用动态SQL创建的，例如使用sp_executesql
    *对象是在另一个对象里面创建的，例如存储过程、触发器、UDF、用户自定义标志函数返回的表
    为了不破坏缓存的规则，我们可以另辟蹊径，例如下面的code：
   
        CREATE PROCEDURE [dbo].[usp_temp_table]  
        AS  
        CREATE TABLE #tmpTable  
        (  
        c1 INT UNIQUE CLUSTERED, --可以缓存  
        c2 INT,  
        c3 CHAR(5000)  
        ) ;  
        --CREATE UNIQUE CLUSTERED INDEX cix_c1 ON #tmptable ( c1 ) ;  --破坏了规则，不能缓存  
        DECLARE @i INT = 0 ;  
        WHILE ( @i < 10 )  
        BEGIN  
        INSERT INTO #tmpTable ( c1, c2, c3 )  
        VALUES ( @i, @i + 100, 'coeo' ) ;  
        SET @i += 1 ;  
        END ;  
        GO  
    跟踪标记 1118------SQL Server 2000已引入，用于帮助减轻SGAM页（2:1:3）上面的竞争，通过在所有数据库中禁用混合范围分配。启用这个跟踪标记后，你创建的每个对象将被分配它自己的范围。SQL Server 2008对混合范围中分配空间引入了改进的算法，因此，你不大可能再SQL Server 2012中遇到这个问题。

监视tempdb I/O性能
核查I/O系统性能的最简单的方法是看I/O请求的延迟。有两种方法来测量磁盘延迟：使用Performance Monitor和使用DMV。

Performance Monitor------你应该感兴趣的PerfMon计数器有Avg. Disk sec/Transfer、Avg. Disk sec/Read和Avg. Disk sec/Write。这3个计数器在Logical Disk下面。

DMV------下面的脚本计算自从SQL Server最后一次启动到现在的对所有数据库文件的读写延迟：


    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(-1, -1)  
    WHERE num_of_reads > 0  
    AND num_of_writes > 0 ;  

阈值----对于包含SQL Server数据库文件的磁盘延迟，微软建议如下性能阈值：

数据库数据文件------目标：<10ms     可接受：10-20ms   不可接受：>20ms

数据库日志文件------目标：<5ms       可接受：5-15ms     不可接受：>15ms
20ms是个好的标准，除非SQL Server花费很多时间等待I/O请求。

故障排除空间问题（Troubleshooting Space Issues）

一个实例上的所有数据库必须共享仅仅一个tempdb，了解tempdb中谁在做什么就显得更加重要，所以，微软提供了3个DMV，让你能够做到这点。

sys.dm_db_file_space_usage--------提供tempdb中所分配的页的数量和类型，允许你看到贯穿数据文件的页的分布。你也可以使用这个DMV对贯穿所有文件的值求和，来获取tempdb使用的单个视图，万一发生意想不到的使用时，这可以帮你缩小问题的范围。脚本如下（user, internal和version页都仅仅来自统一的范围（uniform extents））：


    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使用的细节。任务开始时，值设置为0；任务完成时值被删除。因此，它有助于解决当前正在执行的任务的实况问题。例如，下面的脚本会返回前5个在tempdb正在使用空间的对话，以使用空间总量排序：


    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  

sys.dm_db_session_space_usage------当任务完成，sys.dm_db_task_usage的值会被对话合计，这些合计的值就可以使用sys.dm_db_session_space_usage来查看。下面的代码展示如何使用这个DMV，显示所有的对话，以总的tempdb使用排序：


    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 ;  

它的输出不包括任何正在执行的任务，因此对于实况问题不是很有用，但是你可以在sys.dm_exec_requests中查找session_id，来搜集谁在使用该session_id相关的信息，例如登录细节、连接的服务器，及正在使用的应用系统。