usr's questions

Estou acompanhando esta questão sobre valores estranhos em uma PERSISTEDcoluna computada. A resposta faz algumas suposições sobre como esse comportamento surgiu.

Estou perguntando o seguinte: isso não é um bug definitivo? As PERSISTEDcolunas podem se comportar dessa maneira?

DECLARE @test TABLE (
    Col1 INT,
    Contains2 AS CASE WHEN 2 IN (Col1) THEN 1 ELSE 0 END PERSISTED) --depends on Col1

INSERT INTO @test (Col1) VALUES
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5)),
    (ABS(CHECKSUM(NEWID()) % 5))

SELECT * FROM @test --shows impossible data

UPDATE @test SET Col1 = Col1*1 --"fix" the data by rewriting it

SELECT * FROM @test --observe fixed data

/*
Col1    Contains2
2   0
2   0
0   1
4   0
3   0

Col1    Contains2
2   1
2   1
0   0
4   0
3   0
*/

Observe que os dados parecem "impossíveis" porque os valores da coluna calculada não correspondem à sua definição.

É bem conhecido que funções não determinísticas em consultas podem se comportar de maneira estranha, mas aqui isso parece violar o contrato de colunas computadas persistentes e, portanto, deveria ser ilegal.

Inserir números aleatórios pode ser um cenário artificial, mas e se estivéssemos inserindo NEWID()valores ou SYSUTCDATETIME()? Acho que essa é uma questão relevante que pode se manifestar de forma prática.

Uma grande consulta está sendo executada agora. O resultado de uma junção de hash em modo de lote que se espalha para o disco é transmitido para uma tabela temporária usando select into. A consulta está mostrando tipos de espera HTDELETEe HTREPARTITIONocasionalmente. A consulta não está usando índices columnstore.

Tenho certeza de que esses tipos de espera são normais para junções de hash em modo de lote que se espalham no disco. Estou tentando entender o que esses tipos de espera significam por curiosidade. Talvez esse insight possa ajudar a otimizar a consulta ou encontrar problemas. Então, o que esses tipos de espera significam e em que circunstâncias eles são esperados?

Nenhum índice columnstore. Estou usando o left join ZeroRowCsTable on 0=1truque para ativar o modo de lote para tabelas de modo de linha.

Eu entendo que o COMPUTE BYrecurso está obsoleto. Por curiosidade, gostaria de saber como o processador de consulta executa uma COMPUTE BYconsulta. O plano de execução não contém nenhuma dica:

Como o processador de consultas é executado COMPUTE BYe como posso obter um plano de execução preciso?

Aqui está um pouco de T-SQL para brincar:

 create table employee(
    ID          int,
    name        nvarchar (10),
    salary      int,
    start_date  datetime,
    city        nvarchar (10),
    region      char (1))
GO
insert into employee (ID, name,    salary, start_date, city,       region)
              values (1,  'Jason', 40420,  '02/01/94', 'New York', 'W')

