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.

Material de Apoio – Fevereiro 2017


Salve, salve, amigos…. Bom dia!

Estamos chegando com mais um post da sessão Material de Apoio, sendo esta a sessão mais antiga do meu blog que em 2017 completa 10 anos de existência.

Ao longo destes 10 anos muita coisa mudou não somente na minha vida, mas também no meu blog, dentre elas o número de visitante crescendo ao longo dos dias, meses e anos conseguindo no começo deste ano atinguir a impressionamente marca de 1 milhão de visitantes a contar desde o primeiro dia que o meu antigo blog publicado no extinto livespace foi publicado.

Muito obrigado a todos, só tenho a agradecer á você e estes milhares de visitantes que me ajudaram a atinguir esta marca e fazem parte desta história.

Obrigado, valeu e que venham os próximos 1, 2, 3 milhões de visitantes.

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, como diria naquele filme vou “compartilhagram” os meus scripts catalogados 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:

  • Advanced Query with Tunning;
  • Compactação de Backup;
  • Compactação de Tabelas e Índices;
  • Compressão de Dados;
  • Criptografia de Banco de Dados;
  • Criptografia de Banco de Dados com Master Key;
  • Linked Server para Excel;
  • Transparent Data Encription TDE;
  • XQuery With Data Method;
  • XQuery With Exist Method; e
  • XQuery With Query Method.

Material de Apoio

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

1 – Material de Apoio – Fevereiro – 2017 – advanced-querying-and-query-tuning-in-sql-server-2000-and-2005-sql

2 – Material de Apoio – Fevereiro – 2017 – compactacao-de-backup-sql

3 – Material de Apoio – Fevereiro – 2017 – compactacao-de-tables-e-index-sql

4 – Material de Apoio – Fevereiro – 2017 – exemplo-compressao-de-dados-sql

5 – Material de Apoio – Fevereiro – 2017 – criptografia-sql

6 – Material de Apoio – Fevereiro – 2017 – criptografia-master-key-sql

7 – Material de Apoio – Fevereiro – 2017 – linked-server-para-excel-sql

8 – Material de Apoio – Fevereiro – 2017 – xquery-data-method-sql

9 – Material de Apoio – Fevereiro – 2017 – xquery-exist-method-sql

10 – Material de Apoio – Fevereiro – 2017 – xquery-query-method-sql

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 aqui 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

Mais uma vez obrigado por sua visita, espero lhe encontrar muitas vezes no decorrer deste ano em meu blog.

Um forte abraço, vamos em frente.

Até mais.

#12 – Para que serve


Boa tarde, boa tarde…. Olá pessoal, tudo bem?

Mais uma semana começando, para alguns volta as aulas (kkkkk)…. é a mamata esta acabando e o futuro deste país tem que voltar para sua realidade, no mundo capitalista que estamos vivendo, sem o mínimo de educação civica e moral não somos nada.

Deixando de lado este pequeno pensamento, seguindo em frente este é o post de número 12 dedicado exclusivamente a sessão Para que serve, que lentamente esta atraindo novos seguidores ao meu Blog.

Como você já deve ter percebido os posts relacionados a esta sessão tem o objetivo de apresentar ou demonstrar como  códigos de exemplo, aplicativos, utilitários, enfim recursos relacionados diretamente á banco de dados ou gerenciadores de bancos de dados podem ser utilizados como uma possível solução de problemas, bem como, orientar na sua forma de utilização.

Após esta tradicional saudação, chegou a hora de falar sobre o #12 – Para que serve de hoje, tenho a certeza que você vai gostar.

No post de hoje, vou a destacar uma alteração que a Microsoft introduziu no novo SQL Server 2016, que a partir desta versão alterar de maneira direta o comportamento padrão existente atualmente para alocação de dados e autocrescimento para os bancos de dados de usuário ou para o system database TEMPDB.

Em contra partida, neste post vou destacar um pouco sobre a relação das Trace Flag 1117 e 1118 para com estes dois recursos que compõem o SQL Server, sabendo que durante anos ambas foram recomendadas pelas equipes de engenheiros da Microsoft como técnicas para alterar este comportamento padrão, que a partir da versão 2016 poderá ser realizado de uma maneira bem diferente ou até mesmo de forma automática.

