SQL Maniacs – SQL Server 2016 Temporal Tables

Pessoal, na última quarta feira tive a honra de participar novamente de mais um evento do SQL Maniacs a convite do MVP em SQL Server Vitor Fava. Dessa vez estive falando sobre a mais nova feature do SQL Server 2016 denominada Temporal Tables.

– Desde já agradeço ao Vitor pela sua disponibilidade e predisposição em sempre ajudar a comunidade! –

Através desta tecnologia é possível armazenar dados hostóricos em ambiente OLTP de maneira simples. Com isso é possível realizar rollback de transações já comitadas, fazer consultas históricas em ambiente OLTP e realizar análises de Slowly Changing Dimension em ambientes de Business Intelligence.

Além de explorar as possibilidades da feature, foi realizado também um comparativo com o Change Data Capture e suas principais diferenças.

Segue abaixo o vídeo da apresentação que está no canal do youtube do SQL Maniacs e, se você quiser realizar os testes, clique no LINK para realizar o download da apresentação de slide e e SSMS Project com os scripts usados. 

Espero poder ajudá-los!

Anúncios

Critical Update & Cumulative Update 2 – Resolução de Problemas no SQL Server 2016

Pessoal, ao realizar alguns testes com o SQL Server 2016 encontrei alguns erros na versão que foram corrigidos com alguns hotfix de correção e gostaria de compartilhar com vocês para ajudá-los, caso tenham o mesmo problema.


Ao habilitar o Change Data Capture em uma tabela no SQL Server é possível que você selecione as colunas dessa tabela que irão ser tracked (receberão versionamento de dados).

Porém, ao tentar habilitar o CDC em uma tabela do SQL Server 2016, recebi o erro abaixo:

error_change_data_capture_sql_2016

Para resolução do problema, a Microsoft disponibilizou o Critical Update que pode ser baixado através do seguinte Link.


Ao tentar realizar o Pin dos Dashboards do Reporting Services 2016 em um Painel no Cloud (Power BI) recebi a seguinte mensagem de erro:

“You don’t have any dashboards. You need at least one dashboard that you have permissions to pin to.”

Após muito pesquisar verifiquei em um link do suporte da Microsoft que a resolução do problema fora realizada no Cumulative Update #1 da ferramenta. Para Download e mais informações sobre demais correções dos Cumulative Updates #1 e #2 acesse o Link Link respectivamente.

Espero poder ajudá-los!

Mitos e Verdades – ORDER BY + TOP/OFFSET/FOR XML dentro de Table Expressions

Fui questionado recentemente por um dos alunos de um curso que ministrei de TSQL sobre o porque de ser necessário o uso da cláusula TOP (n), OFFSET ou FOR XML PATH() dentro das expressões de tabela (VIEWs, TVFs, Derived Tables e CTEs) para que seja possível que o resultado execute com sucesso e não retorne erros.

Acredito que esse é um assunto interessante e decidi realizar alguns testes para entender melhor o funcionamento dessa issue dentro do SQL Server.


O TSQL é uma linguagem SET BASED (baseada em conjuntos – teoria Matemática dos Conjuntos). É importante entender que um conjunto, por sua natureza, não possui retorno ordenado.

Quando executado um comando de SELECT fazendo o uso da cláusula ORDER BY você demanda ao SQL Server uma das duas opções abaixo:

  1. Quero definir a ordenação das linhas que serão devolvidas através do comando de SELECT;
  2. Quero definir quais linhas serão devolvidas a partir de uma expressão TOP ou OFFSET;

É necessário entender também que as Expressões de Tabela (Table Expressions) nada mais fazem além de dar a uma consulta uma tabela virtual como origem (ou uma das origens). Essa tabela virtual será criada em tempo de execução através do comando de SELECT localizado dentro da Expressão de Tabela.

Isso quer dizer que o SQL Server irá primeiro realizar a consulta que está dentro da Query localizada na Table Expression e, a partir dessa tabela virtual que será gerada, iniciará a execução da query de fora da Table Expression (Outer query).

Dessa forma é possível entender o porquê de não podermos usar uma Cláusula ORDER BY dentro de uma Table Expression: O Retorno se torna não determinístico. O SQL Server não pode passar por cima da regra que define que um conjunto de OUTPUT não pode possuir ordenação.

Muitas pessoas acreditam que é possível “burlar” essa funcionalidade by design do SQL Server usando a cláusula TOP. Será possível que você veja abaixo quais as verdades e mentiras por detrás disso.


Use para os testes o banco de dados AdventureWorks2014.

Primeiramente veja o plano de execução estimado da query abaixo:

USE AdventureWorks2014
GO

SELECT TOP 10 
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

01

Observe que o operador SORT foi inserido no plano de execução. Isso quer dizer que o SQL Server está realizando a ordenação do retorno da consulta que fora executada.

Após isso, crie uma VIEW denominada [vw_Person_Names] utilizando a query abaixo e crie um plano de execução estimado para o comando SELECT após sua criação.

USE AdventureWorks2014
GO

CREATE VIEW vw_Person_Names
AS
SELECT TOP 10 
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

GO

SELECT *
FROM vw_Person_Names

01

