Andrew Bickerton's questions

Recentemente, migramos nosso banco de dados de distribuição do SQL Server para um cluster de failover de várias sub-redes e descobrimos que, ocasionalmente, ao adicionar artigos/visualizar o status de replicação do editor, obtemos erros como:

Não é possível inicializar o objeto de fonte de dados do provedor OLE DB "SQLNCLI11" para o servidor vinculado "repl_distributor". (Provedor de dados .Net SqlClient)

Isso parece ser devido à conexão do servidor vinculado falando com um cluster de failover de várias sub-redes (conforme descrito aqui )

Além de seguir a solução de implementar uma conexão ODBC em cada nó de nosso editor (também um cluster de failover de várias sub-redes), há mais alguma coisa que possamos tentar para corrigir os servidores vinculados que se conectam a uma FCI de várias sub-redes?

Estou com um problema estranho aqui. Eu estava testando o uso de 'initialize from lsn' em nossa configuração de replicação de teste e agora quebrei todas as publicações de um de nossos bancos de dados, a reconstrução do zero (aplicando um novo instantâneo) não corrigiu o problema.

No monitor de replicação, o agente distribuidor é executado continuamente e relata:

Nenhuma transação replicada está disponível

O token Tracer está relatando que:

• Editor para Distribuidor: 00:00:02
• Distribuidor para Assinante: Pendente...
• Latência total: pendente...

Quando investiguei os logs de distribuição usando:

SELECT time,
       CAST(comments AS XML) AS comments,
       runstatus,
       duration,
       xact_seqno,
       delivered_transactions,
       delivered_commands,
       average_commands,
       delivery_time,
       delivery_rate,
       delivery_latency / (1000 * 60) AS delivery_latency_Min,
       agent_id
FROM dbo.MSlogreader_history WITH (NOLOCK)
WHERE agent_id = 5
ORDER BY time DESC;

SELECT *
FROM dbo.MSdistribution_history
WHERE agent_id = 125
ORDER BY time DESC;

Eu posso ver que o último xact_seqno de:

• O agente LogReader é: 0x00000378000384880003
• O agente de distribuição é: 0x000CA68B000010A8000A000000070000

Como comparação em um dos outros bancos de dados (indo para o mesmo assinante), recebo:

• O agente LogReader é: 0x00000D140001F52A0003
• O agente de distribuição é: 0x00000D140001F52A0003000000000000

Para mim, parece que o agente de distribuição está lembrando do antigo xact_seqno quebrado (de quando cometi um erro ao testar 'initialize from lsn'), mas agora parece ter quebrado permanentemente toda a replicação desse banco de dados.

Servidores:

• PubA - Um editor hospeda vários bancos de dados que são replicados
• Dist - Um distribuidor separado
• SubA - Primeiro assinante
• SubB - Segundo assinante

Bancos de dados:

• PubA.DB1 = Publisher DB que está funcionando bem
• PubA.DB2 = DB Publisher que está quebrado
• SubA.PullWorking1 = 1º assinante tem uma assinatura pull em funcionamento do DB1
• SubA.PullBroken2 = 1º assinante também tem uma assinatura pull quebrada do DB2
• SubA.PushWorking1 = 2º assinante tem uma assinatura push funcional do DB1
• SubA.PushBroken2 = 2º assinante também tem uma assinatura push quebrada do DB2

Publicações:

• PubA.DB1.PushPub_works
• PubA.DB1.PullPub1_works
• PubA.DB1.PullPub2_works
• PubA.DB2.PushPub_broken
• PubA.DB2.PullPub_broken

Venho dizendo há anos que, se você está atribuindo um valor agregado a uma variável, precisa lidar se não houver linhas retornadas. ou seja:

DECLARE @MyVar INT

SELECT @MyVar = SUM(t.MyValue) FROM dbo.MyTable t WHERE /* clause */

IF @MyVar IS NULL
    SET @MyVar = 0

No entanto, um desenvolvedor apontou recentemente que não precisa fazer a verificação @@ROWCOUNT, pois SUM sempre retorna um valor (mesmo que não haja linhas). Ao fazer mais pesquisas, descobri que parece haver um comportamento inconsistente do SQL Server:

Então, se eu executar:

DECLARE @MyTable TABLE(ID INT, MyValue INT)

/* you get a value of 0 back */
SELECT ISNULL(SUM(t.MyValue),0) FROM @MyTable t WHERE t.ID = 100

