Dica do Mês – Conhecendo e aplicando o uso de atualização de estatísticas incrementais


Muito boa tarde pessoal, salve, salve comunidade e amantes de banco de dados.

Tudo bem com vocês? Estou aqui mais uma vez em um novo post do meu blog na sessão Dica do Mês, hoje falando de um assunto que até alguns dias atrás eu sinceramente nunca havia feito uso, mas com base em um post publicado do Ahmad Yaseen no MSSQLTips.com, acabou me servindo como fonte de inspiração para elaborar e compartilhar este post com vocês.

Antes de começarmos a falar sobre o post de hoje, nada mais justo começar agradecendo como de costume a você está aqui neste momento acessando meu blog. Espero que possa ter encontrado o que precisa, bem como, esteja gostando do conteúdo publicado, fique a vontade para entrar em contato expressando suas opiniões e demais pensamentos.

Dando continuidade, vamos conhecer um recurso adicionado na versão 2014 do Microsoft SQL Server a partir do Service Pack 2 e mantido no Microsoft SQL Server 2016 SP1 conhecido como Estatísticas Incrementais ou Incremental Statistics, pode parecer estranho o nome, mas é exatamente isso que este recurso permite, realizar o processo de atualização de estatísticas de maneira incremental, ou para muitos incrementar o processo de atualização de estatísticas aplicadas aos nossos bancos de dados e seus respectivos objetos.

Parece ser coisa de louco isso, mas posso garantir que não é, absolutamente é algo totalmente viável e aplicável a qualquer ambiente que se faça uso do Microsoft SQL Server em conjunto com as funções e scheme de particionamento de dados.

E ai esta curioso para saber um pouco sobre este recurso?

Eu estou, sendo assim, vamos em frente, seja bem vindo ao post Dica do Mês – Conhecendo e aplicando o uso de atualização de estatísticas incrementais.

Seguindo….


Introdução

O otimizador de consultas do Microsoft SQL Server depende fortemente das estatísticas na geração a execução de plano de consulta mais eficiente. Estas estatísticas fornecem ao otimizador a distribuição dos valores de colunas na tabela e o número de linhas, também chamada a cardinalidade que resultará da consulta.

A ausência destas estatísticas, ou a existência de estatísticas desatualizadas, proporciona a ocorrência de querys consideradas lentas, neste sentido, o otimizador de consulta “query optimizer” acaba sendo obrigado a utilizar estatísticas imprecisas para criar o plano de execução, que pode ser considerado um plano não ideal para executar a consulta neste caso.

O SQL Server geralmente faz o seu trabalho em manter estas estatísticas atualizadas, mas como um administrador de banco de dados, você deve fazer seu trabalho, em alguns casos, atualizando as estatísticas manualmente. Atualizar estatísticas manualmente em tabelas grandes pode ser como um grande desafio, bem como, em tabelas pequenas pode-se imaginar que a estatística já esteja atualizada, o que em alguns cenários isso acaba não ocorrendo.

Um dos cenários mais impactados pelo uso de estatísticas desatualizadas ou atualizadas parcialmente são as tabelas particionadas. Como destacado anteriormente através do uso das funções de particionamento de dados introduzido no Microsoft SQL Server 2008, temos a capacidade de distribuir nossos dados em partições “pequenos fatias de armazenamento de dados” que nos possibilitar distribuir respectivos valores com base em uma função que análise e identifica o local de armazenamento do mesmo.

Para este tipo de ambiente, o uso de estatísticas como mecanismo para auxiliar no obtenção mais rápida do dado, pode apresentar simultaneamente o papel de herói como também de vilão, isso pode parecer meio confusão, mas não é! Basicamente quando trabalhamos com estatísticas acreditamos que sempre teremos todas as informações armazenados no histograma atualizadas de forma automática de maneira mais precisa possível, algo que não acontece exatamente desta maneira quando trabalhando com particionamento de dados.

Uma das situações mais comuns quando se uso particionamento de dados é a possibilidade de ocorrer a atualização de estatísticas de maneira parcial, ou seja, apena um partição de todo estrutura de partições acaba tendo suas informações de estatísticas atualizadas, o que poderá provocar uma alteração no plano de execução ou a possibilidade de criação de um plano incoerente.

Sabendo desta possibilidade e comportamento, o time de engenheiros e desenvolvedores do Microsoft SQL Server, implementou a partir da versão 2014 SP1 as Estatísticas Incrementais, funcionalidade que nos permite justamente contornar este tipo de situação.

Estatísticas Incrementais – Incremental Statistics

As estatísticas Incrementais, ajudam na atualização de estatísticas para apenas a partição ou partições que você escolher. Em vez de analisar e varrer a tabela inteira para atualizar as estatísticas, a partição selecionada será verificada somente para a atualização, reduzindo o tempo necessário para executar a operação de atualização de estatísticas, atualizando-se apenas a partição modificada.

O outro ponto importante é que a porcentagem de alterações de dados necessário para acionar a atualização automática de estatísticas, sendo este o valor 20% de linhas alteradas, o que proporcionará o uso de atualização de estatísticas no nível da partição, comportamento que não era permitido anteriormente.

Muito legal este novo recurso e principalmente o comportamento do Microsoft SQL Server, agora que já conhecemos conceitualmente como as estatísticas incrementais funcionam, chegou a hora de colocar as mãos no teclado e começar a conhecer de maneira prática esta funcionalidade.

Preparando o ambiente

Para entender a atualizar as estatísticas incrementais, vamos preparar um banco de dados de teste com uma tabela particionada. Começamos com a criação de um novo banco de dados denominado IncrementalStatistics, formado por quatro novos grupos de arquivos além de grupo de arquivos primário padrão, para tal vamos utilizar o Bloco de Código 1 apresentado a seguir:

— Bloco de Código 1 —

— Criando o Banco de Dados IncrementalStatistics —
Create Database IncrementalStatistics
Go
— Adicionando os Filegroups —
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo1
Go
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo2
Go
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo3
Go
Alter Database IncrementalStatistics
Add Filegroup IncrementalStatisticsGrupo4
Go

 

— Adicionando os Arquivos aos seus respectivos Filegroups —

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo1′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo1-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo1
Go

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo2′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo2-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo2
Go

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo3′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo3-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo3
Go

Alter Database IncrementalStatistics
Add File (Name = N’IncrementalStatisticsGrupo4′,
FileName = N’S:\MSSQL-2016\Data\Arquivo-Grupo4-Data.ndf’,
Size = 4096KB,
FileGrowth =1024KB) To Filegroup IncrementalStatisticsGrupo4
Go

Uma vez que o banco de dados é criado com os novos grupos de arquivos e arquivos de dados, precisamos prepará-lo para hospedar a tabela particionada. Nosso próximo passo consiste na criação da função particionada PartitionFunctionIncrementalStatistics que classifica os dados de acordo com os quatro trimestres do ano, sendo assim, vamos utilizar o Bloco de Código 2 apresentado abaixo:

— Bloco de Código 2 —

— Criando a Partition Function PartitionFunctionIncrementalStatistics —
USE IncrementalStatistics
GO

CREATE PARTITION FUNCTION PartitionFunctionIncrementalStatistics (Int)
AS
RANGE RIGHT FOR VALUES
(20171, 20172, 20173, 20174)
Go

Note que nossa PartitionFunctionIncrementalStatistics é composta por quatro partições de valores subdivididos da seguinte forma: 

  • 20171 – Valor que representa o Primeiro Quartil;
  • 20172 – Valor que representa o Segundo Quartil;
  • 20173 – Valor que representa o Terceiro Quartil do Ano; e
  • 20174 – Valor que representa o Quarto Quartil do Ano.

    Talvez você ainda não esteja entendendo o porque estamos fazendo uso deste tipo de implementação, tenha calma no decorrer do post tudo vai ficar mais claro e você terá total noção do porquê estamos utilizando este recurso.

Continuando com a nossa longa caminhada, você deve saber que para se trabalhar com particionamento de dados devemos além de criar uma Partition Function devemos obrigatoriamente criar um Partition Scheme,  que estará vinculado lógicamente a nossa partition function, sendo assim, este é nosso próximo passo, fazendo uso do Bloco de Código 3:

— Bloco de Código 3 —

— Criando o Partition Scheme PartitionSchemeIncrementalStatistics —
CREATE PARTITION SCHEME PartitionSchemeIncrementalStatistics AS
PARTITION PartitionFunctionIncrementalStatistics
TO
(
IncrementalStatisticsGrupo1,
IncrementalStatisticsGrupo2,
IncrementalStatisticsGrupo3,
IncrementalStatisticsGrupo4,
[PRIMARY])
Go

