All perguntas(dba)

Estou trabalhando com o Microsoft SQL Server 2016 (SP2-CU11) (KB4527378) - 13.0.5598.27 (X64) 27 de novembro de 2019 18:09:22 Copyright (c) Microsoft Corporation Standard Edition (64 bits) no Windows Server 2012 R2 Padrão 6.3 (Build 9600: )

Este servidor está em unidades SSD e tem uma memória máxima de 128 GB. CostTheshold para Paralelismo é 70, MaxDegree de Paralelismo é 3.

Eu tenho uma tabela "Trips" que é referenciada por 23 chaves estrangeiras com a opção ON DELETE CASCADE.

Esta tabela por si só não é tão grande (5,3 milhões de linhas, 1,3 gb de dados). Mas das 23 tabelas referenciadas, duas das tabelas são bem grandes (mais de 1 bilhão de linhas, 54 e 69 gb cada).

O problema é que quando tentamos excluir uma pequena quantidade de linhas na tabela "Trips" (digamos 4 linhas), o SQL estima que tantas linhas serão excluídas, ele pede 10 GB de RAM, estima que milhões de linhas serão retornado e bloqueia a tabela. Tudo para e outras consultas são bloqueadas e o aplicativo expira.

Aqui estão as tabelas principais e a contagem de linhas para 1 instrução de exclusão:

• Viagens (4 linhas)
• Segmentos (27 linhas, relacionadas a Viagens por SegmentId)
• Perfis (linhas de 2012, relacionadas a Segmentos por SegmentId)
• ProfileRanges (2337 linhas, relacionadas a perfis por ProfileId)
• Eventos (7.750 linhas, relacionadas a segmentos por SegmentId)
• EventConditions (9230 linhas, relacionadas a eventos por EventId)

As tabelas EventConditions e ProfileRanges têm mais de 1 bilhão de linhas cada.

Aqui está o cache do plano: https://www.brentozar.com/pastetheplan/?id=HJNg5I0BU

Quando olho no explorador de planos SentryOne, posso ver que o SQL está lendo a tabela inteira, mesmo que o "spool de tabela" filtre e mantenha apenas para 2012 linhas ProfileRanges e quase o mesmo para EventConditions.

Quando olho para a concessão de memória da consulta com o procedimento sp_blitzCache de Brent Ozar, posso ver que a consulta pede cerca de 10 GB de RAM.

Depois disso, a consulta está esperando em SOS_SCHEDULER_YIEL (então esperando a vez de usar a CPU após os 4ms) ou MEMORY_ALLOCATION_EXT. O programa expira e falha.

O que posso fazer para que isso funcione?

Uma das coisas que eu estava pensando era remover as chaves estrangeiras nas duas maiores tabelas e excluir suas linhas em um gatilho em vez de. Mas eu não sou um grande fã de impor a consistência do banco de dados com gatilhos em vez de chaves estrangeiras.

Qualquer conselho ou ajuda será apreciado

A chave primária de ProfileRanges é

• PerfilId int
• ProfileRangeDefId1 int
• ProfileRangeDefId2 int

A chave primária de EventConditions é

• EventId bigint
• EventConditionDefId int

Estou curioso, existe uma boa maneira de pesquisar todas as colunas por um determinado valor? Para meus propósitos, não precisa ser nada rápido, é apenas um tipo de coisa 1-off, e eu realmente não quero ter que digitar cada nome de campo. Isso é precisamente o que vou fazer agora, mas acho que certamente há uma maneira melhor.

Eu gostaria de virar isso:

SELECT * FROM table WHERE col1 = 'val' OR col2 = 'val' OR col3 = 'val';

nisso:

SELECT * FROM table WHERE * = 'val'

...ou, melhor ainda (embora eu duvide seriamente...)

SELECT * FROM table WHERE * like '%val%'

Eu encontrei isso , que parece meio não muito próximo, mas não estou encontrando nada mais próximo:

SELECT whatever WHERE col1,col2 IN ((val1, val2), (val1, val2), ...)

A diferença é que pesquisa uma seleção de colunas para os valores especificados, enquanto estou tentando pesquisar TODAS as colunas por um único valor.

Não é importante, porém, como eu disse mais do que tudo, estou apenas curioso