Vamos lá….começa aqui o #12 – Para que serve – Alterando o comportamento padrão para alocação de dados e autocrescimento no Microsoft SQL Server 2016 –

Introdução

Até a versão 2014 o Microsoft SQL Server apresentava o mesmo padrão definido desde a versão 2000 para alocação de dados e autocrescimento de banco de dados, comportamento que poderia ser alterado através do uso de recursos externos entre deles as tão conhecidas e temidas trace flags.

Para um melhor entendimento, vou abordar brevemente os dois conceitos, visando esclarecer um pouco o papel de cada um deles, começando por:

Alocação de Dados: Quando se referimos a alocação de dados em uma instância ou servidor SQL Server, estamos fazendo referência a dois recursos de extrema importância que forma o SQLOS, me refiro ao Database Engine e Storage Engine, sendo estes responsáveis em possibilitar o armazenamento, contenção e consumo de dados manipulados pelo SQL Server.

Como destacado anteriormente o Microsoft SQL Server até a versão 2014 não apresentava a capacidade de criar páginas de dados iniciais ou as primeiras oito páginas de dados conhecidas como extended (extensão) identificadas internamente como páginas ou extensões mista, no qual as primeiras páginas ou extended deveriam se iguais, uniforme, do mesmo tipo e apresentar a mesma estrutura contendo somente informações relacionadas a tabelas ou índices.

Você pode estar se perguntando, mas isso não era possível de ter alterado nas versões mais antigas? A resposta simples e direta é SIM, e para tal finadade eramos obrigados a utilizar a Trace Flags 1118 (se quiser saber mais sobre ela acesse: https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/)

Mas isso na versão 2016 não é mais necessário, para oferecer e permitir esta mudança de comportamento o time de engenheiros da Microsoft dedicados ao SQL Server aplicaram uma pequena mudança na estrutura da Dynamic Management View: sys.databases existente desde as primeiras versões do produto, na qual foi adicionada uma nova coluna chamada is_mixed_page_allocation_on, que pode ser utilizada através do comando ALTER DATABASE. Falarei um pouco mais sobre esta nova coluna posteriormente.

Dando continuidade, vamos conhecer um pouco sobre AutoGrow (Autocrescimento):

Autocrescimento: Opção aplicada aos bancos de dados que define qual deverá ser a fator e forma de crescimento de um banco de dados, também sofreu algumas mudanças.

A partir do SQL Server 2016 todo processo de autocrescimento e alocação de dados será realizado de forma automática, no qual o Database Engine e parceria com o Storage Engine serão autosuficientes capazes de identificar a necessidade de mudar a forma de alocação e autocrescimento do banco de dados, sem recorrer a necessidade de fazer uso da trace flag 1117.

Desta forma, de acordo com a distribuição dos dados alocados em seus respectivos arquivos de dados ou filegroups permitirá que quando um arquivo de dados crescer todos os demais arquivos relacionados ao banco de dados ou filegroup deverão crescer ao mesmo tempo, sendo este o novo comportamento adotado para este banco de dados, algo revolucionará se levarmos em consideração do processo desempenho pelo Storage Engine para alocar e contar os dados.

Esta mudança de comportamento pode ser considerada uma peça chave para o SQL Server no que se relaciona a performance, pois de maneira simultânea teremos arquivos alocados ao mesmo tempo na mesma transação, oferecendo uma redução no tempo estimado para contenção de alocação de dados, o que no final das contas provacará uma sensível diminuição para o Storage Engine controlar o número de pontos de marcação de dados relacionado ao que está alocado para uso.

MIXED_PAGE_ALLOCATION

Nova opção adicionada ao comando ALTER DATABASE que permite aplicar aos bancos de dados de usuário e system database TEMPDB a nova forma de alocação de dados adotada para a versão 2016 do SQL Server, denominada Mixed Page Allocation ou Alocação de Páginas Mistas, na qual destacado anteriormente será possível alocar para toda estrutura de um banco de dados o uso de páginas ou extended mistas, aplicada de implícita para tabelas e índices.