Observe que o plano de execução estimado será idêntico ao execution plan da query anterior. Isso ocorre porque o retorno do SELECT é determinístico graças ao comando TOP (n) na query. Dessa maneira, estamos usando a cláusula ORDER BY em uma de suas funções no TSQL: definir quais linhas serão devolvidas a partir de uma expressão TOP, OFFSET ou FOR XML PATH().

Neste caso, é possível usar corretamente o comando ORDER BY dentro de uma Table Expression – Seja ela uma VIEW, Table-Valued User-Definid Function (TVF), Common Table Expression (CTE) ou Derived Table.

É importante observar um outro ponto. Algumas vezes, quando o Query Optimizer decide usar um índice em cujo os registros já estão ordenados, o operador SORT não é usado na consulta, porém, se você selecionar o operador de Index Scan com o botão direito e for até a opção Properties, será possível observar que a opção Ordered será TRUE, ou seja, o retorno do operador de busca no index já está ordenado.

É o que será demonstrado no caso abaixo:

USE AdventureWorks2014
GO

SELECT TOP 10 
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY BusinessEntityID DESC

0001

Observe que o operador SORT não foi usado. Agora veja a aba de propriedades do operador Clustered Index Scan:

001


Agora você irá descobrir o outro lado da moeda acerca do ORDER BY sendo usado dentro de uma Expressão de Tabela que é quando você tentar usá-lo para definir qual a ordenação das linhas que serão devolvidas.

Use a query a baixo para realizar a análise do seu plano de execução:

USE AdventureWorks2014
GO

SELECT BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

02

Observe que o operador SORT foi novamente utilizado para realizar a ordenação do retorno da consulta acima.

Como já fora visto no início do post, se tentarmos usar essa mesma query dentro de uma Table Expression (uma view, por exemplo) um erro será retornado pois isso faria com que o SQL Server “fraudasse” uma das regras da teoria matemática dos conjuntos: Não há ordenação dos dados dentro de um SET.

Mas, é possível “burlar” este erro do SQL Server adicionando ao comando localizado dentro da VIEW a cláusula TOP 100 PERCENT. Veja:

USE AdventureWorks2014
GO

CREATE VIEW vw_Person_Names2
AS 
SELECT TOP 100 PERCENT
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

GO

SELECT *
FROM vw_Person_Names2

A VIEW denominada [vw_Person_names2] foi criada com sucesso. Porém, observe um detalhe ao visualizar o plano de execução criado a partir do seu SELECT:

03

O operador SORT não é apresentado. Observe também abaixo na opção Ordered dentro das propriedades do operador Index Scan e veja que realmente a ordenação não ocorreu. O valor é FALSE:

04

Isso ocorre porque o SQL Server percebe que está ocorrendo através do comando TOP 100 PERCENT o retorno de todos os dados do conjunto e, por ser um retorno não determinístico, o Query Optmizer não pode desrespeitar a regra de não ordenação do conjunto.

Logo, ao fazer isso que foi demonstrado o retorno de sua Table Expression (neste caso da VIEW) não é ordenado mesmo com o comando ORDER BY estando presente na query. Veja abaixo:

06
Comando SELECT executado na VIEW.
05
Comando SELECT executado na Query.

Para contornar isso, algumas pessoas tentam novamente enganar o Query Optmizer usando o operador TOP (n) PERCENT onde (n) possui valor de 99.99. Isso quer dizer que você está pedindo ao SQL Server 99,99% dos dados do retorno.

Isso funcionará para forçar o SQL Server a trazer o resultado da consulta a partir de uma Table Expression ordenado, entretanto, isso pode gerar problemas.

USE AdventureWorks2014
GO

CREATE VIEW vw_Person_Names3
AS 

SELECT TOP 99.99 PERCENT
 BusinessEntityID,
 FirstName,
 MiddleName,
 LastName 
FROM Person.Person
ORDER BY FirstName DESC

GO

SELECT *
FROM vw_Person_Names3

Ao criar uma nova VIEW denominada [vw_Person_names3] através do comando acima fora executado um COUNT em ambos objetos (tabela [Person].[Person] e view [dbo].[vw_Person_names3]). Veja o resultado abaixo:

07

Ocorreu perda de registros (neste caso um somente) para o retorno em cuja query fora usado o TOP 99.99 PERCENT.


Por esses motivos, sempre que o resultado de uma Table Expression for não determinístico é recomentado a utilização da cláusula ORDER BY na query de fora da Table Expression (Outer query) evitando, dessa forma, resultados errôneos.

CDC – Parte 06