Enquanto pesquisava, encontrei algumas informações conflitantes.

Alguns sites afirmam que quando não há mais memória física para os dados, o SQL Server move os dados já existentes para o TEMPDB (consulte: SQL Server: Desmistificando o TempDb e as recomendações ).

Mas outros sites afirmam que, quando não há memória física suficiente, o sistema operacional pode usar o PAGE FILE e mover dados da memória física para ele (consulte Arquivo de página para SQL Server ).

Gostaria de saber onde o SQL Server grava dados quando fica sem memória física? Para tempdb ou para o arquivo de página do sistema operacional? Ou talvez os dois?

Eu tenho uma tabela em um banco de dados de produção que tem um tamanho de 525 GB, dos quais 383 GB não são utilizados:

Gostaria de recuperar um pouco desse espaço, mas, antes de mexer no banco de dados de produção, estou testando algumas estratégias em uma tabela idêntica em um banco de dados de teste com menos dados. Esta tabela tem um problema semelhante:

Algumas informações sobre a mesa:

• O fator de preenchimento é definido como 0
• Existem cerca de 30 colunas
• Uma das colunas é um LOB do tipo imagem e está armazenando arquivos que variam em tamanho de alguns KB a várias centenas de MB
• A tabela não possui nenhum índice hipotético associado a ela

O servidor está executando o SQL Server 2017 (RTM-GDR) (KB4505224) - 14.0.2027.2 (X64). O banco de dados está usando o SIMPLEmodelo de recuperação.

Algumas coisas que tentei:

• Reconstruindo os índices: ALTER INDEX ALL ON dbo.MyTable REBUILD. Isso teve um impacto insignificante.
• Reorganizando os índices: ALTER INDEX ALL ON dbo.MyTable REORGANIZE WITH(LOB_COMPACTION = ON). Isso teve um impacto insignificante.
• Copiou a coluna LOB para outra tabela, eliminou a coluna, recriou a coluna e copiou os dados de volta (conforme descrito neste post: Liberando Espaço Não Usado Tabela SQL Server ). Isso diminuiu o espaço não utilizado, mas parecia apenas convertê-lo em espaço usado:

• Usou o utilitário bcp para exportar a tabela, truncá-la e recarregá-la (conforme descrito neste post: Como liberar o espaço não utilizado para uma tabela ). Isso também reduziu o espaço não utilizado e aumentou o espaço usado de forma semelhante à imagem acima.
• Mesmo não sendo recomendado, tentei os comandos DBCC SHRINKFILE e DBCC SHRINKDATABASE, mas eles não tiveram nenhum impacto no espaço não utilizado.
• Correr DBCC CLEANTABLE('myDB', 'dbo.myTable')não fez diferença
• Eu tentei todos os itens acima, mantendo os tipos de dados de imagem e texto e depois de alterar os tipos de dados para varbinary(max) e varchar(max).
• Tentei importar os dados para uma nova tabela em um banco de dados novo, e isso também converteu apenas o espaço não utilizado em espaço usado. Eu descrevi os detalhes desta tentativa neste post .

Não quero fazer essas tentativas no banco de dados de produção se esses forem os resultados que posso esperar, então:

1. Por que o espaço não utilizado está sendo convertido em espaço usado após algumas dessas tentativas? Eu sinto que não tenho uma boa compreensão do que está acontecendo sob o capô.
2. Existe mais alguma coisa que eu possa fazer para diminuir o espaço não utilizado sem aumentar o espaço usado?

