peter's questions

Eu tenho os seguintes dados,

Year        DRC             DISP        Id
0           140.21          0.00        5808
1           112.37          1.00        5808
0           140.21          0.00        5824
1           112.37          0.00        5824

Eu quero girá-lo assim,

Id          DRC Year 0      DISP Year 0     DRC Year 1      DISP Year 1
5808        140.21          0.00            112.37          1.00
5824        140.21          0.00            112.37          0.00

Eu tentei isso,

SELECT *
FROM
(
    SELECT Year, DRC, DISP, ID From OriginalDataTable
) AS SourceTable

PIVOT
(
    max(DRC)
    for Year IN ([0], [1])
) AS PivotTable;

Ele gira no DRC, mas não no DISP também. O resultado é assim (0 e 1 é o valor DRC). Eu entendo por que isso acontece, mas como faço para girar o DISP também?

DISP        Id          0           1
0.0         5808        140.21      112.37
0.0         5824        140.21      112.37

Eu tenho esta consulta no servidor SQL, uma consulta de replicação de mesclagem:

SELECT DISTINCT
    b.tablenick,
    b.rowguid,
    c.generation,
    sys.fn_MSgeneration_downloadonly
    (
        c.generation,
        c.tablenick
    )
FROM #belong b
LEFT OUTER JOIN dbo.MSmerge_contents c ON 
    c.tablenick = b.tablenick
    AND c.rowguid = b.rowguid;

O plano de consulta estimado inclui informações sobre 3 consultas:

1. A consulta acima
2. A chamada de função para fn_MSgeneration_downloadonly
3. A chamada de função para fn_MSArticle_has_downloadonly_property

O plano de consulta real inclui apenas estas informações:

1. A consulta acima

Nada sobre as funções. Por que as informações da função estão faltando no plano real?

Eu tentei essas opções:

SET STATISTICS PROFILE ON
SET STATISTICS XML ON

O que criou um plano real, mas faltavam as partes 2 e 3, como quando usei a opção de plano de consulta real no Management Studio.

Se, por exemplo, eu fosse usar o Profiler para capturar as informações sobre as chamadas de função, quais eventos eu selecionaria?

Não encontrei uma resposta especificamente relacionada aos planos de consulta, mas fiz o perfil SP:StmtStarting e SP:StmtCompleted e mostrou as chamadas de função.

Estou usando o SQL Server 2012 e queria saber como criar o perfil de procedimentos armazenados

Por exemplo, o criador de perfil pode capturar cada instrução SQL individual em um procedimento armazenado, o que é e quanto tempo leva para ser executado, etc.?

Estou tentando diagnosticar procedimentos armazenados de replicação de mesclagem e isso deve ser capturado como parte de uma execução completa do agente de mesclagem. Não parece possível pegar o procedimento armazenado com problemas de desempenho e executá-lo novamente porque nesse ponto ele não é lento.

Estou executando a replicação de mesclagem com o SQL 2012.

Parece haver uma consequência desagradável dos gatilhos de exclusão adicionados para replicação no SQL 2012.

Dentro dos gatilhos de exclusão estão estes,

select @xe_message = CAST('replica_id: ' + convert(nvarchar(100), @replnick) + ', article_id: ' + convert(nvarchar(100), @tablenick) + ', rowguid: ' + case when @article_rows_deleted = 1 then convert(nvarchar(100), @rowguid) else N'0' end + ', generation: ' + case when @is_mergeagent = 1 then N'0' else convert(nvarchar(100), @newgen) end + ', Reason: -1' AS varbinary(1000));
        exec master..sp_repl_generateevent 1, N'Event : ppm_insert', @xe_message

E ocorre um erro quando alguém exclui se não estiver na função sysadmin,

Msg 8189, Level 14, State 10, Procedure sp_repl_generateevent, Line 1
You do not have permission to run 'SP_TRACE_GENERATEEVENT'.

Então, você está me dizendo que todo usuário do meu banco de dados precisa ter acesso de administrador de sistema se for fazer exclusões?

Isso parece errado. Existe uma maneira de desativar esse rastreamento ou uma maneira alternativa de remover esse requisito?

Estou usando replicação de mesclagem no SQL 2012 com sincronização na web.