Eu recebo uma única linha com um valor de 0 de volta.

No entanto, se eu adicionar uma cláusula GROUP BY:

DECLARE @MyTable TABLE(ID INT, MyValue INT)

/* when you add on a GROUP BY, you no longer get a record back */
SELECT ISNULL(SUM(t.MyValue),0) FROM @MyTable t WHERE t.ID = 100 GROUP BY t.ID

Não recebo nenhuma linha de volta (que é o que eu esperava), fiz algumas pesquisas na documentação do MS, mas não consigo encontrar nenhuma referência a essa diferença de comportamento. Alguém pode explicar por que isso está ocorrendo? Além disso, isso é uma mudança na forma como o SQL Server funciona?

Obs: Meus testes foram no SQL Server 2008R2

** Editar: olhei para trás em alguns e-mails que enviei no passado e corrigi meu cheque, tenho dito Se o valor ainda for NULL, lide com isso ... (não lida com linhas e também com NULL atribuído)

Se recebermos uma declaração de atualização que não verifica se o valor foi alterado na cláusula where, quais são as diferentes maneiras de ignorar essa atualização dentro de um gatilho?

Sei que podemos fazer uma comparação de cada campo individual (lidando também com o lado ISNULL), mas onde é uma tabela com mais de 50 campos, existe uma maneira mais rápida/fácil de fazer isso?

Observação: isso pode ser usado para reduzir IO/ruído em registros de auditoria, etc...

EXEMPLO

Para uma tabela de:

CREATE TABLE myTest
(ID int NOT NULL IDENTITY(1,1), 
 Field1 varchar(10) NULL,
 Field2 varchar(20) NULL)

com um acionador do atualizador posterior contendo:

INSERT INTO myTestAudit (ID, Field1, Field2, DateTimeUpdate)
SELECT ID, Field1, Field2, getDate()
FROM inserted

com valores iniciais:

INSERT INTO myTest (Field1, Field2)
SELECT 'a', 'b' UNION ALL
SELECT 'a', 'c'

Agora execute uma atualização:

UPDATE myTest set Field2 = 'b' WHERE Field1 = 'a'

Qual correria mais rápido?

1. Selecione em uma tabela usando a chave primária (inteiro, índice clusterizado, mais de 1.000.000 linhas)
2. Tentar uma atualização em uma tabela diferente, onde a linha não existe? (cláusula where na chave primária inteira, índice clusterizado, mais de 200.000 linhas)

Fundo

Atualmente, temos alguns procedimentos que precisam manter uma cópia filtrada de sua tabela.

Mesas envolvidas:

• [MasterTable]que contém os critérios de filtro
• [ChildTable]para ser filtrado
• [ChildFilterTable]para manter os registros filtrados

Isso está sendo feito atualmente por:

1. Selecionar critérios de filtro
2. Se os critérios do filtro corresponderem, então:
   1. Tentar uma atualização
   2. Se nenhum registro atualizado, insira um novo registro

SQL de exemplo:

DECLARE @FilterValue INT

/* Get FilterValue to check */
SELECT @FilterValue = FilterValue FROM [MasterTable] WHERE ID = @IDFromChildTable

IF @FilterValue = 123
BEGIN
    /* Attempt update */
    UPDATE [ChildFilterTable] SET
        ...
    WHERE ChildID = @IDFromChildTable

    IF @@ROWCOUNT = 0
    BEGIN
        /* Row not there yet, insert it! */
        INSERT INTO [ChildFilterTable] (ChildID, ....) VALUES (@IDFromChildTable, ....)
    END
END

Alteração proposta

Mudar para:

1. Tentar uma atualização
2. Se nenhum registro for atualizado, então:
   1. Selecionar critérios de filtro
   2. Se os critérios do filtro corresponderem, então: insira um novo registro

Então:

DECLARE @FilterValue INT

/* Attempt update */
UPDATE [ChildFilterTable] SET
    ...
WHERE ChildID = @IDFromChildTable

IF @@ROWCOUNT = 0
BEGIN
    /* Get FilterValue to check */
    SELECT @FilterValue = FilterValue FROM [MasterTable] WHERE ID = @IDFromChildTable

    IF @FilterValue = 123
    BEGIN
        /* Row not there yet, insert it! */
        INSERT INTO [ChildFilterTable] (ChildID, ....) VALUES (@IDFromChildTable, ....)
    END
