Material de Apoio – Agosto 2017


Boa tarde pessoal!

Salve, salve amantes de banco de dados, Tudo bem?

Este é mais um post da sessão Material de Apoio, sendo o terceiro no decorrer de 2017 e de número 153 no total desta sessão.

Já passamos da metade de 2017, que loucura isso, como a rotina do dia a dia não nos deixa perceber o quanto o tempo na para de correr. Falando justamente da correria da nossas vida, a relação de arquivos compartilhadas neste post poderá justamente lhe ajudar a economizar muito do seu tempo.

O post de hoje

Para aqueles que já acompanham o meu blog a um certo tempo, os posts dedicados a sessão Material de Apoio, possuem o objetivo de compartilhar o conhecimento de recursos, funcionalidades e procedimentos que podemos realizar no Microsoft SQL Server.

Hoje não será diferente, estou trazendo alguns dos mais recentes scripts  catalogados nos últimos meses, que atualmente estão compondo a minha galeria de códigos formada ao longo dos anos de trabalho como DBA, e hoje principalmente como Professor de Banco de Dados.

Neste post você vai encontrar arquivos relacionados com os seguintes temas:

  • Cláusula Values;
  • Comando Distinct;
  • Comando Select;
  • Comando Top;
  • CTE Recursiva para geração de sequência de datas;
  • CTE Recursiva para geração de sequência numérica de CEPs;
  • Extended Events;
  • Função Format;
  • Função PARSE;
  • Funções de Ranking – Row_Number;
  • Monitoramento de senhas;
  • Operador Cross Apply;
  • Operador Outer Appy;
  • Recursos bloqueados;
  • SPDIDs de Conexões;
  • SPIDs de usuários; e
  • User Defined Function para cálculo de anos em colunas computadas.

Tenho a certeza que este conteúdo poderá lhe ajudar muito em seus atividades profissionais e acadêmicas, fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos.

Material de Apoio

A seguir apresento a relação de scripts selecionados:

 

 

 

 

 

 

 

 

 

Por questões de compatibilidade com a plataforma WordPress.com, todos os arquivos estão renomeados com a extensão .doc ao final do seu respectivo nome, sendo assim, após o download torna-se necessário remover esta extensão, mantendo somente a extensão padrão .sql.

Nota: Todos os arquivos disponibilizados foram obtidos ou criados com autorização de seus autores, sendo estes, passíveis de direitos autorais.

Links

Caso você queira acessar os posts anteriores da sessão, não perca tempo utilize os links listados abaixo:

Agradecimento

Quero agradecer imensamente a sua visita, espero lhe encontrar muitas vezes no decorrer deste ano em meu blog.

Não deixe de acessar os outros posts das demais sessões, e o próximo post desta sessão será publicado no mês de Novembro, até lá continue curtindo sua vida e compartilhando suas experiência.

Um forte abraço.

Primeiro release candidate do SQL Server 2017 agora disponível


A Microsoft anunciou hoje a disponibilidade no TechNet Evaluation Center do SQL Server 2017 RC1 (Release Candidate 1), a versão de testes pública mais recente do novo SQL Server.

Em nossos sete CTPs community technology previews () até à data, o SQL Server 2017 emitiu:

  • Linux suporte para cargas de trabalho de nível 1, missão crítica Suporte a SQL Server 2017 para Linux inclui as mesmas soluções de alta disponibilidade em Linux como Windows Server, incluindo sempre em grupos disponibilidade integrados com soluções de clustering Linux nativas como marca-passo.
  • Gráfico de processamento de dados no SQL Server Com os recursos de dados de gráfico disponíveis no SQL Server 2017 e banco de dados SQL Azure, clientes podem criar nós e bordas e descobrir as relações complexas e muitos-para-muitos.
  • Processamento de consultas adaptável Processamento de consultas adaptativo é uma família de recursos do SQL Server 2017 que mantém automaticamente consultas de banco de dados executando de forma mais eficiente possível sem a necessidade de ajuste adicional de administradores de banco de dados. Além da capacidade de ajustar concessões de memória de modo de lote, o conjunto de recursos inclui junções adaptável de modo lote e intercalados com capacidades de execução.
  • Integração do Python para análises avançadas Serviços de aprendizagem de máquina Microsoft traz-lhe agora a capacidade de executar a análise de no banco de dados usando Python ou R de forma escalável e em paralelo. A capacidade de executar análises avançadas em seu armazenamento operacional sem ETL significa mais rápido tempo de ideias para os clientes enquanto implantação fácil e extensibilidade rica tornam rápido se levantar e correr sobre o modelo certo.

Principais aprimoramentos no Release Candidate 1