Esta quase tudo pronto para nossa brincadeira, seguiremos com a criação na nossa tabela TableIncrementalStatistics, este é um ponto importante do nosso ambiente, onde estamos fazendo uso da tabela particionada para ilustrar como as estatísticas incrementais vão realizar o seu papel.

TableIncrementalStatistics será composta por algumas colunas, dentre as quais a coluna Quartil, responsável em armazenar o valor do quartil de acordo com o ano informado, como também, é através desta coluna que estaremos realizando o particionamento dos dados. Para isso utilizaremos o Bloco de Código 4 a seguir:

— Bloco de Código 4 —

— Criando a Tabela TableIncrementalStatistics —

CREATE TABLE TableIncrementalStatistics
(ID Int Null,
Acao NVarchar(40) Default NewID(),
Data DateTime Null,
Quartil  AS (datepart(year,[Data])*(10)+datepart(quarter,[Data])) PERSISTED
) ON PartitionSchemeIncrementalStatistics (Quartil)
Go

Ótimo, toda estrutura para armazenar nossos dados já esta pronta, bem como, a lógica para distribuir e particionar os dados que serão inseridos na tabela TableIncrementalStatistics.

Ufa, ainda temos um bom caminho pela frente, mas já avançamos bastante, agora temos realizar uma alteração nas configurações do nosso banco de dados IncrementalStatistics, sendo esta necessária para podermos aplicara o uso de estatísticas incrementais, estou me referindo a opção Auto Create Statistics muito conhecida, onde vamos alterar o seu valor default para Incremental = On, conforme apresenta o Bloco de Código 5 abaixo:

— Bloco de Código 5 —

— Habilitando o uso de Incremental Statistics —
Alter Database IncrementalStatistics
Set Auto_Create_Statistics On (INCREMENTAL = On)
Go

O próximo passo consiste na criação do índice que iremos utilizar em na TableIncrementalStatistics pois você deve ter notado que realizamos a criação da tabela sem a definição de uma chave primária, desta maneira  utilizamos o Bloco de Código 6 para criação dos respectivo índice em seguida confirmamos se esta tabela esta fazendo uso das estatísticas incrementais habilitada no bloco de código 5:

— Bloco de Código 6 —

— Criação do índice Clustered —
Create Clustered Index Ind_TableIncrementalStatistics_ID
On [TableIncrementalStatistics] (ID)
GO

— Confirmando se as estatísticas incrementais está habilita —
SELECT
OBJECT_NAME(object_id) TableName
,name
,is_incremental
,stats_id
FROM sys.stats
WHERE name = ‘Ind_TableIncrementalStatistics_ID’
Go

Figura 1 – Confirmando o uso das estatísticas incrementais no índice Ind_TableIncrementalStatistics_ID.

Observação: Note que ao executar o Select realizado na visão de sistema sys.stats a coluna Is_Incremental deverá retornar e apresentar o valor igual á 1, isso indica que TableIncrementalStatistics esta neste momento fazendo uso das estatísticas incrementais.

Muito bem, chegou a hora de popular nossas tabelas, realizaremos a inserção de 8.000 linhas de registros, sendo estes particionados em grupos de 2.000 registros para cada partição que forma e compõem a estrutura da nossa tabela. Vamos então utilizar o Bloco de Código 7 apresentado na sequência:

— Bloco de Código 7 —

— Inserindo os dados na TableIncrementalStatistics —
Insert Into TableIncrementalStatistics (ID, Data)
Values (1, ‘2017-11-22’)
Go 2000

Insert Into TableIncrementalStatistics (ID, Data)
Values (2, ‘2017-06-05’)
Go 2000

Insert Into TableIncrementalStatistics (ID, Data)
Values (3, ‘2017-01-25’)
Go 2000

Insert Into TableIncrementalStatistics (ID, Data)
Values (4, ‘2017-08-13’)
Go 2000

Após a inserção das 8.000 linhas de registros, vamos confirmar a distribuição dos dados através do Bloco de Código 8 declarado abaixo, conforme ilustra o resultado da Figura 2:

— Bloco de Código 8 —

— Consultando a distribuição e particionamento dos dados —
Select partition_number, rows
From sys.partitions
Where OBJECT_NAME(OBJECT_ID)=’TableIncrementalStatistics’
Go

Figura 2 – Distribuição dos dados na tabela TableIncrementalStatistics de acordo com o valor e partição.

Estamos chegando no final, agora vamos realizar algumas manipulações no conjunto de dados armazenados na tabela TableIncrementalStatistics afim de forçarmos o processos de atualização das estatísticas, procedimento que vai nos ajudar a entender o processo de incremento na atualização das estatísticas de armazenamento e processamento utilizados pelo Microsoft SQL quando solicitado acesso aos dados armazenados em nossa table, para tal operação vamos utilizar o Bloco de Código 9:

— Bloco de Código 9 —

— Consultando dados na TableIncrementalStatistics —
Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID = 1
Go

Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID >= 2
Go

Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID <> 3
Go

Pronto, realizamos algumas operações de Select com intuito de forçar a criação de novas estatísticas, e principalmente a atualização das estatísticas atuais. Por enquanto nada de diferente, na sequência vamos consultar as informações sobre as estatísticas relacionadas a nossa tabela, fazendo uso do Bloco de Código 10 e analisando o resultado apresentado através da Figura 3:

— Bloco de Código 10 —

— Consultando as informações sobre as estatísticas da tabela TableIncrementalStatistics —
Select object_id, stats_id , last_updated , rows , rows_sampled , steps
From sys.dm_db_stats_properties(OBJECT_ID(‘[TableIncrementalStatistics]’),1);
Go

Figura 3 – Dados relacionados a estatísticas da TableIncrementalStatistics.

Como você pode ver, o DMF sys.dm_db_stats_properties mostra-nos que as estatísticas foram atualizadas na data do dia 23/05/2017 ás 16:55, para a tabela que tem 8000 linhas.

Neste momento, podemos nos perguntar: Qual partição da tabela inclui as estatísticas atualizadas?

A resposta para esta sua pergunta vem justamente atráves do uso nova DMF sys.dm_db_incremental_stats_properties já apresentada aqui no meu blog. Sendo esta DMF responsável em apresentar as propriedades estatísticas incremental, recuperando as mesma informação obtida a partir do DMF sys.dm_db_stats_properties, também super conhecida e apresentada no meu blog. Neste caso a sys.dm_db_stats_properties vai apresentar dados de  cada partição da tabela particionada, fornecendo-lhe com os mesmos parâmetros; a identificação do objeto e a identificação de estatísticas.

Caminhando mais um pouco, estamos próximos do final, vamos então formar o SQL Server a justamente realizar o processo de atualização das estatísticas para nossa partição de número 3, realizando o processo de exclusão de 1.500 linhas de registros, em seguida consultando nossa TableIncrementalStatistics, conforme apresenta o Bloco de Código 11:

— Bloco de Código 11 —

— Excluíndo 1.500 linhas —
Delete Top (1500) From TableIncrementalStatistics
Where ID = 2
Go

— Consultando os dados —
Select Id, Acao, Data, Quartil From TableIncrementalStatistics
Where ID <> 4
Go

Agora vamos novamente consultar os dados estatísticas, sendo assim repita a execução do Bloco de Código 10, observe que você deverá receber um conjunto de valores similares a Figura 4, onde a coluna Last_Updated deverá apresentar a data e hora da última atualização:

Figura 4 – Data e hora da última atualização da estatística.

Ufa, estamos quase lá, agora chegou a hora da verdade, hora de comprovar se realmente o SQL Server esta fazendo as coisas certas, vamos fazer uso da DMF sys.dm_incremental_stats_properties para validar se a estatística da partição 3 foi atualizada, o resultado pode ser analisado através da Figura 5. Para isso vamos utilizar o Bloco de Código 12 a seguir:

— Bloco de Código 12 —

— Consultando as informações sobre as estatísticas incrementais —
Select object_id, stats_id,
partition_number,
last_updated,
rows, rows_sampled,
steps
From sys.dm_db_incremental_stats_properties(OBJECT_ID(‘TableIncrementalStatistics’),1)
Go

Figura 5 – Informações sobre as atualizações de estatísticas, onde a partição 3 foi atualizada de maneira independente das demais.

Sensacional, conseguimos, muito legal este recurso, como sempre o Microsoft SQL Server nos surpreende com a sua capacidade e potencialidade de recursos.

Referências

Post Anteriores

https://pedrogalvaojunior.wordpress.com/2017/04/13/dica-do-mes-microsoft-sql-server-identificando-as-transacoes-que-estao-utilizando-o-transact-log/

https://pedrogalvaojunior.wordpress.com/2017/03/01/dica-do-mes-microsoft-sql-server-2016-sp1-novo-argumento-use-hint-disponivel-para-query-hints/

https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