Quando insiro registros nas tabelas principais da minha hierarquia de filtro, isso causa muitos bloqueios nas tabelas, incluindo as tabelas de replicação de mesclagem, como MSMerge_contents.

Isso significa que nenhum outro processo pode inserir nas mesmas tabelas ao mesmo tempo.

Por causa da minha configuração particular, isso é um problema. Existe uma maneira de reduzir a quantidade de bloqueio/bloqueio que ocorre nos gatilhos de inserção da replicação de mesclagem?

Estou usando a replicação de mesclagem no SQL 2012. Alguns dos meus artigos de mesclagem usam rastreamento em nível de coluna.

Estou tentando entender como usar os campos 'lineage' e 'colv1' na tabela MSmerge_contents para descobrir quais colunas foram alteradas em uma linha específica do meu banco de dados.

Possível duplicação:
Limitação rígida para contagem de artigos de replicação de mesclagem?

Estou usando a replicação de mesclagem no SQL 2012. Estou procurando aqui:

• Especificações de capacidade máxima para SQL Server (2012)

Role até o final e, em 'Replication Objects', diz que o número máximo de artigos em uma publicação de mesclagem é 256.

Eu não estava ciente dessa aparente limitação, mas, enquanto isso, tenho executado a replicação de mesclagem com 456 artigos e tudo parece funcionar bem.

Achei esses 3 links sobre isso:

• Muitos nomes de tabela na consulta sql 2005 erro de instantâneo de replicação de mesclagem
• CORREÇÃO: O Merge Snapshot Agent falha com o erro 50007: "Muitos nomes de tabela na consulta. O máximo permitido é 256."
• Limitação rígida para contagem de artigos de replicação de mesclagem?

Resumindo, aparentemente no SQL 2000 havia um bug que causava esse limite. Foi corrigido em um service pack, mas foi reintroduzido no SQL 2005.

Essa limitação ainda existe ou não? Não parece haver nenhuma informação oficial sobre o SQL 2012.

Prefiro que o estúdio de gerenciamento me diga 'você não pode adicionar mais de 256 artigos' em vez de ter que adivinhar se isso será um problema para nós ou não.

Estou usando a replicação de mesclagem no SQL 2012.

Quando insiro linhas em massa em uma tabela e executo este procedimento armazenado para adicionar referências à tabela de rastreamento de mesclagem,

sp_addtabletocontents

Isso está perdendo a pré-computação das partições usando as otimizações de partição pré-computadas?

Se eu tiver uma tabela como esta,

CREATE TABLE [dbo].[StreetLight](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](50) NOT NULL,
    [Shape] [geometry] NOT NULL,
 CONSTRAINT [PK_StreetLight] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

Qual estrutura é ideal para usar para vincular as luzes da rua de forma relacional. Por exemplo, tenho os postes de luz 1, 2, 3, 4, 5, 6 e quero dizer que os postes de luz 6 e 2 estão relacionados. Eles podem estar na mesma rua, por exemplo.

Quais são as maneiras que eu posso fazer isso?

Por que a consulta a seguir é lenta?

select count(*) 

from [dbo].[mt_dispatch_link] 
   , [dbo].[_mt_dispatch] [_mt_dispatch]

where   (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)

   or   (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)

Demora mais de 10 minutos e, em seguida, costumo pará-lo nesse ponto. Minha pergunta é mais sobre como entender o plano de consulta.

Olhando para o plano de consulta, posso ver que a varredura de índice clusterizado inferior está retornando cerca de 250.000 registros, mas o custo é 0% e está sendo colocado em uma tabela temporária.

A varredura de índice superior é de cerca de 25.000 registros.

Mas o custo de 95% vem da junção aninhada. Que conclusões devo tirar disso?

O plano de consulta acima mostra duas varreduras de índice, isso significa que está fazendo 25.000 + 250.000 varreduras de índice ou significa que está fazendo 25.000 * 250.000 varreduras de índice?

Se eu mudar a consulta para isso (adicionando FORCESEEK):

select count(*) 

from [dbo].[mt_dispatch_link] 
   , [dbo].[_mt_dispatch] [_mt_dispatch] 

    WITH (FORCESEEK)

where   (mt_dispatch_link.contract_id_1 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_1 = _mt_dispatch.dispatch_id)

   or   (mt_dispatch_link.contract_id_2 = _mt_dispatch.contract_id 
     and mt_dispatch_link.dispatch_id_2 = _mt_dispatch.dispatch_id)