No SQL Server RC1 de 2017, houve vários aprimoramentos de recurso da Nota:

  • SQL Server no Active Directory Linux integração – Com RC1, o SQL Server no Linux suporta autenticação de diretório ativo, que permite que clientes associados ao domínio em Windows ou Linux para autenticar ao SQL Server usando suas credenciais de domínio e o protocolo Kerberos. Confira o Guia de introdução instruções.
  • Transport Layer Security (TLS) para criptografar dados – SQL Server no Linux pode usar TLS para criptografar os dados transmitidos através de uma rede entre um aplicativo cliente e uma instância do SQL Server. SQL Server no Linux suporta os seguintes protocolos TLS: TLS 1.0, 1.1 e 1.2. Confira o Guia de introdução instruções.
  • Aprimoramentos de serviços de aprendizagem de máquina – No RC1, nós adicionamos mais recursos de modelo de gerenciamento para R Services no Windows Server, incluindo gestão de biblioteca externa. A nova versão também oferece suporte nativo marcando.
  • SQL Server Analysis Services (SSAS) Além das melhorias para SSAS de CTPs anterior do SQL Server 2017, RC1 adiciona adicionais exibições de gerenciamento dinâmico, permitindo a análise de dependência e emissão de relatórios. Consulte o blog do Analysis Services para obter mais informações.
  • SQL Server Integration Services (SSIS) no Linux O preview do SQL Server Integration Services no Linux agora adiciona suporte para qualquer driver de ODBC Unicode, se ele segue as especificações do ODBC. (Driver ODBC ANSI não é suportado).
  • SQL Server Integration Services (SSIS) no Windows Server RC1 adiciona suporte para dimensionamento do SSIS em ambientes de alta disponibilidade. Os clientes agora podem habilitar sempre para SSIS, configurando o Windows Server failover clustering para o dimensionamento do mestre.

SQL Server 2017 para desempenho mais rápido

SQL Server 2017 tem vários novos parâmetros, demonstrando um desempenho mais rápido do que os bancos de dados do competidor e contra as versões mais antigas do SQL Server:

Dinamize sua DevOps usando SQL Server 2017

Em SQL Server 2017, introduzimos o suporte para SQL Server em contêineres baseados em Linux, um benefício para os clientes usando recipientes em desenvolvimento ou produção. Estamos também trabalhando para ajudar os desenvolvedores a começar a desenvolver um app para o SQL Server mais rápido possível com instruções de instalação, trechos de código e outras informações úteis.

No nosso novo microsite DevOps usando o SQL Server, que lançou hoje, desenvolvedores e gerentes de desenvolvimento podem aprender como integrar o SQL Server em suas tarefas de DevOps. Encontre demos, documentação e blogs, bem como vídeos e apresentações da conferência. Ou então, junte-se a conversa de DevOps em nossos canais de Gitter.

Começar com o SQL Server RC1 de 2017 hoje!

Tente o release candidate do SQL Server 2017 hoje! Começar com nossos tutoriais de desenvolvedor atualizadas que mostram como instalar e usar o SQL Server 2017 no macOS, Docker, Windows e Linux e rapidamente construir um aplicativo em uma linguagem de programação de sua escolha. Para mais formas de começar, tente o seguinte:

Fontes e Direitos Autorais: July 17, 2017.

Lançado: Preview público para SQL Server 2017 e pacote de gerenciamento de replicação (CTP4)


Microsoft anuncia o SQL Server 2017 Management Pack CTP4 bits com novas funcionalidades na versão (6.7.60.0).

Faça o download do preview público em:

Pacote de gerenciamento do Microsoft System Center (comunidade técnica Preview 4) para o SQL Server 2017

Pacote de gerenciamento do Microsoft System Center (Community Technology Preview 4) para replicação do SQL Server 2017

Novos recursos e correções para o SQL Server 2017 Windows e Linux MP (6.7.60.0)

  • Implementado sempre na monitorização em Windows e Linux
  • Fluxos de trabalho implementados de latência de disco
  • Adicionado nova “Falha no logon” alerta regra para evento SQL Server #18456
  • Adicionado suporte para as credenciais do AD no modo sem agente no Windows
  • Adicionado relatório de previsão de espaço DB
  • Corrigido problema: informação de localização de arquivo diferente do “sys. master_files” e “sysfiles” causa erro quando arquivos do grupo de disponibilidade de banco de dados secundário estão em caminho diferente
  • Corrigido problema: fluxos de trabalho não podem se conectar a uma instância quando apenas o protocolo de memória compartilhada está habilitado
  • Introduziu uma série de melhorias para o pacote de gerenciamento

