Perguntas[tempdb](dba)

Estou em um debate sobre se você também precisa definir ALLOW SNAPSHOT ISOLATION no próprio tempdb ou apenas nos bancos de dados do usuário para que a funcionalidade seja ativada (além do código que faz referência a isso)?

A maioria dos artigos e exemplos parece definir a propriedade apenas no próprio banco de dados do usuário e deixar o tempdb como OFF como está. Mas então encontrei este link Pro SQL Server 2005 Database Design and Optimization , que diz:

(Você pode até fazer isso no tempdb)
alter database tempdb set allow_snapshot_isolation on

É necessário configurá-lo também no tempdb além do banco de dados do usuário ou apenas no banco de dados do usuário?

O objetivo principal é para um aplicativo que precisa ter o isolamento de instantâneo ativado.

Nosso trabalho Ola Hallengren IntegrityCheck falhou devido a um tempo limite de trava de buffer durante a execução de DBCC CHECKDB em um banco de dados de usuário.

No entanto, o tempo limite de trava do buffer relatado estava em TempDB (ID do banco de dados 2).

Saída do trabalho:

Date and time: 2022-01-22 09:04:15 [SQLSTATE 01000]
Database context: [master] [SQLSTATE 01000]
Command: SET LOCK_TIMEOUT 600000; DBCC CHECKDB ([SentryOne]) WITH NO_INFOMSGS, ALL_ERRORMSGS, MAXDOP = 4 [SQLSTATE 01000]
Msg 845, Sev 17, State 1, Line 1 : Time-out occurred while waiting for buffer latch type 2 for page (6:222), database ID 2. [SQLSTATE 42000]
Outcome: Failed [SQLSTATE 01000]
Duration: 12:40:32 [SQLSTATE 01000]
Date and time: 2022-01-22 21:44:47 [SQLSTATE 01000]

Mensagens no log de erros do SQL:

Date 1/22/2022 9:35:22 PM
Log SQL Server (Archive #1 - 1/23/2022 12:00:00 AM)

Source spid777

Message
A time-out occurred while waiting for buffer latch -- type 2, bp 0000016D7A1DE340, page 6:222, stat 0x40d, database id: 2, allocation unit Id: 536870912/281475513581568, task 0x0000016CD624E4E8 : 2, waittime 300 seconds, flags 0x1a, owning task 0x0000016CD624E4E8. Not continuing to wait.

BobMgr::GetBuf: Sort Big Output Buffer write not complete after 60 seconds.

DBCC CHECKDB (SentryOne) WITH all_errormsgs, no_infomsgs, maxdop = 4 executed by NT SERVICE\SQLSERVERAGENT terminated abnormally due to error state 6. Elapsed time: 12 hours 39 minutes 57 seconds.

[INFO] Database ID: [12]. Cleaning up StorageArray. LastClosedCheckpointEndTs: '1225176490'

[WARNING] ALTER or DROP TABLE could not clean up root row within 10 seconds.

A página em questão é uma página do IAM no TempDB. Saída DBCC PAGE para ele:

PAGE: (6:222)

BUFFER:

BUF @0x0000016D6B622C40

bpage = 0x00000161A6C52000 bPmmpage = 0x0000000000000000 bsort_r_nextbP = 0x0000016D6B622AD0
bsort_r_prevbP = 0x0000016D6B622B80 bhash = 0x0000000000000000 bpageno = (6:222)
bpart = 4 ckptGen = 0x0000000000000000 bDirtyRefCount = 0
bstat = 0x9 breferences = 3 berrcode = 0
bUse1 = 19321 bstat2 = 0x0 blog = 0x215a215a
bsampleCount = 1 bIoCount = 0 resPoolId = 0
bcputicks = 330 bReadMicroSec = 848 bDirtyContext = 0x0000000000000000
bDbPageBroker = 0x0000000000000000 bdbid = 2 bpru = 0x0000016D3A170040

PAGE HEADER:

Page @0x00000161A6C52000

m_pageId = (6:222) m_headerVersion = 1 m_type = 10
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 1837007664 m_indexId (AllocUnitId.idInd) = 28675
Metadata: AllocUnitId = 8071415347312328704
Metadata: PartitionId = 2162691495132069888 Metadata: IndexId = 0
Metadata: ObjectId = -1083263203 m_prevPage = (0:0) m_nextPage = (0:0)
pminlen = 90 m_slotCnt = 2 m_freeCnt = 6
m_freeData = 8182 m_reservedCnt = 0 m_lsn = (324:1435184:42)
m_xactReserved = 0 m_xdesId = (0:0) m_ghostRecCnt = 0
m_tornBits = -551517712 DB Frag ID = 1

Allocation Status

GAM (6:2) = ALLOCATED SGAM (6:3) = ALLOCATED
PFS (6:1) = 0x70 IAM_PG MIXED_EXT ALLOCATED 0_PCT_FULL DIFF (6:6) = NOT CHANGED
ML (6:7) = NOT MIN_LOGGED

IAM: Header @0x00000036C4278064 Slot 0, Offset 96

sequenceNumber = 0 status = 0x0 objectId = 0
indexId = 0 page_count = 0 start_pg = (8:511232)

IAM: Single Page Allocations @0x00000036C427808E

Slot 0 = (0:0) Slot 1 = (0:0) Slot 2 = (0:0)
Slot 3 = (0:0) Slot 4 = (0:0) Slot 5 = (0:0)
Slot 6 = (0:0) Slot 7 = (0:0)

IAM: Extent Alloc Status Slot 1 @0x00000036C42780C2

(8:511232) - (8:592656) = NOT ALLOCATED
(8:592664) - = ALLOCATED
(8:592672) - (8:770424) = NOT ALLOCATED

Encontrei esta pergunta do DBA StackExchange que parece ser mais relevante: Obtendo o erro "Ocorreu o tempo limite enquanto aguardava o tipo de trava de buffer 2 para a página (1:3564879), ID do banco de dados 7". Mas um banco de dados com Id 7 NÃO existe

No entanto, nesse cenário, a página expirada relatada está em um banco de dados do usuário e não no TempDB.

Além disso, estamos na versão mais recente do SQL Server (2019-CU14 Enterprise), portanto, isso não deve acontecer de qualquer maneira.

Percebi que havia latência muito alta nos arquivos de dados TempDB por volta das 18h e 21h40. Não tenho certeza se está relacionado, no entanto, porque o pico de latência às 18h é maior e, no entanto, o trabalho falhou apenas por volta das 21h44.

Esses picos de latência não se correlacionam com nenhum pico nas métricas do Azure Monitor VM Cached IOPS Consumed Percentagee VM Cached Bandwidth Consumed Percentage.

Informação adicional:

• O servidor está instalado em uma VM do Azure:Standard E8s v3 (8 vcpus, 64 GiB memory)
• O banco de dados do usuário em questão (SentryOne) possui tabelas com otimização de memória.
• Os metadados com otimização de memória TempDB estão DESATIVADOS.
• A inicialização instantânea do arquivo está ATIVADA.
• Bloquear Páginas na Memória está DESATIVADO.

Alguma idéia de por que isso aconteceu e como evitá-lo no futuro?

Obrigado!

Acabei de fazer um boo-boo na minha instância SQL pessoal. Eu queria mover os arquivos tempdb para C:\tempdb.mdf e C:\templog.ldf, respectivamente. O comando alter database ...modify file... foi executado com sucesso. No entanto, o que eu não percebi é que não posso criar um arquivo em C:\ , apenas pastas podem ser criadas lá. Portanto, após executar o comando modify file e reiniciar o serviço, os novos arquivos não foram criados, ou seja, C:\tempdb.mdf e C:\templog.ldf.

Quando tentei obter as propriedades do banco de dados tempdb para ver para onde os arquivos estavam apontando, descobri que não conseguia obter as propriedades nem me conectar à instância do banco de dados. Portanto, embora eu possa reiniciar os serviços no Config Manager, não consigo me conectar à instância usando SSMS ou SQLCMD. Eu recebo o erro que o arquivo não pode ser encontrado.

Além de reinstalar a instância (já que não tenho backup do banco de dados mestre), existe alguma outra maneira de corrigir esse problema, por favor?

Obrigada.

Eu estava aprendendo sobre RCSI quando descobri algo estranho no Banco de Dados SQL do Azure.

O DMV sys.dm_tran_version_store_space_usagesempre relata 0 como o espaço usado pelo armazenamento de versão, mesmo se eu executar uma carga de trabalho CRUD antes.

Para demonstrar esse comportamento, criei um pequeno teste.

-- Server info
select @@version as sql_version;

-- Database info
select
    is_read_committed_snapshot_on,
    snapshot_isolation_state_desc
from sys.databases
where database_id = db_id();

-- Just to be sure the current database has its version store empty
select reserved_page_count as pre_workload_space_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

-- Test workload
drop table if exists RCSI_TEST;

create table RCSI_TEST (
    id uniqueidentifier default newid()
);
go

insert into RCSI_TEST default values;
go 100

update RCSI_TEST
set id = newid();

delete from RCSI_TEST;

-- Metrics
select reserved_page_count as post_workload_page_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

waitfor delay '00:01:30'; -- Just to be sure! ;)