Acabo com um plano muito melhor e a consulta é executada instantaneamente:

Executei estatísticas de atualização em ambas as tabelas. Não corrigiu infelizmente. O design da tabela não é muito bom, então acho que o SQL Server realmente não entende e, portanto, apresenta um plano de consulta ruim. Mais algumas informações sobre o design da tabela em Como otimizar a consulta .

Por que o otimizador de consulta não apresenta o plano ideal?

Estou executando o seguinte procedimento armazenado para adicionar um filtro de junção de replicação de mesclagem,

sp_addmergefilter

O que mais esse procedimento armazenado faz nos bastidores?

Estou chamando esse procedimento armazenado e, em um caso, ele atinge o tempo limite após 10 minutos. O filtro de junção é este para as duas tabelas DispatchLink e Dispatch,

Dispatch.DispatchId = DispatchLink.DispatchLink1 and Dispatch.ContractId = DispatchLink.ContractLink1
or Dispatch.DispatchId = DispatchLink.DispatchLink2 and Dispatch.ContractId = DispatchLink.ContractLink2

A estrutura dessas duas tabelas é explicada aqui,

Como otimizar a consulta

Se eu remover todas as coisas após o 'ou', ele será executado rapidamente.

Eu tenho uma estrutura de banco de dados semelhante a esta,

CREATE TABLE [dbo].[Dispatch](
    [DispatchId] [int] NOT NULL,
    [ContractId] [int] NOT NULL,
    [DispatchDescription] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Dispatch] PRIMARY KEY CLUSTERED 