https://pedrogalvaojunior.wordpress.com/2016/11/28/dica-do-mes-sql-server-2016-sp1-comando-create-or-alter/

https://pedrogalvaojunior.wordpress.com/2016/10/24/dica-do-mes-sql-server-2016-obtendo-informacoes-sobre-o-cache-de-execucao-de-funcoes/

Conclusão

Administrar, gerenciar, cuidar e prover um ambiente sempre no melhor estado possível não é uma das atividades mais tranquilas e simples desempenhadas por profissionais ou administradores de banco de dados, mas também não pode ser considerada um “bicho de sete cabeças” ou uma “caixa preta” ainda mais quando este servidor de banco de dados utiliza o Microsoft SQL Server.

Neste post você pode conhecer um pouco mais de como o Microsoft SQL Server trabalha de maneira árdua na busca da melhor maneira para encontrar e retornar os dados solicitados em nossas transações. Ao longo de novas versões o produto esta cada vez mais maduro, confiável e inteligente, sempre nos surpreendendo com sua capacidade.

Algo que não poderia ser diferente no uso das Estatísticas Incrementais, recurso que nos permite adotar uma nova maneira de atualização dos dados internos relacionados ao armazenamento das nossas informações, mas principalmente prover um auxílio para próprio Database Engine mas atividades para identificar o melhor caminho para se processar uma query.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Um forte abraço, nos encontramos logo logo…

Valeu….

Dica do Mês – Microsoft SQL Server – Identificando as transações que estão utilizando o Transact Log


Bom dia pessoal, tudo bem?

Véspera de feriado religioso e um dos mais importantes para boa parte da população mundial! Justamente por ser véspera de feriado hoje é um daqueles dias que qualquer profissional da área de banco de dados reza para que nada ocorra de errado próximo ás 17hrs ou mais conhecido como 5 horas da tarde.

Pensando neste tipo de necessário o post de hoje dedicado a sessão Dica do Mês tem o objetivo de ajudar você profissional que esta em apuros neste momento tentando identificar o que pode estar ocorrendo em seu ambiente ou servidor Microsoft SQL Server.

Hoje vou apresentar como você pode no Microsoft SQL Server a partir da versão 2008 R2 identificar quais transações estão realizando o acesso e demandando informações para o Transact – Log, arquivo utilizado por qualquer banco de dados existente em seu servidor ou instância SQL Server para registrar dados relacionados as transações atuais ou já processadas, sendo este mais conhecido como log de transações.

Estão vamos em frente…..


Introdução

Quando se referimos ao log de transações, temos sempre aquele boa e velha definição, “o transact log é o arquivo utilizado pelo banco de dados para registrar o que esta sendo processado por nossas transações, conhecido como o dedo duro”, na verdade o transact-log é bem mais do que isso, trata-se de um dos principais componentes de qualquer banco de dados criado no Microsoft SQL Server que nos permite através do seu uso garantir a continuidade e disponibilidade de nossos dados para diversos cenários de armazenamento e principalmente recuperação de informações.

Durante o processamento de uma ou mais querys o transact-log pode apresentar comportamentos diversas de acordo com o que esta sendo processado, mas principalmente no que se relacionado aos processos de escrita e leitura do log, onde nossas querys podem gerar processos de gravação de dados no transact-log e internamente o SQL Server pode necessitar a acessar outros dados em log para atender outras possíveis demandas.

Estes processos são conhecidos como leitura ou escrita do transact-log, situações que podem impactar aleatoriamente no processamento de nossas querys.

Pensando neste tipo de possibilidade, o post de hoje visa ilustrar e apresentar como através do Management Studio ferramenta de administração e gerenciamento do Microsoft SQL Server podemos em conjunto com as DMVs – Dynamic Management Views listadas abaixo, monitorar, catalogar e identificar quais querys ou transações estão fazendo uso do Transact-Log em possíveis processos de leitura ou escrita do log.

Dynamic Management Views

Para saber mais sobre cada DMV acesse as referências apresentadas no final do post:

  • sys.dm_tran_session_transactions;
  • sys.dm_tran_active_transactions;
  • sys.dm_tran_database_transactions;
  • sys.dm_exec_sessions;
  • sys.dm_exec_requests; e
  • sys.dm_exec_sql_text.

O Cenário

Para tentar ilustrar como podemos realizar de maneira fácil este tipo de monitoramento e identificação, vamos criar um pequeno cenário através do Bloco de Código 1, onde estaremos realizando a criação de um novo banco de dados denominado UsingTransactLog para armazenar a tabela TabelaTransactionLog.

— Bloco de Código 1 —
— Criando o banco de dados —
Create Database UsingTransactLog
Go

— Acessando o banco de dados —
Use UsingTransactLog
Go
— Criando a tabela —
Create Table TabelaTransactLog
(TransactionID Int Default Current_Transaction_ID(),
TransactionUser Varchar(100) Default System_User,
TransactionDate DateTime Default Current_TimeStamp)
Go

 

Note que nosso tabela TabelaTransactLog possui um simples estrutura composto por três colunas: TransactionID, TransactionUser e TransactionDate, sendo estes elementos básicos utilizados para catalogar o ID da transação em processamento, o usuário conectado ao banco de dados e data/hora do momento da inserção da linha.

Até aqui nada de tão importante, mas importante para podemos simular o uso do log, nosso próximo passo consiste no processamento do Bloco de Código 2, onde estaremos realizando o processamento de uma transação de inserção de 50.000 mil linhas de registros, sendo a operação de insert uma das mais diversas operações que demando processos de escrita ao transact – log, sendo assim, apresento a seguir o Bloco de Código 2:

— Bloco de Código 2 —
Insert Into TabelaTransactLog Default Values
Go 50000

Observe que não estamos passando nenhum tipo de valores para nossas colunas, na verdade estou forçando o SQL Server a utilizar a criação de valores padrões durante o processamento de cada linha de registro através da instrução Default Values (valores padrões), o que vai obrigar nossa instância e ter que gerar valores durante o tempo de processamento e posteriormente registrar em log.

Muito bem, o processamento desta massa de dados, teremos a necessidade de abrir uma nova query para justamente fazer uso do Bloco de Código 3, sendo este o principalmente elemento do nosso cenário, a query que nos permiti monitorar e identificar as transações que estão em tempo real utilizando o transact-log. Então abra uma nova query e log o Bloco de Código 3 apresentado a seguir:

— Bloco de Código 3 —
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Go

SELECT tst.session_id As ‘Sessão’,
es.original_login_name As ‘Login’,
DB_NAME(tdt.database_id) AS ‘Database’,
DATEDIFF(SECOND, tat.transaction_begin_time, GETDATE()) AS ‘Tempo decorrido’,
tdt.database_transaction_log_record_count AS ‘Espaço Utilizado’,
CASE tat.transaction_state
WHEN 0 THEN ‘A transação não foi completamente inicializada ainda…’
    WHEN 1 THEN ‘A transação foi inicializada, mas não começou…’
    WHEN 2 THEN ‘A transação esta ativa…’
    WHEN 3 THEN ‘A transação foi encerrada…’
    WHEN 4 THEN ‘Foi iniciado o processo de confirmação sobre o transação distribuída…’
    WHEN 5 THEN ‘A transação está em estado preparação e esperando resolução…’
    WHEN 6 THEN ‘A transação foi confirmada…’
    WHEN 7 THEN ‘A transação esta sendo revertida para o estado anterior…’
    WHEN 8 THEN ‘A transação foi revertida para o estado anterior…’
   ELSE ‘Estado da transação desconhecido’
   END AS ‘Estado da Transação’,
SUBSTRING(TXT.text, ( er.statement_start_offset / 2 ) + 1, ((CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), TXT.text)) * 2
ELSE er.statement_end_offset
END – er.statement_start_offset ) / 2 ) + 1) AS ‘Query Atual’,
TXT.text AS ‘Query relacionada’,
es.host_name As ‘Hostname’,
   CASE tat.transaction_type
    WHEN 1 THEN ‘Transação Read/Write’
    WHEN 2 THEN ‘Transação Read-Only’
    WHEN 3 THEN ‘Transação de Sistema’
                WHEN 4 THEN ‘Transação distribuída’
            ELSE ‘Tipo de Transação desconhecido’
            END AS ‘Tipo da Transação’,
