SQL Server TempDB Tutorial

By: Eric Blinn / Updated: 2020-07-17 / Comments (2) | Related: More > System Databases

Problem

SQL Server comes installed with four system databases by default. Eles são mestre, modelo, msdb, e TempDB. Preciso de saber o que é a base de dados do sistema “TempDB” e para que é utilizada. Estes tutoriais anteriores cobrammaster, model andmsdb em grande detalhe.

solução

esta ponta cobrirá a base de dados TempDB. O TempDB é um banco de dados que tem muitas funções dentro do servidor SQL, mas raramente é chamado explicitamente. Ele tem tantas funções que é muitas vezes um dos mais movimentados, se não o banco de dados mais movimentado na maioria das instâncias do servidor SQL. Leia mais para saber o que são muitas dessas funções!

SQL Server TempDB Overview

Qual é o propósito do SQL Server TempDB?

uma das funções do TempDB é agir como uma página ou um ficheiro de swap seria ao nível do sistema operativo. Se uma operação de servidor SQL é muito grande para ser concluída na memória ou se a concessão de memória inicial para uma consulta é muito pequena, a operação pode ser movida para o disco no TempDB.

outra função do TempDB é a de tabelas temporárias. Qualquer um que tenha criado uma tabela temporária em T-SQL usando um prefixo de libra ou hash ( # ) ou o prefixo doublepound/hash ( # # # ) criou um objeto no TempDB como este é o lugar onde eles estão estritos.

CREATE TABLE #MSSQLTips (RowID int);

Quando atrigger está a executar o inserted e deleted tabelas virtuais são storedin TempDB.

qualquer banco de dados que useREAD tenha cometido isolamento instantâneo (RCSI) terá a sua informação de versionamento futura armazenada no TempDB.

cada uma das funções acima do TempDB são completadas sem nunca realmente mencionar oempdb pelo nome e, portanto, podem ser usos surpreendentes para o TempDB.

TempDB também pode ser chamado explicitamente de algumas maneiras. Tables can be generatedin TempDB by referencing the database in a create statement. O código lookksexactly como uma operação DDL normal, mas quando executado no TempDB a tabela é,por definição, uma tabela temporária.

CREATE TABLE TempDB.dbo.MSSQLTips(RowID int);

TempDB é regenerada em cada início da instância do servidor SQL. Quaisquer projetos que possam ter sido criados no TempDB durante uma sessão anterior não se repetirão após um reinício do serviço. O TempDB obtém a sua lista de objectos inicial a partir da base de dados themodel, que geralmente estará vazia ou quase vazia.

a manutenção do Índice pode, opcionalmente, ser solicitada para fazer sortes no TempDB, em vez de tentar fazê-lo usando espaço livre na base de dados do utilizador.

ALTER INDEX ON . REBUILD WITH (SORT_IN_TEMPDB = ON);

posso executar o servidor SQL sem uma base de dados TempDB?

SQL Server cannot run (except in very extreme recovery scenarios and for veryshort periods) without a TempDB.

a versão do banco de dados TempDB é única para a versão, edição e patchlevel do servidor SQL?

TempDB é um banco de dados em branco e não há nada particularmente único sobre qualquer edição ou patch nível do servidor SQL.

houve apenas 2 mudanças significativas na TempDB nos últimos anos no nível de versionalidade. O primeiro é que a partir do SQL Server 2016 o comportamento do tempdb foi alterado de tal forma que permitir o trace flags T1117 e T1118 não são mais considerados uma melhor prática. O segundo é que a partir do SQL Server 2019 alguns objetos do sistema heavilyused no TempDB foram atualizados para tabelas de memória para reduzir o contention e melhorar o desempenho geral do servidor.

pode largar ou mudar o nome da Base de dados TempDB e deve fazê-lo por alguma razão?

TempDB não pode ser descartado, desligado, desligado ou renomeado. Tentar qualquer uma destas operações irá retornar um erro. Não há razão para que isto nunca deva ser tentado, uma vez que se trata de uma base de dados crítica do sistema.

localização da Base de dados TempDB do servidor SQL

que Ficheiros físicos e nomes suportam a base de dados TempDB?

the default logical filenames are tempdev for data and templog for log.Eles podem ser encontrados no disco como tempdb.mdf e templog.ldf, respectivamente. O TempDBcommonly tem muitos arquivos de dados.Quantos ficheiros de dados do TempDB devem existir?

deve existir um ficheiro de dados TempDB para cada thread/core/vCPU no instanciamento com um máximo de 8.

Onde posso encontrar a base de dados TempDB no disco e no SSMS?

os arquivos podem ser encontrados por querying sys.o ‘sysfiles’ dmv ou a área de ficheiros na janela de propostas de bases de dados.

SELECT * FROM TempDB.sys.sysfiles;

faz alguma diferença onde os arquivos de banco de dados do TempDB residem(eles precisam estar em C:\, SAN, etc.)?

as melhores práticas ditam que os seus ficheiros de dados TempDB devem estar no armazenamento mais rápido disponível devido aos seus níveis de actividade. Para algumas instâncias isso pode significar armazená-los com os arquivos de dados do banco de dados do usuário, mas um volume separado em disco separado é referenciado, se disponível.

existem opiniões diferentes no ficheiro de registo do TempDB, mas este autor prefere inserir este ficheiro com os outros ficheiros de registo de transacções.A base de dados TempDB pode ser movida?

a base de dados TempDB pode ser movida. O método para mover o TempDB é fácil de implementar. Basta modificar a propriedade do nome do(s) ficheiro (s) desejado (s). não é necessário mover ficheiros como acontece com as outras bases de dados do sistema. O SQLServer irá criar o(s) ficheiro (s) com o novo nome e/ou no novo local no início do serviço de texto. Apenas não se esqueça de voltar e apagar os arquivos TempDB agoraunused que serão deixados para trás.

ALTER DATABASE MODIFY FILE ( NAME = N'tempdev', FILENAME = N'G:\DATA\tmp.mdf');GOALTER DATABASE MODIFY FILE ( NAME = N'templog', FILENAME = N'H:\LOG\tmplog.ldf');GO

SQL Server TempDB database Size and Growth

How should the database and transaction log growth settings be configured for TempDB database?

a configuração do Auto-Crescimento deve ser definida para números específicos de MB, Não uma percentagem.Cada arquivo de dados deve ter o mesmo número de MBs definido para o crescimento.

Right-dimensioning the files for a database is a generally a best practice, but never more so than for TempDB. Monitore a instância-especialmente se for nova-e determine se os arquivos estão crescendo durante a operação normal.Se assim for, altere o tamanho inicial de tal modo que o servidor SQL não será forçado a aumentar os arquivos consistentemente após cada início de serviço.

Qual é o tamanho típico da Base de dados TempDB e qual o tamanho dela?Não há uma boa resposta para esta pergunta. O que é típico para um exemplo pode não ser típico para outro. Este autor tem visto casos em que o TempDB é basically-unused média de apenas alguns MB de espaço usado em qualquer momento. No entanto, outros estão constantemente sendo questionados e usando centenas de GB de espaço – mesmo no espaço medido em TB – especialmente durante uma janela de manutenção de índice.

ao decidir quanto espaço dedicar ao TempDB em uma nova instância, há poucas coisas que podem ser feitas. O novo caso está a substituir um caso antigo existente? Se assim for, use um traço perfmon ou captura agendada DMV para determinar o espaço usado no TempDB ao longo de vários dias. Certifique-se de incluir uma janela de manutenção nessa captura. Tente alocar espaço suficiente na nova instância para contabilizar o maior espaço usado capturado durante o trace com margem de manobra adicional. Em seguida, tamanho certo os arquivos para caber nessa alocação de espaço.

as 2 perguntas abaixo podem ajudar. O primeiro irá tirar uma foto do tamanho dos dados e arquivos de log junto com o espaço usado dentro dos arquivos. Isto pode ser executado em uma base regular para acompanhar o uso ao longo do tempo. A segunda consulta irá reverter o número de vezes que o arquivo de log cresceu desde a última vez que o instanciamento foi reiniciado.

Se não houver uma carga de trabalho existente que pode ser usado como um guia e thereisn não de outras orientações, talvez a partir de um fornecedor de software, então a única thingthat pode ser feita é certificar-se de que há espaço em TempDB a conta para o largestanticipated objetos individuais que podem ser classificados em TempDB durante um índice maintenancewindows. A partir daí, monitore o uso e ajuste de acordo.

USE TempDB;GOSELECT GETUTCDATE() AS SnapshotDateTime, groupid --0 = data, 1 = log, SUM(size/128.) SizeOnDiskInMB, SUM(FILEPROPERTY(name, 'spaceused')/128.) MBUsedWithinFile FROM TempDB.sys.sysfilesGROUP BY groupid; SELECT * FROM sys.dm_os_performance_countersWHERE counter_name = 'Log Growths' AND instance_name = 'tempdb';

manutenção da Base de dados TempDB do servidor

pode encolher a base de dados e o registo de transacções da Base de dados TempDB?Tecnicamente, Sim, mas esta não é uma operação que deva ser concluída.O objetivo deve ser sempre o TempDB do tamanho certo. Se algum tipo de operationcause esses arquivos para crescer inexplicavelmente, eles retornarão ao normal após o reinício do nextservice.Deve reconstruir os índices?

TempDB não fará parte de qualquer plano de manutenção relacionado com o índice. Isto significa que não há índices de reconstrução, reorganização ou atualização de estatísticas.

você deve fazer verificações de integridade Qual é o código?

existem opiniões divergentes sobre se o empdb deve ser verificado quanto à integridade.Este autor irá expor a argumentação a favor e contra e permitir que o leitor decida.

Against: An integrity check operation cannotcheck TempDB with the same totality as it can all other databases.Os dados que existem no TempDB são extremamente transitórios, pelo que a operação de verificação da integridade não vai encontrar muito para verificar em qualquer momento.Pela mesma razão, todos os dados que se tornam corruptos é provável que sejam dados ativos que causarão um alerta quando beingread do disco muito antes de uma operação de verificação de integridade provavelmente seria chamado.

for: While an integrity check operationcannot check everything in TempDB that it checks in other databases that doesn’t mean it has no value.O TempDB é uma base de dados integral e se há corrupção a saber que é imperativo para que sejam tomadas medidas correctivas.

o código para executar uma verificação de integridade é abaixo.

DBCC CHECKDB ('TempDB'); 

SQL Server TempDB Database Objects

What are the key tables and procedures in the TEMPDB database that are important for SQL Server Professionals to know about?Não há nenhuma.Esta base de dados é utilizada para a temporaristoragem de objects.It não vem instalado com nada ou procedimentos.

posso armazenar os meus próprios objectos na base de dados TEMPDB?

objetos podem certamente ser criados no TempDB, mas eles não irão persistir após o início do serviço de servidor SQL.

SQL Server TempDB Permissões de banco de dados

quem tem acesso à base de dados TempDB e existem diferentes níveis de permissões?

todos têm acesso e o mesmo acesso à TempDB.

SQL Server TempDB banco de dados Backup

preciso de backup da Base de dados TempDB?

TempDB não pode ser apoiada. Ele só armazena dados transitórios e é recriado sempre que o serviço começa para que não há nada a ser recuperado em caso de um desastre.

SQL Server TempDB modelo de recuperação de banco de dados TempDB

que Recuperação TempDB deve ter e o modelo de recuperação pode ser alterado?

TempDB está em recuperação simples e isso não pode ser alterado.

SQL Server TempDB database Restore

Is there ever a need to restore the TempDB database?

TempDB não pode ser restaurada. Não deve haver dados e nenhum código no TempDBto ser recuperado. Se o TempDB se tornar corrupto, basta apagar os dados e os ficheiros de log para que os novos possam ser gerados no próximo arranque do serviço.

Próximos Passos

• Esta dica mostra como tomake alertas para problemas encontrados em TempDB sizesand espaço em disco
• Esta dica mostra como toenable inicialização instantânea de arquivos, um recurso importante para todos os DBs, butespecially TempDB se não está correctamente dimensionada.

Atualizada: 2020-07-17

Sobre o autor

Eric tipo blinn é o Sr. Arquitecto de dados para o Escudeiro Patton Boggs. Ele também é um autor SQL e líder de grupo local.
ver todas as minhas dicas

recursos relacionados

• mais dicas de DBA do servidor SQL…