Novos recursos e correções para janelas de replicação SQL Server 2017 MP (6.7.60.0)

  • Adicionado um número de monitores e regras de desempenho para criar o mesmo modelo de saúde, tal como apresentado no MPs de replicação do SQL Server 2008-2016
  • Melhorada e refatorado gestão pacote de módulos
  • Corrigido uma série de questões
Recurso/Workflow CTP1 (6.7.18.0) CTP2 (6.7.40.0) CTP3 (6.7.55.0) CTP4 (6.7.60.0)
Objetos de núcleo
Descoberta do mecanismo de DB W/L W/L W/L W/L
Descoberta de motor local DB (característica única do windows) Em Em Em
Instâncias de Cluster do SQL Em Em Em Em
A descoberta de DB W/L W/L W/L W/L
Descoberta de grupo de arquivos DB W/L W/L W/L W/L
Detecção de arquivos DB W/L W/L W/L W/L
Arquivo de Log DB descoberta W/L W/L W/L W/L
Descoberta de política DB W/L W/L W/L
Descoberta de grupo de arquivos FILESTREAM Em Em Em
Descoberta de grupo de arquivos de dados otimizados para memória W/L W/L W/L
Dados de memória otimizado de arquivos recipientes descoberta W/L W/L W/L
Pool de recursos de motor DB descoberta W/L W/L
Descoberta do agente SQL W/L W/L
Sempre na descoberta Em W/L
Características
Monitoramento sem agente W/L W/L W/L W/L
Misto de acompanhamento (característica única do windows) Em Em Em Em
Agente de monitoramento (característica única do windows) Em Em Em Em
Credenciais do SQL W/L W/L W/L W/L
Credenciais do AD Em Em Em Em
Suporte OLTP na memória W/L W/L W/L
Suporte a SQL Server Express Em Em Em Em
Módulo de MSSQL LogReader W/L W/L W/L
Suporta nomes longos (característica única do windows) Em Em Em Em
Assistente de multithreading W/L W/L W/L
Execução de tarefas W/L W/L
Suporte de estivador L L
Cenários de monitoramento
Estado de saúde de motor DB monitoramento W/L W/L W/L W/L
Monitoramento de desempenho de motor do DB W/L W/L W/L W/L
Serviço de mecanismo de DB pacote de monitoramento de conformidade W/L W/L W/L
Monitoramento de configuração DB W/L W/L W/L W/L
Desempenho de grupo de arquivos otimizado para memória de dados DB monitoramento W W W
Métricas de monitoramento da CPU e desempenho W W W
Envio de monitoramento de logs W W W
Monitoramento da integridade do WMI W/NA W/NA W/NA
Base do evento monitoramento (400 + regras de alerta) W/L W/L W/L
Coleta de métricas DB espaço monitoramento e desempenho W W W W
Coleta de métricas DB espaço monitoramento e desempenho para objetos Filestream W W W
Coleta de métricas DB espaço monitoramento e desempenho para objetos ἑκατόν W W W
Serviço iniciador Daemon de filtro de texto completo SQL monitoramento W W W
Monitoramento do SQL Agent W/L W/L
Sempre a monitorização W W/L

Gend Le

Concluído

W – suportado em Windows

L – suportado no Linux

W/L – com suporte em Windows e Linux

W/NA – com suporte no Windows, mas não aplicável para Linux

Todos os detalhes sobre a nova funcionalidade podem ser encontrados no guia de operações do que pode ser baixado junto com o pacote de gerenciamento. Funcionalidade completa estará disponível com o SQL Server 2017 GA. Esta versão CTP cobre apenas um subconjunto de regras e monitores. Vamos trabalhar no sentido de funcionalidade completa como nós liberamos novos CTPs.

Fontes e Direitos Autorais: SQL Server Release Services – SourabhAgarwal.
https://blogs.msdn.microsoft.com/sqlreleaseservices/released-public-preview-for-sql-server-2017-and-replication-management-pack-ctp4/

Microsoft anuncia o SQL Server 2012 Service Pack 4


A Microsoft anunciou no blog SQL Server Release Services que está planejando lançar o SQL Server 2012 Service Pack 4 ainda este ano.

O Service Pack 3 (ou SP3) foi lançado em novembro de 2015 e trouxe novidades como:

• Aprimoramentos de escalabilidade e desempenho para o mecanismo de armazenamento.

• Melhorias no desempenho da verificação de consistência.

• Dicas de consulta para fornecer controle granular enquanto estiver usando o Resource Governor.

• Adicionais recursos por meio de melhorias na DMV, Extended Events e planos de consulta de monitoramento.