Nesta etapa da série será possível aprender acerca das melhores práticas e pontos importantes para habilitação e manutenção do Change Data Capture em ambientes de Produção.


  1. Uma ótima prática de utilização do Change Data Capture é evitar habilitar a feature em colunas de uma tabela cujo não existe importância em armazenar o histórico de seus dados. Tabelas com grandes quantidades de colunas tendem a aumentar de maneira significativa a latência nas capturas das alterações;
  2. Somente habilite o suporte a Net Changes caso realmente seja necessário o seu uso. O trabalho que o CDC executa por detrás para realizar o controle dessas mudanças também prejudica a performance na utilização da feature;
  3. O Job de Cleanup dos dados históricos não possui um desempenho favorável quando executado em paralelo com o workload. Se possível, o job de Cleanup dos dados de alteração deve ser programado para horários com baixa ou nenhuma carga de trabalho;
  4. Uma das coisas mais importantes a que deve-se ter atenção no que diz respeito à captura de dados de alteração é o subsistema de log de transações. O arquivo de Log cresce significativamente quando o Change Data Capture é habilitado em um banco de dados. Além disso, registros de log permanecem ativos até o CDC processá-los. Isto significa que, especialmente em ambientes onde uma grande latência se acumula, o arquivo de Log pode crescer significativamente, porque o espaço de log não pode ser reutilizado até que o CDC execute o trabalho de carga dos dados ainda processados dentro do Log, mesmo no Recovery Mode Simple, ou mesmo depois de um backup de log no Recovery Mode Full;
  5. O Change Data Capture funciona em todos os Recovery Modes. Mas quando o CDC é habilitado, as operações que normalmente seriam minimamente logadas nos modos de recuperação Simple ou Bulk-logged são totalmente logadas para permitir ao CDC a capturar de todas as alterações;
  6. Esteja ciente que quando o disco de Log ficar cheio, você não poderá reduzí-lo fazendo um backup de log manualmente ou mesmo realizando um Shrink no arquivo até que o CDC tenha processado todas as transações;
  7. O Change Data Capture não pode processar as transações quando o disco de Log está cheio, porque a captura de dados de alteração escreve no arquivo de Log o que é realizado nas tabelas de histórico. Neste caso, a maneira mais fácil de resolver essa situação é adicionar temporariamente outro arquivo de Log em um disco diferente e deletá-lo após o CDC concluir a carga das tabelas de histórico;
  8. Planeje o crescimento do arquivo de Log. Esteja ciente que o Log File pode crescer de 200-300% a partir do tamanho original quando o Change Data Capture está habilitado na base de dados;
  9. Uma boa prática para manter o filegroup PRIMARY pequeno e para que possa-se ter uma clara distinção do que são dados transacionais e o que são dados históricos, é habilitar o CDC em um filegroup diferente;

No próximo post da série você começará a aprender sobre os componentes do MS Integration Services que são utilizados para trabalhar com o Change Data Capture, utilizando dessa maneira os dados históricos como fonte para cargas de Data Warehouses (DWs) e Operational Data Stores (ODSs).

Referência para o post – Tuning the Performance of Change Data Capture in SQL Server 2008.

CDC – Parte 05

Nesta etapa da série será possível entender o funcionamento das TVFs (Table Valued Function) denominadas [cdc].[fn_cdc_get_all_changes_…][cdc].[fn_cdc_get_net_changes_…] e também da IF (Inline Table-Valued Function) denominada [sys].[fn_cdc_map_time_to_lsn] usada para transformar o conteúdo de data em LSN (Log Serial Number). Essas funções são usadas para manipulação dos dados contidos nas tabelas de CDC.

Um outro ponto importante com relação ao entendimento dessas funções é que elas são usadas internamente pelo Integration Services dentro do componente de origem de dados do CDC. Ou seja, seus conceitos são um ponto muito importante mais tarde, quando estivermos gerando as cargas de ETL.


Como foi explanado na Parte 3 do conjunto de posts, o CDC cria dentro das tabelas de histórico algumas colunas onde, através delas, é possível realizar o controle de versão dos registros com base nos LSNs do Log de Transação.

Trabalhar com base no log é complexo, dessa forma, é possível que realizemos a conversão de LSN em data através da IF [sys].[fn_cdc_map_time_to_lsn]. Essa função captura a data desejada com base em algums parâmetros e transforma internamento esse range de período no LSN correto a ser interpretado pela função [cdc].[fn_cdc_get_all_changes_…] ou [cdc].[fn_cdc_get_net_changes_…].

Para trabalhar com a função [sys].[fn_cdc_map_time_to_lsn] é necessário entender acerca de sues parâmetros que são:

@relacional_operator – É usado para identificar um valor de LSN distinto dentro da tabela cdc.lsn_time_mapping com um tran_end_time associado que satisfaça a relação quando comparado ao valor @tracking_time (BOL). Dentro desse parâmetro é possível usar os seguintes valores:

‘smallest greater than’ ou ‘smallest greater than or equal’, onde:

smallest

‘largest less than’ ou ‘largest less than or equal’, onde:

largest

@tracking_time – É o valor de data e hora ao qual corresponder. tracking_time é datetime (BOL).


Agora que compreendida a Função [sys].[fn_cdc_map_time_to_lsn] você irá iniciar o entendimento dos parâmetros das funções [cdc].[fn_cdc_get_all_changes_…][cdc].[fn_cdc_get_net_changes_…], onde cada uma delas possui os seguintes parâmetros:

@from_lsn – LSN de início da transação;

@to_lsn – LSN de fim da transação;

@row_filter_option – Rege o conteúdo das colunas de metadados, bem como as linhas retornadas no conjunto de resultados.

Para trabalhar com ambas as functions para manipulação de registros é necessário usar o código abaixo. Lembrando que, conforme foi dito na Parte 02 do conjunto de posts,  o suporte a Net Changes somente é habilitado caso o parâmetro @supports_net_changes seja preenchido como um na hora de habilitar o CDC para tabela. Caso não, a função [cdc].[fn_cdc_get_net_changes_…] não será criada.


-- ===========================
-- checking get_all funtion --
-- ===========================

