Início / dba / Perguntas / 187533
Accepted
Brent Ozar
Asked: 2017-10-04 06:47:30 +0800 CST

Como você cria uma exibição com SNAPSHOT_MATERIALIZATION no SQL Server 2017?

  • 772

O SQL Server 2017 tem alguns novos procedimentos armazenados:

  • sp_refresh_single_snapshot_view – parâmetro de entrada para @view_name nvarchar(261), @rgCode int
  • sp_refresh_snapshot_views – parâmetro de entrada para @rgCode int

E novas entradas em sys.messages:

  • 10149 – O índice que tem SNAPSHOT_MATERIALIZATION não pode ser criado na exibição '%.*ls' porque a definição da exibição contém tabelas com otimização de memória.
  • 10642 – SNAPSHOT_MATERIALIZATION não pode ser definido para o índice '%.*ls' em '%.*ls' porque só é aplicável a índices em exibições.
  • 10643 – SNAPSHOT_MATERIALIZATION não pode ser definido para '%.*ls' em '%.*ls' porque é aplicável apenas a índices clusterizados em exibições.
  • 10648 – SNAPSHOT_MATERIALIZATION não pode ser definido para o índice particionado '%.*ls' em '%.*ls'.
  • 10649 – O índice não clusterizado '%.*ls' não pode ser criado em '%.*ls' que tenha o índice clusterizado '%.*ls' com SNAPSHOT_MATERIALIZATION.
  • 10650 – A atualização de visualizações de instantâneos requer que o isolamento de instantâneos seja habilitado no banco de dados.
  • 3760 – Não é possível descartar o índice '%.*ls' na exibição '%.*ls' que possui SNAPSHOT_MATERIALIZATION.
  • 4524 – Não é possível alterar a visualização '%.*ls' porque possui materialização de instantâneo.
  • 4525 – Não é possível usar a dica '%ls' na exibição '%.*ls' que tem materialização de instantâneo antes que a exibição seja atualizada.

E novos Eventos Estendidos:

Eventos estendidos da visualização de instantâneo

Então, como podemos criar uma visão materializada por instantâneo? (A Microsoft ainda não documentou isso, obviamente.) Aqui está um resumo das coisas que tentei até agora que não funcionaram.

sql-server materialized-view

1 respostas

  1. Best Answer

    Você não pode. O recurso está desabilitado em 2017 RTM.

    Dito isso, você pode...

    Usando AdventureWorks:

    CREATE VIEW dbo.TH
WITH SCHEMABINDING
AS
SELECT P.ProductID, COUNT_BIG(*) AS cbs
FROM Production.Product AS P
JOIN Production.TransactionHistory AS TH
    ON TH.ProductID = P.ProductID
GROUP BY P.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH (ProductID)
WITH (SNAPSHOT_MATERIALIZATION = ON);

    As alterações nas tabelas subjacentes não são refletidas imediatamente na exibição (como normalmente é o caso do SQL Server). Da mesma forma, as modificações de dados nas tabelas subjacentes não precisam manter a exibição indexada do instantâneo.

    Para atualizar o conteúdo da visualização, é necessário chamar um dos novos procedimentos armazenados:

    EXECUTE sys.sp_refresh_single_snapshot_view
    @view_name = N'dbo.TH',
    @rgCode = 0; -- don't know what this is for yet

    Isso produz o plano de execução:

    Plano

    Isso provavelmente não funcionará para você, porque um sinalizador de rastreamento não documentado é necessário ou você precisa fazer a coisa particularmente desagradável que fiz: gravar no local da memória que contém o sinalizador de recurso (usando um depurador) para habilitar esse recurso.

    Se você estiver curioso, o sinalizador de recurso é o byte em sqllang!g_featureSwitchesLangSvc+0x10f. É verificado durante sqllang!SpRefreshSingleSnapshotView.

    Se você quiser jogar junto e estiver totalmente preparado para aceitar as consequências de hackear o código do SQL Server enquanto ele estiver em execução e usar um recurso que a Microsoft acha que ainda não está pronto:

    1. Anexe um depurador ao processo do SQL Server 2017. Eu uso WinDbg.

    2. Defina um ponto de interrupção:

      bp sqllang!SpRefreshSingleSnapshotView

    3. Retome o SQL Server usando o comando Go ( g)

    4. Crie a visualização acima, mas ainda não o índice clusterizado exclusivo
    5. Execute o sys.sp_refresh_single_snapshot_viewcomando acima

    6. Quando o ponto de interrupção for atingido, percorra até ver a linha de código:

      cmp byte ptr [sqllang!g_featureSwitchesLangSvc+0x10f (00007fff`328dfbcf)],0

      O deslocamento pode ser diferente em outras construções, por exemplo, em 2017 RTM CU3 ésqllang!g_featureSwitchesLangSvc+0x114

    7. O endereço de memória dentro dos parênteses pode ser diferente. Use o que você vê.

    8. Use o comando display memory para ver o valor atual no endereço de memória que você encontrou:

      db 00007fff`328dfbcf L1

    9. Isso deve mostrar um zero, indicando que o recurso está desabilitado.

    10. Altere o zero para um, usando o comando enter values ​​(novamente com seu endereço de memória):

      eb 00007fff`328dfbcf 1

    11. Desabilite o ponto de interrupção e retome a execução do SQL Server.

    12. O recurso agora está ativado.
    13. Crie o índice clusterizado exclusivo na exibição.
    14. Brinque.

    Note SNAPSHOT_MATERIALIZATIONnos permite materializar um instantâneo de uma especificação de consulta que normalmente não poderia ser indexada, por exemplo, o abaixo usa MAX:

    CREATE VIEW dbo.TH2
WITH SCHEMABINDING
AS
SELECT TH.ProductID, MaxTransactionID = MAX(TH.TransactionID)
FROM Production.TransactionHistory AS TH
GROUP BY TH.ProductID;
GO
CREATE UNIQUE CLUSTERED INDEX cuq ON dbo.TH2 (ProductID)
WITH (SNAPSHOT_MATERIALIZATION = ON);

    Resultado:

    Comandos concluídos com sucesso.
    • 58

relate perguntas