• Melhorias no desempenho ao abrir e ler arquivos XEL usando leitor XEvent Linq.

Microsoft anuncia o SQL Server 2012 Service Pack 4

SQL Server 2012 Service Pack 4

De acordo com a Microsoft, o SQL Server 2012 Service Pack 4 será o último Service Pack para a versão 2012 do SQL Server e além de ser uma coleção de hotfixes, ele também trará mais de 20 melhorias com foco na performance, escalabilidade e diagnóstico com base no feedback de clientes e da comunidade.

Mais especificamente, o Service Pack 4 incluirá:

– Todas as correções e atualizações cumulativas para o SQL Server 2012.

– Melhorias na performance e escalabilidade.

– Recursos de monitoramento adicionais através de melhorias no DMV, Extended Events e Query Plans.

– Melhorias com base no feedback da comunidade.

– Melhorias introduzidas originalmente pelo SQL Server 2014 SP2 e SQL Server 2016 SP1.

A Microsoft confirmou que o SQL Server 2012 Service Pack 4 será lançado em setembro deste ano e um anúncio com mais informações sobre a atualização será publicado na época do lançamento.

Fontes e Direitos Autorais: SQL Server Release Services – SourabhAgarwal https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-sql-server-2012-service-pack-4/

#15 – Para que serve


Bom dia, bom dia, bom dia!

Oi gente, tudo bem? Você que esta acessando mais um post do meu blog, pode estar se perguntando. Cara como pode um pessoa ás 6:30hrs de uma quarta – feira esta acordado escrevendo mais um post.

A resposta será bem simples, isso se chama profissionalismo e respeito aos seus compromissos, e escrever algo para o meu blog é mais que um compromisso é um grande prazer, por isso estou aqui ás 6:32hrs da manhã terminando este parágrafo (kkkkk).

Dando continuidade, este é o novo post da sessão Para que serve, sendo o post de número 15, muito bom, lentamente esta sessão esta ganhando corpo e força com os meus seguidores.

Nos últimos dias pesquisei novidades, recursos, comandos, enfim algo que poderia trazer para vocês hoje e sinceramente falando tive bastante dificuldade para encontrar algum conteúdo que fosse ao mesmo tempo interessante porém simples, e por incrível que pareça acabei me lembrando de algo lançado já faz um tempinho na versão 2014 do Microsoft SQL Server.

Poxa vida, versão 2014 do SQL Server sendo que já estamos na versão 2017 prestes a ser lançada, então não sempre algo que foi lançado a algum tempo pode ser considerado novo muito menos totalmente conhecido, sempre temos alguma coisa nova para conhecer, aprender e descobrir com produtos e suas versões mais antigas e foi justamente pensando nisso que estou trabalhando no conteúdo para este post.

Seguindo como a costumeira apresentação, vou destacar neste post um dos recursos mais importantes adicionados ao SQL Server a partir da versão 2014 conhecido como Native Backup Encryption ou Backup Nativo Encriptado, talvez você nunca tenha ouvido falar sobre ele ou não tenha até o presente momento a necessidade de usar, mas tenha a certeza um é um recurso de fácil utilização.

Então chegou a hora de conhecer um pouco mais sobre esta funcionalidade, sua forma de uso, características, importância, limitações, entre outros.

Desta forma, seja bem vindo ao #15 – Para que serve – Native Backup Encryption.

Introdução

Quando pensamos nas possibilidades de perda de dados ou informações, normalmente um dos recursos mais conhecidos e utilizados por todos é o bom e velho backup, capacidade que ao longo dos anos também evoluiu muito e hoje pode ser feito de maneira muito simples, tanto para um pen-drive como diretamente para um repositório disponibilidade de maneira on-line não tão falada e prosperada Cloud Computing.

Mas se fazer o backup é algo simples, imagine então o processo de restauração deste conteúdo que também se torna cada vez mais ágil, rápida e fácil. Você já pensou nisso? Não adianta fazer o backup e pensar “estou seguro, fiz o backup do meu banco de dados, quando eu precisar basta restaurar”, parece ser algo que nunca vai acontecer, mas não é o que atualmente estamos vendo.

Pensando neste sentido seu eu que pergunto: “E se por acaso o seu backup foi roubado, sequestrado, enfim alguém mal intencionado acabou se apoderando dos seus dados?” Isso parece ser bastante assustador e perigoso, foi justamente pensando nisso que a partir da versão CTP2 do Microsoft SQL Server 2014, o time de engenheiros, desenvolvedores e especialistas da Microsoft decidiram adicionar de forma nativa a capacidade de criarmos backups diretamente em uma instância ou servidor SQL Server fazendo uso de criptografia de dados através dos já conhecidos algoritmos, por mais simples que isso possa parecer até a versão 2012 do Microsoft SQL Server não tínhamos esta funcionalidade disponibilidade no produto de forma nativa e totalmente suportada para nossos bancos de dados, tínhamos a necessidade de utilizar ferramentas de terceiros para aplicar este tipo de recurso.

