Melhores Praticas com TEMPDB

Faaaaaaala Galera,

.:: The Mission Today is: TEMPDB ::.

 

TEMPDB é um assunto recorrente no dia a dia do DBA, como aplicar as melhores praticas? como minimizar o ponto de falha? pontos de gargalos, entre outros…

TEMPDB é um banco de sistema, os chamados System Databases e é considerado um recurso global a nível de instancia do SQL Server. Ele é recriado toda vez que iniciamos o serviço e sem ele o SQL não inicia, como assim? Isso mesmo, se por algum motivo o SQL não conseguir recriar o Tempdb durante o processo de start, o SQL não sobe. Por isso e mais um pouco podemos considerar como um ponto de atenção e monitoramento importante.

ErrorLog SQL Server: Perdendo o Tempdb no Start.

Clearing tempdb database.
Error: 5123, Severity: 16, State: 1.
CREATE FILE encountered operating system error 3(O sistema não pode encontrar o caminho especificado.) while attempting to open or create the physical file
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Tempdb\tempdb.mdf for file number 1.
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file “C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\Tempdb\tempdb.mdf”. Operating system error 3.
Error: 1802, Severity: 16, State: 4.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server.
SQL Server shutdown has been initiated
SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

 

As funções deste banco foram aumentando durante cada ciclo de atualização da Engine e agregando mais importância dentro da instancia, dentre suas funções segue as quais acredito ser mais importantes:

=> Manter objetos temporários como, Tempory Table Locais (#) e Globais (##).
=> Armazenamento de variáveis do tipo tabela @Table.
=> Controle de Cursores durante execução.
=> Utilizado na Criação/Rebuild de índices quando especificado a clausula (SORT_IN_TEMPDB).
=> Objetos e processamentos internos da Engine como, Sorting, Works Tables, Controle de Versionamento de linhas e objetos, Multiple Active Result Sets (MARS), After Trigger.
=> DBCC CheckDB utiliza as tabelas de trabalho do Tempdb para manter os resultados intermediários e para operações de Sorting.
=> Service Broker.

E entre outras features que possam se apoderar dos poderes do Tempdb. Outro exemplo interessante é referente a “estimativa errada” de memoria alocada para execução de uma query. A grosso modo quando o QE (Query Execution) estima a quantidade “errada” de memoria, acontece o processo de Query Memory Spill e o SQL passa a utilizar o Tempdb como “extensão” da RAM para concluir a execução da Query. Mais detalhes aqui.
Best-Practices.jpg

 

Um pouco de Best Practices no Tempdb:

Sabe a GAM, SGAM? O TEMPDB é um dos bancos mais propicio a sofrer com contenção de alocação de paginas, devido a grande utilização do recurso. Este artigo do Paul Randal detalha com clareza os conceitos de Allocation Maps dentro da Storage Engine.

Falar sobre boas praticas é fundamental, se você ainda não teve problemas de performance que estava relacionado ao TEMPDB, não se alegre, certamente esse dia chegará (hahaha não estou jogando praga).

Começando pelo Sizing do TEMPDB, qual o tamanho ideal?
A resposta é a padrão: DEPENDE. Definir o tamanho é sempre um problema, ainda mais em ambientes novos onde não sabemos qual será a real utilização de carga no SQL Server.

Lendo vários artigos a respeito, observei uma regra referente ao tamanho.
Como Já dizia Brent Ozar: Definir inicialmente 10 GB para cada arquivo da dados e 20 GB para arquivos de log. Desta forma, evitaríamos a fragmentação e com o tempo de monitoramentos, Baselines você saberá se este valor se encaixa no seu ambiente ou não. E poderá alterar caso seja necessário.

Referente a quantidade de arquivos (MDF) para o TEMPDB, também já vi vários assuntos e até palestras focadas no Tempdb e este é sempre um ponto interessante. Existe uma logica baseando-se na quantidade de cores do servidor, onde a intenção é evitar as contenções de paginas, abrindo mais a “boca” do tempdb e melhorando a performance.

.:: CPU Cores ::. .:: TempDb Data Files ::.
2 2
4 4
8 8
32 8

Segue Script para criação dos Datafiles, baseando-se na logica acima:

USE Tempdb
GO

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’tempdev’, SIZE = 1GB, FILEGROWTH = 512MB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N’templog’, SIZE = 1GB, FILEGROWTH = 512MB )
GO

IF (SELECT COUNT(1) FROM sys.database_files) = 2
BEGIN
DECLARE
@physical_name VARCHAR(200),
@cpus TINYINT

