Eu tenho duas instruções de atualização na mesma tabela que recebem bloqueios de página em ordens diferentes. Isso leva a impasses.
Como posso forçar ambas as instruções a fazerem bloqueios de página na mesma ordem?
Eu tenho uma consulta SQL que estou tentando otimizar:
DECLARE @Id UNIQUEIDENTIFIER = 'cec094e5-b312-4b13-997a-c91a8c662962'
SELECT
Id,
MIN(SomeTimestamp),
MAX(SomeInt)
FROM dbo.MyTable
WHERE Id = @Id
AND SomeBit = 1
GROUP BY Id
MyTabletem dois índices:
CREATE NONCLUSTERED INDEX IX_MyTable_SomeTimestamp_Includes
ON dbo.MyTable (SomeTimestamp ASC)
INCLUDE(Id, SomeInt)
CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp)
Quando executo a consulta exatamente como escrito acima, o SQL Server verifica o primeiro índice, resultando em 189.703 leituras lógicas e uma duração de 2 a 3 segundos.
Quando inline a @Idvariável e executo a consulta novamente, o SQL Server busca o segundo índice, resultando em apenas 104 leituras lógicas e uma duração de 0,001 segundo (basicamente instantânea).
Eu preciso da variável, mas quero que o SQL use o bom plano. Como solução temporária, coloco uma dica de índice na consulta, e a consulta é basicamente instantânea. No entanto, tento ficar longe de dicas de índice quando possível. Normalmente, presumo que, se o otimizador de consulta não puder fazer seu trabalho, há algo que posso fazer (ou parar de fazer) para ajudá-lo sem dizer explicitamente o que fazer.
Então, por que o SQL Server apresenta um plano melhor quando inline a variável?
Criei uma publicação no meu servidor. Eu tive que usar essa opção especial para poder inicializar a replicação de um backup:
exec sp_addpublication
...
@allow_initialize_from_backup = N'true',
...
Então, fiz um backup do meu banco de dados:
BACKUP DATABASE [MyDatabase]
TO URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak'
WITH CREDENTIAL = 'mycredential', COMPRESSION, STATS = 5;
GO
E restaurado a partir dele no servidor de destino:
RESTORE DATABASE [MyDatabase]
FROM URL = 'https://fakestorageaccount.blob.core.windows.net/mycontainer/MyDatabase.bak'
WITH CREDENTIAL = 'mycredential'
,MOVE 'MyDatabase' to 'SomePath\Data\MyDatabase.mdf'
,MOVE 'MyDatabase_log' to 'SomePath\Log\MyDatabase.ldf'
,STATS = 5
GO
Agora estou pronto para criar o assinante. O problema que estou tendo é que não sei quais opções fornecer ao procedimento sp_addsubscription .
Eu posso passar pelo processo usando um disco rígido comum em vez de um URL. Eu podia ver os dados sendo replicados da origem para o destino. Faço backup em disco, copio o backup para o servidor de destino, restauro e inicializo a partir do backup usando este comando:
exec sp_addsubscription
@publication = N'MyPub',
@subscriber = N'MyTargetServer',
@destination_db = N'MyDatabase',
@subscription_type = N'Push',
@sync_type = N'initialize with backup',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0,
@backupdevicetype = 'disk',
@backupdevicename = 'D:\Backups\MyDatabase.bak'
Como faço para ajustar essas opções para inicializar a replicação de uma URL (que no meu caso é um contêiner de armazenamento de BLOBs do Azure para o qual ambos os servidores têm as credenciais)?
Em Por que o log de transações continua crescendo ou fica sem espaço? , dois motivos comuns são descritos para o crescimento de um log de transações:
Tanto quanto eu posso dizer, nenhuma dessas razões se aplica a mim. Meu banco de dados está no modo de recuperação simples e não há transações demoradas ocupando muito espaço.
Observe que tive sorte quando tirei a captura de tela DBCC OPENTRANe não havia transações ativas. Na maioria das vezes eu vejo e vejo uma transação que começou segundos atrás.
Outra resposta sugere que meu log pode ser grande, mas vazio. Para verificar isso, cliquei com o botão direito do mouse no banco de dados e naveguei até Tarefas -> Encolher -> Arquivos e selecionei o tipo de arquivo de log. Eu posso ver que meu arquivo de log é grande e também tem muito pouco espaço livre.
Usei a consulta de Aaron Bertrand para descobrir quais sessões estão causando o crescimento do arquivo de log e em qual intervalo. A consulta me diz que o arquivo de log cresce em um intervalo semi-regular, cerca de uma vez a cada 10 minutos. O SPID é sempre diferente e nem sempre vem da mesma aplicação. Eu usei uma consulta (que não consigo encontrar agora por algum motivo) para descobrir qual instrução SQL essa sessão estava executando pela última vez. Estudei essas consultas para ver se alguma coisa se destacou e não vi nada óbvio. Por fim, decidi que, se DBCC OPENTRANdisse que nenhuma transação estava sendo executada por muito tempo, o problema não deveria estar nas consultas que estão sendo executadas pelos aplicativos.
Recentemente, tentamos configurar a replicação transacional com esse banco de dados como origem. Acabamos tendo que abandonar, mas tivemos problemas para remover a publicação. Tivemos que descartar manualmente o banco de dados de distribuição e usar sp_removedbreplicationpara limpar. Eu li que a replicação pode fazer com que o log de transações cresça desmarcado . Meus pensamentos são que, como a exclusão da publicação não foi tão tranquila, talvez o arquivo de log esteja crescendo porque algum resquício da replicação ficou por perto.
Então, além dos dois motivos mais comuns, o que mais posso fazer para descobrir por que o log de transações está crescendo tanto (e corrigi-lo)?
É possível desabilitar todos os bloqueios de uma tabela específica em qualquer versão do SQL Server?
Eu tenho um punhado de tabelas que são preenchidas com dados no momento da implantação. Por exemplo, uma das tabelas menores, [items].[Colour], contém 'Blue', 'Green', 'Red', 'White'. Após a implantação, o(s) aplicativo(s) sempre seleciona(m) dessas tabelas (nunca insere, atualiza ou exclui). Eu comecei a pensar, se essas tabelas são apenas lidas, então qual é o sentido de ter bloqueios? Suponho que demore algum tempo para obter um bloqueio. Mesmo que essa quantidade de tempo seja insignificante, parte de mim não ficará satisfeita até que eu mesmo teste a diferença.
Enquanto procurava a resposta por conta própria, descobri que posso marcar um banco de dados inteiro como somente leitura e que pode haver alguns benefícios . Eu quero esses benefícios, mas numa base de tabela por tabela. Eu também li sobre desabilitar o escalonamento de bloqueio, mas se eu entendi a documentação corretamente, isso só levaria a mais bloqueios (embora mais refinados), que é o oposto do que eu quero alcançar.
Eu sei que posso atualizar cada consulta que usa a tabela para ter uma NOLOCKdica, mas isso apresenta um problema secundário. No futuro, quando eu ensinar meu aplicativo a aprender novos Colours em tempo de execução, quero atualizar todas as consultas que usam a tabela para remover a NOLOCKdica? Isso soa como um incômodo.
Parece que a pergunta está recebendo alguns votos negativos e, sem nenhuma explicação fornecida, vou assumir que é porque isso parece que o que estou pedindo é otimização prematura. A otimização prematura é, como apontado em um comentário agora excluído, a raiz de todo mal. Eu sei disso e não estou otimizando prematuramente. Aqui está o porquê.
Tenho permissão para pesquisar essa API uma vez por segundo. Não está declarado na página vinculada, mas vi a regra em algum lugar (confie em mim). De qualquer forma, meu aplicativo leva de 100ms a 800ms para fazer a solicitação HTTP, baixar o arquivo de texto gzip'd e descompactá-lo. A maior parte desse tempo é gasto aguardando a resposta da API. Isso me deixa quase sem tempo para processar todos os dados em tabelas de banco de dados bonitas, organizadas e normalizadas. Para ser competitivo, tenho que processar todos os dados em 200 ms ou menos. Estou tão perto de atingir meu objetivo que estou cortando custos descartando dados que talvez não precise, enviando todos os dados para o banco de dados de uma só vez em um grande e feio pacote etc. Então, sim, estou otimizando, mas não prematuramente (na minha opinião).
Também reconheço que o que estou pedindo pode não trazer benefícios de desempenho. Na verdade, a única solução que foi fornecida até agora não pareceu afetar o desempenho em nada. Eu não poderia saber disso antes de fazer a pergunta, e agora quaisquer futuros visitantes que estejam se perguntando se isso os ajudará podem ver o teste que realizei e saber que não vale a pena. Isso não é útil?
Se isso não resolver as preocupações dos downvoters, por favor, considere deixar um comentário com o que você acha que eu poderia melhorar sobre a pergunta.
Eu tenho uma página da web onde o usuário pode configurar um login e senha do SQL Server. Estou tentando implementar a validação para esta página.
Pesquisei os requisitos para um logon do SQL Server e encontrei este artigo da Microsoft . De acordo com esse artigo, os logons do SQL Server não podem ser um nome de logon reservado. "sa" e "public" são fornecidos como exemplos. Uma lista completa de nomes de login reservados não é fornecida e não consegui encontrar uma pesquisando.
Existe uma lista de nomes de logon do SQL Server reservados? Se sim, onde posso encontrar?
Eu crio um tipo de tabela em SQL:
CREATE TYPE [dbo].[MyObject_TableType] AS TABLE
(
[Name] VARCHAR(MAX) DEFAULT '',
[Index] VARCHAR(MAX) DEFAULT ''
)
Eu construo um DataTablee o preencho com um DataRow. Eu forneço essa tabela como um parâmetro para um procedimento armazenado:
// Set up connection and command variables (code omitted)
// ...
// Make the data table
var table = new DataTable();
table.Columns.Add("Name");
table.Columns.Add("Index");
// Add one row that is missing an Index
var row = table.NewRow();
row["Name"] = "ObjectOne";
table.Rows.Add(row);
// Make a parameter for the table
var tableParameter = new SqlParameter("MyObjects", SqlDbType.Structured)
{
TypeName = "MyObject_TableType",
Value = table
};
command.Parameters.Add(tableParameter);
command.ExecuteNonQuery();
Eu esperava que dentro do procedimento armazenado, o valor do índice fosse padronizado para ''(vazio VARCHAR). Em vez disso, observei que o valor era nulo. Sei disso porque tentei mesclar o tipo de tabela em uma tabela real e a coluna de índice em minha tabela não permite nulos.
Por que o SQL Server não honra o padrão que coloquei no meu tipo de tabela? Existe uma maneira de forçar o SQL Server a honrá-lo?
Acho que posso contornar o problema usando a propriedade DataTable.DefaultValue , mas essa solução tem a desvantagem de precisar declarar os mesmos padrões em dois lugares diferentes. Eu quero evitar código redundante, se possível.
Eu tenho duas tabelas que compartilham um relacionamento muitos para muitos. Os relacionamentos são armazenados em uma terceira tabela.
CREATE TABLE Person
(
Id int NOT NULL,
Name varchar(255) NOT NULL,
PRIMARY KEY (Id)
)
CREATE TABLE Filenodes
(
Id hierarchyid NOT NULL,
Name varchar(255) NOT NULL,
PRIMARY KEY (Id)
)
CREATE TABLE PersonFilenodes
(
FileId hierarchyid NOT NULL,
PersonId int NOT NULL,
PRIMARY KEY (FileId, PersonId)
)
Preciso selecionar todos os nós de arquivo que pertencem a uma pessoa, bem como todos os arquivos que são ancestrais desses arquivos.
Eu sei selecionar todos os Filenodesque pertencem a uma determinada pessoa:
SELECT Id, Name
FROM PersonFilenodes
JOIN Filenodes on Filenodes.Id = PersonFilenodes.FileId
WHERE Id = @personId
Graças a marc_s , também sei como selecionar todos os ancestrais de um determinado filenode :
SELECT Id, Name
FROM Filenodes
WHERE @filenodeId.IsDescendantOf(Id) = 1
Simplesmente não consigo descobrir como combinar as duas consultas para obter o conjunto de resultados de que preciso. Como posso selecionar todos os nós de arquivo que pertencem a uma pessoa, bem como todos os arquivos que são ancestrais desses arquivos?
Eu quero mesclar uma tabela em outra. Preciso aplicar a lógica condicional na minha cláusula WHEN MATCHED, que idealmente seria feita assim:
MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Do insert
WHEN MATCHED AND b.NeedsAdjustment = 1 THEN
UPDATE SET
Col1 = b.Col1 + b.Adjustment
,Col2 = b.Col2 + b.Adjustment
,Col3 = b.Col3 + b.Adjustment
WHEN MATCHED THEN -- Default case (b.NeedsAdjustment <> 1)
UPDATE SET
Col1 = b.Col1
,Col2 = b.Col2
,Col3 = b.Col3
Este não é um SQL válido. De acordo com a documentação do MSDN:
Se houver duas cláusulas WHEN MATCHED, uma deverá especificar uma ação UPDATE e a outra deverá especificar uma ação DELETE.
Isso me leva à seguinte consulta:
MERGE INTO ATable AS a
USING BTable AS b
ON a.ID = b.ID
WHEN NOT MATCHED THEN
-- Insert happens here
WHEN MATCHED THEN
UPDATE SET
Col1 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col1 ELSE b.Col1 + b.Adjustment END
,Col2 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col2 ELSE b.Col2 + b.Adjustment END
,Col3 = CASE WHEN b.NeedsAdjustment = 1 THEN b.Col3 ELSE b.Col3 + b.Adjustment END
A lógica condicional é movida dentro da atualização para contornar o fato de que as mesclagens podem ter apenas uma WHEN MATCHED THEN UPDATEcláusula. Agora, em vez de uma verificação por linha, tenho uma verificação por linha por coluna (e há muito mais colunas do que as três do exemplo).
Posso evitar repetir essa condição para cada coluna que precisa ser atualizada? Existe uma maneira melhor de fazer atualizações condicionais que talvez não envolvam mesclagens?
Existe um atalho para mesclar linhas com base na chave primária? Por exemplo:
MERGE INTO [MyDatabase].[MySchema].[MyTable] AS target
USING [MyLinkedServer].[MyDatabase].[MySchema].[MyTable] AS source
-- The following line is pseudocode illustrating what we want to accomplish
ON (target primary key equals source primary key)
WHEN MATCHED THEN
UPDATE SET target.MyColumn = source.MyColumn
WHEN NOT MATCHED BY TARGET THEN
INSERT (MyCol1, MyCol2, MyCol3)
VALUES (source.MyCol1, source.MyCol2, source.MyCol3);
Escrever as condições é tedioso se a chave primária contiver um grande número de colunas. Além disso, se a chave primária for alterada, muitas junções e fusões podem ser interrompidas repentinamente.
Você pode perguntar: "Por que a chave primária está mudando?" Cenário real: Fomos informados de que um objeto foi identificado exclusivamente por seu nome distinto. Mais tarde, descobrimos que um nome distinto poderia mudar se o objeto fosse movido para outra sub-rede. Novos requisitos que mudaram a maneira como identificamos os objetos de maneira exclusiva. Isso quebrou o produto em vários lugares. Parte da confusão poderia ter sido evitada se tivéssemos mesclado na chave primária em vez de em colunas específicas.