select reserved_page_count as post_cleaning_space_count
from sys.dm_tran_version_store_space_usage
where database_id = db_id();

Executei este script no SQL Server 2019 Developer Edition (no Docker) e em um Banco de Dados SQL do Azure (camada S0, 10DTU) e aqui estão os resultados.

SQL Server 2019

sql_version
Microsoft SQL Server 2019 (RTM-CU8) (KB4577194) - 15.0.4073.23 (X64)Sep 23 2020 16:03:08    Copyright (C) 2019 Microsoft Corporation    Developer Edition (64-bit) on Linux (Ubuntu 18.04.5 LTS) <X64>

is_read_committed_snapshot_on   snapshot_isolation_state_desc
1                               ON

pre_workload_space_count
0

post_workload_page_count
8

post_cleaning_space_count
0

Banco de Dados SQL do Azure

sql_version
Microsoft SQL Azure (RTM) - 12.0.2000.8     Oct  1 2020 18:48:35    Copyright (C) 2019 Microsoft Corporation

is_read_committed_snapshot_on   snapshot_isolation_state_desc
1                               ON

pre_workload_space_count
0

post_workload_page_count
0

post_cleaning_space_count
0

O SQL Server 2019 parece se comportar corretamente, relatando 8 páginas de dados no armazenamento de versão logo após a carga de trabalho e, em seguida, limpo após um minuto ou mais. No Banco de Dados SQL do Azure, no entanto, o espaço usado é sempre zero! Isso é um comportamento correto? O que isso significa?

Books Online relata que esse DMV é compatível com o SQL Server e o Banco de Dados SQL do Azure, mas também diz "A consulta a seguir pode ser usada para determinar o espaço consumido em tempdb, por armazenamento de versão de cada banco de dados em uma instância do SQL Server. " . Tanto quanto sei, os Bancos de Dados SQL do Azure têm um escopo limitado para sua instância pai por design. Essa pode ser a causa raiz?

https://learn.microsoft.com/it-it/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-version-store-space-usage?view=sql-server-ver15

Eu sei que os Bancos de Dados SQL do Azure são executados em RCSI por padrão e também que eles têm uma quantidade fixa de espaço tempdb com base na camada que você obtém, então eu estava preocupado em preencher isso com transações de longa duração ou outros processos mantendo muitos registros no armazenamento de versão por muito tempo. Mas eu não posso gerenciar algo que não posso medir, certo?

Ocasionalmente, encontramos o erro “Já existe um objeto chamado 'PK__#TempTab__0796211ACE71813B' no banco de dados” quando alguns de nossos procedimentos tentam criar tabelas temporárias com uma restrição PK nomeada pelo sistema assim:

CREATE OR ALTER PROCEDURE dbo.StoredProc AS
BEGIN
    CREATE TABLE #TempTable (id INT NOT NULL, PRIMARY KEY CLUSTERED (id))
    ...
END

A consulta de tempdb.sys.objects revela algumas centenas de restrições no formato PK__#TempTab__XYZ. Muitos foram criados horas atrás e não foram modificados desde então. Havia muito poucas sessões ativas quando olhei para tempdb.sys.objects, então é difícil acreditar que existem muitas tabelas temporárias em uso atualmente. Temos muitas tabelas temporárias com nomes semelhantes criadas em muitos procedimentos armazenados.

Acho que o cache de tabela temporária é responsável aqui. Parece que essas restrições de PK permanecem no tempdb até que seus planos em cache associados sejam removidos. Os testes mostraram que desabilitar o cache da tabela temporária (criar estatísticas, adicionar restrições nomeadas etc.) faz com que a restrição PK seja removida de tempdb.sys.objects quando a tabela temporária sai do escopo. Acionar recompilações e liberar o cache do proc também limpa essas restrições de PK de tempdb.sys.objects.

Estou ciente de que as restrições nomeadas pelo sistema não são garantidas como exclusivas, conforme descrito nesta postagem O SQL Server pode criar colisões em nomes de restrição gerados pelo sistema? .

Minhas perguntas são:

• Estou errado em supor que o cache de tabela temporária aumenta a chance de um conflito de nomes para restrições nomeadas pelo sistema em tabelas temporárias?
• O excesso de cache do plano poderia ser responsável pelo grande número de restrições PK__#TempTab__XYZ que estou vendo em tempdb.sys.objects, aumentando assim a chance de duas restrições terem o mesmo nome? Estou tentando entender por que vimos esse erro raro com tanta frequência.
• Se não pudermos garantir que as restrições nomeadas pelo sistema nas tabelas temporárias nunca encontrarão colisões de nomenclatura, o que podemos fazer para reduzir a chance dessas colisões em geral?

Eu quero alterar o tempdb como tal (estou aumentando o tamanho)

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp2', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp3', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'temp4', SIZE = 10240KB )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 10240KB )
GO

Isso exigirá que eu reinicie o servidor SQL?

Ao tentar investigar alguns problemas de desempenho com nosso banco de dados SQL Server, notei que nossas estatísticas de espera parecem altas para ASYNC_IO_COMPLETION e PAGEIOLATCH_*. No Monitor de Recursos, notei que o tempDB é frequentemente (pelo menos 1 vez por hora) Lendo/Gravando grandes quantidades de dados, intervalo total (B/Sec) de 5 GB - 25 GB.

O tempDB está em um disco virtual separado do banco de dados e dos arquivos de log. O tamanho do TempDB é de 4 GB. Nosso arquivo principal de banco de dados e log tem 40 GB de tamanho cada. Eu tentei pesquisar contadores de perfmon para isso, mas não consegui encontrar nada que eu possa entender para descobrir por que estou recebendo certos números.

É incomum que o total de dados para tempDB seja tão alto por meio do monitor de recursos? Nosso banco de dados principal está crescendo apenas cerca de 50 MB por dia. O que devo procurar para descobrir o que está causando dados tão altos no tempDB e se isso está causando um gargalo de E/S? Se o melhor método é usar contadores perfmon específicos, quais contadores devo observar e quais valores são bons ou ruins?

Verificando o SQL Server 2019 CTP 2.5 (15.0.1500.28) Encontrei essa nova opção de configuração e estava vagando o que está por trás dela. Não foi possível encontrar nenhuma informação até agora e queria saber se você sabe onde encontrar mais sobre isso?

Estou lutando com esse problema há muitos meses, estou dizendo que não é um problema de banco de dados e atribuo este caso à equipe de armazenamento e sistema operacional e eles o estão atribuindo a mim posteriormente. Esse problema está acontecendo repetidamente e não há nenhum padrão definido de ocorrência.