insert into employee (ID, name,    salary, start_date, city,       region)
              values (2,  'Robert',14420,  '01/02/95', 'Vancouver','N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (3,  'Celia', 24020,  '12/03/96', 'Toronto',  'W')

insert into employee (ID, name,    salary, start_date, city,       region)
              values (4,  'Linda', 40620,  '11/04/97', 'New York', 'N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (5,  'David', 80026,  '10/05/98', 'Vancouver','W')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (6,  'James', 70060,  '09/06/99', 'Toronto',  'N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (7,  'Alison',90620,  '08/07/00', 'New York', 'W')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (8,  'Chris', 26020,  '07/08/01', 'Vancouver','N')
GO

insert into employee (ID, name,    salary, start_date, city,       region)
              values (9,  'Mary',  60020,  '06/09/02', 'Toronto',  'W')
GO

select * from employee
GO
/*
ID          name       salary      start_date              city       region
----------- ---------- ----------- ----------------------- ---------- ------
          1 Jason            40420 1994-02-01 00:00:00.000 New York   W
          2 Robert           14420 1995-01-02 00:00:00.000 Vancouver  N
          3 Celia            24020 1996-12-03 00:00:00.000 Toronto    W
          4 Linda            40620 1997-11-04 00:00:00.000 New York   N
          5 David            80026 1998-10-05 00:00:00.000 Vancouver  W
          6 James            70060 1999-09-06 00:00:00.000 Toronto    N
          7 Alison           90620 2000-08-07 00:00:00.000 New York   W
          8 Chris            26020 2001-07-08 00:00:00.000 Vancouver  N
          9 Mary             60020 2002-06-09 00:00:00.000 Toronto    W
*/


-- COMPUTE BY
SELECT salary, Name
FROM Employee
ORDER BY ID, Name
COMPUTE SUM(Salary)
GO

/*
salary      Name
----------- ----------
      40420 Jason
      14420 Robert
      24020 Celia
      40620 Linda
      80026 David
      70060 James
      90620 Alison
      26020 Chris
      60020 Mary
sum
===========
     446226
*/

GO

drop table employee

( Fonte )

Os resultados deste single SELECTsão:

Estou construindo um índice Columnstore agora. A saída de sp_whoisactive:

Esta é uma máquina de desenvolvimento. Nada mais está em execução. O servidor tem 8 GB de memória disponível para ele. A CPU está quase ociosa. A compilação parece estar na fase de amostragem do dicionário global porque sqlservr.exeestá fazendo IO de disco aleatório e escrevendo quase nada.

Isso SLEEP_TASKindica que posso tomar medidas para tornar a construção do índice mais rápida? Por que a compilação está "dormindo" às vezes?

Este é o SQL Server 2014 SP1.

Quando um índice columnstore está sendo usado em uma consulta, o SQL Server pode usar o modo de lote. A documentação é escassa sobre o que pode ser executado em modo de lote e o que não pode. Observe o seguinte plano de consulta (motivador), onde um número surpreendente de coisas é executado em modo de lote (verde):

_{(Este é um plano estimado. Usei o plano real para verificar se o modo de execução real é realmente em lote.)}

Observe que apenas o lado da construção de T1 usa um índice columnstore. Todas as entradas de teste (T2 e T3) são rowstores. Seus dados parecem fazer a transição para o modo de lote. Sempre pensei que o modo de lote está sendo usado para o fluxo de dados que é executado apenas no lado da sonda.

Parece que os dados podem fazer a transição para o modo de lote, mesmo que não sejam originários de um índice columnstore. Isso levanta a questão: por que o SQL Server também não usa o modo de lote para consultas somente de armazenamento de linha? Pode ser benéfico para alguns deles. O uso de um índice columnstore é um requisito formal necessário para fazer o SQL Server considerar o modo batch? Poderíamos talvez adicionar uma tabela fictícia de linha zero com um índice columnstore para induzir o modo de lote e obter ganhos de desempenho?

O que exatamente pode ser executado em modo de lote a partir do SQL Server 2014?

Em um determinado tutorial que li, o autor está filtrando sys.indexescom base no predicado index_id < 256000. O que isso faz?

Background: Estou no processo de otimização de uma consulta estilo Data Warehouse com tabelas de grandes dimensões. O otimizador não gosta de escolher o plano típico de junção de esquema em estrela no momento. Quando forço esse plano (usando uma dica de índice), posso ver que esse plano de star join é um pouco mais caro do que o plano que estou usando agora. Suspeito que seja porque as dimensões não cabem na RAM da minha máquina de desenvolvimento. A versão é SQL Server 2014, mas espero que a pergunta possa ser generalizada para todas as versões recentes.

Pergunta: Existe alguma forma de fazer o otimizador acreditar que tem muita memória RAM à sua disposição? Para fins de teste, gostaria de obter o plano que ele geraria se tivesse 1 TB de RAM disponível (em vez de 8 GB no momento).

Na DELETEinstrução a seguir, tento excluir todas as linhas, exceto a primeira ordenada por alguns critérios. (A consulta real faz mais sentido, isso é apenas uma reprodução. Todo o sys.objectsmaterial é apenas para gerar dados de teste.)

Observe o filtro r <> 1. No entanto, a OUTPUTcláusula gera linhas excluídas com r = 1. Como isso pode ser?

USE tempdb
SET XACT_ABORT ON

BEGIN TRAN

    SELECT *
    INTO #o
    FROM sys.objects

    SELECT TOP 2 name FROM #o ORDER BY object_id --debug output

    DELETE k
    OUTPUT Deleted.name, Deleted.r
    FROM (
        SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r
        FROM #o k
    ) k
    WHERE r <> 1 --OUTPUT returns rows with (r = 1)

    SELECT TOP 2 name FROM #o ORDER BY object_id --debug output

ROLLBACK

Resultados da consulta:

(O terceiro conjunto de resultados está completo - apenas uma linha.)

Observe que todas as linhas, exceto a primeira, foram excluídas. A ordem de numeração da coluna rparece não corresponder ao que foi solicitado. E há uma linha com r = 1.

Este é o SQL Server 2014 CU3 com o sinalizador de rastreamento 4199 ativado.

Como posso saber se uma instância do SQL Server já teve replicação em uso ou não? Estou depurando um problema com um software de terceiros. Este software (Red Gate SQL Backup) informa que o banco de dados de distribuição está ausente. A empresa pediu para descobrir se este servidor já usou replicação ou não. Como posso descobrir?

A replicação não parece estar instalada e o banco de dados de distribuição não está presente. Nenhum dos bancos de dados usa replicação.

Há sempre uma chance muito pequena de que um backup que acabou de ser gravado no disco esteja corrompido. Os discos não são 100% confiáveis. Os bits podem ser invertidos com probabilidade muito baixa. Já vi isso acontecer em várias máquinas em discos que funcionam bem.

É possível detectar tais erros usando somas de verificação de backup. Mas o que você faz quando um erro é encontrado? Se um backup de log for corrompido, a cadeia de backup de log será interrompida. Se for um backup completo dependente, os backups diferenciais agora não podem ser usados. Um novo backup completo é necessário para reiniciar o agendamento de backup normal.

O que pode ser feito para evitar a perda aleatória de backups devido à corrupção de dados espúria?

Entendo que posso usar a EXECUTE AS OWNERcláusula como parte de um procedimento que criei para fazer com que o corpo desse procedimento seja executado como um usuário diferente. Meu objetivo é executar um comando que requer o sysadminpapel ( DBCC TRACEON(1224)). Este procedimento deve ser chamado por um usuário sem privilégios.

Eu executei o seguinte script no sausuário:

SELECT USER_NAME(), USER_ID(), IsSysAdmin = IS_SRVROLEMEMBER('sysadmin')
-- dbo  1   1

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'MyProc')
    DROP PROCEDURE MyProc

GO
CREATE PROCEDURE MyProc
WITH EXECUTE AS OWNER
AS 
    SELECT USER_NAME(), USER_ID(), IsSysAdmin = IS_SRVROLEMEMBER('sysadmin');
-- dbo  1   0

    DBCC TRACEON(1224)
--Msg 2571, Level 14, State 3, Procedure MyProc, Line 7
--User 'dbo' does not have permission to run DBCC TRACEON.

RETURN
GO

EXEC MyProc

A saída está embutida nos comentários. Acontece que fora do procedimento pareço ter sysadminadesão, mas não dentro do procedimento.

O procedimento é de propriedade do dbousuário. Entendo que não é possível conceder a sysadminfunção a um usuário do banco de dados (pelo menos a GUI não oferece essa possibilidade). Portanto, não vejo como poderia fazer um usuário de banco de dados ter uma função de servidor.

Eu também tentei o EXECUTE AS 'sa'que resulta em Cannot execute as the user 'sa', because it does not exist or you do not have permission.. A documentação afirma que só posso especificar um nome de usuário, não um nome de login. Então eu entendo porque isso não funcionou.

Como posso executar meu procedimento com sysadminassociação de função?

Como parte de uma MERGEconsulta que desejo executar, gostaria de afirmar em tempo de execução que uma determinada condição é válida. Quando um MERGE-match é encontrado, gostaria de atualizar uma determinada coluna e executar a seguinte lógica:

1. Se a coluna de destino for NULL, escreva o valor de origem
2. Se o destino for NOT NULL, afirme que o destino e a origem são idênticos

Espero que os dois valores sejam sempre idênticos no caso 2, mas posso ter cometido um erro (tenho um bug). Quando isso acontecer, gostaria de travar a instrução e deixar meu aplicativo relatar o erro. Esta é uma condição de erro muito rara, não algo que possa acontecer como parte do processamento normal.

Então, eu estava pensando que poderia abusar de uma exceção de divisão por zero para acionar uma falha:

MERGE
...
WHEN MATCHED BY TARGET THEN UPDATE SET
  TargetCol = CASE
    WHEN TargetCol IS NULL THEN SourceCol
    WHEN TargetCol = SourceCol THEN SourceCol
    ELSE 0/0 END --crash!

Isso funcionará de forma confiável? Existe uma razão para isso não ser feito?

Estou lidando com um problema em que os instantâneos do banco de dados não podem ser descartados após a atualização do SQL Server para 2014 . Os instantâneos estão desativados, mas não podem ser descartados.

Agora, embora o item Connect tenha um comentário decepcionante, presumo que isso será corrigido eventualmente. No entanto, preciso lidar com isso logo.

Existe uma maneira de descartar um instantâneo do banco de dados que não pode ser excluído devido a esse bug? Temo ter esgotado todos os meios documentados para fazer isso. Pode levar modificações não documentadas nas tabelas do sistema. Existe uma maneira de forçar um banco de dados (instantâneo) a ser descartado?

Os backups diferenciais usam as páginas Mapa Alterado Diferencial para determinar de quais páginas fazer backup. Eu gostaria de investigar uma estratégia de backup envolvendo backups diferenciais. Para esse fim, gostaria de rastrear automaticamente a quantidade de dados alterados ao longo do tempo para vários bancos de dados.

Eu gostaria de consultar programaticamente o Mapa Diferencial Alterado para determinar quantas páginas foram alteradas.

Isso parece ser possível de DBCC PAGEacordo com http://www.practicalsqldba.com/2013/07/sql-server-understanding-differential.html . Existe uma maneira mais limpa ou "mais documentada" do que essa? Parece que isso será estranho e propenso a erros de implementar.

O SQL Server pode executar restaurações pontuais usando o argumento STOPAT . Em que fuso horário está esse valor de data e hora?

• É UTC?
• É local para o servidor em que o backup foi feito?
• É local para o servidor o backup é restaurado?

Eu tenho um banco de dados que foi marcado SUSPECTporque o volume em que os dados e o arquivo de log estavam desapareceu repentinamente (um acidente - esta é uma estação de trabalho do desenvolvedor). Eu tentei trazer o banco de dados de volta online:

ALTER DATABASE D SET ONLINE

Isso não fez nada. Na web o procedimento sp_resetstatusestá sendo recomendado , mas parece estar obsoleto .

Não quero fugir DBCC CHECKDB, pelo menos não agora. O banco de dados é muito grande. Qual é a maneira oficial de colocar um SUSPECTbanco de dados online o mais rápido possível ?

Parece que o SQL Server considera 0x e 0x00 valores iguais:

SELECT CASE WHEN 0x = 0x00 THEN 1 ELSE 0 END

Isso gera 1.

Como posso obter um verdadeiro comportamento de comparação bit a bit binário? Além disso, quais são as regras exatas sob as quais dois (var)binaryvalores são considerados iguais?

Observe também o seguinte comportamento:

--prints just one of the values
SELECT DISTINCT [Data]
FROM (VALUES (0x), (0x00), (0x0000)) x([Data])

--prints the obvious length values 1, 2 and 3
SELECT DATALENGTH([Data]) AS [DATALENGTH], LEN([Data]) AS [LEN]
FROM (VALUES (0x), (0x00), (0x0000)) x([Data])

O pano de fundo da questão é que estou tentando deduplicar dados binários. Eu preciso de GROUP BYdados binários, não apenas comparar dois valores. Estou feliz por ter notado esse problema.

Observe que HASHBYTESisso não oferece suporte a LOBs. Eu também gostaria de encontrar uma solução mais simples.

TL;DR: Eu tenho uma corrupção não corrigida em uma exibição indexada. Aqui estão os detalhes:

Corrida

DBCC CHECKDB([DbName]) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS

em um dos meus bancos de dados produz o seguinte erro:

Msg 8907, Nível 16, Estado 1, Linha 1 O índice espacial, índice XML ou exibição indexada 'ViewName' (objeto ID 784109934) contém linhas que não foram produzidas pela definição de exibição. Isso não representa necessariamente um problema de integridade com os dados desse banco de dados. (...)

CHECKDB encontrou 0 erros de alocação e 1 erro de consistência na tabela 'ViewName'.

repair_rebuild é o nível mínimo de reparo (...).

Entendo que esta mensagem indica que os dados materializados da exibição indexada 'ViewName' não são idênticos aos produzidos pela consulta subjacente. No entanto, a verificação manual dos dados não revela nenhuma discrepância:

SELECT * FROM ViewName WITH (NOEXPAND)
EXCEPT
SELECT ...
from T1 WITH (FORCESCAN)
join T2 on ...

SELECT ...
from T1 WITH (FORCESCAN)
join T2 on ...
EXCEPT
SELECT * FROM ViewName WITH (NOEXPAND)

NOEXPANDfoi usado para forçar o uso do (somente) índice em ViewName. FORCESCANfoi usado para impedir que a correspondência de exibição indexada aconteça. O plano de execução confirma que ambas as medidas estão funcionando.

Nenhuma linha está sendo retornada aqui, o que significa que as duas tabelas são idênticas. (Existem apenas colunas inteiras e guid, os agrupamentos não entram em jogo).

O erro não pode ser corrigido recriando o índice na exibição ou executando DBCC CHECKDB REPAIR_ALLOW_DATA_LOSS. Repetir as correções também não ajudou. Por que DBCC CHECKDBrelata esse erro? Como se livrar dele?

(Mesmo que a reconstrução corrigisse, minha pergunta ainda permaneceria - por que um erro é relatado, embora minhas consultas de verificação de dados sejam executadas com êxito?)

Atualização: O bug foi corrigido em algumas versões. Não consigo mais reproduzi-lo no SQL Server 2014 SP2 CU 5. O 2014 SP2 KB contém uma correção sem artigo do KB: Creating non-clustered index causes DBCC CheckDB With Extended_Logical_Checks to raise corruption error. Os dois bugs de conexão sobre isso foram fechados:

• https://connect.microsoft.com/SQLServer/feedback/details/847233/creating-non-clustered-index-causes-dbcc-checkdb-with-extended-logical-checks-to-raise-corruption-error
• https://connect.microsoft.com/SQLServer/feedback/details/795478/unfixable-dbcc-checkdb-error-that-is-also-a-false-positive-and-otherwise-strange

Pretendo usar a UNIQUEIDENTIFIERcomo uma chave de acesso que os usuários possam usar para acessar determinados dados. A chave funcionará como uma senha nesse sentido.

Preciso gerar vários desses identificadores como parte de uma INSERT...SELECTdeclaração. Por motivos de arquitetura, desejo gerar os identificadores do lado do servidor neste caso.

Como posso gerar um aleatório com segurança UNIQUEIDENTIFIER? Observe que NEWIDisso não seria aleatório o suficiente, pois não promete nenhuma propriedade de segurança. Estou procurando o equivalente do SQL Server de System.Security.Cryptography.RandomNumberGenerator porque preciso de IDs indecifráveis. Qualquer coisa baseada em CHECKSUM, RANDou GETUTCDATEtambém não se qualificaria.

Considero agendar um trabalho do SQL Server que reorganize todas as tabelas todas as noites . Não temos empresa, então não podemos reconstruir online (isso significa que precisamos nos reorganizar). Também não queremos investir muito tempo na fragmentação e manutenção do espaço, por isso buscamos uma solução que possa ser implementada rapidamente e que não exija mais investimentos de tempo no futuro.

O teste em um backup de produção restaurado mostrou que reorganizar é quase tão bom quanto reconstruir para reduzir a fragmentação e recuperar espaço.

Esta é uma ideia razoável? Este esquema cria problemas de disponibilidade ou manutenção? Esta é uma solução razoável a longo prazo?