tat.transaction_begin_time AS ‘Iniciado em’
FROM sys.dm_tran_session_transactions AS tst INNER JOIN sys.dm_tran_active_transactions AS tat
ON tst.transaction_id = tat.transaction_id
INNER JOIN sys.dm_tran_database_transactions AS tdt
ON tst.transaction_id = tdt.transaction_id
INNER JOIN sys.dm_exec_sessions es
ON tst.session_id = es.session_id
INNER JOIN sys.dm_exec_requests er
ON tst.session_id = er.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) TXT
ORDER BY [Tempo decorrido] DESC
Go
Pois bem, você pode estar querendo saber um pouco mais sobre o que exatamente esta acontecendo ao processar o Bloco de Código 3, pode ficar tranquilo que a seguir vou fazer um pequeno overview sobre este bloco de código, começando pelo conjunto de colunas retornadas por ele:
  • Sessão – Apresenta o ID da sessão que esta utilizando o transact-log;
  • Login – Nome da conta de login que esta conectada ao SQL Server;
  • Database – Nome do banco de dados utilizado pela query que esta acessando o transact-log;
  • Tempo decorrido – Representa o tempo de processamento que o query esta demandando para utilizar o transact-log;
  • Log Record Count– Identifica a quantidade de registros de log gerados pela query ou transações em processamento;
  • Estado da transação – Esta coluna apresenta o estado atual da query em processamento, sendo este formado por um conjunto de valores;
  • Query Atual – Apresenta as instruções que estão sendo processadas por cada query;
  • Query Relacionada– Apresenta as instruções processadas por querys que podem estar relacionadas com as atuais querys em processamento;
  • Hostname – Apresenta o nome dos hosts utilizados por cada query em processamento;
  • Tipo de Transação – Identifica se a transação em processamento esta realizando acesso de leitura ou escrita ao log, bem como, se esta query é uma query de sistema ou distribuída; e
  • Iniciado em – Apresenta o horário de início do acesso ao transact-log por parte da query em processamento.

Agora que você já conhece um pouco sobre cada coluna que será retornada após o processamento do Bloco de Código 3, vou abordar o uso da instrução Set Transaction Isolation Level declarada no início do código, que possui a finalidade de alterar no nível de isolamento do nosso banco de dados para Read Uncommitted afim de força o SQL Server a descartar ou desconsiderar qualquer possível ocorrência de bloqueios tanto no nível de tabela e registros, mas principalmente no escopo de banco de dados.

Nota: Se você me perguntar que isso é obrigatório ser feito para conseguir identificar as querys que estão utilizando o transact-log eu vou responder NÃO, isso não é obrigatório, mas pode ser considerada uma possível solução para garantir que o Bloco de Código 3 possa ser processado e consultar as dynamic management views na busca das informações catalogadas por ela em tempo de execução.

Ufa, estamos quase lá, agora nos resta fazer a execução do mesmo e observar o retorno dos dados coletados por este bloco de código, com base, na Figura 1 apresentada abaixo:

Figura 1 – Relação de querys em execução utilizando transact-log.

Perfeito, nosso monitoramento funcionou, conseguimos coletar os dados referentes as querys que estão neste momento em processamento fazendo acesso e utilizando o transact-log criado para o banco de dados UsingTransactLog.

Isso não é sensacional, desta forma, chegamos ao final de mais um post dedicado a sessão Dica do Mês, espero que você tenha gostado.

Referências

Post anteriores

  1. https://pedrogalvaojunior.wordpress.com/2017/03/01/dica-do-mes-microsoft-sql-server-2016-sp1-novo-argumento-use-hint-disponivel-para-query-hints/
  2. https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

  3. https://pedrogalvaojunior.wordpress.com/2016/11/28/dica-do-mes-sql-server-2016-sp1-comando-create-or-alter/

  4. https://pedrogalvaojunior.wordpress.com/2016/10/24/dica-do-mes-sql-server-2016-obtendo-informacoes-sobre-o-cache-de-execucao-de-funcoes/

Conclusão

Administrar, gerenciar, cuidar e prover um ambiente sempre no melhor estado possível não é uma das atividades mais tranquilas e simples desempenhadas por profissionais ou administradores de banco de dados, mas também não pode ser considerada um “bicho de sete cabeças” ou uma “caixa preta” ainda mais quando este servidor de banco de dados utiliza o Microsoft SQL Server.

Neste post você pode observar que através do uso de alguns DMVs – Dynamic Management Views a Microsoft nos permite coletar, analisar e entender de forma visual e muito intuitiva como o um servidor ou instância SQL Server gerencia o que esta sendo processado por nossas aplicações, querys ou transações no que se relaciona aos seus componentes e recursos internas, algo que nos permite em diversas situações de administração ou manutenção tentar identificar o que pode estar ocorrendo, bem como, impactando em nosso ambiente.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Um forte abraço, bom feriado, espero que neste momento você não esteja no seu trabalho tentando resolver problemas do seu servidor (kkkkk).

Valeu….

Dica do Mês – Microsoft SQL Server 2016 SP1 – Novo argumento USE HINT disponível para Query Hints


Boa tarde minha comunidade, salve, salve meu mestre SQL e porta bandeira Server……

É isso pessoal, quarta – feira de cinzas para muitos no brasil, mas graças a deus deste mal ou depressão eu não vou morrer, estou retornando em mais um post da sessão Dica do Mês, e se por acaso você não tem acessado o meu blog nos últimos meses não tem problema, uso um dos links abaixo para acessar as três últimos dicas do mês publicadas:

1.      https://pedrogalvaojunior.wordpress.com/2017/01/16/dica-do-mes-conhecendo-a-nova-dmf-sys-dm_exec_input_buffer-no-microsoft-sql-server-2016/

2.      https://pedrogalvaojunior.wordpress.com/2016/11/28/dica-do-mes-sql-server-2016-sp1-comando-create-or-alter/

3.      https://pedrogalvaojunior.wordpress.com/2016/10/24/dica-do-mes-sql-server-2016-obtendo-informacoes-sobre-o-cache-de-execucao-de-funcoes/

Hoje vou destacar mais uma melhoria adicionada recentemente ao Microsoft SQL Server 2016 a partir da instalação do Service Pack 1, sendo este sem dúvida nenhuma o maior conjunto de melhorias e inovações já adicionadas ao SQL Server desde suas primeiras versões.

Caso você ainda não fez download deste service pack, não perca tempo, acesse o link abaixo e realize o download o quanto antes: https://pedrogalvaojunior.wordpress.com/2016/11/19/microsoft-disponibiliza-service-pack-1-para-o-microsoft-sql-server-2016/

Dando continuidade, hoje vou apresentar o novo Query Hint adicionado após a instalação do SP1 chamado de USE HINT, quando encontrei informações sobre este novo recursos acabei ficando um pouco confuso e sem entender em qual momento ele poderia ser útil, mas após um busca mais detalhada pude ter a certeza que valia a pena compartilhar um pouco sobre esta novidade com vocês.

Se você ainda não ouvir falar sobre uso de query hints ou dicas de consulta existentes no SQL Server, isso assunto não é nada novo muito pelo contrário trata-se de uma das práticas mais comuns utilizadas por profissionais de bancos de dados e desenvolvedores com objetivo de otimizar a execução de uma ou mais transações.

Agora no SP1 do 2016 o time de engenheiros do SQL Server adicionou este novo argumento que nos permite dentre suas finalidades fazer uso de alguns dos mais específicos query hints sem exigir elevação no nível de permissão, declaração de trace flags ou até mesmo que o usuário que esteja fazendo uso deste hint pertença ao server role sysadmin.

Bom se você quiser saber um pouco mais sobre este novo argumento, continue lendo este post….


Introdução

Cenários no qual SQL Server deve ser sugerido mudança de comportamento para o query optimizer são bastante comuns de serem encontrados, tradicionalmente o uso de query hints apresentam um papel fundamental neste contexto independente de qual sinalizador você esteja utilizando, sendo este reconhecido como um documentado ou não-documentados.

Entretanto, quando estes sinalizadores de rastreamento são definidos globalmente, eles podem ter um efeito adverso sobre outras cargas de trabalho. Além disso, o uso por sessão não é prático com aplicativos, bem como, por consulta com QUERYTRACEON, sendo que esta opção requer associação na função de servidor fixa sysadmin. Embora você pode contornar esse comportamento usando um guia de plano ou um procedimento armazenado, credenciais elevadas são ainda necessárias.

Estes sinalizadores de rastreamento são conhecidos como trace flags,  usados para configurar temporariamente características de servidor específico ou para desligar um determinado comportamento, podem ser difíceis de gerenciar e compreender sua forma de uso ou impactos.

A partir do Microsoft SQL Server 2016 SP1 temos a capacidade de fazer uso do novo query hint argument USE HINT diretamente em nossas querys ou transações de forma simples, rápida e segura, contornando qualquer necessidade de elevação permissional ou mudança no contexto de execução.

Dentre os diversos query hints existentes no SQL Server, o USE HINT nos permite fazer uso dos seguintes:

Opção Sinalizador de rastreamento equivalente Descrição
FORCE_LEGACY_CARDINALITY_ESTIMATION TF 9481 Permite que você defina o modelo de estimativa de cardinalidade do otimizador de consulta para o SQL Server 2012 e versões anteriores, independentemente do nível de compatibilidade do banco de dados.
ENABLE_QUERY_OPTIMIZER_HOTFIXES TF 4199 Controles de consulta alterações otimizador lançadas em Service Packs e atualizações cumulativas do SQL Server.
DISABLE_PARAMETER_SNIFFING TF 4136 Desabilita o parâmetro de rastreamento, a menos que o valor OPTION(RECOMPILE), WITH RECOMPILE ou OPTIMIZE FOR seja usado.
ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES TF 4137 Faz com que o SQL Server gere um plano usando seletividade mínima ao estimar e predicados para filtros de correlação, em modelo de estimativa de cardinalidade de consulta otimizador do SQL Server 2012 e versões anteriores.
DISABLE_OPTIMIZER_ROWGOAL TF 4138 Faz com que o SQL Server gere um plano que não usa ajustes de meta de linha com consultas que contenham as palavras-chave TOP, OPTION (FAST N), IN ou EXISTS.
ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS TF 4139 Ativar automaticamente gerado estatísticas rápidas (emenda do histograma), independentemente do status da coluna de chave. Se essa opção for usada, independentemente do status de coluna estatísticas principais (em ordem crescente, decrescente ou papel de carta), o histograma usado para estimar a cardinalidade será ajustado em tempo de compilação de consulta.
ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS TF 9476 Faz com que o SQL Server gerar um plano usando a suposição de confinamento simples em vez da suposição de confinamento de Base padrão, no query optimizer cardinalidade estimativa modelo do SQL Server 2014 através de versões do SQL Server 2016.
DISABLE_OPTIMIZED_NESTED_LOOP TF 2340 Faz com que o SQL Server não use uma operação de classificação (classificação em lotes) para associações de loop aninhado otimizado ao gerar um plano.
FORCE_DEFAULT_CARDINALITY_ESTIMATION TF 2312 Permite que você definir o modelo de estimativa de cardinalidade de Otimizador de consulta para o SQL Server de 2014 por meio de versões do SQL Server 2016, dependentes do nível de compatibilidade do banco de dados.

Para consultar esta relação de hint options disponíveis para uso com USE HINT, a Microsoft adicionou um nova DMV denominada: sys.dm_exec_valid_use_hints, sendo esta melhor detalhada no próximo post da sessão Para que serve (não deixe de conferir). A seguir destaco algumas formas de uso do novo USE HINT em conjunto com alguns hints options.

Exemplos

Para ilustrar e ajudar a entender melhor os exemplos a seguir, utilize o banco de Dados AdventureWorksDWCTP3 disponível para download através do link: https://www.microsoft.com/en-us/download/details.aspx?id=49502

— Exemplo 1 – Utilizando o hint option FORCE_LEGACY_CARDINALITY_ESTIMATION —

usehint

— Exemplo 2 – Utilizando o hint option FORCE_LEGACY_CARDINALITY_ESTIMATION em conjunto com a QUERYTRACEON 9481 —

usehint2

Dica: Para que você possa entender e analisar melhor como o SQL Server 2016 se comportar a partir do momento que o argumento USE HINT é declarado, recomendo que você habilite a apresentação do recurso de Include Actual Execution Plan em seu Management Studio e execute estes exemplos sem a declaração do USE HINT e posteriormente com a declaração do mesmo, além disso, também realize a comparação dos planos de execução para notar e identificar as mudanças comportamentais.

Referências

·         https://msdn.microsoft.com/en-us/library/ms188396.aspx

·         https://support.microsoft.com/en-us/help/3189813/update-introduces-use-hint-query-hint-argument-in-sql-server-2016

·         https://msdn.microsoft.com/en-us/library/ms181714.aspx

·         https://msdn.microsoft.com/en-us/library/mt791356.aspx

·         https://blogs.msdn.microsoft.com/sql_server_team/developers-choice-use-hint-query-hints/

Conclusão

Como de costume a cada nova versão ou atualização a Microsft esta apresentando diversas inovações e melhorias no Microsoft SQL Server, mantendo o produto no seu mais alto nível de funcionalidades, recursos e inovações.

Neste post você pode perceber mais uma melhoria adicionada ao produto, onde através deste novo query hint USE HINT temos a capacidade de usar dicas de consulta orientando o query optimizer a mudar sua forma de execução e comportamento sem necessitarmos de elevação no nível de permissão ou fazermos partes de uma função de servidor sysadmin, algo que representa uma grande evolução por parte do produto no que se relacionado a controle de permissões e níveis de segurança.

Sinceramente eu vejo este novo recurso como um pequeno easter egg, pois na minha humilde opinião estava muito mas muito bem escondido.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Valeu….

Dica do Mês – Conhecendo a nova DMF sys.dm_exec_input_buffer no Microsoft SQL Server 2016


Bom dia, bom dia, bom dia….. Feliz 2017

Salve, salve comunidade, estou retorno hoje, conforme o prometido após alguns dias de “descanso mental”. Espero que todos tenham passado um ótimo fim de ano e estejam preparados para os desafios de 2017.

Estamos completando o primeiro ano da sessão Dica do Mês, sendo este o post de número 12, poxa vida muito legal ver o quanto de conteúdo e conhecimento já foi transmitido nesta sessão.

Hoje dia 16 de Janeiro primeiro post de 2017 dedicado mais uma vez ao Microsoft SQL Server, dentre os quais voltados exclusivamente a versão 2016, vou destacar um assunto bem conhecido de qualquer DBA denominado Input Buffer.

Não vou destacar do que se trata este conceito mais sim apresentar como a partir do SQL Server 2016 podemos recurperar e coletar as informações relacionado a ele de uma maneira diferente se comparado com as versões anteriores.

Então vamos lá, seja bem vindo ao Dica do Mês número 12……

Introdução

Reconhecer e identificar o que esta sendo transacionado dentro do seu servidor ou instância do Microsoft SQL Server para muitos é coisa de outro mundo, para outros coletar estes dados não passa de um simples comando que você pode executar.

Na verdade os lados da moeda tem a sua verdade, identificar e entender o que esta sendo transacionado não é uma tarefa fácil por isso pode ser considerado algo fora da terra, como também, coletar e armazenar é algo muito simples, e realmente é!!!

Desde as versões mais antigas do SQL Server a maneira mais comum e menos consumista de se obter informações sobre o Buffer ou Input Buffer dentro de um servidor ou instância era através do comando DBCC Input Buffer, onde bastava simplesmente executar este comando para se obter a informações sobre o buffer de uma sessão específica.

Agora na versão 2016 desta RC0 a Microsoft de um novo jeitinho para se obter estes dados através do uso da nova DMF – Dynamic Management Function ou Função de Gerenciamento Dinâmico chamada Sys.dm_exec_input_buffer.

Ao executar pela primeira vez esta DMF, pensei que seria um recurso substituto ao bem e velho DBCC Input Buffer, ao começar a brincar um pouco mais com ela  observei que existe uma pequena similaridade entre ambos.

Similaridade que se tornou mais clara na maneira que a sys.dm_exec_input_buffer apresentar os dados coletados que estão sendo transacionados, o que também acabou ficando somente nisso, durante as diversas execuções que realizei, foi possível  reconhecer algumas pequenas diferenças que podemos reconhecer como vantagens no uso da sys.dm_exec_input_buffer em comparação ao DBCC InputBuffer.

Sys.dm_exec_input_buffer x DBCC Input Buffer

Basicamente a forma de uso de ambos os recursos não posso dizer que seja algo muito diferente, o DBCC InputBuffer você executa de forma direta passando o SID da sessão a qual você deseja obter o buffer, já a sys.dm_exec_input_buffer o mínimo a favor é executar um comando Select direcionado para esta DMF.

Falando das vantagens destaco abaixo as mais fáceis de se identificar:

  1. Ao executar a dmf o resultado é apresentado diretamente como um conjunto de linhas, o que permite em uma bloco de código obter os input buffers de diversas sessões, uma grande vantagem se comparada com o DBCC Input Buffer;
  2. Outra diferença clara é a capacidade de realizar joins com outras DMFs dentre elas: sys.dm_exec_sessions, sys.dm_exec_connections e sys.dm_exec_requests através do uso do operador Cross Join;
  3. Através da execução de uma simples query através do comando select podemos recuperar o buffer de diversas entradas de sessões distintas sem a necessidade de criar um script, tabela temporária ou tabela auxiliar; e
  4. Possibilidade armazenar o resultado da relação de buffers coletados em uma nova tabela.

Exemplos