Eu verifiquei a mesma pergunta feita aqui e posso dizer que isso não é um problema com corrupção de banco de dados, pois estou usando o script de Ola Hallengren para trabalho de manutenção e verificação de integridade do banco de dados (checkdb) é feito semanalmente para banco de dados de usuário e sistema e nenhum problema relatado naquilo.

Acessou o segundo link também para a pergunta semelhante e pode confirmar que o tempdb está em estado de recuperação simples.

Eu adicionei um arquivo adicional para dados e registrei ambos para que, se um ficar indisponível, o outro ainda estará acessível, mas depois soube que o acesso do tempdb é sequencial, então ele irá para o segundo arquivo somente quando o primeiro ficar cheio:

Toda vez que esse problema ocorre, posso ver que temos outro erro no log do aplicativo do Windows, conforme abaixo:

SQLServerLogMgr::LogWriter: Operating system error 170(The requested resource is in use.) encountered.

A equipe de armazenamento excluiu esses arquivos da verificação antivírus.

Uma coisa a notar aqui - verifiquei outros bancos de dados do sistema e seus locais de arquivos e pude ver que, para mestre e modelo, dados e arquivo de log estão na mesma unidade (unidade E), enquanto para tempdb e msdb, os dados estão na unidade E e log arquivo está na unidade G, não tenho certeza se isso é relevante.

Existe alguma sequência de verificação do banco de dados do sistema quando um trabalho é acionado ou qualquer evento é acionado? Se for um problema com essa unidade, o msdb também estará na mesma unidade.

Este é um servidor em cluster com unidades de banco de dados compartilhadas em dois servidores e o servidor é usado para o aplicativo sharepoint.

Server Version: Windows Server 2012 Standard
SQL Server: Microsoft SQL Server 2012 (SP4-GDR) (KB4057116) - 11.0.7462.6 (X64) 
    Jan  5 2018 22:11:56 
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)

A única opção que nos resta é reiniciar o serviço ou fazer failover - simplesmente não parece bom fazê-lo.

Qualquer ajuda neste sentido é apreciada.

Hoje eu estava solucionando um problema no TempDB no SQL Server 2016 SP2 usando eventos estendidos e adicionei o evento mixed_extent_allocation e transaction_log ao meu rastreamento.

Eu esperava que mixed_extent_allocation não aparecesse nos meus resultados porque a seguinte consulta retornou 0 como resultado:

select is_mixed_page_allocation_on  
from sys.databases 
where database_id=2

Mas para minha surpresa este evento apareceu várias vezes. Isso foi precedido por um evento transaction_log com SGAM como contexto e operação LOP_SET_BITS.

Isso me deixou curioso e verifiquei o conteúdo da primeira página SGAM do TempdB:

DBCC TRACEON(3604)
dbcc page(tempdb,1,3,3)
DBCC TRACEOff(3604)

Este é um trecho do resultado:

╔═════════════════════════════════════════════╗
║ (1:0)        - (1:176)      = NOT ALLOCATED ║
║ (1:184)      -              =     ALLOCATED ║
║ (1:192)      -              = NOT ALLOCATED ║
║ (1:200)      - (1:208)      =     ALLOCATED ║
║ (1:216)      - (1:256)      = NOT ALLOCATED ║
║ (1:264)      -              =     ALLOCATED ║
║ (1:272)      -              = NOT ALLOCATED ║
║ (1:280)      -              =     ALLOCATED ║
║ (1:288)      - (1:296)      = NOT ALLOCATED ║
║ (1:304)      -              =     ALLOCATED ║
║ (1:312)      -              = NOT ALLOCATED ║
║ (1:320)      -              =     ALLOCATED ║
║ (1:328)      - (1:336)      = NOT ALLOCATED ║
║ (1:344)      -              =     ALLOCATED ║
║ (1:352)      - (1:65528)    = NOT ALLOCATED ║
╚═════════════════════════════════════════════╝

Isso me fez concluir que extensões mistas ainda são usadas. Eu pensei que o SQL Server 2016 usa apenas extensões uniformes (exceto master, msdb e model). Ou minha conclusão está errada?

Por que ainda estou vendo o evento mixed_extent_allocation aparecendo?