Native Backup Encryption

Através desta nova funcionalidade ao executar um procedimento ou rotina de backup de banco de dados, o Microsoft SQL Server sabendo da escolha deste recurso além de criar um arquivo contendo todo conteúdo estabelecido para o banco de dados selecionado, também realizará para o mesmo arquivo que esta sendo criado a aplicação de uma camada de criptografia de dados, onde de uma maneira direta o conteúdo armazenado neste arquivo de backup estará totalmente criptografado.

Dentre as principais características existentes para esta funcionalidade, para que esta capacidade de adicionar uma camada de criptografia diretamente para todo o backup, torna-se necessário o uso de alguns recursos adicionais em nosso banco de dados para que seja possível criarmos backups criptografados, estou me referindo ao uso de certificados e chaves assimétricas em conjunto com os algoritmos suportados pelo SQL Server sendo eles:

  • AES 128;
  • AES 192;
  • AES 256; e 
  • Triple DES.

Utilizando o Native Backup Encryption

Como já destacado anteriormente, antes de criarmos um backup criptografado de nosso banco de dados, temos a necessidade de criamos um certificado de segurança para garantir que todo conteúdo existente esta sendo validado e possui um mecanismo de segurança.

Para começarmos, vamos realizar o primeiro passo que consiste na criação do nosso Banco de Dados chamado NativeBackupEncryption, em seguida criaremos nossa chave assimétrica e na sequência o certificado denominado CertNativeBackupEncryption. Vale ressaltar, que tanto o certificado como também a chave assimétrica serão obrigatoriamente armazenadas na banco de dados de sistema Master. Para isso utilizaremos o Bloco de Código 1 apresentado a seguir:

— Bloco de Código 1 —
Create Database NativeBackupEncryption
Go

Use Master
Go

Create Master Key Encryption By Password = ‘Backup@@01’
Go

Create Certificate CertNativeBackupEncryption
With Subject = ‘Certificado para Criptografia de Backup’;
Go

Perfeito o primeiro passo já foi realizado e podemos observar nas árvores de recursos do nosso banco de dados que tanto o certificado como principalmente a chave assimétrica estão criadas, conforme ilustra a Figura 1 apresentada abaixo:

Figura 1 – Certificado CertNativeBackupEncryption criado.

Nosso segundo passo também é um dos mais importantes, para conseguirmos aplicar a criptografia em nosso backup de dados, consiste basicamente no procedimento de backup da nossa chave assimétrica em conjunto com o backup do certificado CertNativeBackupEncryption, para que posteriormente seja possível realizar o backup criptografado.

Vale ressaltar que se este procedimento não venha a ser realizado o Microsoft SQL Server durante o processo de Backup Database emitirá um alerta informando a necessidade que este procedimento venha a ser realizado.

Vamos então executar o segundo passo através do Bloco de Código 2 apresentado na sequência:

— Bloco de Código 2 —

Backup Certificate CertNativeBackupEncryption
To File = ‘S:\MSSQL-2016\Backup\Backup-Certificate-CertNativeBackupEncryption.cert’
With Private Key
(
File = ‘S:\MSSQL-2016\Backup\Backup-Master-Key-File.key’,
Encryption By Password = ‘Backup@@01’
)
Go

Legal, legal, conseguimos realizar o backup da nosso Certificado e também do nossa Chave Assimétrica, observe que no procedimento de backup do certificado estamos informando o uso do nossa chave assimétrica na instrução With Private Key, passando como parâmetros os mesmos valores informados para o backup da chave.

A Figura 2 ilustra o local de armazenamento dos arquivos gerados após o backup da chave assimétrica e do certificado:

Figura 2 – Arquivos de backup da chave e certificados criados e armazenados.

Importante: Por questões de facilidade os arquivos de backup foram criados no mesmo local, mas pensando em segurança e boas práticas é altamente recomendável que cada arquivo de backup seja criado e armazenado em locais distintos por questões óbvias de segurança.

Agora que os backups de chave assimétrica e certificados foram realizados, vamos executar nosso último passo que consiste justamente na realização do Backup do nosso banco de dados NativeBackupEncryption aplicando as técnicas de compressão de dados para economia de espaço em disco e principalmente o uso da opção Encrytpion que nos permite escolher o algoritmo de criptografia e qual certificado a nível de servidor vamos utilizar, sendo assim, podemos executar o Bloco de Código 3 apresentado a seguir:

