Contenção de Tempdb, como resolver?

Olá pessoal, tudo certo?

Como no Brasil o ano só começa depois do carnaval, estamos de volta com as baterias recarregadas para mais um ano de muitos compartilhamento de informação e conhecimento.

Neste primeiro post do ano quero compartilhar com vocês a experiência que tive logo na primeira semana do ano. A principal instância da empresa que trabalho começou a ter sérios problemas de performance, ao realizar uma analise percebi que estava tendo uma quantidade muito elevada de PAGELATCH_UP, que significa que estava tendo uma contenção no Tempdb, especificamente uma contenção na PFS (Page Free Space), que é responsável por registrar o status de alocação de cada pagina, ou seja, responsável por mapear a quantidade de espaço livre tem cada pagina.

O que me chamou a atenção para o problema de contenção foi o resultado da execução da sp_whoisactive, mostrando vários wait_info de PAGELATCH_UP:tempdb:1(PFS).

Ao verificar o resultado acima executei um script que utilizo, desenvolvido pelo Paul Randal (link).

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',             N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',                N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',              N'CHECKPOINT_QUEUE',
        N'CHKPT',                           N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',                N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',              N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',           N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',                 N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                        N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT',     N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',               N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',            N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',                 N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',                  N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                    N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_SHUTDOWN_QUEUE',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH',     N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',               N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',                 N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',             N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',            N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',                N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',             N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP',     N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',           N'WAIT_FOR_RESULTS',
        N'WAITFOR',                         N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',              N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',             N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',              N'XE_TIMER_EVENT')
    AND [waiting_tasks_count] > 0
 )
SELECT
    MAX ([W1].[wait_type]) AS [WaitType],
    CAST (MAX ([W1].[WaitS]) AS DECIMAL (16,2)) AS [Wait_S],
    CAST (MAX ([W1].[ResourceS]) AS DECIMAL (16,2)) AS [Resource_S],
    CAST (MAX ([W1].[SignalS]) AS DECIMAL (16,2)) AS [Signal_S],
    MAX ([W1].[WaitCount]) AS [WaitCount],
    CAST (MAX ([W1].[Percentage]) AS DECIMAL (5,2)) AS [Percentage],
    CAST ((MAX ([W1].[WaitS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgWait_S],
    CAST ((MAX ([W1].[ResourceS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgRes_S],
    CAST ((MAX ([W1].[SignalS]) / MAX ([W1].[WaitCount])) AS DECIMAL (16,4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum]
HAVING SUM ([W2].[Percentage]) - MAX ([W1].[Percentage]) < 95; -- percentage threshold
GO

Que comprovou que o Wait Type que estava gerando o gargalo na minha instância era o PAGELATCH_UP.

Para identificar que estava tendo um problema de contenção na Tempdb executei um outro script no momento da lentidão, que identifica os wait_type e diz qual o tipo de recurso que ele esta gerando gargalo.

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

O resultado acima mostra que o database id 2, que é o Tempdb, o arquivo de dados 1, e a pagina 1 que é a PFS estava sofrendo com o PAGELATCH_UP e estava gerando a lentidão da instância.

Uma das possíveis solução e até mesmo evitar que este problema ocorra, é adicionar mais arquivos de dados ao Tempdb, para que o SQL Server possa distribuir as requisições.

Quando criamos vários arquivos de dados para o Tempdb o SQL Server passa a utilizar um algoritmo de preenchimento proporcional para determinar qual arquivo ele vai utilizar para cada requisição. Os arquivos devem ser configurados no mesmo tamanho e ter o mesmo autogrowth, eles podem estar no mesmo disco, mas recomendo se possível colocar em discos separados por questão de IO no disco.

Quanto a quantidade de arquivos de dados em linha geral a Microsoft recomenda 1 arquivo de dados para 1CPU lógica, se o numero de processadores lógicos for maior que 8, é recomendado usar 8 arquivos, porem se a contenção persistir aumente o numero de arquivos de dados em múltiplos de 4 até o numero de processadores lógicos (https://support.microsoft.com/pt-br/kb/2154845).

Para adicionar adicionar os arquivos de dados você pode fazer via GUI, ou por script, nesta demo como o meu servidor está configurado com 4 processadores estou adicionando mais 3 arquivos de dados totalizando 4 arquivos de dados para 4 processadores.

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'Tempdb_1_Data', FILENAME = N'H:\tempdb_1_data.ndf' , SIZE = 5120000KB , MAXSIZE = 15360000KB , FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'Tempdb_2_Data', FILENAME = N'I:\tempdb_2_data.ndf' , SIZE = 5120000KB , MAXSIZE = 15360000KB , FILEGROWTH = 1048576KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'Tempdb_3_Data', FILENAME = N'J:\tempdb_3_data.ndf' , SIZE = 5120000KB , MAXSIZE = 15360000KB , FILEGROWTH = 1049600KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 5120000KB , MAXSIZE = 15360000KB , FILEGROWTH = 1048576KB )
GO

Ou via interface gráfica.

Botão direito no tempdb

Propriedades – Files

Adiciona os arquivos, os arquivos pode ser adicionado todos no mesmo disco, mas por boa pratica é recomendado que coloque os arquivos em discos separados.

Após a adição dos novos arquivos é necessário reiniciar a instância, após reiniciar a instância o SQL Server vai começar a distribuir as requisições entre os arquivos,

O problema de performance por contenção do Tempdb foi solucionado na instância, venho acompanhando os contadores para verificar se tem tido muitas ocorrências de  PAGELATCH_UP, o que não vem acontecendo.

Bom por hoje é isso, e que o ano comece de maneira produtiva para todos e que Deus nos ajude.

Abraços,

Tiago Neves