All perguntas(dba)

Fico confuso com a terminologia como liberar e gravar no disco e sincronizar no disco. Alguém poderia me explicar qual é a diferença entre liberar, gravar e sincronizar no disco, especialmente no contexto do MySQL.

Eu costumava pensar que ambas eram as mesmas operações, mas ao ler vários conceitos isso me confunde.

Por exemplo. A documentação diz para a variável rpl_semi_sync_source_wait_point que, se eu defini-la como AFTER_SYNC , a origem grava as transações primeiro no log bin e na réplica e, em seguida, sincroniza o log binário no disco.

https://dev.mysql.com/doc/refman/8.0/en/replication-options-source.html#sysvar_rpl_semi_sync_source_wait_point

AFTER_SYNC (the default): The source writes each transaction to its binary log and the replica, and syncs the binary log to disk. 

Esta afirmação é em si confusa. Primeiro diz source writes each transaction to its binary log and the replicae depois dizsyncs the binary log to disk.

Então, o que exatamente está acontecendo neste caso. O que exatamente é liberar, sincronizar e gravar no disco no contexto do MySQL? A questão não é sobre sync_binlog ou qualquer outro parâmetro no MySQL. A questão é sobre liberar, sincronizar e escrever e quando essas atividades acontecem?

Novamente quando tentei ler mais, no link a seguir, explica que existe um cache binlog, um cache de página e uma operação fsync. Assim, quando uma transação acontece, se bem entendi, o buffer fica sujo, o conteúdo da transação é gravado no cache do log binário (no nível do MySQL), que é um cache separado para cada sessão, então o cache do log binário é gravado no cache da página ( no nível Linux), dependendo do sync_binlogvalor, ele é liberado/gravado/sincronizado com os arquivos no disco.

https://betterprogramming.pub/understanding-mysqls-binlog-4ac8de4d20ee

Eu uso uma transformação de coluna derivada para substituir um valor assim que uma condição em duas outras colunas for atendida. Como posso ver no fluxo de dados com que frequência um valor muda com essa substituição? Existe uma maneira de mostrar isso na seta de saída ou de que outra forma posso mostrar as alterações dentro do fluxo de dados?

PS (não necessário para perguntas e respostas)

Quero verificar se a entrada da string é igual à saída da pesquisa. Mas às vezes, nomes (nomes e sobrenomes) têm mais de um nome no valor, como Taylor-Smith contra Smith ou Bob Thomas contra Bob. As pessoas podem se casar ou, às vezes, preencher seus dois primeiros nomes, de modo que os dados podem não corresponder a duas contas de dados mestre.

(DT_BOOL)(FINDSTRING(name,[Lookup.name],1)) ? 1 : [score_name]

O valor da coluna score_nameé substituído 1assim que for nameencontrado dentro de Lookup.name, caso contrário o valor é mantido entre 0 e 1.

"Nome na pesquisa?":

Você poderia pegar qualquer outro exemplo, como Substituir valores da coluna? , a questão não depende do que é feito.

Eu tenho um banco de dados que contém um índice de texto completo excessivamente grande. DBCC CHECKDB WITH PHYSICAL_ONLYleva cerca de 25 minutos neste banco de dados, mas DBCC CHECKDB WITH DATA_PURITY, EXTENDED_LOGICAL_CHECKSleva cerca de 2 horas a mais. Isso me sugere que PHYSICAL_ONLYignore os índices de texto completo.

Estou correto? Quais parâmetros precisam ser passados ​​para CHECKDBque ele verifique os índices de texto completo?

Eu tenho um banco de dados que possui colchetes em seu nome. (Não pergunte! :-\)

Ele aparece no SSMS, mas não no Azure porque foi cancelado durante a criação. Não aparece na listagem do banco de dados no Azure. Ele aparece no SSMS, mas não pode ser excluído clicando com o botão direito. Gostaria de eliminá-lo usando SQL, mas não consigo descobrir a sintaxe para incluir os colchetes como caracteres para o nome do banco de dados, em oposição ao caractere de escape/delimitador.

Eu tentei o seguinte sem sucesso:

descartar banco de dados [xxx_2024-07-24]

Msg 911, Nível 16, Estado 1, Linha 1 Banco de dados 'xxx_2024-07-24' não existe. Certifique-se de que o nome foi digitado corretamente.

descartar banco de dados [[xxx_2024-07-24]]

Msg 105, Nível 15, Estado 1, Linha 1 Aspas não fechadas após a sequência de caracteres '[xxx_2024-07-24]'. Msg 102, Nível 15, Estado 1, Linha 1 Sintaxe incorreta próxima a '[xxx_2024-07-24]'.

descartar banco de dados [[[xxx_2024-07-24]]]

Msg 911, Nível 16, Estado 1, Banco de dados da linha 1 '[[xxx_2024-07-24]' não existe. Certifique-se de que o nome foi digitado corretamente.

Alguma ideia sobre a sintaxe correta para eliminar esse banco de dados? [xxx_2024-07-24]