USE AdventureWorks2014
GO

-- function all change - Used for capture all changes in tables with cdc excluding the last value in update modification
DECLARE @begin_time datetime,
@end_time datetime,
@from_lsn binary(10),
@to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'smallest greater than', @begin_time);
-- the parameter <relational_operator> may have two values: SMALLEST GREATER THAN OR EQUAL or SMALLEST GREATER THAN

SET @to_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'largest less than or equal', @end_time);
-- the parameter <relational_operator> may have two values: LARGEST LESS THAN OR EQUAL or LARGEST LESS THAN

SELECT * 
FROM cdc.fn_cdc_get_all_changes_Person_Person(@from_lsn, @to_lsn, /*<row_filter_options>*/'all update old');
-- the parameter <row_filter_options> may have three values: ALL and ALL UPDATE OLD

-- ===========================
-- checking get_net funtion --
-- ===========================

USE AdventureWorks2014
GO
-- function net change - Used for capture the last value of the data, dispensing information of the update and delete statements
DECLARE @begin_time datetime,
@end_time datetime,
@from_lsn binary(10),
@to_lsn binary(10);

SET @begin_time = GETDATE() -1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'smallest greater than', @begin_time);
-- the parameter <relational_operator> may have two values: SMALLEST GREATER THAN OR EQUAL or SMALLEST GREATER THAN

SET @to_lsn = sys.fn_cdc_map_time_to_lsn(/*<relational_operator>*/'largest less than or equal', @end_time);
-- the parameter <relational_operator> may have two values: LARGEST LESS THAN OR EQUAL or LARGEST LESS THAN

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, /*<row_filter_options>*/'all with mask');
-- the parameter <row_filter_options> may have three values: ALL, ALL WITH MASK (return the final LSN of the row) and ALL WITH MERGE

Primeiramente execute os comandos abaixo que irão Inserir e Deletar alguns registros na tabela Person.Person e, também alguns Updates nos seus dados:

USE AdventureWorks2014
GO

-- ====================================
-- ----- insert operation test --------
-- ====================================

INSERT INTO [Person].[BusinessEntity]
 ([rowguid]
 ,[ModifiedDate])
VALUES ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50011',GETDATE()), 
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50081',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50082',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50083',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50084',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50085',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50086',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50090',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50088',GETDATE()),
 ('0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50089',GETDATE())
GO

INSERT INTO [Person].[Person]
 ([BusinessEntityID]
 ,[PersonType]
 ,[NameStyle]
 ,[Title]
 ,[FirstName]
 ,[MiddleName]
 ,[LastName]
 ,[Suffix]
 ,[EmailPromotion]
 ,[AdditionalContactInfo]
 ,[Demographics]
 ,[rowguid]
 ,[ModifiedDate])
