Ronaldo's questions

Eu quero criar um alerta como os de Brent Ozar's How to Configure SQL Server Agent Alerts , mas não consegui encontrar uma mensagem relacionada ao erro que eu quero monitorar em sys.messages como diz o documento sp_add_alert e o problema que eu quero monitorar não gerou nenhum erro registrado. Se eu usar apenas sp_addmessage , acredito que ainda faltará a verificação que aciona o erro. E se eu criar um trabalho para fazer a verificação, não preciso do alarme, pois o próprio trabalho pode enviar o e-mail.

Qual é a maneira correta de criar um novo alerta?

Fundo

Recentemente as aplicações do meu ambiente começaram a falhar na conexão no SQL Server e demoramos um pouco para percebermos que o problema era devido a instância atingir aquele limite de 32k da instância (o SQL Server permite no máximo 32.767 conexões de usuário ). O connection eater era um aplicativo mal configurado e o desenvolvedor o corrigiu agora, mas não quero ser surpreendido novamente porque algum outro aplicativo causou a mesma situação, daí a necessidade do alerta. Um alerta para notificar quando o número de conexão atingir um limite como 10K seria ótimo, mas apenas alertar se o limite de 32K foi atingido ajudaria.

Estou simulando a situação em que faço um backup da instância A e o restauro na instância B . Eu segui o doc para Backup to Disk with Encryption . Como estou usando apenas meu desktop para simular todo o processo, após fazer o backup do banco de dados fiz um backup do certificado que foi utilizado da seguinte forma:

BACKUP CERTIFICATE MyTestDBBackupEncryptCert
TO FILE = N'C:\Databases\MyTestDBBackupEncryptCert.cert';

Então eu larguei o MyTestDBBackupEncryptCertcertificado e o banco de dados da minha instância para simular que agora é a instância B . Eu restaurei o certificado usando o comando do documento Criando um certificado a partir de um arquivo com uma pequena modificação onde comentei as linhas relacionadas às opções PRIVATE KEYe DECRYPTIONjá que o certificado foi criptografado pelo MASTER KEY(bem, foi o que entendi no primeiro link):

CREATE CERTIFICATE MyTestDBBackupEncryptCert   
    FROM FILE = N'C:\Databases\MyTestDBBackupEncryptCert.cert'   
    --WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk',   
    --DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');  
GO 

O certificado foi criado, mas quando tento restaurar o banco de dados, recebo este erro:

Msg 15507, Level 16, State 30, Line 33
Uma chave exigida por esta operação parece estar corrompida.
Msg 3013, Level 16, State 1, Line 33
RESTORE DATABASE está terminando de forma anormal.

A documentação não diz como obter a chave privada necessária para restaurar corretamente o certificado. Como faço para obtê-lo do SQL Server?

Tentando usar sp_stop_jobpara parar um trabalho simples que insere dados em uma tabela quando 100 linhas foram inseridas, fiquei confuso ao ver a tabela acabar com mais de 100 linhas todas as vezes. Na seção Observações do sp_stop_jobdocumento, diz:

sp_stop_job envia um sinal de parada ao banco de dados. Alguns processos podem ser interrompidos imediatamente e alguns devem atingir um ponto estável (ou um ponto de entrada para o caminho do código) antes de poderem parar.

Não entendo o que significa atingir um ponto estável (ou um ponto de entrada para o caminho do código) e acredito que o trabalho continua inserindo os dados na tabela porque ainda está atingindo esse ponto estável.

O código:

CREATE TABLE [dbo].[myStopJobTable](
    [id] [int] NULL,
    [nome] [varchar](50) NULL
);

Criei um job com uma etapa para executar o seguinte código:

WHILE (1=1)
BEGIN  

    INSERT INTO MyLab.dbo.myStopJobTable
    VALUES (1, 'Some Name');

    IF ((SELECT COUNT(*) FROM myStopJobTable) = 100)
    BEGIN
        EXEC msdb.dbo.sp_stop_job N'JobToBeStopped';
    END

END;

A partir desse código de exemplo, eu esperava que o trabalho parasse antes que a linha 101 fosse inserida, mas isso não acontece. Ao adicionar um BREAKdentro do IFbloco, o processo é interrompido antes que a inserção da linha 101 aconteça, mas nesse caso não está sp_stop_jobfuncionando.