Como já mencionei anteriormente a forma de uso da sys.dm_exec_input_buffer é bem simples e fácil, como também, a apresentação dos dados coletados, os dois exemplos apresentados a seguir demonstram como podemos fazer uso desta nova DMF no Microsoft SQL Server 2016:

— 1 – Executando um simples Select —
SELECT * FROM sys.dm_exec_input_buffer(52, 0);

dica-12-01Figura 1 – Buffer coletado da sessão 52.

— 2 – Utilizando o operador Cross Apply —
SELECT es.session_id, ib.event_info
FROM sys.dm_exec_sessions AS es
CROSS APPLY sys.dm_exec_input_buffer(es.session_id, NULL) AS ib
WHERE es.session_id > 50;

dica-12-02Figura 2 – Buffers coletados após a execução do exemplo 2.

Referências

Conclusão

Como de costume a cada nova versão ou atualização a Microsft esta apresentando diversas inovações e melhorias no Microsoft SQL Server, mantendo o produto no seu mais alto nível de funcionalidades, recursos e inovações.

Neste post você pode perceber que mais uma vez isso esta presente, uma nova maneira de se obter informações sobre os buffers que estão sendo transacionados e processados dentro de um servidor ou instância do SQL Server através da DMF sys.dm_exec_input_buffers.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Conto com a sua presença em mais este ano aqui no meu blog….

Feliz 2017!!!

Dica do Mês – SQL Server 2016 SP1 – Comando Create Or Alter


Muito bom dia…..

Olá pessoal, mais uma semana começando. Estou retornando com mais um post dedicado a sessão Dica do Mês, este é o post de número 11 dedicado de forma exclusiva a esta sessão.

Como você já deve ter observado desde o mês de outubro todos os posts das sessões: Dicas do Mês, Para que Serve, Short Scripts e Material de Apoio estão apresentado o mesmo layout, sendo esta uma das sugestões que os meus seguidores solicitaram, e hoje não será diferente.

A dica de hoje é bastante simples e esta totalmente relacionada a uma novidade apresentanda com o lançamento do Service Pack 1 do Microsoft SQL Server 2016.

E ai você já esta se informando sobre este novo service pack? Já realizou o download? Posso lhe dizer que diversas melhorias foram implementas, bem como, muitas mas muitas mudanças identificadas pela equipe de desenvolvimento da Microsoft foram realizadas.

Se quiser saber mais sobre este novo conjunto de atualizações e melhorias dedicadas para o Microsoft SQL Server 2016, acesses os links abaixo:

 

Voltando ao post de hoje, vamos em frente, seja bem vindo a mais um Dica do Mês!!!


Introdução

A cada nova versão do Microsoft SQL Server que a Microsoft libera ou até mesmo após um service pack ou cummulative pack disponibilizados para download, sempre fazemos aquelas perguntas:

  • O que será que melhorou?
  • Quais correções foram definitivamente corrigidas?
  • Será que alguma solicitação do Connection foram atendidas?

Enfim sempre estamos querendo descobrir o que realmente o tipo de engenheiros e desenvolvedores voltados para o SQL Server estão pensando em melhorar e corrigir no produto.

Para esta versão não foi diferente como de costume desde a versão 2012 a Microsoft esta liberando em doses homeopáticas pequenas novidades voltadas para a linguagem Transact-SQL e isso se manteve no SP1 da versão 2016 com a introdução do comando Create or Alter na mesma instrução.

Isso mesmo você pode estar se perguntando ou até mesmo pensando, mas somente agora na versão 2016 este tipo de instrução foi adicionada? A resposta é sim, somente agora pois, algo que para muitos parecia ser simples se comparado com outras ferramentas de banco de dados como Oracle ou MySQL, mas como diz aquele velho ditado: “Nem tudo o que parece é” e isso também se justifica ao Microsoft SQL Server.

Problema

Você saberia me dizer quantas vezes teve que realizar a criação de um recurso programável em um servidor ou instância SQL Server? Algo do tipo Stored Procedure, Function, Trigger ou Views?

E realizar uma ou várias alterações na estrutura de codificação de algum tipo destes recursos? Com certeza é algo bastante comum de ser realizado principalmente quando estamos criando nossos ambientes.

Por muitos anos este tipo de atividade a ser realizada na linguagem Transact-SQL deveria ser feita de forma separada. Onde devemos basicamente seguir uma regra de criar um destes tipos de objetos através do comando Create e caso tenhamos a necessidade de realizar algum tipo de alteração utilizar o comando Alter.

Então, e se agora com o lançamento do service pack 1 para o SQL Server 2016 eu te dizer que isso já pode ser realizado de uma única vez, você acredita?

Eu posso te dizer que acredito pois é verdade…..

Solução

Dizer que demorou um pouco para a equipe de desenvolvimento entender o quanto esta melhoria seria útil e importante não é verdade, ao meu ver demorou bastante, eu posso dizer isso pois trabalho com SQL Server desde a versão 7, isso já se vão 18 anos.

Mas enfim dizer do passado muitas vezes não ajuda em nada, o que vale e deve ser destacado é que agora temos esta pequena mas importante melhoria implementada no Microsoft SQL Server 2016 SP1 que nos permite ao mesmo tempo que declaramos o comando Create adicionar na mesma linha o comando Alter, mas a princípio somente para os objetos programáveis dentre eles:

  • Function;
  • Stored Procedure;
  • Triggers; e
  • Views.

O Create or Alter voltado para tabelas ainda não esta presente neste momento, e sinceramente falando acredito que vai demorar mais um pouco para ser adicionado ao produto.

Legal, mesmo assim já tivemos um grande avanço com a liberação deste nova capacidade adicionada a linguagem Transact-SQL.

Exemplos

Pois bem, seguindo em frente e dando seta para direita afim de realizar uma ultrapassagem na atual versão do SQL Server 2016 mudando para nova versão SQL Server 2016 SP1 ou se preferir versão 13.0.4001 número que identifica e evolução do kernel para o SP1.

Acelarando um pouco mais, vamos trabalhar com alguns exemplos de código que ilustram como podemos fazer uso do comando Create or Alter, para tal torna-se necessário realizar o download do SP1, caso você ainda não tenha feito, utilize o link apresentado a seguir:

Dando continuidade vamos executar o primeiro bloco de código, denominado Bloco de Código 1 apresentado abaixo:

— Bloco de Código 1 —

create-alter-procedure-1
Podemos observar que a forma de uso desta instrução é bastante simples, não necessitando de algum tipo de alteração na sintaxe tanto do comando Create como também do Alter.

Observe que realizamos a criação de um simples Stored Procedure denonimado P_Teste e após a execução do create/alter realizar a apresentação do código fonte da mesma através do system stored procedure SP_HelpText.

Um detalhe importante que vale a pena ser destacado, se relacionado a apresentação do código fonte da nosso stored procedure, note que o SQL Server não adiciona a palava chave Alter na apresentação do código, você pode pensar, mas é claro nós acabamos de criar o objeto. Eu também pensei assim, mas isso não altera caso você realize qualquer tipo de alteração no código fonte, mesmo após realizar alguma mudança na codificação do seu objeto o Database Engine não reconhece a execução do comando Alter ele mais uma vez adiciona o comando ou palavra chave create antes do nome do objeto.

Isso me faz pensar que todas as vezes que utilizarmos este tipo de implementação o Database Engine vai na verdade realizar o processo de drop físico do objeto e posteriormente a criação deste objeto.

Vamos agora trabalhar da mesma forma para criação/alteração de uma function, para isso vamos utilizar o Bloco de código 2 apresentado a seguir:

— Bloco de Código 2 —

create-alter-function-1

Legal, legal, muito bem, simples, fácil, rápido e por quê não dizer que é prático! Ao mesmo tempo que criamos um objeto programável já podemos realizar a alteração. Isso não é algo que realmente estava faltando para o SQL Server?

Acredito que sim, e fazendo um comparativo era a mesma coisa que faltava quando se referiamos a existência de tipos de dados para armazenar somente data ou hora, algo que também foi implementada após um longo tempo a partir da versão 2008 do SQL Server, ou os comandos IIF ou Choice que já existiam na linguagem C#, mas não estavam presentes no Microsoft SQL Server até a versão 2012.

Bom vou deixar para que praticar o uso deste comando na criação de Triggers e Views, com certeza não terá problemas.

Referências

Conclusão

Como de costume a cada nova versão ou atualização a Microsft esta apresentando diversas inovações e melhorias no Microsoft SQL Server.

Desta forma, em um novo service pack lançado novas melhorias ou mudanças de comportamento do produto são esperadas, algo que a partir do service pack o SQL Server 2016 também vai apresentar.

O que a partir do momento que este conjunto de melhorias for instalada em seu ambiente, será possível dentre elas fazer uso da nova sintaxe combinado dos comandos Create e Alter utilizados de maneira simultânea formando o Create or Alter.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Até mais.