A coluna is_mixed_page_allocation_on apresenta dois valores, sendo eles:

  • 0 = A estrutura de tabelas e índices será alocada de forma uniforme e não permitirá que as primeras páginas de dados ou a primeira extended possa ser formada por uma estrutura mista.
  • 1 = A estrutura de tabelas e índices poderá ser alocada de forma mista permitindo que as primeras páginas de dados ou a primeira extended possa ser formada por uma estrutura mista.

Vale ressaltar que ao realizar uma simples consulta na DMV sys.databases, valor padrão apresentado na coluna is_mixed_page_allocation_on para os bancos de dados de usuário é 0(zero), e para os bancos de dados de sistema: Master, Model e MSDB é 1(Hum) sendo que para este bancos de dados não é permitido alterar a forma de alocação.

Perguntas e respostas

Muito bem, você pode estar coçando a sua cabeça e ainda contendo algumas dúvidas sobre ese possível novo comportamento entre outros conceitos aqui apresentados, no intuito de tentar ajudar, elaborei algumas perguntas:

1. Afinal esta nova opção Mixed_Page_Allocations possui alguma relação com as trace flags 1117 e 1118?
Respondendo de bate pronto: SIM possuem total relação.

2. Esta nova opção substituio uso de ambas as traces flags?
Sim, tem este finalidade mas aplicada somente a partir da versão 2016.

3. Após alterar a forma de alocação para Mixed Page Allocation posso voltar ao formato anterior?
Sim, sem nenhum tipo de risco ou impedimento.

Além das questões a Tabela 1 apresentada abaixo poderá lhe ajudar a entender em qual cenário você poderá fazer uso da mixed_page_allocation ou das trace flags 1117 e 1118:

Database TF 1117 TF 1118
tempdb Não requerida (default) Não requerida (default)
user databases Por padrão será realizada o autocrescimento de forma simples, ou seja, de um único arquivo por vez. Use ALTER DATABASE <dbname> MODIFY FILEGROUP [PRIMARY] AUTOGROW_ALL_FILES para habilitar o crescimento para todos os arquivos de forma simultânea Não requerida. Use ALTER DATABASE <dbname> SET MIXED_PAGE_ALLOCATION  ON para voltar a utilizar alocação mista.
Other system databases (master, model, msdb) -NA- Alocação de páginas em modo mista não pode ser alterada para estes bancos de dados.

Tabela 1 – Cenários para uso da alocação mista ou mudança no autocrescimento.

Referências

https://technet.microsoft.com/pt-br/library/ms190969(v=sql.105).aspx

https://msdn.microsoft.com/en-US/library/bb522682.aspx

https://support.microsoft.com/en-us/kb/2964518

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

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

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

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/2016/12/16/11-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/11/15/10-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/10/08/09-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/08/06/07-para-que-serve/

Conclusão

Cuidar da vida de nossos dados é algo muito importante, mas saber como e de que forma estes podem ser armazenados esta bem acima de qualquer outra preocupação, pensando nisso a Microsoft permitiu a partir da versão 2016 alterar de forma simples, rápida e segura a maneira com nossos bancos de dados podem crescer no decorrer do tempo, bem como, as estruturas internas podem ser criadas e alocadas, capacidade que nos permite melhrorar de maneira sensível atividades relacionadas a como nossos dados podem estar alocados para consulta, possibilitando ganhos de processamento de dados.
Neste post você pode mais uma vez observar que o Microsoft SQL Server esta em constante evolução, um dos produtos mais prestigiados pela Microsoft, buscando sempre trazer melhorais e inovações, algo de extrema importância para qualquer profissional que trabalha com esta tecnologia.

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.

Nos encontramos em breve, até lá…..

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!!!

#11 – Para que serve


Boa tarde, boa tarde, boa tardeeeeee…..

Tudo bem pessoal?

Hoje 16 de Dezembro, data inesquecível para qualquer corinthiano, estamos completando mais um aniversário da fantástica e memorável conquista do Mundial de Clubes da Fifa, melhor dizendo o Bi-Campeonato Mundial de Clubes em 2012.

Fico honrado com a sua ilustre visita, seja bem – vindo mais uma vez ao meu blog, espero que você consiga encontrar o que esta procurando ou algo que possa lhe agradar.

Este é mais um post da sessão Para que serve, lançada no início de 2016 e que esta chegando ao post de número 11, isso mesmo estamos no décimo primeiro post dedicado a esta sessão que aos poucos esta conseguindo se tornar uma referência de conhecimento diferenciado no meu Blog.