Eu tenho dois servidores.

Um lixo recolhido, colado com fita adesiva e chiclete. É uma mistura de peças Dell R620 e Gen 8 HPE com 40 núcleos/1,5 TB de RAM. Executando o Ubuntu 22.04 e o SQL Server 2022 Dev Version. Raid 10 SSDs de 3,7 TB. O MDOP está definido como 16.

O outro é uma VM hospedada em um cluster de cabeça tripla Xenon Gold com SSD Nas em todos os 9 metros. Servidor 2019, padrão sql server 2022. 128 gigas/16 núcleos alocados. O MDOP também está definido como 16. O banco de dados foi um backup/restauração do servidor de teste.

A tabela principal abriga dados transmitidos ao vivo com uma média de 100 a 200 mil novos registros por dia, atualmente se aproximando de 30 bilhões de linhas com 4 colunas. Ele é configurado com um índice de armazenamento de coluna e os dados são classificados e preenchidos por meio de um campo de carimbo de data/hora.

Fazer uma consulta simples leva de segundos a minutos no servidor de teste, mas de minutos a quase uma hora no servidor que será de produção em breve.

O servidor de teste mostra todos os 16 núcleos sendo utilizados. No servidor de produção utilizará apenas dois núcleos. Se eu executar várias instâncias da mesma consulta, o servidor de produção consumirá um conjunto adicional de dois núcleos por consulta executada. Isso pode ser feito até que a CPU esteja com 100% de utilização.

A consulta que estou testando é select top 100 * from mlinkdatacsunixtime order by serialtime descNo servidor de teste, ela é executada em 1 minuto a 1,5 min. Na produção são 11 minutos.

Eu verifiquei: soquete VM vs contagem de núcleos. 2 soquetes/8 núcleos cada. O servidor SQL verificado mostra todos os 16 como online e visíveis. Combinei todas as opções entre as duas instâncias sql. Configurações duplicadas do servidor e do banco de dados MDOP. Consulta executada diversas vezes para tentar carregar a tabela na memória. A tabela tem cerca de 50 GB, o servidor Prod mostra apenas 63 GB dos 128 utilizados após testar várias consultas. O servidor de teste gira em torno de 250 GB, mas tem muito mais recursos para outros projetos.

Realmente espero que alguém possa me dizer que perdi algo estúpido.

Meu próximo teste é reconstruir o servidor com a versão sql Dev para ver se é algo com padrão sql. Minha última gota é tentar convencer o administrador e o gerenciamento do sistema a me deixar executar um servidor Linux em produção.

Dadas estas duas tabelas:

Tabela 1

Mesa 2

Eu gostaria de criar esta tabela:

Tabela 3

onde diff= (valor de id2) - (valor de id1).

Tentei fazer isso com várias subconsultas aninhadas com junções, mas parecia uma maneira bastante elaborada/desajeitada de fazer isso. Qual é a maneira mais simples de fazer isso?

Temos um enorme ambiente de produção do MS SQL Server com milhares de tabelas que precisam urgentemente de ajuste e remoção de índices. Alguma sugestão sobre as melhores maneiras de testar as alterações propostas fora da produção?

Obviamente, podemos (e fazemos) criar um servidor de banco de dados DEV onde podemos fazer alterações, mas essa cópia do DEV não refletirá as demandas do mundo real feitas nas tabelas. Também temos um espelho de produção que usa envio de log de transações, mas os espelhos são sempre somente leitura (a menos que você faça failover deles para o primário), portanto, não podemos testar alterações de índice neles.

Existe alguma maneira de enviar logs de transações para uma instância que não seja um espelho somente leitura? Obviamente, isso pode encontrar problemas, especialmente se forem feitas alterações no esquema no ambiente de teste. Mesmo com alterações apenas no índice, você pode ter tempos limite de consulta e outros comportamentos inconsistentes.

Mas é exatamente isso que pretendemos fazer: dado um conjunto de alterações de índice, como funcionam as consultas do mundo real, sob carga do mundo real? Eles são mais rápidos? Eles falham?

Algumas consultas de teste não serão suficientes. Executamos todos os tipos de trabalhos de processamento noturnos, semanais e mensais que afetam fortemente o banco de dados. Também temos muitos scripts e serviços externos que consultam e atualizam o banco de dados de maneiras que nem sempre prevemos.

Dado o escopo das mudanças necessárias, estou muito hesitante em fazer quaisquer alterações no índice de produção ao vivo sem verificação.

Recebi um arquivo de backup do banco de dados.

Após a restauração, executei o script neste banco de dados e ele me mostra a contagem de linhas em termos de consulta:

SELECT qs.execution_count,  
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1,   
                 (CASE WHEN qs.statement_end_offset = -1   
                       THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2   
                       ELSE qs.statement_end_offset end -  
                            qs.statement_start_offset  
                 )/2  
             ) AS query_text,   
     qt.dbid, dbname= DB_NAME (qt.dbid), qt.objectid,   
     qs.total_rows, qs.last_rows, qs.min_rows, qs.max_rows  