— Bloco de Código 3 —
Backup Database NativeBackupEncryption
To Disk = ‘S:\MSSQL-2016\Backup\Backup-NativeBackupEncryption.Bak’
With Compression,
Encryption
(Algorithm = AES_256,
Server Certificate = CertNativeBackupEncryption)
Go

Muito bem, como todo procedimento de backup, ao final da execução do comando Backup Database o Management Studio apresenta aquele tradicional conjunto de informações relacionadas ao nosso backup, algo que também não é diferente quando fazendo uso de um backup criptografado. A Figura 3 apresentado o arquivo de backup Backup-NativeBackupEncryption.Bak criado e armazenado após a conclusão da execução do comando Backup Database:

Figura 3 – Arquivo NativeBackupEncryption.Bak criado e armazenado em disco.

Estamos quase no final, continuando mais um pouco, vamos garantir e comprovar que realmente nosso backup foi criptografado. Você pode estar querendo ter a certeza que nosso backup esta criptografado, para realizarmos as conhecida prova dos nove, vamos fazer uso do tradicional comando Restore HeaderOnly, através do Bloco de Código 4 declarado abaixo:

— Bloco de Código 4 —

Restore HeaderOnly
From Disk = ‘S:\MSSQL-2016\Backup\Backup-NativeBackupEncryption.Bak’
Go

Para ilustrar o resultado obtido apos a execução do bloco de código 4, podemos observar os valores apresentados nas colunas: KeyAlgorithm, EncryptorThumbprint e EncryptorType, conforme apresenta a Figura 4.

Figura 4 – Informações referentes ao uso da criptografia no arquivo de backup.

Note que estão sendo apresentados para as respectivas colunas o algoritmo que utilizamos no procedimento de backup e seus respectivos encryptors, mecanismos utilizados para aplicar a criptografia.

Sensacional, conseguimos criar um backup com criptografia de seu conteúdo de forma nativa, sem ter a necessidade de utilizar ferramentas ou recursos de terceiros, fazendo uso total das funcionalidades e características existentes no Microsoft SQL Server. Mesmo assim, alguns pontos importantes devem ser destacados antes de concluirmos mais um post, a seguir destaco os benefícios e limitações do Native Backup Encryption.

Benefícios

  1. O uso deste tipo de recurso com certeza poderá trazer aos organizações e profissionais de banco de dados um grande benefício no que se relacionada as questões de segurança e armazenamento de dados após o processo de backup.
  2. Caso você esteja utilizando atualmente uma ferramenta de terceiros para backups criptografados, você pode comparar essa ferramenta com a funcionalidade e o desempenho de backups criptografados nativos e ver se isso preenche sua exigência.

Limitações

  1. O Native Backup Encryption não esta disponível nas edições Express e Web do Microsoft SQL Server.
  2. O processo de appending capacidade de abrir um arquivo de backup já existente e adicionar o novo conteúdo ao seu final não é suportado para backups criptografados.

Referências

https://blogs.technet.microsoft.com/dataplatforminsider/2013/10/17/sql-server-2014-ctp-2-now-available/

https://www.pythian.com/blog/sql-server-2014-ctp-2-native-backup-encryption/

https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-certificate-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-headeronly-transact-sql

Links

Caso você ainda não tenha acessado os posts anteriores desta sessão, fique tranquilo é fácil e rápido, basta selecionar um dos links apresentados a seguir:

https://pedrogalvaojunior.wordpress.com/2017/04/30/14-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/03/25/13-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2017/01/23/12-para-que-serve/

Conclusão

Durante muito tempo este foi um dos recursos mais esperados e aguardos pelos profissionais do Microsoft SQL Server, principalmente pela necessidade até então da aquisição de ferramentas de terceiros, o que gerava custos, bem como, para realizar um procedimento simples trabalhar com dois produtos distintos ao mesmo tempo, o que para alguns pode parecer dificultoso.

Neste post fizemos uso do algoritmo AES_256 considerado por muitos profissionais um dos mais seguros, mas vale a pena fazer uso e comparação dos demais para justamente identificar suas diferenças de comportamento ainda mais se levarmos em consideração diferenças no tempo de execução de um backup criptografado com outro algoritmo.

Mas esse desafio e análise vou deixar para você!!!

Agradecimentos

Antes de finalizar, são 8:54hrs da manhã, estou terminando o post, mas com um lindo dia me esperando para estudar e trabalhar, faça você isso também aproveite a sua vida, pois ela passa muito rápido.