Após esta tradicional saudação, chegou a hora de falar sobre o #11 – Para que serve de hoje, tenho a certeza que você vai gostar….


Introdução

Como você já deve ter percebido os posts relacionados a esta sessão tem o objetivo de apresentar ou até mesmo em outros casos demonstrar como exemplos de código, aplicativos, utilitários, entre outros elementos envolvidos a banco de dados ou gerenciadores de bancos de dados dentro eles o Microsoft SQL Server podem ser utilizados para se obter uma possível solução de um problema, como em outros casos orientar na sua forma de utilização.

Para o post de hoje vou destacar um script que utilizei no decorrer desta semana, na área de uma determinada aplicação, o mesmo não foi a solução do meu problema, mas ajuda de forma considerável.

Basicamente a minha necessidade era identificar ao longo de um período de tempo quais tipos de objetos estavam gerando e acumulando cache em uma instância de banco de dados Microsoft SQL Server 2014, foi justamente para este cenário que de maneira direta realizei o uso da DMF – Dynamic Management Function – sys.dm_exec_cached_plans implementada a partir da versão 2008 do Microsoft SQL Server.

Sys.dm_exec_cached_plans

Esta foi uma das primeiras DMF adicionadas pelo time de desenvolvimento do Microsoft SQL Server na versão 2008, onde a Microsoft introduziu uma nova forma de gerenciamento um servidor ou instância de Banco de Dados.

A sys.dm_exec_cachec_plan tem como finalidade principal retornao conjunto de linhas relacionadas para cada plano de consulta armazenada em cache mantido em memória no SQL Server, possibilitando assim uma execução do conjunto de recursos que necessitam destes objetos de forma mais rápido e simplificada.

Você pode usar essa função de gerenciamento dinâmico para encontrar e identificar dados em tempo real relacionados a:

  • Cached Query Plans;
  • Cached Query Text;
  • Quantidade de memória usada pelo plan cached; e
  • Quantidade de reutilização de plan cached.

Para muitos profissionais, a sys_dm_exec_cached_plans é considerada uma das DMFs mais fácies e práticas para ser utilizada em atividades de análise e manutenção de um banco de dados, devido principalmente por possuir uma sintaxe simples que não requer parâmetros adicionais no momento da sua execução.

#11 Para que serve – Identificando os tipos de cache e quantidade armazenada no Query Plan —

Agora que conhecemos um pouco do que esta relacionada com este post, vamos então conhecer este script que poderá nos ajudar a obter todas as possíveis informações relacionadas aos tipos de cache e demais dados relacionados com esta importante área de armazenamento em memória do Microsoft SQL Server.

Para isso vamos utilizar o Bloco de Código apresentado a seguir, que demonstra como a sys_dm_exec_cached_plans pode ser utilizada:

– Bloco de Código —

sys_dm_exec_cached_plans

Realizando uma rápida análise, fica fácil observar que o uso desta DMF realmente é muito simples, como também, trata-se de um bloco de código com um nível de complexidade muito pequena, onde estamos fazendo uso do conjunto de colunas existente na própria DMF.

A parte mais complexa do código esta relacionado a contagem do total de planos gerados e armazenados em cached para cada tipo de recurso, em conjunto com o total em megabytes ocupado em memória pelo mesmo. Vale ressaltar que este bloco de código foi testado e validado a partir da versão 2008 e demais versões: R2, 2012, 2014 e também 2016.

Após executarmos o bloco de código apresentando anteriormente, o Management Studio deverá retornar um conjunto de colunas e valores similares ao apresentado na Figura 1 apresentada abaixo:

sys_dm_exec_cached_plans-2Figura 1 – Relação de tipos de cache e suas respectivas quantidades armazenadas e memória ocupada.

Agora fica mais fácil notar a existência das colunas Cache TypeTotal Plans e Total MBs, são justamentes estas as colunas que nos permitem encontrar as informações relacionadas aos tipos de cache, total de planos armazenados e espaço ocupado em memória em nosso servidor ou instância de bancos de dados Microsoft SQL Server.

Falando um pouco sobre estas três colunas é possível observar:

Cache Type: Esta coluna apresenta os tipos de cache identificados pela sys_dm_exec_cached_plans.

Total Plans: Retorna a quantidade de Plan Cached armazenadas em tempo real para os respectivos tipos de cache coletados na sys_dm_exec_cached_plans.

Total MBs: Apresenta a quantidade de memória ocupada para cada Cache Type identificados pela sys.dm_exec_plans, sendo este valor correspondente ao total armazenado ao longo do tempo de execução da instância ou servidor de banco de dados, com base, no último processo de reinicialização.

Acredito que agora ficou bem mais fácil entender os dados retornados após o processamento do bloco de código, como também, o que esta DMF pode nos oferecer de recursos em um momento de análise mais aprofundada do uso dos tipos de cached.

Referências

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

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

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

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

Links

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

https://pedrogalvaojunior.wordpress.com/2016/11/15/10-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/10/08/09-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/08/06/07-para-que-serve/

Conclusão

Administrar um banco de dados não é uma tarefa das mais complicadas do mundo, mas quando se referimos em administratar um servidor de banco de dados ou conjunto de servidores de bancos de dados o cenário com certeza muda bastante.

Foi pensando neste tipo de situação que compartilhei com vocês hoje este script no #11 – Para que serve, que apresenta como podemos de maneira fácil, rápida, segura e muito prática encontrar informações relacionadas aos tipos de cache existentes no Microsoft SQL Server, suas respectivas quantidades armazenadas e principalmente o espaço ocupado.

Esta não é uma atividade que você vai realizar todos os dias, particularmente falando, foram poucas as vezes que tive a real necessidade de mergulhar mais a fundo nesta parte de uma instância ou servidor de banco de dados, mas sempre vale a pena conhecer, saber que este de funcionalidade existe e pode ser utilizada de forma rápida e prática.

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.

Aproveitando este final de ano, quero lhe desejar um Feliz Natal e Próspero Ano Novo.

Nos encontramos em breve, até lá…..

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.

#10 – Para que serve


O louco meu, pleno feriadão e você esta passando por aqui no meu blog……

Que legal, sensacional, fico honrado com a sua ilustre visita, seja bem – vindo mais uma vez ao meu blog, espero que você consiga encontrar o que esta procurando ou algo que possa lhe agradar.

Este é mais um post da sessão Para que serve, lançada no início de 2016 e que esta chegando ao post de número 10, isso mesmo estamos no décimo post dedicado a esta sessão que aos poucos esta conseguindo se tornar uma referência de conhecimento diferenciado no meu Blog.

É isso ai, após esta tradicional saudação, chegou a hora de falar sobre o #10 – Para que serve de hoje, tenho a certeza que você vai gostar….


Introdução

Como você já deve ter percebido os posts relacionados a esta sessão tem o objetivo de apresentar e em alguns casos demonstrar como exemplos de código, aplicativos, utilitários, entre outros elementos envolvidos a banco de dados ou gerenciadores de bancos de dados dentro eles o Microsoft SQL Server podem ser utilizados para se obter uma possível solução de um problema, como em outros casos orientar na sua forma de utilização.

Para o post de hoje vou destacar um script que utilizei recentemente e posso dizer que foi de grande ajuda, mas antes de apresentar este recurso vou destacar um pouco sobre alguns elementos relacionados a ele, dentre os quais destaco File Growth.

File Growth

E ai você já ouviu falar file growth, ou simplesmente crescimento de arquivo de dados ou log? Se você é um administrador de banco de dados, ou um profissional que já trabalha a algum tempo com o banco de dados, tenho a certeza que já deve ter ouvido falar sobre a importância de se saber como esta configurado o fator de crescimento de um banco de dados e seu arquivos de transações.

Trata-se de uma configuração que pode ser aplicada durante a criação de um banco de dados ou posteriormente, sua importância esta totalmente relacionada ao espaço de armazenamento de dados durante sua utilização, o que poderá impactar na capacidade física de uma unidade de disco em gerenciar o quanto estes arquivos podem consumir e alocar espaço em disco no decorrer do seu tempo de vida.