FROM sys.dm_exec_query_stats AS qs   
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt   
WHERE qt.text like '%SELECT%'   
ORDER BY qs.execution_count DESC;

No entanto, isso não retorna a mesma saída que vejo ao executá-lo no banco de dados de produção.

Os valores sys.dm_exec_query_stats são armazenados na alavanca do servidor ou no nível do banco de dados? Quero dizer, os dados não são retidos após a restauração?

Tenho duas tabelas temporais unidas por uma view, e quando altero a segunda tabela, e chamo sp_refreshview, gera o erro:

A visualização ou função 'ViewTest' tem mais nomes de colunas especificados do que colunas definidas.

Percebi que depois de chamar sp_refreshview, select *na segunda tabela agora está incorporando as colunas ocultas de data e hora destinadas ao controle de versão do sistema.

Abaixo está o código de teste que escrevi para demonstrar o problema.

USE Test;

ALTER TABLE Department SET ( SYSTEM_VERSIONING = OFF)
GO
ALTER TABLE DepartmentTwo SET ( SYSTEM_VERSIONING = OFF)
GO

DROP TABLE IF EXISTS DepartmentTwo; 
DROP TABLE IF EXISTS Department; 

CREATE TABLE Department
(
    DeptID INT NOT NULL PRIMARY KEY CLUSTERED,
    DeptName VARCHAR(50) NOT NULL,
    ManagerID INT NULL,
    ParentDeptID INT NULL,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);

CREATE TABLE DepartmentTwo
(
    DeptID INT NOT NULL FOREIGN KEY REFERENCES Department(DeptId),
    DeptTwoID INT NOT NULL PRIMARY KEY CLUSTERED,
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);


DROP VIEW IF EXISTS ViewTest; 
GO

CREATE VIEW ViewTest
    AS 
SELECT 
    d.DeptName,
    d.ManagerID,
    d.ParentDeptID,
    dt.*
FROM Department d
LEFT OUTER JOIN DepartmentTwo dt on d.DeptID = dt.DeptID
GO

ALTER TABLE DepartmentTwo ADD NewCol int;
GO

execute sp_refreshview 'ViewTest';
GO

select * from ViewTest;

Existe algum comportamento específico nas tabelas temporais que está fazendo com que as colunas de data e hora comecem a ser mostradas na visualização, mesmo que devam estar ocultas? Abaixo está um exemplo de como começa a incorporar as colunas de versão do sistema (ValidFrom, ValidTwo), com a instrução select gerada via SSMS:

    /****** Script for SelectTopNRows command from SSMS  ******/
SELECT TOP (1000) [DeptName]
      ,[ManagerID]
      ,[ParentDeptID]
      ,[DeptID]
      ,[DeptTwoID]
      ,[ValidFrom]
      ,[ValidTo]
      ,[NewCol]
  FROM [Test].[dbo].[ViewTest]

Restaurei uma chave mestra e criei um certificado e uma chave simétrica SSMS com:

use AdventureWorks2022

DROP MASTER KEY
RESTORE MASTER KEY
    FROM FILE = 'c:\stuff\master_key_backup.mas' 
    DECRYPTION BY PASSWORD = 'ASDF$E£%^IJYK*&234'
    ENCRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD'
OPEN MASTER KEY DECRYPTION  BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';

-- create new certificate and symkey to encode a new column
CREATE CERTIFICATE Certificate_test2 WITH SUBJECT = 'Protect my second  data';
CREATE SYMMETRIC KEY SymKey_test2 WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE Certificate_test2;

Em seguida, criptografei uma coluna de uma tabela usando:

open symmetric key SymKey_test2 decryption by certificate Certificate_test2
update AdventureWorks2022.[Person].[EmailAddress]
    set email_encrypt = ENCRYPTBYKEY(key_guid('symkey_test2'), emailaddress)
    from AdventureWorks2022.[Person].[EmailAddress]
close symmetric key symkey_test2

e testei que funcionava com:

open symmetric key symkey_test2 decryption by certificate certificate_test2
select convert(nvarchar, decryptbykey(email_encrypt)) as 'decrypted email',* 
from AdventureWorks2022.[Person].[EmailAddress]
close symmetric key symkey_test2

Coloquei o código que funcionou em um script novo, mas veio com o erro:

Please create a master key in the database or open the master key in the session before performing this operation.

Posso fazê-lo funcionar se incluir a linha: OPEN MASTER KEY DECRYPTION BY PASSWORD = '259087M#MyjkFkjhywiyedfgGDFD';, mas não quero expor minha senha em um novo script que será chamado em um procedimento armazenado e em chamadas de dados do Excel.

Existe uma maneira de abrir uma chave mestra sem expor a senha? O objetivo final é configurar isso em um procedimento armazenado que é chamado pela conexão de dados do Excel, mas claro, não quero que os usuários finais tenham acesso à senha mestra.