EDIT: Aqui está o relatório de uso do disco e o script para a tabela:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MyTable](
    [Column1]  [int] NOT NULL,
    [Column2]  [int] NOT NULL,
    [Column3]  [int] NOT NULL,
    [Column4]  [bit] NOT NULL,
    [Column5]  [tinyint] NOT NULL,
    [Column6]  [datetime] NULL,
    [Column7]  [int] NOT NULL,
    [Column8]  [varchar](100) NULL,
    [Column9]  [varchar](256) NULL,
    [Column10] [int] NULL,
    [Column11] [image] NULL,
    [Column12] [text] NULL,
    [Column13] [varchar](100) NULL,
    [Column14] [varchar](6) NULL,
    [Column15] [int] NOT NULL,
    [Column16] [bit] NOT NULL,
    [Column17] [datetime] NULL,
    [Column18] [varchar](50) NULL,
    [Column19] [varchar](50) NULL,
    [Column20] [varchar](60) NULL,
    [Column21] [varchar](20) NULL,
    [Column22] [varchar](120) NULL,
    [Column23] [varchar](4) NULL,
    [Column24] [varchar](75) NULL,
    [Column25] [char](1) NULL,
    [Column26] [varchar](50) NULL,
    [Column27] [varchar](128) NULL,
    [Column28] [varchar](50) NULL,
    [Column29] [int] NULL,
    [Column30] [text] NULL,
 CONSTRAINT [PK] PRIMARY KEY CLUSTERED 
(
    [Column1] ASC,
    [Column2] ASC,
    [Column3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column4]  DEFAULT (0) FOR [Column4]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column5]  DEFAULT (0) FOR [Column5]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column15]  DEFAULT (0) FOR [Column15]
GO
ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_Column16]  DEFAULT (0) FOR [Column16]
GO

Aqui estão os resultados da execução dos comandos na resposta de Hannah Vernon:

╔════════════╦═══════════╦════════════╦═════════════════╦══════════════════════╦════════════════════╗
║ TotalBytes ║ FreeBytes ║ TotalPages ║ TotalEmptyPages ║ PageBytesFreePercent ║ UnusedPagesPercent ║
╠════════════╬═══════════╬════════════╬═════════════════╬══════════════════════╬════════════════════╣
║  9014280192║ 8653594624║     1100376║          997178 ║            95.998700 ║          90.621500 ║
╚════════════╩═══════════╩════════════╩═════════════════╩══════════════════════╩════════════════════╝

╔═════════════╦═══════════════════╦════════════════════╗
║ ObjectName  ║ ReservedPageCount ║      UsedPageCount ║
╠═════════════╬═══════════════════╬════════════════════╣
║ dbo.MyTable ║           5109090 ║            2850245 ║
╚═════════════╩═══════════════════╩════════════════════╝

ATUALIZAR:

Eu executei o seguinte, conforme sugerido por Hannah Vernon:

DBCC UPDATEUSAGE (N'<database_name>', N'<table_name>');

E aqui estava a saída:

DBCC UPDATEUSAGE: Usage counts updated for table 'MyTable' (index 'PK_MyTable', partition 1):
        USED pages (LOB Data): changed from (568025) to (1019641) pages.
        RSVD pages (LOB Data): changed from (1019761) to (1019763) pages.

Isso atualizou o uso do disco para a tabela:

E o uso geral do disco:

Portanto, parece que o problema foi que o uso do disco rastreado pelo SQL Server ficou totalmente fora de sincronia com o uso real do disco. Vou considerar esse problema resolvido, mas gostaria de saber por que isso aconteceu em primeiro lugar!

Estou criando um banco de dados SQL Server com outra pessoa. Uma das tabelas é pequena (6 linhas) com dados que provavelmente permanecerão constantes. Existe uma possibilidade remota de que uma nova linha seja adicionada. A tabela fica mais ou menos assim:

CREATE TABLE someTable (
    id int primary key identity(1,1) not null,
    name varchar(128) not null unique
    );
INSERT INTO someTable values ('alice', 'bob something', 'charles can dance', 'dugan was here');

Estou olhando para o comprimento de caracteres dessa namecoluna e acho que seus valores provavelmente nunca serão maiores que, digamos, 32 caracteres, talvez nem maiores que 24. Há alguma vantagem em mudar essa coluna para, por exemplo, varchar(32)?

Além disso, há alguma vantagem em manter os tamanhos de coluna padrão em múltiplos de 4, 8, 32, etc?

Eu escrevi um procedimento armazenado que faz uso de uma tabela temporária. Eu sei que no SQL Server, as tabelas temporárias têm escopo de sessão. No entanto, não consegui encontrar informações definitivas sobre exatamente do que uma sessão é capaz. Em particular, se for possível que esse procedimento armazenado seja executado duas vezes simultaneamente em uma única sessão, um nível de isolamento significativamente mais alto será necessário para uma transação nesse procedimento devido às duas execuções que agora compartilham uma tabela temporária.

Estou tentando criar uma função no Postgres que possa percorrer cada par de chave/valor em um determinado jsonbobjeto.

