neste artigo, vamos discutir as melhores práticas que devem ser seguidas, a fim de manter o banco de dados TempDB em um estado saudável e evitar o crescimento inesperado do banco de dados, além de o procedimento pode ser seguido para detectar esse crescimento inesperado ocorreu uma vez.A base de dados TempDB é uma das mais importantes bases de dados do sistema SQL Server system, que é usada para armazenar objetos temporários do usuário, tais como as tabelas temporárias que são definidas pelo usuário ou devolvidas a partir da execução da função de valor de tabela, procedimentos temporários armazenados, variáveis de tabela ou índices.
além dos objetos do usuário, o TempDB será usado para armazenar objetos internos que são criados pelo motor de banco de Dados SQL Server durante as diferentes operações internas, tais como ordenação intermediária, spooling, operações agregadas ou de cursor.
a base de dados do sistema TempDB também é usada para armazenar as versões das linhas, a fim de suportar as características que requerem o acompanhamento das alterações que são realizadas nas linhas de tabela, tais como o nível de isolamento instantâneo das transações, o índice Online reconstrói ou a funcionalidade de múltiplos conjuntos de resultados ativos.
a base de dados TempDB será retirada e recriada de novo cada vez que o serviço do servidor SQL for reiniciado, começando com uma nova cópia limpa da base de dados. Com base nesse fato, todos os usuários e banco de dados internos objetados que são armazenados nesta base de dados serão descartados automaticamente quando o serviço de servidor SQL é reiniciado ou quando a sessão onde esses objetos criados é desconectado. Portanto, as operações de backup e restauração não estão disponíveis para o TempDB.
Why TempDB
the TempDB system database plays an important role in SQL Server performance tuning process. Porque é usado como armazenamento de cache para armazenar diferentes tipos de objetos de banco de dados do Usuário e para armazenar os objetos internos do sistema, a fim de acelerar os processos relacionados com o motor de banco de dados do servidor SQL.
devido a este papel vital que o banco de dados do sistema TempDB desempenha na melhoria do desempenho geral da instância do servidor SQL, é muito importante aplicar o tamanho inicial, auto-crescimento e melhores práticas de localização nos dados do banco de dados do TempDB e arquivos de log. A aplicação destas melhores práticas de uma forma que se ajuste à sua carga de trabalho de instância de servidor SQL irá impedir a expansão dos dados de banco de dados do TempDB e arquivos de log com muita frequência, levando em consideração que o processo de expansão do arquivo é um processo caro no qual o motor de servidor SQL irá solicitar espaço extra do sistema operacional, que irá executar zeroing nesse espaço antes de alocá-lo para o motor de servidor SQL. Você pode imaginar o tempo e os recursos necessários para este processo de alocação.
se você já aplicar todas as melhores práticas e uma operação de crescimento inesperada ocorreu, você pode acelerar o processo de alocação usando a opção de Inicialização instantânea de arquivos de banco de dados, na qual o sistema operacional irá confiar no serviço do servidor SQL e alocar o espaço necessário sem executar o tempo e os recursos desperdiçando operação no espaço alocado.
Melhores práticas
Há um número de TempDB sistema de configuração de banco de dados de melhores práticas que são recomendadas a fim de obter o melhor desempenho do que o recurso compartilhado e proteger a sua instância do SQL Server a partir de qualquer inesperado crescimento no banco de dados TempDB dados ou arquivos de log que podem preencher o espaço em disco subjacente.
Essas configurações incluem:
- O número de arquivos de dados do TempDB deve corresponder ao número de processadores lógicos, até oito arquivos, na máquina onde a instância do SQL Server é instalado. Se o número de processadores lógicos em que a máquina é maior que oito, definir o número de arquivos de dados para oito, com a possibilidade de estendê-lo por múltiplos de 4, no caso de você ter um TempDB problema de contenção
- certifique-se de que os arquivos TempDB tem tamanho igual, no mesmo grupo de arquivos para proporcionar o maior grau possível de operações paralelas eficiência, devido ao Preenchimento Proporcional escrever mecanismo que é usado para gravar os dados dentro de arquivos de dados. Para obter mais informações, consulte noções sobre o SQL Server algoritmo de preenchimento Proporcional
- Configurar o banco de dados TempDB de dados e arquivos de log com um razoável tamanho inicial e o crescimento automático de valores que se encaixa o esperado quantidade de dados a que irá ser escrito nesses arquivos, e os diferentes tipos de cargas de trabalho realizada em sua instância de SQL Server, incluindo as atividades do usuário e operações de manutenção de índice, a fim de evitar a freqüente de pequenas quantidades de incremento que afeta o desempenho de ocorrência
- Localizar os arquivos TempDB em um rápido subsistema de e/S, a fim de evitar qualquer Gargalos I / O
- localizar o TempDB numa unidade de disco separada, longe das bases de dados dos utilizadores e de outras bases de dados do sistema. Desta forma, as pesadas operações de leitura e escrita realizadas no TempDB não afetarão ou serão afetadas pela carga de trabalho das bases de dados do usuário, aumentando o desempenho geral da instância do servidor SQL
para mais informações sobre as novas melhorias no desempenho do TempDB, verifique o documento da Base de dados do TempDB.
onde aplicar
antes da versão do SQL Server 2016, a alocação de tamanho do TempDB pode ser realizada após a instalação da instância do servidor SQL, a partir da Página de Propriedades do banco de dados. Onde você pode controlar o número de arquivos de dados de banco de dados que se encaixam na instância atual do servidor SQL, o tamanho inicial para cada dados de banco de dados e arquivo de registro, o valor adequado para a opção Autogrowth e a localização dos dados de banco de dados e arquivos de registro.
executar estas alterações, expanda o nó Bancos de dados no SQL Server Management Studio, expanda Bancos de dados do Sistema de nó, em seguida, clique com o botão direito do mouse no banco de dados tempdb e escolha a opção Propriedades como abaixo:
a Partir do Banco de dados aberto janela de Propriedades, procurar a página de Arquivos e verificar ou alterar o banco de dados diferentes de dados e arquivos de log de configurações mostradas abaixo:
a partir do SQL Server 2016 versão, todas estas alterações, com as melhores práticas que se encaixam no atual servidor de recursos já sugerido pelo assistente de instalação pode ser marcada e realizada durante a instância do SQL Server processo de instalação, usando o SQL Server Assistente de instalação, sob o Mecanismo de Banco de dados de Configurações | TempDB, como mostrado abaixo:
Controlar o TempDB crescimento
o Acompanhamento do crescimento da TemDB arquivos de banco de dados e as consultas que estão a consumir o TempDB recursos podem ser realizadas utilizando os contadores do Monitor de Desempenho associados com os arquivos TempDB e a ferramenta de SQL Profiler que ouve um banco de dados específico tipo de carga de trabalho.
uma forma mais amigável do administrador de bases de dados para acompanhar o crescimento dos ficheiros de bases de dados do TempDB está a questionar as seguintes áreas de gestão dinâmica:
- sys.dm_db_file_space_usage que retorna as informações de uso do espaço para cada arquivo de banco de dados, sem mostrar o que de sessão ou tarefa consumida que o espaço
- sys.dm_db_session_space_usage que retorna o número da alocados e desalocados páginas por cada sessão
- sys.dm_db_task_space_usage que retorna o número da alocados e desalocados páginas por cada tarefa
Observe que o resultado retornado por sys.dm_db_session_space_usage DMV resumiu o resultado retornado pela função sys.dm_db_task_space_usage DMV quando todas as tarefas são concluídas com sucesso, devido ao fato de que, várias tarefas relacionadas com a mesma consulta serão realizadas sob a mesma sessão. Outra vantagem destes DMVs é que ele mostrará o tipo de objeto que consome os recursos do TempDB e se este objeto é um objeto usuário, como uma tabela temporária ou variável ou um objeto interno, como manutenção de índices ou operação de ordenação. Assim, com base no número de páginas consumidas por cada objeto, você pode especificar se o TempDB é consumido por um objeto interno ou usuário e otimizar esse consumidor.
sys.dm_db_file_space_usage
sys.dm_db_file_space_usage DMV pode ser usado para monitorar o espaço em disco utilizado no banco de dados TempDB arquivos. As consultas abaixo mostram o espaço não alocado, o espaço usado para armazenar versões de linhas, o espaço consumido pelos objetos internos e, finalmente, o espaço consumido pelos objetos do usuário, como segue:
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECIONE (SOMA(unallocated_extent_page_count)*1.0/128) COMO TempDB_FreeSpaceAmount_InMB
FROM sys.dm_db_file_space_usage;
SELECT (SOMA(version_store_reserved_page_count)*1.0/128) COMO TempDB_VersionStoreSpaceAmount_Inmb
FROM sys.dm_db_file_space_usage;
SELECT (SOMA(internal_object_reserved_page_count)*1.0/128) COMO TempDB_InternalObjSpaceAmount_Inmb
FROM sys.dm_db_file_space_usage;
SELECT (SOMA(user_object_reserved_page_count)*1.0/128) COMO TempDB_UserObjSpaceAmount_InMB
FROM sys.dm_db_file_space_usage;
|
a Partir do resultado, o qual é devolvido a partir do meu local de instância do SQL Server, você pode ver que, há 60 MB não utilizados em arquivos TempDB, com apenas 0,5 MB usado para objetos internos e 1,5 MB utilizada para os objetos de usuário, como mostrado abaixo:
sys.dm_db_task_space_usage e sys.dm_db_session_space_usage
sys.dm_db_task_space_usage e sys.dm_db_session_space DMVs são utilizados para verificar o número de páginas alocadas e desalocadas por cada tarefa ou sessão no banco de dados TempDB. Desta forma, você será capaz de ver qual usuário ou um objeto interno que está consumindo o espaço TempDB.
por exemplo, o resultado abaixo é devolvido por questionar o sistema.dm_db_task_space_usage DMV em banco de dados TempDB, onde ele irá retornar o número de páginas alocadas e desalocadas para cada usuário ou interna de tarefas, como segue:
Mas o DBA não vai exigir que muitos detalhes para começar a controlar a consulta que está consumindo o TempDB recursos. Então, precisamos resumir o resultado anterior para cada sessão, usando a consulta abaixo:
|
1
2
3
4
5
6
7
8
|
SELECIONE session_id,
SUM(internal_objects_alloc_page_count) COMO NumOfPagesAllocatedInTempDBforInternaltask,
SUM(internal_objects_dealloc_page_count) COMO NumOfPagesDellocatedInTempDBforInternaltask,
SUM(user_objects_alloc_page_count) COMO NumOfPagesAllocatedInTempDBforUsertask,
SUM(user_objects_dealloc_page_count) COMO NumOfPagesDellocatedInTempDBforUsertask
FROM sys.dm_db_task_space_usage
GRUPO session_id
ORDER BY NumOfPagesAllocatedInTempDBforInternaltask DESC, DESC NumOfPagesAllocatedInTempDBforUsertask
|
E o resumidas páginas consumo de resultado por cada sessão será como abaixo:
O mesmo resultado pode ser devolvidos consultando a sys.dm_db_session_space_usage DMW, que resume as páginas de alocação e desalocação para todas as tarefas em execução sob a mesma sessão, como mostrado abaixo:
Vamos realizar algumas ações no banco de dados TempDB usando tanto interno e objetos de usuário como abaixo:
, em Seguida, executar o consolidado consulta abaixo, que retornam o número de alocados e desalocados páginas internas e objetos de usuário, e mostrar a operações concluídas (usando o sistema.dm_db_session_space_usage DMV) e o não-concluídas as operações (usando o sistema.dm_db_task_space_usage DMV):
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SELECIONE SpacePerTask.session_id,
SpacePerSession.internal_objects_alloc_page_count
+ SUM (tarefa espacial.internal_objects_alloc_page_count) como Numofagesafectadointempdb para a Missão Internacional,
SpacePerSession.internal_ objects_dealloc_page_count
+ SUM (tarefa espacial.internal_objects_dealloc_page_count) como Numofagesdelocadointempdb para a Missão Internacional,
SpacePerSession.User_ objects_ alloc_page_count
+ SUM (SpacePerTask.User_objects_alloc_page_count) como Numofpagesafectadointempdbforusertask,
SpacePerSession.User_ objects_dealloc_page_count
+ SUM (tarefa espacial.User_objects_dealloc_page_count) COMO NumOfPagesDellocatedInTempDBforUsertask
FROM sys.dm_db_session_space_usage COMO SpacePerSession
INNER JOIN sys.dm_db_task_space_usage COMO SpacePerTask NO SpacePerSession.session_id = SpacePerTask.session_id
GROUP BY SpacePerTask.session_id, SpacePerSession.internal_objects_alloc_page_count, SpacePerSession.internal_objects_dealloc_page_count, SpacePerSession.User_ Objects_ Alloc_page_count, SpacePerSession.User_objects_dealloc_page_count
ORDER BY NumOfPagesAllocatedInTempDBforInternaltask DESC, DESC NumOfPagesAllocatedInTempDBforUsertask
|
E as operações anteriores resultado vai refletir a saída da consulta, mostrando o número de páginas alocadas e desalocadas criando o usuário tabela temporária e classificação dos dados durante o processo de reconstrução de índice, como mostrado abaixo:
Para obter o texto da consulta que consome o TempDB recursos, você pode usar o SQL desenvolvimento de competências juntando-se o resultado dos ys.dm_db_task_space_usage DMV com outros DMVs como sys.dm_exec_requests e sys.dm_exec_sessions com a sys.dm_exec_sql_text Função de Gerenciamento Dinâmico, onde ele irá exibir as consultas que estão a consumir espaço em TempDB agora, como nesta consulta.
o resultado será semelhante ao abaixo:
- Autor
- Posts Recentes
He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.
também, ele está contribuindo com suas dicas de SQL em muitos blogs.
Ver todos os posts por Ahmad Yaseen
- Azure Data Fábrica de Perguntas e Respostas da Entrevista – 11 de fevereiro de 2021
- Como monitor Azure Dados de Fábrica – janeiro 15, 2021
- Usando o Controle de Origem no Azure Dados de Fábrica de janeiro 12, 2021