(
    [DispatchId] ASC,
    [ContractId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DispatchLink](
    [ContractLink1] [int] NOT NULL,
    [DispatchLink1] [int] NOT NULL,
    [ContractLink2] [int] NOT NULL,
    [DispatchLink2] [int] NOT NULL
) ON [PRIMARY]

GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (1, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (2, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (3, 1, N'Test')
GO
INSERT [dbo].[Dispatch] ([DispatchId], [ContractId], [DispatchDescription]) VALUES (4, 1, N'Test')
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 2)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 1, 1, 3)
GO
INSERT [dbo].[DispatchLink] ([ContractLink1], [DispatchLink1], [ContractLink2], [DispatchLink2]) VALUES (1, 3, 1, 2)
GO

O objetivo da tabela DispatchLink é vincular dois registros de Dispatch. A propósito, estou usando uma chave primária composta em minha tabela de despacho por causa do legado, então não posso mudar isso sem muito trabalho. Além disso, a tabela de links pode não ser a maneira correta de fazer isso? Mas novamente legado.

Então, minha pergunta, se eu executar esta consulta

select * from Dispatch d
inner join DispatchLink dl on d.DispatchId = dl.DispatchLink1 and d.ContractId = dl.ContractLink1
or d.DispatchId = dl.DispatchLink2 and d.ContractId = dl.ContractLink2

Nunca consigo fazer uma busca de índice na tabela DispatchLink. Ele sempre faz uma varredura de índice completa. Isso é bom com alguns registros, mas quando você tem 50.000 nessa tabela, ele verifica 50.000 registros no índice de acordo com o plano de consulta. É porque há 'ands' e 'ors' na cláusula join, mas não consigo entender por que o SQL não pode fazer algumas buscas de índice, uma para o lado esquerdo do 'or', e um para o lado direito do 'ou'.

Eu gostaria de uma explicação para isso, não uma sugestão para tornar a consulta mais rápida, a menos que isso possa ser feito sem ajustar a consulta. O motivo é que estou usando a consulta acima como um filtro de junção de replicação de mesclagem, portanto, infelizmente, não posso simplesmente adicionar outro tipo de consulta.

ATUALIZAÇÃO: Por exemplo, esses são os tipos de índices que venho adicionando,

CREATE NONCLUSTERED INDEX IDX1 ON DispatchLink (ContractLink1, DispatchLink1)
CREATE NONCLUSTERED INDEX IDX2 ON DispatchLink (ContractLink2, DispatchLink2)
CREATE NONCLUSTERED INDEX IDX3 ON DispatchLink (ContractLink1, DispatchLink1, ContractLink2, DispatchLink2)

Então ele usa os índices, mas faz uma varredura de índice em todo o índice, então 50.000 registros ele varre 50.000 registros no índice.

Estou usando a replicação de mesclagem no SQL 2012.

Por que você não pode marcar as restrições padrão como NOT FOR REPLICATION? Você pode desabilitar todas as restrições padrão para um artigo de mesclagem, mas é tudo ou nada, então não parece oferecer controle suficiente.

Que tal este cenário,

1. O usuário insere um registro 'Shape' em um cliente
2. O 'Shape' tem uma restrição padrão para definir o campo NumberOfSides como 4
3. O usuário do cliente edita o valor de NumberOfSides e o define como 5
4. Eles sincronizam com o servidor

Quando eles sincronizam com o servidor, ele não repete a restrição padrão e define o valor de 'NumberOfSides' de volta para 4?

Estou perdendo alguma coisa aqui?

Não quero desabilitar as restrições padrão que estão sendo replicadas para o cliente porque há valores padrão válidos que precisam ser definidos. O usuário do lado do cliente precisa ter esses valores definidos para usar o software.

ATUALIZAR:

A explicação sobre a restrição padrão sendo aplicada apenas uma vez faz sentido.

Portanto, minha única pergunta restante é se eu tiver restrições padrão que usam sequências, estou com problemas porque as sequências não podem ser replicadas. Essas são minhas duas únicas opções?

1. Desative todas as restrições padrão para uma tabela específica (não é ideal se algumas delas não forem para sequências)
2. Crie as sequências manualmente no lado do cliente e, em seguida, crie um manipulador personalizado para aplicar os valores de sequência corretos no servidor durante a sincronização

Eu tenho uma tabela no SQL 2012 que podemos chamar de 'Tabela' por exemplo. Tem itens como este nele,

Column

_one
_two
three_blah
four_blah

Eu gostaria de poder fazer isso,

select * from Table order by Column

E o resultado é este,

four_blah
one
three_blah
two

Portanto, ele ordena os itens com base na remoção dos caracteres de sublinhado principais. Ele não pode apenas remover cegamente os caracteres de sublinhado, porque os sublinhados podem estar no meio dos itens.

Por exemplo, isso não funciona,

select Replace(Column, '_', '') from Table order by Replace(Column, '_', '')

Estou implementando a replicação de mesclagem no SQL 2012 com sincronização na web.

Estou me perguntando duas coisas,

1. Por que é sugerido gerar novamente os instantâneos a cada 14 dias por padrão?
2. Com a sincronização na web, você é um assinante anônimo, então como a replicação pode saber quando limpar metadados para um usuário que nunca sincroniza

Então a primeira pergunta aí. Se uma pessoa sincroniza todos os dias e está retirando a mesma quantidade de alterações especificamente, por que teríamos que gerar novamente o instantâneo? Não sei por que uma sincronização como essa se tornaria mais lenta.

É o caso de saber quando pode limpar os metadados? O usuário com o instantâneo mais antigo acabou de gerar novamente um novo instantâneo, então isso significa que podemos limpar os metadados até o próximo instantâneo mais antigo?

Isso leva à segunda pergunta. Se eu tiver um usuário para quem dei uma solução de replicação de mesclagem como uma demonstração. Acontece que eles nunca usam o sistema e apenas o sincronizaram uma vez no início para testá-lo. Eles podem até mesmo removê-lo de seu computador.

Se o trabalho de shapshot foi desativado e eles nunca sincronizam, isso significa que ficamos presos a um monte de metadados que a replicação não pode limpar? A replicação em algum momento conclui que aquela pessoa não está usando o sistema e a bloqueia?

A razão pela qual pergunto isso é que estou usando assinantes anônimos. Quando temos assinantes normais e excluímos a assinatura ao qual o servidor está conectado diretamente e a assinatura é removida da publicação. Isso não acontece para a sincronização da web.

Estou usando a replicação de mesclagem com o SQL 2012. Procuro no diretório de instantâneos, mas o maior arquivo é um arquivo prc com 646 KB.

Tenho certeza de que a maior das minhas tabelas replicadas é de 25 MB no banco de dados após a replicação, então não tenho certeza se entendi por que não há arquivos maiores no diretório de instantâneo.

Além disso, há um lugar onde eu possa procurar os arquivos de instantâneo à medida que são baixados para o assinante?

Por exemplo, o agente de mesclagem gera mensagens como,

Downloaded 100 change(s) in 'tableName' (100 inserts) : 144083 total

Eu gostaria de ver os dados sendo baixados e ter uma ideia de quão grande eles são.

Estou no processo de definição de filtros em uma publicação de replicação de mesclagem para nosso banco de dados.

O problema que estou enfrentando é que a replicação de mesclagem tem essas regras, não gosto de chamá-las de limitações porque posso entender o propósito.

1) Ao criar um filtro de artigo, você não pode incluir uma subconsulta, ou pelo menos não deveria. Se fizer isso, parecerá funcionar na primeira vez que você sincronizar, mas se algo mudar na tabela de subconsulta, o filtro não será reavaliado.