create or replace function myFunction
(input jsonb)
returns jsonb as $$
BEGIN

    // foreach(key in input)
    //       do some math operation on its corresponding value

    returns input;

END; $$

Espera-se que a entrada do argumento seja um objeto jsonb, como {"a":1, "b":2, "c":3}.

Eu quero percorrer cada par de chave/valor no objeto. Como você pode ver pelo meu comentário, eu costumava escrever código de back-end com linguagens de programação mais gerais, como c, java, etc. Portanto, não sou bom em SQL. Eu tentei pesquisar on-line, mas eles falam sobre como percorrer o jsonbarray em vez do objeto. Então realmente preso aqui.

Eu quero entender o quão seguro é o comando rename:

ALTER INDEX old_name RENAME TO old_name;

O índice foi criado pelo comando (para a coluna de chave estrangeira):

CREATE INDEX old_name ON table_t1 (user_id);

Isso significa atualização simples nas tabelas do sistema postgres ou fazer muito mais operações. Eu tento encontrá-lo nos documentos, mas sem sucesso.

Estou inserindo alguns dados XML em uma coluna XML no SQL Server, mas depois que os dados foram inseridos, eles foram alterados pelo sql server. Aqui estão os dados que eu insiro

              <xsl:value-of select="name/n/given" />
            <xsl:text> </xsl:text>
          <xsl:value-of select="name/n/family" />

Quando eu li de volta, é assim

              <xsl:value-of select="name/n/given" />
          <xsl:text />
          <xsl:value-of select="name/n/family" />

Preste atenção na segunda linha. Isso é um problema porque altera como será a saída da transformação XSLT. O primeiro exemplo criará um espaço entre dado e nome de família, enquanto o segundo não criará nenhum espaço, então será como JohnJohnsen, enquanto o primeiro será como John Johnsen.

Existe alguma maneira de resolver isso?

Devido à lógica de negócios, precisamos de uma nova coluna em uma tabela que seja essencial para garantir que ela seja sempre preenchida. Portanto, deve ser adicionado à tabela como NOT NULL. Ao contrário das perguntas anteriores que explicam como fazer isso manualmente , isso precisa ser gerenciado pela publicação do SSDT.

Eu tenho batido minha cabeça contra a parede por um tempo por causa dessa tarefa que parece simples devido a algumas realizações:

1. Um valor padrão não é apropriado e não pode ser uma coluna computada. Talvez seja uma coluna de chave estrangeira, mas para outras não podemos usar um valor falso como 0 ou -1 porque esses valores podem ter significado (por exemplo, dados numéricos).
2. Adicionar a coluna em um script de pré-implantação falhará na publicação quando tentar criar automaticamente a mesma coluna, uma segunda vez (mesmo que o script de pré-implantação seja escrito para ser idempotente) _{(este é realmente agravante, pois posso de outra forma pense em uma solução fácil)}
3. A alteração da coluna para NOT NULL em um script pós-implantação será revertida toda vez que ocorrer a atualização do esquema SSDT (portanto, no mínimo, nossa base de código não corresponderá entre o controle de origem e o que está realmente no servidor)
4. Adicionar a coluna como anulável agora com a intenção de mudar para NOT NULL no futuro não funciona em várias ramificações/bifurcações no controle de origem, pois os sistemas de destino não necessariamente terão a tabela no mesmo estado na próxima vez que forem atualizados _{( não que esta seja uma boa abordagem de qualquer maneira IMO)}

A abordagem que ouvi de outras pessoas é atualizar diretamente a definição da tabela (para que a atualização do esquema seja consistente), escrever um script de pré-implantação que mova todo o conteúdo da tabela para uma tabela temporária com a nova lógica de preenchimento de coluna incluída e, em seguida, mova as linhas de volta em um script pós-implantação. Isso parece arriscado como o inferno, e ainda irrita o Publish Preview quando detecta que uma coluna NOT NULL está sendo adicionada a uma tabela com dados existentes (já que a validação é executada antes do script de pré-implantação).

Como devo adicionar uma nova coluna não anulável sem arriscar dados órfãos ou mover dados para frente e para trás em cada publicação com longos scripts de migração que são inerentemente arriscados?

Obrigado.