SELECT @cpus = cpu_count FROM sys.dm_os_sys_info
SELECT
@physical_name = REPLACE(physical_name, REVERSE(SUBSTRING(REVERSE(physical_name), 1, CHARINDEX(‘\’, REVERSE(physical_name)) – 1)), ”)
FROM sys.database_files
WHERE name = ‘tempdev’

— Cria os arquivos de acordo com a quantidade de CPUs
IF @cpus >= 2
BEGIN
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev02, FILENAME = ”’ + @physical_name + ‘tempdb02.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
END
IF @cpus >= 4
BEGIN
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev03, FILENAME = ”’ + @physical_name + ‘tempdb03.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev04, FILENAME = ”’ + @physical_name + ‘tempdb04.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
END
IF @cpus >= 8
BEGIN
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev05, FILENAME = ”’ + @physical_name + ‘tempdb05.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev06, FILENAME = ”’ + @physical_name + ‘tempdb06.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev07, FILENAME = ”’ + @physical_name + ‘tempdb07.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
EXEC (‘ALTER DATABASE tempdb ADD FILE (NAME = tempdev08, FILENAME = ”’ + @physical_name + ‘tempdb08.ndf”, SIZE = 1GB, FILEGROWTH = 512MB)’)
END
RAISERROR (‘Arquivos criados com sucesso’, 10, 1)
END
ELSE
RAISERROR (‘O tempdb já possui mais que 2 arquivos, analise e crie manualmente’, 10, 1)

Outro ponto dentro das boas praticas, é referente aos Trace Flags 1117 e 1118.

O TF 1117 faz com que todos os data files trabalhem simultaneamente no caso de um Autogrowth, garantindo o tamanho uniforme dos arquivos para o funcionamento do Proportional Fill.

O TF 1118 está intimamente ligado a alocação de paginas e Extents. Rapidamente, Extents é unidade logica dentro do SQL Server para alocação de paginas. Cada Extents comporta até 64kb, portando cada extents tem 8 paginas de 8.060 bytes. A utilização do TF 1118 se aplica ao modo como Extend será alocado, no modo “Mixed Extents” ou “Full Extents”. Mais sobre Extents.

Kendra Little escreveu um artigo muito interessante sobre a utilização destes Traces Flags.

Esses Traces Flags são muito interessante e por ser considerado como boa pratica, a Microsoft tornou esse comportamento padrão no SQL Server 2016. Agora não será mais preciso habilitar o TF 1117 e 1118 na mão para o Tempdb. Corrigindo o problema de contenção dos mapas de alocação.

Bonus: Script que retorna a utilização dos data files do Tempdb.

SELECT files.physical_name, files.name,
stats.num_of_writes, (1.0 * stats.io_stall_write_ms / stats.num_of_writes) AS avg_write_stall_ms,
stats.num_of_reads, (1.0 * stats.io_stall_read_ms / stats.num_of_reads) AS avg_read_stall_ms
FROM sys.dm_io_virtual_file_stats(2, NULL) as stats
INNER JOIN master.sys.master_files AS files
ON stats.database_id = files.database_id
AND stats.file_id = files.file_id
WHERE files.type_desc = ‘ROWS’

Outro ponto importante, é referente  ao disco onde o Tempdb será alocado fisicamente. Qual tipo de disco e RAID é mais indicado?, Bora lá…

Se me perguntar qual disco, diria logo de cara que um SSD é a melhor opção (hahaha) Calma, calma eu sei que SSD é um disco nobre e nem todas as empresas disponibilizam deste recurso, então na falta do SSD, um disco SAS nobre de 15k serviria. Claro, isso é uma recomendação e não uma regra, pode depende de ambiente para ambiente.

Quanto ao RAID, este é um assunto muito interessante, neste post comentei sobre os conceitos e suas diversas variações. Mas, para Tempdb que é o que importa agora, o recomendado é o RAID 0 ou 10, claro tudo depende da sua infra e configurações de Storage e novamente pode existir variações de ambiente para ambiente.

Como ultimo ponto, é sobre a separação dos arquivos de dados (MDF, NDF) e Log (LDF), isso cabe uma grande discussão, pois na maioria dos casos não temos muitos discos nobres disponíveis e a divisão física desses arquivos em unidades diferentes, faria o pessoal de Storage ficar mais bravo ainda. Mas sem duvida, sempre é uma boa pratica separar dados de log, por n motivos

Agora passo a bola para vocês, existe alguma boa pratica que você utiliza em seu ambiente? alguma particularidade ?

 

Grande abraços …. []s

Anúncios

Um comentário sobre “Melhores Praticas com TEMPDB

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s