END

Observação: as regras de negócios confirmam que o valor do filtro nunca será alterado após a configuração, [MasterTable]portanto, não precisamos nos preocupar em atualizar um valor que não corresponda ao registro do filtro (ou seja: se estiver na ChildFilterTable, queremos atualizá-lo.

Cenário

Temos uma tabela existente em 3 publicações diferentes (indo para 3 assinantes diferentes), 1 dessas publicações não publica todas as colunas.

Todas essas são assinaturas pull transacionais.

Agora preciso adicionar mais algumas colunas a esta tabela e queremos adicioná-las apenas às duas primeiras publicações. No entanto, do MSDN :

[ @publication_to_add =] 'publication_to_add' É o nome da publicação à qual a nova coluna é adicionada. publication_to_add é nvarchar(4000), com um padrão de ALL. Se ALL, todas as publicações que contêm esta tabela são afetadas. Se a publicação_to_add for especificada, somente esta publicação terá a nova coluna adicionada.

o que implica que você espera que especifique 1 publicação ou TODAS as publicações.

Um exemplo de detalhe de tabela/publicação para ajudar a demonstrar o problema:

Exemplo de tabela

[MyTable] (
    ID int IDENTITY(1,1) NOT NULL,
    myName varchar(50) NULL,
    myDOB datetime,
    myJob varchar(100),
    mySpouseID int )

Exemplo de publicação/assinante

[InternalPublication] All fields, sent to InternalAdmin system
[Reporting] All fields, sent to Reporting/Datawarehouse system
[External] sent to External accessable system, fields sent:
    (ID, myName, myDOB)

Pergunta

Como faço para adicionar:

[myMarriageDate] datetime NULL,
[myDivorceDate] datetime NULL

e apenas adicionado às publicações [InternalPublication] e [Reporting] ?

Nota: a amostra é inventada para proteger os inocentes

Problema-chave

A latência que estamos experimentando não está aplicando as alterações do Distribuidor aos Assinantes (se observarmos o Replication Monitor, a latência geralmente é <1 segundo), é o tempo que uma transação confirmada no banco de dados do publicador leva para chegar ao distribuidor .

Parte do problema é que temos vários filtros aplicados a uma de nossas tabelas mais voláteis e, a cada atualização/inserção/exclusão, o LogReader.exe precisa verificar em qual publicação colocar esse registro com base nos filtros aplicados.

Pelos testes que fizemos parece que cada filtro adicional aplicado pode aumentar exponencialmente o tempo que o LogReader leva para processar cada transação.

( editar 23/06/2011: adicionado mais detalhes sobre os filtros )

Fundo

Em nosso setup de Replicação temos diversas publicações com filtros em uma tabela altamente volátil (média de 1,5 milhões de transações em um período de 2h). Durante períodos pesados, isso pode resultar na execução do LogReader em uma latência de 20 segundos (geralmente < 1 segundo)

Identificamos várias áreas de melhoria (reduzindo o número de filtros, reduzindo o número de atualizações, eliminando o processamento etc.). Uma área potencial de melhoria é mudar a forma como as atualizações são aplicadas.

Uma tabela de exemplo (para ajudar na explicação)

myTable
----------------
myID         int
myGroupID1   int
myGroupID2   int
Suspended    bit
FilterFlag1  int
FilterFlag2  int
FilterFlag3  int

Para replicação para ilustrar isso, teríamos 5 publicações de [myTable] :

Publication  Filter
-----------  ------
NoFilter1    [all records]*  
NoFilter2    [all records]*  
Filter1      FilterFlag1 = 1
Filter2      FilterFlag2 = 1
Filter3      FilterFlag1 = 1 AND FilterFlag2 = 1  

• Outras tabelas são combinadas nessas publicações e é por isso que a mesma tabela com filtros idênticos (nenhum) está em mais de uma publicação.

Processo de atualização atual

A maioria dos aplicativos que atualizam essa tabela faz isso percorrendo sua coleção de objetos, aplicando a alteração a esse único objeto e, em seguida, confirmando sua alteração no banco de dados antes de passar para o próximo objeto.

De uma perspectiva de rastreamento de banco de dados, isso significa que obtemos até 120 instruções de atualização quando a alteração ocorre

Exemplo

update [myTable] set Suspended = @Suspended 
where myID = @pID

Processo de atualização proposto

Como as coleções de objetos são realmente baseadas nos IDs do grupo, uma melhoria potencial é fazer uma atualização em massa (em vez de atualizações individuais) e, em seguida, atualizar a coleção de objetos. Reduzindo o número de instruções de atualização para 1 ou 2 (dependendo dos cenários de negócios).

Exemplo

update [myTable] set Suspended = @Suspended 
where myGroupID1 = @groupID1 
      and myGroupID1 = @groupID2

Impacto no LogReader?

Do ponto de vista do processamento do aplicativo, fazer uma única atualização faz sentido para mim (menos idas e voltas entre o aplicativo e o banco de dados = mais rápido), porém não tenho certeza de como o LogReader tratará os dois cenários, pois precisa processar cada registro atualizado por a transação..

O LogReader processará esses registros mais rapidamente ou mais lentamente em uma atualização em massa?

Sou um grande fã de muitas ferramentas Redgate , no entanto, no meu local de trabalho atual, eles são muito paranóicos com qualquer coisa que interaja com servidores ativos (em particular, afetando o desempenho).

Portanto, minha pergunta é especificamente sobre a ferramenta SQL Search , qual o impacto que isso causa em um servidor? Presumo que haverá:

1. Indexação inicial (com o que ele interage, ideia sobre o impacto no servidor, pode ser personalizado para não indexar servidores ativos)
2. Conexões/atualizações subsequentes (como isso determina se há uma alteração a ser reindexada e qual impacto de desempenho/carga isso terá em um servidor)?

No SQL Server, sempre achei difícil obter o máximo de linhas para um conjunto de dados. Estou procurando uma lista dos métodos para recuperar o máximo de linhas com algumas orientações sobre desempenho e capacidade de manutenção.

Tabela de amostra:

DECLARE @Test TABLE (ID INT IDENTITY(1,1), name VARCHAR(50), 
                     dateOfBirth DATETIME, TaxNumber varchar(10))

INSERT INTO @Test (name, dateOfBirth, TaxNumber)
SELECT 'Fred', convert(datetime, '25/01/1976', 103), '123' UNION ALL
SELECT 'Bob', convert(datetime, '03/03/1976', 103), '234'  UNION ALL
SELECT 'Jane', convert(datetime, '13/06/1996', 103), '345' UNION ALL
SELECT 'Fred', convert(datetime, '14/02/1982', 103), '456' UNION ALL
SELECT 'Bob', convert(datetime, '25/10/1983', 103), '567' UNION ALL
SELECT 'Jane', convert(datetime, '12/04/1995', 103), '678' UNION ALL
SELECT 'Fred', convert(datetime, '03/03/1976', 103), '789'

select * from @Test

Dá:

ID          name      dateOfBirth             TaxNumber
----------- --------- ----------------------- ----------
1           Fred      1976-01-25 00:00:00.000 123
2           Bob       1976-03-03 00:00:00.000 234
3           Jane      1996-06-13 00:00:00.000 345
4           Fred      1982-02-14 00:00:00.000 456
5           Bob       1983-10-25 00:00:00.000 567
6           Jane      1995-04-12 00:00:00.000 678
7           Fred      1976-03-03 00:00:00.000 789

Se eu quiser recuperar os detalhes completos da pessoa mais velha (grupo por nome), que métodos posso usar?

Saída desejada:

ID          name      dateOfBirth             TaxNumber
----------- --------- ----------------------- ----------
1           Fred      1976-01-25 00:00:00.000 123
2           Bob       1976-03-03 00:00:00.000 234
6           Jane      1995-04-12 00:00:00.000 678

No SQL Server, você pode especificar as dicas de junção:

• HASH JOIN
• MERGE JOIN
• LOOP JOIN

Qual é a definição dessas três dicas de junção e quando cada uma deve ser usada?

Eu tenho algum SQL padrão que executo em vários bancos de dados em um único servidor para me ajudar a diagnosticar problemas:

select 
    so.name,
    so.type,
    MAX(case when sc.text like '%remote%' then '' ELSE 'N' END) AS Relevant,
    @@ServerName as Server,
    DB_Name() as DBName 
from
    sysobjects so with (nolock)
    join syscomments sc with (nolock) on so.id = sc.id
where (sc.text like '%emote%')
group by so.name, so.type
order by so.type, so.name

Como posso executar isso em todos os bancos de dados em um único servidor? (além de conectar manualmente um de cada vez e executar)