Espero que este conteúdo possa lhe ajudar e ser útil em suas atividades profissionais e acadêmicas.

Um forte abraço, até o próximo post da sessão Para que serve…..

Valeu.

Short Scripts – Junho 2017


Boa noite galera, olá comunidade de banco de dados.

Tudo bem? Graças a deus mais um final de semana se aproximando, finalzinho de noite de quinta – feira, acredito que neste momento minha linda esposa esta curtindo mais um episódio de uma das suas séries favoritas, ou lendo mais um dos seus intermináveis livros (kkkkkk), isso não é um crítica, muito ao contrário, a leitura faz bem para a alma e principalmente para a mente, mas posso dizer que minha pequena Fernanda é uma degustadora insaciável de livros.

Mudando de assunto, este é o segundo post deste ano dedicado exclusivamente a sessão Short Scripts, sessão criada a alguns anos que  esta atraindo um número interessante de visitantes, principalmente de profissionais da área de banco de dados na busca por exemplos de códigos que possam solucionar ou elucidar na resolução de um determinado problema.

O post de hoje

Para este post, selecionei como de costume os principais scripts armazenados recentemente na minha biblioteca de códigos, que apresentam os seguintes assuntos:

  • Check Constraint;
  • Common Table Expression;
  • Índices;
  • Information_schema.columns;
  • Monitoramento de Processos;
  • Network Protocol;
  • Operador Outer Apply;
  • Requisição de Disco;
  • Sys.dm_server_registry;
  • Sys.dm_tcp_listener_states;
  • sys.dm_exec_query_plan;
  • sys.dm_exec_sql_text;
  • sys.dm_exec_query_stats;
  • sys.types;
  • sys.tables;
  • sys.dm_db_index_usage_stats;
  • Sys.Identity_Columns; e
  • User Defined Function.

Então mãos no teclado, a seguir apresento os códigos e exemplos selecionados para o Short Script – Junho 2017. Vale ressaltar que todos os scripts publicados nesta sessão são devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, vale sim todo cuidado possível para evitar maiores problemas.

Fique a vontade para compartilhar, comentar e melhorar cada um destes códigos.

Short Scripts

— Short Script 1  – Utilizando Check Constraint para cálculo de datas —

Create Table Alunos
(Codigo Int)
Go

— Adicionando a coluna e constraint —
Alter Table Alunos
Add DataNascimento DateTime
Constraint CK_Alunos_DataNascimento Check (DateDiff(Year,DataNascimento, GetDate()) >=18)
Go

— Adicionando somente a constraint —
Alter Table Alunos
Add Constraint CK_Alunos_DataNascimento
Check (DateDiff(Year,DataNascimento, GetDate()) >=18)
Go

— Default – Estado —
Alter Table Alunos
Add Constraint [DF_Estado] Default ‘SP’ for Estado
Go

— Short Script 2  – Aplicando o uso de Common Table Expression para inserir registros com valores de um registro anterior —

DECLARE @Metas TABLE ( Data DATE, Meta INT );

INSERT INTO @Metas
( Data, Meta )
VALUES ( DATEFROMPARTS(2017, 03, 29), 50 ),
( DATEFROMPARTS(2017, 04, 11), 35 ),
( DATEFROMPARTS(2017, 04, 13), 50 );

DECLARE @Producao TABLE
(
Data DATE ,
Quantidade INT
);

INSERT INTO @Producao
( Data, Quantidade )
VALUES ( DATEFROMPARTS(2017, 04, 10), 49 ),
( DATEFROMPARTS(2017, 04, 11), 35 ),
( DATEFROMPARTS(2017, 04, 12), 36 ),
( DATEFROMPARTS(2017, 04, 13), 50 ),
( DATEFROMPARTS(2017, 04, 14), 50 );

WITH DadosProduzidos
AS ( SELECT P.Data ,
Quantidade = SUM(P.Quantidade)
FROM @Producao AS P
GROUP BY P.Data
)
SELECT D.Data ,
[Produzido] = D.Quantidade ,
Meta = ( SELECT TOP 1 M.Meta
FROM @Metas AS M
WHERE M.Data <= D.Data
ORDER BY M.Data DESC
)
FROM DadosProduzidos D;

— Short Script 3  – Identificando a relação de todos os índices existentes em um banco de dados —

SELECT DB_NAME(Database_ID) DBName,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(ius.OBJECT_ID) ObjName,
i.type_desc,
i.name,
user_seeks,
user_scans,
user_lookups,
user_updates
FROM sys.dm_db_index_usage_stats ius INNER JOIN sys.indexes i
ON i.index_id = ius.index_id
AND ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.tables t
ON t.OBJECT_ID = i.OBJECT_ID
GO