Dica do Mês – SQL Server 2016 – Obtendo informações sobre o cache de execução de funções


Pessoal, boa tarde.

Tudo bem? Estou retornando com mais um post dedicado a sessão Dica do Mês, este é o post de número 10 dedicado de forma exclusiva a esta sessão.

Recentemente alguns dos leitores do meu blog, fizeram alguns comentários e sugestões pedindo para que eu fosse um pouco mais objetivo no conteúdo e procurando organizar melhor a estrutura do post.

Quero dizer a todos que sugestões, críticas, comentários, enfim tudo é sempre muito bem vindo e estou ainda procurando estabelecer um padrão. No post de hoje, já começo a apresentar um pouco do layout e organização que pretenso manter para os próximos posts.

Então vamos lá, seja bem vindo a mais um Dica do Mês!!!


Introdução

Um das principais atividades de um DBA (Database Administrator) é cuidar e selar para vida de seus servidores e bancos de dados. Mas por diversas situações em alguns momentos algo pode fugir do controle ou simplesmente começar a funcionar de uma maneira diferente da qual estava sendo executado.

Isso também pode acontecer com o Microsoft SQL Server e seus recursos programavéis, dentre eles aqueles que os desenvolvedores tendem a criar para atender necessidades específicas em seus projetos.

Para tais recursos como: Stored Procedure, Triggers, Functions o Microsoft SQL Server apresenta uma infinidade de funcionalidades que permitem a cada versão ou até mesmo atualização serem implementados de maneiras e formas distintas, situação muito comum de se encontrar.

O post de hoje trata justamente uma situação muito corriqueira de se encontrar dentro de um ambiente de banco de dados, no qual estamos fazendo uso de user functions e precisamos de alguma maneira descobrir se esta função esta gerando algum tipo de mudança de comportamento durante ou após sua execução, estou me referindo as chamadas estatísticas de execução.

Problema

Em diversos momentos temos a necessidade obter informações sobre as estatísticas de processamento por parte de um determinado recurso que o Microsoft SQL Server possa estar processando ou tenha sido processado, até o Microsoft SQL Server 2000 essa uma tarefa muito árdua ou praticamente impossível de ser realizada de maneira rápida, cenário que começou a mudar um pouco de comportamento a partir do Microsoft SQL Server 2005.

Solução

A partir da versão 2005 a Microsoft introduziu o conceito de visões de sistemas que permitiam coletar dados estatísticos de execução de querys, posteriormente melhorado no Microsoft SQL Server 2008 a partir da adição do recurso de DMV – Dynamic Management Views, onde tinhamos a possibilidade de rastrear e encontrar informações sobre Stored Procedures e Triggers, através das DMVs:

Onde respectivamente estas DMVs, permitiam obter informações sobre os dados de execução de Stored Procedures e Triggers executadas.

Agora na versão 2016 a Microsoft introduziu no novo SQL Server uma nova DMV, chamada sys.dm_exec_function_stats, que nos permite obter informações sobre os dados estatísticos de processamento e execucação de uma user function.

Vamos então colocar a “mão na massa” ou melhor nos teclados e executar o exemplo apresentando abaixo para que possamos entender como esta nova dmv poderá nos ajudar.

Exemplo

Para que possamos realizar este cenário de exemplo vou utilizar o Microsoft SQL Server 2016 Express em conjunto com o banco de dados de exemplo Microsoft SQL Server 2016 Adventure Works disponibilizados nos seguinte link: https://www.microsoft.com/download/details.aspx?id=49502

Dando continuidade vamos executar o primeiro bloco de código, denominado Bloco de Código 1.

— Bloco de Código 1 —

bloco-de-codigo-1

 

Não se precoupe com a lógica aplicada ao código, o importante é que você observe a existência de três user functions existentes dentro do banco de dados AdventureWorks2016, sendo elas:

  • dbo.ufnGetStock;
  • dbo.ufnGetProductDealerPrice; e
  • dbo.ufnGetProductStandardCost.

Agora que nosso bloco de código 1 foi executado o Microsoft SQL Server através do Database Engine em conjunto com Execution Plan, deve ter criado para nosso select e principalmente as functions utilizadas dentro um cache de execução contendo as informações sobre o processamento realizado e o quanto custou para executar cada function envolvida neste código.

E justamente neste momento que poderemos fazer uso da nova DMV sys.dm_exec_function_stats para obter os principais indicadores estatísticos coletados através do cache criado pelo SQL Server com base no bloco de código 1. Vamos então executar o Bloco de Código 2 apresentado abaixo.

— Bloco de Código 2 —

bloco-de-codigo-2

Observe que além da sys.dm_exec_function_stats estamos utilizando em conjunto a sys.dm_exec_sql_text que nos permite obter mais detalhes sobre o código do objeto programado executado, neste caso as functions apresentadas anteriormente.

A Figura 1 apresentada abaixo ilustra a relação de dados estatísticos armazenando no cache de execução do database engine e coletados através da  sys.dm_exec_function_stats:

figura1-sys-dm_exec_function_statsFigura 1 – Dados estatísticos de processamento das functions utilizadas no bloco de código 1.

Referências

Conclusão

Como de costume a cada nova versão ou atualização a Microsft esta apresentando diversas inovações e melhorias no Microsoft SQL Server.

Isso não foi diferente na versão 2016 que agora através da nova DMV sys.dm_exec_function_stats nos permite obter informações estatísticas de processamento de nossas functions armazenadas em cache.

Desta forma, temos a possibilidade de analisar estes dados e permitir ter uma melhor análise de processamento por parte das aplicações que necessitam fazer uso de functions, com certeza este recurso será muito importante e de extrema utilidade para qualquer profissional da área de banco de dados e desenvolvimento.

Agradecimentos

Mais uma vez obrigado por sua visita, agradeço sua atenção, fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Até mais.

Dica do Mês – Restrições de Integridade para Banco de Dados


Fala galera, bom dia.

Tudo bem?

Estou um pouco ausente neste mês devido as correrias da minha vida profissional e acadêmica, mas sempre que possível compartilhando com vocês um pouco do meu conhecimento e experiência.

O post de hoje poderia ser diferente dos outros, na sessão Dica do Mês vou apresentar um pouco mais sobre os conceitos básicos de banco de dados voltados para área de modelagem, estou me referindo as chamadas Restrições de Integridade (RI), algo bastante útil e importante quando estamos realizando as definições da estrutura lógica para armazenamento de dados em qualquer banco de dados. Então vamos começar a falar um pouco mais sobre este conceito também criado na década dos anos 70 por Edgar Frank Codd.


Falando um pouco sobre integridade…

A integridade de dados é uma das características essenciais da segurança da informação, e garante que as informações não sofreram alterações que não foram autorizadas ou que são impróprias. Utilizada para assegurar que um documento não é alterado depois de ter sido assinado.

Quando vamos projetar um banco de dados, imaginamos as possíveis formas para que nossa aplicação grave os dados corretamente no banco de dados, mas as vezes, esquecemos de definir, a nível de banco, quais as validações que devem ser feitas para evitar inconsistências nos dados e que, futuramente, se tornariam dores de cabeça.

No contexto de bancos de dados relacional é comum falar de integridade referencial, que tem como objetivo conservar as relações existentes entre tabelas quando algumas linhas são inseridas ou eliminadas.

Restrições de Integridade….

As chamadas RIs possuem o objetivo de garantir a exatidão e a consistência dos dados em uma Banco de dados relacional. Ou seja, garantir que dados representem assertivamente a realidade modelada. A integridade dos dados é tratada nas bases de dados através do conceito de integridade relacional e é garantida pelo próprio SGDB.

Existem vários tipos de restrições de integridade. Codd, inicialmente definiu 2 tipos de restrições, mas na sua segunda versão do modelo relacional ele definiu 5 tipos de restrições de integridade.

Mas antes de conhecer este tipos, vamos entender um pouco o conceito de domínio dos atributos: O domínio indica os possíveis valores de um atributo. A integridade de domínio verifica se os dados são do tipo permitido (alfanumerico, numerico,etc), tamanho do campo, se ele pode ser nulo ou não. Por exemplo, é possível definir que um atributo “idade” de um funcionário é sempre um valor inteiro positivo.

Os cinco tipos de restrições…

Restrição de Chave: Impede que uma chave primária se repita. Um campo chave primária diferencia de forma única os registros (linhas) de uma relação (tabela).

Restrição de Domínio: Impede que uma chave primária receba como valor NULL (nulo).

Integridade de vazio: Verifica se um campo pode ou não receber valor NULL. Sub-item da integridade de domínio.