2) Ao usar um filtro de junção, você só pode unir duas tabelas.

O problema que estou encontrando é que os relacionamentos em nosso banco de dados são mais complicados do que isso. Por exemplo, aqui está um de nossos relacionamentos,

user <- regions selected <- STREET LIGHTS -> settings (are we syncing street lights) -> user

Aqui está um exemplo de estrutura de tabela para explicar o que foi dito acima com mais detalhes,

Tabela de usuários,

Id    Username
1     petermc

UserRegion Table, (pense nisso como uma tabela de segurança, qual região um determinado usuário pode ver)

Id    UserId    RegionId
1     1         2
1     2         4

Tabela de configuração de sincronização,

Id    UserId    Table
1     1         StreetLight

Tabela de regiões (esta é apenas uma tabela de pesquisa para mostrar alguns exemplos de regiões)

Id    Region Name
1     North Auckland
2     South Auckland
3     Central Auckland
4     Great Barrier Island

Mesa Rodoviária,

Id    RegionId    Name
1     1           Rosedale Rd
2     1           North Shore Rd

Mesa Street Light

Id    RoadId    Last Replaced
1     1         2012-05-01
2     1         2009-06-03
3     2         2001-06-08

Portanto, neste caso, se eu escrevesse uma instrução select para aplicar minha filtragem para petermc, ficaria assim,

select * from StreetLight where
roadId in (select Id from Road where RegionId in (select regionId from UserRegion where userid = 1))
and exists (select 1 from syncsetting where userid = 1 and [table] = 'StreetLight')

Então eu estou fazendo duas coisas lá. Primeira filtragem com base na região para reduzir uma tabela muito grande em um subconjunto menor e mais gerenciável.

A segunda é especificar se o assinante está interessado na tabela StreetLight. Caso contrário, o assinante deve ter uma tabela StreetLight vazia. Esta parte é importante porque a publicação terá um grande número de tabelas nela, então não faz sentido incluir coisas que o assinante não vai usar.

Nosso maior banco de dados possui milhões de registros em algumas tabelas, e haverá uma quantidade moderada de atualizações desses registros também. Devemos obter esta filtragem correta. A opção de não filtrar essas tabelas não é viável.

Estou usando replicação de mesclagem com SQL 2012.

Eu tenho uma tabela e seria útil propagar essa tabela para os assinantes, mas apenas a estrutura da tabela, não os dados reais.

Os dados na tabela são preenchidos por sessão, ou seja, quando você faz login no software, os dados são adicionados à tabela e excluídos quando você sai do software.

Isso significa que os dados na tabela não devem ser replicados de publicação para assinante ou de assinante para publicação. Mas seria bom que a estrutura da tabela fosse replicada para não termos que rodar um script manualmente nos assinantes.

No SQL 2012, você pode replicar objetos de sequência e, em caso afirmativo, as alterações na sequência (incluindo o consumo de valores) são replicadas?

Se não, há uma boa razão para isso não ter sido incluído?

Estou executando o envio de logs com o SQL Server 2008 R2.

Eu tenho uma situação em que a unidade de banco de dados secundária ficou sem espaço e não estava aplicando logs de transação de envio de log.

A maneira como quero corrigir isso é excluir os bancos de dados no secundário e configurar o envio de logs do zero.

O problema que tenho agora é que meus bancos de dados secundários estão no estado de restauração e não posso excluí-los. Como posso proceder?

Por exemplo, se eu tentar colocá-los offline, recebo o erro,

ALTER DATABASE is not permitted while the database is in the Restoring state.