Não quero uma correção para o trabalho, o que procuro é entender esse ponto estável (ou um ponto de entrada para o caminho do código) . O que isto significa?

Estou usando a função DATABASEPROPERTYEX para verificar a propriedade Recovery dos bancos de dados, mas o resultado está errado para o banco de dados AdventureWorks(posteriormente notei o mesmo comportamento estranho para bancos de dados ReportServere ReportServerTempDB).

Aqui está uma versão simplificada do que eu tentei:

USE master;
GO

CREATE PROCEDURE [dbo].[sp_isSimpleRecovery] 
AS
BEGIN
    PRINT DB_NAME() + CHAR(13);

    IF DATABASEPROPERTYEX(DB_NAME(), 'Recovery') = 'SIMPLE'
        PRINT '--Database in SIMPLE recovery model' + CHAR(13);
    ELSE
        PRINT '--Database in ANOTHER recovery model' + CHAR(13);
END

Criei o procedimento masterusando o prefixo sp_ para poder executá-lo de qualquer banco de dados. Mas quando eu executo ele AdventureWorkso resultado difere do mesmo código rodando sem o procedimento:

USE AdventureWorks2016;
GO

EXEC sp_isSimpleRecovery;

PRINT DB_NAME() + CHAR(13);

IF DATABASEPROPERTYEX(DB_NAME(), 'Recovery') = 'SIMPLE'
    PRINT '--Database in SIMPLE recovery model' + CHAR(13);
ELSE
    PRINT '--Database in ANOTHER recovery model' + CHAR(13);

Funcionou bem para muitos outros bancos de dados, mas para o AdventureWorks a saída é:

AdventureWorks2016

--Banco de dados em OUTRO modelo de recuperação

AdventureWorks2016

--Banco de dados em modelo de recuperação SIMPLES

O que está causando esse comportamento?

Bancos de dados de exemplo AdventureWorks que usei:

• AdventureWorks2014.bak
• AdventureWorks2016.bak

Versões do SQL Server:

• Microsoft SQL Server 2016 (SP2-GDR) (KB4532097) - 13.0.5102.14 (X64) 31 de dezembro de 2019
• Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1 (X64) 31 de outubro de 2020

Como posso medir a distância de uma consulta do limite aceitável (em relação ao desempenho) com base no número de leituras relatadas após sua execução?

Exemplo: Só para me esclarecer, se estivéssemos falando de milhas por galão de um carro cujo consumo de combustível é de 40 mpg e devemos percorrer uma distância de 40 milhas, o limite para o desempenho ideal é de 1 galão para viajar aquela distância. Como nem todo mundo dirige como Max Verstappen ou Lewis Hamilton , é aceitável gastar 1,3 ou até 2 galões para percorrer essa distância se você for um mau piloto. Mas se você gastar 30 galões para chegar a um lugar que fica a 40 milhas de distância, posso dizer que você certamente está indo na direção errada.

Background: às vezes eu executo sp_whoisactive apenas para encontrar uma consulta relatando algo como 1.248.909 leituras (já que cada página tem 8 KB, isso significa que cerca de 9,7 GB de dados foram processados ​​de um banco de dados de 4,4 GB). Isso significa que uma consulta lê o equivalente a todo o banco de dados duas vezes. Quando vejo algo assim, meu instinto me diz que algo está errado, mas o desenvolvedor às vezes argumenta que " é uma consulta complexa e era esperado que se comportasse assim ". Então eu tenho que melhorar a consulta para provar que, embora uma consulta complexa possa consumir mais recursos, há um limite que mostra que você está fora do caminho.

Existe uma fórmula para calculá-lo com base no número de tabelas envolvidas, tipos de junções, uso de funções e assim por diante? Se não, existe uma regra prática que poderia ser usada para fazer um argumento lógico?

Após configurar o XE em 23 de novembro de 2020 para capturar alguns erros, pude contar 2.163.665 ocorrências do mesmo erro provenientes de solicitações de um aplicativo legado (que provavelmente não será corrigido tão cedo). Os erros podem ser classificados na 1ª e 2ª etapas para Processamento de uma Instrução SQL :