Integridade referencial: Uma chave estrangeira de uma relação tem que coincidir com uma chave primária da sua tabela “pai” a que a chave estrangeira se refere. Ou seja, não só deve existir o atributo (campo), como também, o valor referenciado.

Integridade definida pelo usuário: Permite definir regras comerciais que não se encaixam em outras categorias de integridade.

Elementos que formam as Restrições de Integridade…

Integridade Semântica: Garante que o dado inserido em uma linha da tabela seja um valor válido. Para esse valor ser válido deve ser do mesmo tipo de dados definido na especificação da coluna na tabela.

Imagine o atributo de uma determinada entidade definido como DATA, por padrão este atributo deverá conter somente dados relativos a DATA. É justamente esta definição que nos permite ter a certeza que no campo DATA_CONTRATACAO só terá datas válidas.

Caso um SGDB permita a inserção de um outro tipo de dado diferente do definido, a integridade semântica será violada. A integridade semântica em um SGDB é aplicada com a utilização de constraints.

Constraints: Pode ser definido resumidamente como uma regra que limita o valor que pode ser inserido, modificado ou eliminado em uma tabela. Na linguagem SQL temos os seguintes tipos de constraints:

  • Constraint de dados;
  • Constraint NOT NULL (não nulo);
  • Constraint única; e
  • Constraint de validação (check constraint).

Constraints de Dados: Esse tipo de constraint pode ser considerado o mais simples e por muitas vezes ignorado como um constraint. Ele é o que delimita o tipo de dado de cada coluna em uma tabela.

Os tipos de informações disponíveis na maioria dos SGDBs existentes pode ser dividia em:

  • Numérico;
  • Alfanumérico ou caracteres;
  • Data e tempo; e
  • Grandes objetos.

Constraints Not Null: O conceito de nulo é utilizado quando uma determinada coluna ou atributo de uma linha na tabela não possui valor ou este valor é desconhecido. Por outro lado, existem colunas / atributo que obrigatoriamente precisam de valor informado.

Por exemplo, em uma tabela chamada FUNCIONARIO, onde estão dados de funcionários, o atributo NUMERO_FUNCIONARIO é obrigatório. Nesse caso é possível utilizar a constraint NOT NULL para garantir que haverá informação nessa coluna.

Importante frisar que NULO é diferente de brancos e zeros. Temos que lembrar também que tanto branco quanto zero são valores válidos e que são levados em conta em funções de coluna, tais como média, somatório, máximo, mínimo. Sendo que o NULO é desconsiderado nessas funções.

Constraints Única (Unique): Reconhecida e tratata como uma regra única que garante e não permite a existência de valores duplicados da mesma coluna ou em um conjunto de colunas na mesma tabela.

Usando o mesmo exemplo da tabela FUNCIONARIO, podemos utilizar uma constraint única na coluna NUMERO_FUNCIONARIO para garantir que dois ou mais funcionários possuam o mesmo número de identificação.

Podemos considerar que a chave primaria (primary key), que será explicada mais adiante, é um tipo de constraint única. Lembrando que uma tabela pode ter apenas uma chave primária, porém diversas constraint únicas.

Constraints de Validação (Check): Esta constraint determina um conjunto de valores permitidos para uma determinada coluna na tabela. Através deste tipo de constraints podemos definir de forma explícita através da linguagem DDL (Data Definition Language) de uma tabela com expressões Booleanas similares a clausula WHERE da linguagem Transact-SQL.

Uma constraint de validação é forçada em qualquer inserção ou atualização da coluna. Caso a inserção ou atualização da coluna não esteja de acordo com a definição da constraint, a mesma não será executada.

Por exemplo, vamos supor que a tabela FUNCIONARIO possua uma coluna SALARIO e que o valor do salário de cada funcionário não possa ser maior que 50.000,00, é possível criar uma constraint para erra regra:

CREATE TABLE FUNCIONARIO

(NUMERO_FUNCIONARIO SMALLINT NOT NULL,

SALARIO DECIMAL (9,2) NOT NULL CHECK SALARIO >= 50.000);

Observações: Uma constraint de validação pode ser muito útil para garantir regras de negócio, pois ela não pode ser sobreposta. Uma vez definida é dada a garantia que a regra será respeitada.

Utilizar esse tipo de integridade torna as suas aplicações mais robustas, consistentes e simples, pois não é necessário controlar as regras dentro do próprio código de programação ou utilizando uma subrotina. Dessa maneira é isolada em apenas um lugar a regra de negócio; e

Havendo a necessidade de mudar alguma regra de negócio, basta apenas alterar a constraint de validação na tabela ao invés de sair alterando códigos e mais códigos de programação uma vez que a mesma regra pode estar replicada em diversos pontos da sua aplicação.

Realizando uma prática…

Após conhecermos um pouco sobre o conceito e elementos que formam as restrições de integridade, vamos então colocar “a mão na massa” ou melhor como eu sempre digo no teclado e construir um simples exemplo de como podemos fazer uso de forma mais coerente e organizada do uso da restrição de integridade em nossas tabelas. Para tal utilizaremos o Bloco de Código 1 apresentado abaixo:

— Bloco de Código 1 — Aplicando o conceito de restrições de integridade —

— Criando o Banco de Dados —
Create Database RI
Go
— Acessando o Banco de Dados —
Use RI
Go
— Criando a Tabela Funcionarios utilizando Constrainst – Not Null, Null, Check, Default e Unique —
Create Table Funcionarios
(Codigo Int Primary Key Identity(1,1),
Nome Varchar(80) Not Null,
Sexo Char(1) Check (Sexo = ‘F’ or Sexo = ‘M’),
RG Int Not Null Unique NonClustered,
CPF Int Not Null Unique NonClustered,
DataNascimento Date Check (DataNascimento >= ‘1950-01-01’),
DataCadastro DateTime Default GetDate(),
Email Varchar(100) Null)
Go
— Criando a Tabela Clientes utilizando Constrainst – Not Null, Null, Check, Default e Unique —
Create Table Clientes
(Codigo Int Identity(1,1),
Nome Varchar(80) Not Null,
Sexo Char(1),
RG Int Not Null,
CPF Int Not Null,
DataNascimento Date,
 DataCadastro DateTime Constraint DF_Clientes_DataCadastro Default GetDate(),
Email Varchar(100) Null
  Constraint PK_Clientes_Codigo Primary Key (Codigo),
  Constraint CK_Clientes_Sexo Check (Sexo = ‘F’ or Sexo = ‘M’),
  Constraint UQ_Clientes_RG Unique NonClustered (RG),
  Constraint UQ_Clientes_CPF Unique NonClustered (CPF),
  Constraint CK_Clientes_DataNascimento Check (DataNascimento >= ‘1950-01-01’))
Go
— Adicionando uma nova Constraint —
Alter Table Clientes
Add Constraint DF_Clientes_Sexo Default ‘M’ for Sexo
Go
— Removendo uma Constraint já existente —
Alter Table Clientes
Drop Constraint CK_Clientes_DataNascimento
Go
— Adicionando uma nova Constraint do tipo Check —
Alter Table Clientes
Add Constraint CK_Clientes_DataNascimento
Check(DataNascimento >=’1900-01-01′)
Go

Perfeito, após executarmos este bloco de código temos nosso ambiente totalmente criado seguindo as definições de restrições de integridade que aplicamos no script.

Você pode estar se perguntando mas o que existe de diferença entre criar uma tabela sem definir o nome dados constraints em comparação com uma tabela que possui o nome das constraints definidas. A resposta para esta sua dúvida será respondida através da Figura 1 apresentada abaixo:

constraints
Figura 1 – Restrições de integridade criadas em cada tabela.

Analisando a Figura 1 podemos notar claramente a diferença, quando definimos um nome para nossas constrainst o Microsoft SQL Server atribui exatamente o nome de definimos no momento da crição da tabela, com isso, teremos mais facilidade para realizar uma manutenção nestes objetos, bem como, toda documentação e apresentação da estrutura do nosso banco de dados será mais limpa e organizada.


 

Sendo assim chegamos ao final de mais uma dica do mês.

O conhecimento técnico é muito importante para qualquer profissional, mas não podemos deixar de lado o conhecimento acadêmico adquirido ao longo dos anos dentro das instituições de ensino.

Este é um ponto fundamental, valorizar e conhecer a diferença entre um bom profissional e o profissional reconhecido e respeitado no mercado de trabalho, está justamente ligado na capacidade do mesmo em saber aliar o conhecimento teórico com o conhecimento prático, como muitos costumam dizer aliar a téoria a prática, sendo este o objetivo deste post.

Espero que você tenha gostado, que as informações e exemplos publicadas possam de alguma maneira ajudar e colaborar com suas atividades diárias, profissionais e ou acadêmicas.

Desejo um forte abraço, agradeço mais uma vez a sua visita.

Até mais.