VALUES (20778,'IN',0,NULL,'Arthur','J','Luz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50011',GETDATE()),
 (20779,'IN',0,NULL,'Heitor','D','Luz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50081',GETDATE()),
 (20780,'IN',0,NULL,'Ulisses','L','Silva Neto',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50082',GETDATE()),
 (20781,'IN',0,NULL,'Raquel','F','Nascimento',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50083',GETDATE()),
 (20782,'IN',0,NULL,'Miguel','C','Luz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50084',GETDATE()),
 (20783,'IN',0,NULL,'Rafael','M','Guedes',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50085',GETDATE()),
 (20784,'IN',0,NULL,'Marcelo','F','Moreira',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50086',GETDATE()),
 (20785,'IN',0,NULL,'Ana','L','Dos Santos',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50090',GETDATE()),
 (20786,'IN',0,NULL,'Leticia','B','Mendes',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50088',GETDATE()),
 (20787,'IN',0,NULL,'Lucas','A','Cruz',NULL,1,NULL,NULL,'0C7D8F81-D7B1-4CF0-9C0A-4CD8B6B50089',GETDATE())
GO

-- ====================================
-- ----- delete operation test --------
-- ====================================

DELETE FROM [Person].[Person]
WHERE [BusinessEntityID] IN (
 20785,
 20786,
 20787
)
GO

-- ====================================
-- ----- update operation test --------
-- ====================================

UPDATE [Person].[Person] 
SET [FirstName] = 'William',
 [LastName] = 'Golçalves',
 [MiddleName] = 'D'
WHERE [BusinessEntityID] = 20783
GO

UPDATE [Person].[Person] 
SET [Title] = 'Sr.'
WHERE [BusinessEntityID] = 20778
GO

UPDATE [Person].[Person] 
SET [PersonType] = 'SC'
WHERE [BusinessEntityID] = 20782
GO

UPDATE [Person].[Person]
SET [PersonType] = 'SC',
    [Title] = 'Sr.'
WHERE [BusinessEntityID] IN (1,2,3,4)
GO

Para facilitar o entendimento, para cada uma das opções de @row_filter_option de cada uma das TVFs, você usará um dos códigos abaixo (partindo do pressuposto de que o ambiente já foi criado seguindo os passos anteriores do conjunto de posts):

[fn_cdc_get_all_changes_…] – Value ALL

Usado para retornar todos registros da tabela de histórico do CDC eliminando os registros que possuem o valor 3 no campo [__$operation] pois estes registros são os valores anteriores ao valor atual do update (vide Parte 3 do conjunto de posts).

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_all_changes_Person_Person(@from_lsn, @to_lsn, 'all'); 
GO

01

[fn_cdc_get_all_changes_…] – Value ALL UPDATE OLD

Usado para retornar todos registros da tabela de histórico do CDC incluindo os registros que possuem o valor 3 no campo [__$operation].

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all update old
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_all_changes_Person_Person(@from_lsn, @to_lsn, 'all update old'); 
GO

02


[fn_cdc_get_net_changes_…] – Value ALL

Retorna somente a ultima atualização para cada um dos registros da tabela de histórico. Por exemplo, caso um registro tenha sido inserido, sofrido um update e depois deletado, a query abaixo irá mostrar apenas o registro deletado.

A coluna [__$update_mask] aparece com o valor NULL.

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, 'all'); 
GO

03

[fn_cdc_get_net_changes_…] – Value ALL WITH MASK

Retorna somente a ultima atualização para cada um dos registros da tabela de histórico assim como na situação anterior, porém, nesse caso, quando a coluna [__$operation] retornar o valor 4 (novo valor de um update), temos a coluna [__$update_mask] preenchida com a máscara da ultima atualização.

Você entenderá melhor sobre a utilização dessa coluna mais abaixo neste post.

USE AdventureWorks2014
GO

-- get_all_changes with row filter options equal all with mask
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, 'all with mask'); 
GO

04

[fn_cdc_get_net_changes_…] – Value ALL WITH MERGE

Retorna somente a ultima atualização para cada um dos registros da tabela de histórico, porém realiza um merge entre os registros Inseridos e Atualizados mostrando, neste caso, para ambas as situações, a coluna [__$operation] com valor igual a 5. O valor igual a 1 é usado pra deleção.

USE AdventureWorks2014
GO

--delete registry existing before cdc initialization
ALTER TABLE [Person].[EmailAddress]
DROP CONSTRAINT [FK_EmailAddress_Person_BusinessEntityID]
GO

ALTER TABLE [HumanResources].[Employee]
DROP CONSTRAINT [FK_Employee_Person_BusinessEntityID]
GO

ALTER TABLE [Person].[Password]
DROP CONSTRAINT [FK_Password_Person_BusinessEntityID]
GO

ALTER TABLE [Person].[PersonPhone]
DROP CONSTRAINT [FK_PersonPhone_Person_BusinessEntityID]
GO

DELETE FROM [Person].[Person]
WHERE [BusinessEntityID] IN (10,11,12)
GO
-- get_all_changes with row filter options equal all with merge
DECLARE @begin_time datetime, 
 @end_time datetime, 
 @from_lsn binary(10), 
 @to_lsn binary(10);

SET @begin_time = GETDATE()-1;
SET @end_time = GETDATE();
SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time); 
SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @end_time);

SELECT * 
FROM cdc.fn_cdc_get_net_changes_Person_Person(@from_lsn, @to_lsn, 'all with merge'); 
GO

Capture

Observer que para os registros de [BusinessEntityID] igual a 20785, 20786 e 20787 da tabela [Person].[Person] que foram inseridos hoje e também deletados hoje, ao realizar a operação de merge de acordo com o Range de data que foi passado (de ontem para hoje), o CDC desconsidera a operação de INSERT e nem o apresenta no retorno do SELECT. É como se esse registro não houvesse sido inserido.

Porém, para os registros 10, 11 e 12 que já faziam parte da tabela, ele mostra no select para a coluna [__$operation] o valor 1.


A coluna [__$update_mask] é responsável por “armazena uma máscara de bits com base nos ordinais de coluna da tabela de alteração que identificam as colunas que foram alteradas” (BOL).

Em outras palavras, através dela é possível descobrir quais colunas foram alteradas dentro de uma instrução de UPDATE. Caso a instrução seja de DELETE ou INSERT todas as colunas serão apresentadas.

No código será possível verificar quais registros, de acordo com o range de data fornecido, sofreram alterações de UPDATE ([__$operation] = 4) na coluna [Title] da tabela [Person].[Person]:

-- =======================================
-- understand the column __$update_mask --
-- =======================================

DECLARE @StartDate datetime = GETDATE()-1;
DECLARE @EndDate datetime = GETDATE();
DECLARE @begin_lsn BINARY(10); 
DECLARE @end_lsn BINARY(10);

-- Set the LSN values
SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @StartDate);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than', @EndDate);

-- get all updated rows where Title was updated
SELECT *
FROM cdc.fn_cdc_get_all_changes_Person_Person(@begin_lsn, @end_lsn, 'all')
WHERE __$operation = 4 
 AND sys.fn_cdc_is_bit_set (
 sys.fn_cdc_get_column_ordinal ('Person_Person','Title'),__$update_mask
 ) = 1
GO

Capture2

Já usando o código abaixo será possivel identificar quais colunas foram alteradas para cada um dos registros da tabela de histórico do CDC capturando somente aqueles que possuem valor de [__$operation] = 4 (todos os valores de UPDATE).

-- =======================================
-- understand the column __$update_mask --
-- =======================================

-- capture all columns changed in each transaction
SELECT ( SELECT CC.column_name + ','
 FROM cdc.captured_columns CC
 INNER JOIN cdc.change_tables CT ON CC.[object_id] = CT.[object_id]
 WHERE capture_instance = 'Person_Person'
 AND sys.fn_cdc_is_bit_set(CC.column_ordinal,
 PD.__$update_mask) = 1
 FOR
 XML PATH('')
 ) AS ChangedColumns,
 *
FROM cdc.Person_Person_CT PD
WHERE PD.__$operation = 4
GO

Capture3


No proximo post da série será possível aprender sobre as melhores práticas e observações para a utilização do Change Data Capture. Após isso, iniciaremos a etapa de construção de ETLs para consumo da feature em cargas de dados de Data Warehouse (DWs) e/ou Operational Data Stores (ODSs).

Capturando a Collation de Todas as bases e Colunas da Instância

Recentemente fui incumbido de realizar um levantamento das collations das bases de dados e colunas do sistema transacional da ANEEL para que pudéssemos mensurar a complexidade de uma migração de Collation.

Com base nisso, desenvolvi uma query usando as DMVs do SQL Server que busca em cada um dos bancos de dados de uma instância sua collation e também quantidade de colunas que cada base possui com collation divergente da collation da base de dados.


DECLARE @informations TABLE (
 
 database_name VARCHAR (500),
 db_collation_name VARCHAR(500),
 
 type_desc VARCHAR(500),
 
 tb_name VARCHAR(500),
 tb_columns_name VARCHAR(500),
 tb_column_collation_name VARCHAR(500),

 vw_name VARCHAR(500),
 vw_columns_name VARCHAR(500),
 vw_column_collation_name VARCHAR(500),

 tvf_name VARCHAR(500),
 tvf_columns_name VARCHAR(500),
 tvf_column_collation_name VARCHAR(500),

 itf_name VARCHAR(500),
 itf_columns_name VARCHAR(500),
 itf_column_collation_name VARCHAR(500),

 tt_name VARCHAR(500),
 tt_columns_name VARCHAR(500),
 tt_column_collation_name VARCHAR(500)
)

INSERT INTO @informations 

EXEC sp_msforeachdb '
 SELECT (SELECT DISTINCT name FROM sys.databases WHERE name = ''?'') AS database_name,
 (SELECT DISTINCT collation_name FROM sys.databases WHERE name = ''?'') AS db_collation_name,
 
 ob.type_desc,
 
 tb.name AS tb_name,
 co_tb.name AS tb_columns_name,
 co_tb.collation_name AS tb_column_collation_name, 
 
 vw.name AS vw_name,
 co_vw.name AS vw_columns_name,
 co_vw.collation_name AS vw_column_collation_name, 
 
 CASE WHEN ob.type = ''TF'' THEN ob.name ELSE NULL END AS tvf_name,
 co_tvf.name AS tvf_columns_name,
 co_tvf.collation_name AS tvf_column_collation_name, 
 
 CASE WHEN ob.type = ''IF'' THEN ob.name ELSE NULL END AS itf_name,
 co_itf.name AS itf_columns_name,
 co_itf.collation_name AS itf_column_collation_name,

 CASE WHEN ob.type = ''TT'' THEN ob.name ELSE NULL END AS tt_name,
 co_tt.name AS tt_columns_name,
 co_tt.collation_name AS tt_column_collation_name

 FROM [?].sys.all_objects ob
 LEFT JOIN [?].sys.tables tb ON tb.object_id = ob.object_id
 LEFT JOIN [?].sys.all_views vw ON vw.object_id = ob.object_id
 LEFT JOIN [?].sys.all_columns co_tb ON co_tb.object_id = tb.object_id AND co_tb.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_vw ON co_vw.object_id = vw.object_id AND co_vw.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tvf ON ob.type = ''TF'' and co_tvf.object_id = ob.object_id AND co_tvf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_itf ON ob.type = ''IF'' and co_itf.object_id = ob.object_id AND co_itf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tt ON ob.type = ''TT'' and co_tt.object_id = ob.object_id AND co_tt.collation_name IS NOT NULL
 WHERE ob.type in (''U'',''V'',''TF'',''IF'',''TT'')'

SELECT --database name and collation
 database_name,
 db_collation_name,
 
 --verify amount of tables, table text columns and table text columns with collation difference of database
 COUNT( DISTINCT tb_name) AS qtd_tables,
 COUNT(tb_columns_name) AS qtd_table_columns,
 SUM(CASE WHEN db_collation_name <> tb_column_collation_name THEN 1 ELSE 0 END) AS qtd_table_columns_with_different_collation,
 
 --verify amount of views, view text columns and view text columns with collation difference of database
 COUNT(DISTINCT vw_name) AS qtd_views,
 COUNT(vw_columns_name) AS qtd_view_columns,
 SUM(CASE WHEN db_collation_name <> vw_column_collation_name THEN 1 ELSE 0 END) AS qtd_view_columns_with_different_collation,

 --verify amount of table valued functions, table valued function text columns and table valued function text columns with collation difference of database
 COUNT(tvf_name) AS qtd_tvf,
 COUNT(tvf_columns_name) AS qtd_tvf_columns,
 SUM(CASE WHEN db_collation_name <> tvf_column_collation_name THEN 1 ELSE 0 END) AS qtd_tvf_columns_with_different_collation,

 --verify amount of inline table valued functions, inline table valued function text columns and inline table valued function text columns with collation difference of database
 COUNT(itf_name) AS qtd_itf,
 COUNT(itf_columns_name) AS qtd_itf_columns,
 SUM(CASE WHEN db_collation_name <> itf_column_collation_name THEN 1 ELSE 0 END) AS qtd_itf_columns_with_different_collation,
 
 --verify amount of table types, table type text columns and table type text columns with collation difference of database
 COUNT(tt_name) AS qtd_tt,
 COUNT(tt_columns_name) AS qtd_tt_columns,
 SUM(CASE WHEN db_collation_name <> tt_column_collation_name THEN 1 ELSE 0 END) AS qtd_tt_columns_with_different_collation

FROM @informations
WHERE database_name NOT IN ('master','tempdb','model','msdb')
GROUP BY database_name,
 db_collation_name

Caso você queira saber quais os nomes das colunas cujo Collation diverge da collation da base de dados para cada objeto, basta usar a query abaixo:

DECLARE @informations TABLE (
 
 database_name VARCHAR (500),
 db_collation_name VARCHAR(500),
 
 type_desc VARCHAR(500),
 
 tb_name VARCHAR(500),
 tb_columns_name VARCHAR(500),
 tb_column_collation_name VARCHAR(500),

 vw_name VARCHAR(500),
 vw_columns_name VARCHAR(500),
 vw_column_collation_name VARCHAR(500),

 tvf_name VARCHAR(500),
 tvf_columns_name VARCHAR(500),
 tvf_column_collation_name VARCHAR(500),

 itf_name VARCHAR(500),
 itf_columns_name VARCHAR(500),
 itf_column_collation_name VARCHAR(500),

 tt_name VARCHAR(500),
 tt_columns_name VARCHAR(500),
 tt_column_collation_name VARCHAR(500)
)

INSERT INTO @informations 

EXEC sp_msforeachdb '
 SELECT (SELECT DISTINCT name FROM sys.databases WHERE name = ''?'') AS database_name,
 (SELECT DISTINCT collation_name FROM sys.databases WHERE name = ''?'') AS db_collation_name,
 
 ob.type_desc,
 
 tb.name AS tb_name,
 co_tb.name AS tb_columns_name,
 co_tb.collation_name AS tb_column_collation_name, 
 
 vw.name AS vw_name,
 co_vw.name AS vw_columns_name,
 co_vw.collation_name AS vw_column_collation_name, 
 
 CASE WHEN ob.type = ''TF'' THEN ob.name ELSE NULL END AS tvf_name,
 co_tvf.name AS tvf_columns_name,
 co_tvf.collation_name AS tvf_column_collation_name, 
 
 CASE WHEN ob.type = ''IF'' THEN ob.name ELSE NULL END AS itf_name,
 co_itf.name AS itf_columns_name,
 co_itf.collation_name AS itf_column_collation_name,

 CASE WHEN ob.type = ''TT'' THEN ob.name ELSE NULL END AS tt_name,
 co_tt.name AS tt_columns_name,
 co_tt.collation_name AS tt_column_collation_name

 FROM [?].sys.all_objects ob
 LEFT JOIN [?].sys.tables tb ON tb.object_id = ob.object_id
 LEFT JOIN [?].sys.all_views vw ON vw.object_id = ob.object_id
 LEFT JOIN [?].sys.all_columns co_tb ON co_tb.object_id = tb.object_id AND co_tb.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_vw ON co_vw.object_id = vw.object_id AND co_vw.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tvf ON ob.type = ''TF'' and co_tvf.object_id = ob.object_id AND co_tvf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_itf ON ob.type = ''IF'' and co_itf.object_id = ob.object_id AND co_itf.collation_name IS NOT NULL
 LEFT JOIN [?].sys.all_columns co_tt ON ob.type = ''TT'' and co_tt.object_id = ob.object_id AND co_tt.collation_name IS NOT NULL
 WHERE ob.type in (''U'',''V'',''TF'',''IF'',''TT'')'


SELECT * 
FROM (
 SELECT --database name and collation
 database_name,
 db_collation_name,
 
 --verify name of tables, table text columns and table text columns with collation difference of database
 tb_name AS table_name,
 tb_columns_name AS table_column_name,
 CASE WHEN db_collation_name <> tb_column_collation_name THEN tb_column_collation_name ELSE NULL END AS table_column_collation,
 
 --verify name of views, view text columns and view text columns with collation difference of database
 vw_name AS view_name,
 vw_columns_name AS view_column_name,
 CASE WHEN db_collation_name <> vw_column_collation_name THEN vw_column_collation_name ELSE NULL END AS view_column_collation,

 --verify name of table valued functions, table valued function text columns and table valued function text columns with collation difference of database
 tvf_name AS tvf_name,
 tvf_columns_name AS tvf_column_name,
 CASE WHEN db_collation_name <> tvf_column_collation_name THEN tvf_column_collation_name ELSE NULL END AS tvf_column_collation,

 --verify name of inline table valued functions, inline table valued function text columns and inline table valued function text columns with collation difference of database
 itf_name AS itf_name,
 itf_columns_name AS itf_column_name,
 CASE WHEN db_collation_name <> itf_column_collation_name THEN itf_column_collation_name ELSE NULL END AS itf_column_collation,
 
 --verify name of table types, table type text columns and table type text columns with collation difference of database
 tt_name AS tt_name,
 tt_columns_name AS tt_column_name,
 CASE WHEN db_collation_name <> tt_column_collation_name THEN tt_column_collation_name ELSE NULL END AS tt_column_collation

 FROM @informations
) AS detail_infornmations
WHERE database_name NOT IN ('master','tempdb','model','msdb')
 AND (table_column_collation IS NOT NULL 
 OR view_column_collation IS NOT NULL
 OR tvf_column_collation IS NOT NULL
 OR itf_column_collation IS NOT NULL
 OR tt_column_collation IS NOT NULL)

CDC – Parte 04

Neste post da série você aprenderá sobre a procedure de sistema  sys.sp_cdc_change_job e seus parâmetros usada para alterar os jobs de captura e limpeza dos dados nas tabelas de histórico do Change Data Capture.


A procedure sys.sp_cdc_change_job é usada para configurar os jobs [_capture] [_cleanup] que são responsáveis pelas rotinas de captura e cleanup das tabelas de CDC. essa sp possui alguns parâmetros. São eles:

@job_type – Identifica qual o job que será alterado/configurado. Parâmetro obrigatório;


Para o Job de Captura de dados existem os seguintes parâmetros de configuração:

@maxtrans – Fornece o número máximo de transações a processar em cada ciclo de scan. Esse parâmetro só pode ser utilizado para o job de captura. O valor inicial é de 500 transações por ciclo;

@maxscans – Fornece o número máximo de ciclos de scan a serem executados para captura de todos os registros do log de transação. Esse parâmetro só pode ser utilizado para o job de captura O valor inicial é de 10 ciclos de scan;

@pollinginterval Quantidade de segundos entre cada ciclo de scan. Esse parâmetro só pode ser utilizado para o job de captura. O valor inicial é de 5 segundos de espera entre cada ciclo;

@continuous – Indica se o job de captura de dados do Log terá execução continua ou não.

Quando definido como 1, o job examina o log e processa até (max_trans * max_scans) transações. Em seguida, aguarda o número de segundos especificados no polling_interval antes de começar um novo ciclo de verificações.

Se definido como 0, a execução do job ocorrerá (max_trans * max_scans) e depois sairá do job. Esse parâmetro só pode ser utilizado para o job de captura. O valor inicial é 1;

A arquitetura do job de captura funciona conforme figura abaixo:

Capturar

É importante ao utilizar o Change Data Capture que você faça uma verificação, tanto de Hardware quanto de Volumetria de dados de alteração para poder otimizar o seu ambiente. A latência de captura das alterações e a quantidade de transações capturadas por segundo são dois pontos de suma importância a serem observados.

Sobre isso, deixo como abaixo um dos exemplos realizados Steffen Krause em um post escrito para o TechNet em 2008 onde uma comparação é realizada entre dois tipos de configuração do job de captura.

Imagem1
Padrão – @continuous = 1, @maxscans = 10, @maxtrans = 500 e @pollinginterval = 5
Imagem2
Modificado – @continuous = 1, @maxscans = 100, @maxtrans = 5000 e @pollinginterval = 5
Imagem3
Outras possibilidades de configuração e observações realizadas nos testes.

Para maiores informações sobre configurações avançadas do Change Data Capture, leia o post Tuning the Performance of Change Data Capture in SQL Server 2008 do Steffen Krause na íntegra. 

As informações demonstradas acima estão relacionadas ao hardware usado no artigo.


Para o Job de Cleanup existem os seguintes parâmetros de configuração:

@retention Número em minutos de armazenamento dos dados de alteração nas tabelas de histórico do CDC antes que a limpeza desses dados ocorra. Esse parâmetro só pode ser utilizado para o job de Cleanup. O valor inicial é 4320 minutos (3 dias);

@threshold Número máximo de exclusões que podem ser efetuadas para cada operação de cleanup de dados. Esse parâmetro só pode ser utilizado para o job de Cleanup. O valor inicial é 5000 deleções;


Para consultar as configurações atuais dos Jobs [_capture] e de [_cleanup] de dados use a tabela msdb.dbo.cdc_jobs.

Abaixo um exemplo de execução da procedure sys.sp_cdc_change_job:


-- ===================================
-- changes in the CDC configuration --
-- ===================================
USE AdventureWorks2014
GO

-- Used for to verify the current configuration inside the jobs
SELECT *
FROM msdb.dbo.cdc_jobs

/* Case the change data capture is enabled in database 
 with transactional replication, the jobs of the replication are used */

-- Jobs
EXEC sys.sp_cdc_change_job @job_type = 'capture'
 ,@maxtrans = 5000 -- number of transactions read between each cicle
 ,@maxscans = 10 -- number of cycles between each polling interval 
 ,@continuous = 1 -- enable the continuous execution of the work of the capture 
 ,@pollinginterval = 1 -- Interval in seconds between each cycle of the log read

EXEC sys.sp_cdc_change_job @job_type = 'cleanup'
 ,@retention = 10080 -- time in minutes of retention of the dates in cdc tables (default is 3 days)

No próximo post da série você aprenderá sobre as functions [_get_all_changes] e [_get_net_changes] para manipular os dados salvos dentro das tabelas de histórico do Change Data Capture.