1. O DBMS primeiro analisa a instrução SQL. Ele divide a declaração em palavras individuais, chamadas tokens, garante que a declaração tenha um verbo válido e cláusulas válidas, e assim por diante. Erros de sintaxe e erros ortográficos podem ser detectados nesta etapa.
2. O DBMS valida a instrução. Ele verifica a instrução no catálogo do sistema. Todas as tabelas nomeadas na instrução existem no banco de dados? Todas as colunas existem e os nomes das colunas são inequívocos? O usuário tem os privilégios necessários para executar a instrução? Certos erros semânticos podem ser detectados nesta etapa.

O documento também diz que Parsing uma instrução SQL não requer acesso ao banco de dados e pode ser feito muito rapidamente , mas a quantidade de solicitações disparadas no servidor me fez pensar que deveria verificar.

Eu gostaria de medir o impacto no consumo de recursos causado por essas solicitações erradas sendo rejeitadas nessas etapas. Tem algum jeito de fazer isso?

O Query Optimizer cria mais de um plano de execução possível. Como posso ver todos os planos gerados antes da escolha do executado?

Microsoft diz :

O Otimizador de Consultas deve analisar os planos possíveis e escolher aquele com o menor custo estimado.

Por favor, note que não estou falando de Execution Plan Caching e Reuse , estou falando de planos de execução candidatos, aqueles que foram gerados mas não selecionados para serem executados.

De acordo com o artigo de Benjamin Nevarez The SQL Server Query Optimizer :

A geração de planos de execução candidatos é realizada dentro do Query Optimizer usando regras de transformação, e o uso de heurísticas limita o número de escolhas consideradas para manter o tempo de otimização razoável. Os planos candidatos são armazenados na memória durante a otimização, em um componente chamado Memo.

Seria possível visualizar os planos candidatos renderizados da forma como podemos exibir um plano de execução real ?

Bancos de dados do sistema SQL Server , que eu saiba, sempre possuem esses mesmos IDs e já vi muitos scripts de manutenção na internet contando com o predicado WHERE database_id > 4para excluí-los das ações do script.

Além disso, se eu executar SELECT name, schema_id FROM sys.schemas;em um novo banco de dados de usuário, recebo:

name                 schema_id
dbo                  1
guest                2
INFORMATION_SCHEMA   3
sys                  4
db_owner             16384
db_accessadmin       16385
db_securityadmin     16386
db_ddladmin          16387
db_backupoperator    16389
db_datareader        16390
db_datawriter        16391
db_denydatareader    16392
db_denydatawriter    16393

Executei essa consulta em duas instâncias diferentes, uma no SQL Server 2016 e a outra no SQL Server 2005, e ambas retornaram o mesmo resultado.

Perguntas:

1. Existe alguma situação (ou versão do sql server) em que os bancos de dados do sistema master, tempdb, model e msdb teriam um database_id diferente de 1,2,3,4 respectivamente?
2. Posso realmente confiar no fato de que os esquemas listados sempre terão os mesmos IDs em qualquer instância do SQL Server para que eu possa escrever scripts de manutenção com base nesses IDs?

Verificando as versões de compilação do SQL Server 2014 para identificar a atualização correta para o meu servidor, fiquei um pouco confuso sobre qual atualização usar. Fiz algumas pesquisas e descobri que devo aplicar o Service Pack mais recente e a atualização cumulativa . Ok, mas restava uma dúvida:

Atualmente, tenho o SP2 instalado e há uma CU para o SP2 com a data de lançamento de 29 de julho de 2019 , mas o SP3 foi lançado em 30 de outubro de 2018 .

A meu ver, o CU15-CU18 para SP2 não deveria existir. (Sei que estou errado, mas não sei por quê)
O que há em um service pack que talvez não queira instalá-lo, mas instalará outras CUs lançadas após o SP mais recente?

Disposto a resolver esse problema relacionado a um valor errado para a is_media_read_onlypropriedade do banco de dados fiz algumas pesquisas e testes, mas no final não consegui descobrir o que exatamente aciona o UPDATE da coluna is_media_read_onlyno sys.database_files.

De acordo com a documentação sys.database_files , a coluna is_media_read_onlydeve ter um dos dois valores possíveis:

1 = O arquivo está em mídia somente leitura.

0 = O arquivo está em mídia de leitura e gravação.

Com essas informações fiz o seguinte experimento com duas versões diferentes do SQL Server:

Microsoft SQL Server 2014 (SP3-GDR) (KB4505218) - 12.0.6108.1 (X64)
Microsoft SQL Server 2017 (RTM-CU16) (KB4508218) - 14.0.3223.3 (X64)

Conectei um pen-drive no meu notebook (Drive E:) e criei um banco de dados da seguinte forma:

CREATE DATABASE [MyDB]
 ON  PRIMARY 
( NAME = N'MyDB_01', FILENAME = N'D:\DataBases\MyDB_01.mdf'), 
 FILEGROUP [SECONDARY] 
( NAME = N'MyDB_02', FILENAME = N'E:\DataBasesPendrive\MyDB_02.ndf')
 LOG ON 
( NAME = N'MyDB_log', FILENAME = N'D:\DataBases\MyDB_log.ldf')
GO

sys.database_files consultados:

USE MyDB;
GO
SELECT file_id, name, physical_name, is_media_read_only, is_read_only, state_desc  
FROM sys.database_files;
GO

Este foi o resultado:

Depois que o banco de dados foi criado, abri um prompt do CMD e executei o diskpart. No utilitário, emiti os seguintes comandos:

list disk
select disk 2
attributes disk set readonly
attributes disk

O disco 2 (meu pen-drive) agora é uma mídia somente leitura , então esperava que o is_media_read_onlyvalor mudasse, mas quando sys.database_filesconsultei novamente não houve alteração: is_media_read_only ainda era 0. Então comecei a fazer procedimentos aleatórios com o banco de dados para ver se qualquer coisa faria o SQL Server perceber que o banco de dados agora estava em uma mídia somente leitura e atualizar o valor para 1. Eu poderia conseguir isso em duas situações:

1º: alterando o banco de dados para o modo READ_ONLY e de volta para READ_WRITE. (deve ser ambas as ações, apenas uma delas não fará o truque):

USE master;
GO
ALTER DATABASE MyDB SET READ_ONLY;
ALTER DATABASE MyDB SET READ_WRITE;
GO

2º: desanexar e anexar o banco de dados novamente.

Estas foram as duas situações em que eu poderia ter is_media_read_onlyatualizado 1como você pode ver na foto:

Agora era hora de reverter o pen-drive para read_write novamente, então voltei diskparte executei:

select disk 2
attributes disk clear readonly

Quando mudei o disco 2 para ler o modo de gravação novamente, a atualização sóis_media_read_only seria feita após um procedimento de desconexão e anexação. Eu até tentei reiniciar a instância do SQL Server esperando que atualizasse o valor, mas sem sorte. 0

Até um desanexar e anexar do banco de dados SQL Server me deixaria com valor 1para is_media_read_onlymesmo que o disco não estivesse mais READ_ONLY.

Assim, minhas perguntas: O que exatamente aciona o UPDATEda coluna is_media_read_onlyem sys.database_files? O comportamento mostrado com este experimento pode ser um bug do SQL Server?

Minha pergunta está relacionada ao seguinte experimento com duas instâncias:

Instância do SQL Server 2017 Express (Microsoft SQL Server 2017 (RTM-CU16)) Instância do
SQL Server 2014 Express (Microsoft SQL Server 2014 (SP2-CU18))

Usei a função ENCRYPTBYPASSPHRASE para criptografar um texto e usei o resultado como @ciphertext para DECRYPTBYPASSPHRASE . O resultado dos meus testes foram estes:

De acordo com esta correção da Microsoft ,

[...] SQL Server 2017 usa o algoritmo de hash SHA2 para fazer o hash da senha. SQL Server 2016 e versões anteriores do SQL Server usam o algoritmo SHA1 que não é mais considerado seguro.

Mas como ele sabe qual foi o algoritmo usado para criptografar os dados se não houver nenhum argumento relacionado a isso na função DECRYPTBYPASSPHRASE? Faz parte dos dados criptografados?

Pelos resultados dos meus testes, acho que o SQL Server sempre usa a versão mais recente do algoritmo disponível na instância para criptografar dados, mas tenta todos os algoritmos para descriptografar os dados até encontrar um que se ajuste ou retorne NULL quando nenhum algoritmo correspondente for encontrado . É apenas um palpite, já que não consegui encontrar nenhuma maneira de verificar qual algoritmo de hash o SQL Server usou para descriptografar os dados criptografados.