Ao definir a forma de crescimento ou até mesmo o quanto este arquivo poderá ou não crescer de forma ilimitado o Microsoft SQL Server vai trabalhar no processo de alocação, escrita e manipulação da estrutura física e lógica tanto para os arquivos de dados, como principalmente para os arquivos de log.

Justamente sendo estes os arquivos que normalmente consomem um grande espaço física das unidades de disco para catalogar todas as operações processadas em um banco de dados que devem ser registradas em sua estrutura.

Para este tipo de cenário os gerenciadores de banco de dados através de seu mecanismo de Storage Engine observam e monitoram o que esta sendo processado e armazenado dentro de cada arquivo, caso o mesmo tenho que crescer para alocar uma nova área é com base nas configurações de File Growth definidas para o respectivo arquivo que este crescimento poderá ser realizado em fatores de Kilobytes, Megabytes, Gigabytes ou até mesmo em valores de porcentagem.

#10 Para que serve – Obtendo informações sobre database filegrowth —

Agora que conhecemos um pouco que esta relacionada com este post, vamos então conhecer este script que poderá nos ajudar a obter todas as possíveis informações relacionadas ao fator de crescimento de nossos bancos de dados e suas respectivas estruturas de dados e log.

— Bloco de Código —

filegrowth

Muito bem, observe que este código é bastante simples, estamos basicamente fazendo uso das catalogs views existentes no Microsoft SQL Server desdes suas primeiras versões o que nos permite dizer que este bloco de código pode ser aplicado facilmente a partir da versão 2005 em qualquer nível de edição, além disso, o mesmo já foi testado e aprovado nas últimos duas edições 2014 e 2016.

Após executarmos o bloco de código apresentando anteriormente, o Management Studio deverá retornar um conjunto de colunas e valores similares ao apresentado na Figura 1 apresentada abaixo:

filegrowth1Figura 1 – Relação de bancos de dados e informações sobre o filegrowth.

Podemos notar a existência das colunas AutoGrowthStatus, GrowthValue e GrowthIncrement, são justamentes estas as colunas que nos permitem encontrar as informações relacionadas aos fatores de crescimento configurados para cada banco de dados armazenado em nosso servidor ou instância de bancos de dados Microsoft SQL Server.

Falando um pouco sobre estas três colunas é possível observar:

AutoGrowthStatus: Esta coluna apresenta o status da propriedade Auto Growth, sendo esta definida para informar e o arquivo deverá ou não crescer de forma automática.

GrowthValue: Apresenta que pode ser informado a partir de 0 (zero) que indica ao Microsoft SQL Server que o determinado banco de dados não deverá crescer. Os demais valores podem representar uma indicação de crescimento em tamanho fixo ou até mesmo em porcentagem.

GrowthIncrement: Mostra a forma de incremento do fator de crescimento do banco de dados, sendo orientado e calculado através do número de páginas de dados, se o valor apresentado for igual á 0 (zero) significa que este banco de dados não terá seu crescimento realizado, qualquer outro valor acima de 0 (zero) significa que este banco de dados será impactado em algum momento pelo valor definido nas configurações do crescimento do banco de dados. Vale ressaltar que este valor esta relacionado ao tamanho de 8Kb (Kilobytes) para cada página de dados.

Após esta análise posso dizer que fica mais fácil descobrir qual banco de dados poderá apresentar problemas de crescimento acima no normal ou simplesmente aquele banco de dados que necessita crescer além do estimado.

Referências

https://technet.microsoft.com/pt-br/library/ms181338(v=sql.110).aspx

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

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

Links

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

https://pedrogalvaojunior.wordpress.com/2016/08/06/07-para-que-serve/

https://pedrogalvaojunior.wordpress.com/2016/08/06/07-para-que-serve/

Conclusão

Administrar um banco de dados não é uma tarefa das mais complicadas do mundo, mas quando se referimos em administratar um servidor de banco de dados ou conjunto de servidores de bancos de dados o cenário com certeza muda bastante.

Foi pensando neste tipo de situação que compartilhei com vocês hoje este script no #10 – Para que serve, que apresenta como podemos de maneira fácil, rápida, segura e muito prática encontrar informações relacionadas ao file growth, ou simplesmente fator de crescimento.

Considerada uma das configurações mais importantes de um qualquer banco de dados alocado em uma instância ou servidor Microsoft SQL Server.

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.