— Short Script 4 – Obtendo informações de network protocols e device através da sys.dm_server_registry —

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Default Port’ FROM sys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpPort%’
AND CONVERT(float,value_data) > 0
Go

SELECT MAX(CONVERT(VARCHAR(15),value_data)) As ‘Dynamic Port ‘ FROM sys.dm_server_registry
WHERE registry_key LIKE ‘%MSSQLServer\SuperSocketNetLib\Tcp\%’
AND value_name LIKE N’%TcpDynamicPort%’
AND CONVERT(float,value_data) > 0
Go

— Short Script 5 – Obtendo informações de network protocols e device através da sys.dm_tcp_listener_states —

SELECT port As ‘Default Port’ FROM sys.dm_tcp_listener_states
WHERE is_ipv4 = 1
AND [type] = 0
AND ip_address <> ‘127.0.0.1’
Go

— Short Script 6 – Criando uma User Defined Function com operador Outer Apply —

CREATE FUNCTION AttributesOfTable (@tableToSearch nvarchar(500))
returns table
return SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME
from information_schema.columns
where TABLE_NAME = @tableToSearch;
go

Declare @tableToSearch table (nome_tabela varchar(50));
INSERT into @tableToSearch values (‘Customer’), (‘Order’), (‘Papagaio’);

SELECT T1.nome_tabela as [nome da tabela],
T2.TABLE_SCHEMA as [nome do esquema],
T2.COLUMN_NAME as [nome da coluna]
from @tableToSearch as T1
outer apply dbo.AttributesOfTable(T1.nome_tabela) as T2;

— Short Script 7 – Utilizando a DMV sys.identity_columns para identificar o valor identity de uma determinada coluna —

SELECT sys.tables.name AS [Table Name],
sys.identity_columns.name AS [Column Name],sys.types.name as Type,
last_value AS [Last Value]
FROM sys.identity_columns
INNER JOIN sys.tables
ON sys.identity_columns.object_id = sys.tables.object_id
Inner join sys.types on sys.types.user_type_id = sys.identity_columns.user_type_id
ORDER BY last_value DESC

— Short Script 8 – Consultando informações sobre o SQL Server armazenadas no Registro do Windows —

— A. Display the SQL Server services —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N’%ControlSet%’
Go

— B. Display the SQL Server Agent registry key values —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE key_name LIKE N’%SQLAgent%’
Go

— C. Display the current version of the instance of SQL Server —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE value_name = N’CurrentVersion’
Go

— D. Display the parameters passed to the instance of SQL Server during startup —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE registry_key LIKE N’%Parameters’
Go

— E. Return network configuration information for the instance of SQL Server —
SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry
WHERE keyname LIKE N’%SuperSocketNetLib%’
Go

— Short Script 9 – Identificando a relação de querys que apresentam o maior consumo de disco durante seu período de processamento – 

SELECT TOP 20 SUBSTRING(qt.text,
(qs.statement_start_offset/2)+1,
((CASE
qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.text)
ELSE
qs.statement_end_offset
END –
qs.statement_start_offset)/2)+1),
qs.execution_count,

qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads,
qs.max_logical_reads,
qs.total_elapsed_time,
qs.last_elapsed_time,
qs.min_elapsed_time,
qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats
qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY
sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY
qs.total_logical_reads DESC

Legal, mais uma relação de short scripts acaba de ser compartilhada, mesmo sendo denominados short entre aspas “pequenos”, posso garantir que todos estes exemplos são de grande importância e apresentam um valor e conhecimento do mais alto nível.


Chegamos ao final de mais um Short Scripts, espero que este material possa lhe ajudar, ilustrando o uso de alguns recursos e funcionalidades do Microsoft SQL Server.

Acredito que você tenha observado que estes códigos são bastante conhecidos em meu blog, todos estão relacionados aos posts dedicados ao Microsoft SQL Server publicados no decorrer dos últimos anos.

Boa parte deste material é fruto de um trabalho dedicado exclusivamente a colaboração com a comunidade, visando sempre encontrar algo que possa ser a solução de um determinado problema, bem como, a demonstração de como se pode fazer uso de um determinado recurso.

Links

Caso você queira acessar os últimos posts desta sessão, não perca tempo acesse os links listados abaixo:

Agradecimento

Agradeço sua visita, com certeza é imenso prazer saber que você teve interesse em acessar este post.

Nos encontramos novamente no mês de setembro, para o próximo post dedicado a sessão Short Scripts.

Um grande abraço, boa noite, bom descanso.

Valeu.

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….