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 – Maio 2017


Muito boa tarde comunidade e amantes de banco de dados!!!

Tudo bem? Este é mais um post da sessão Material de Apoio, sendo o segundo no decorrer de 2017 e de número 152 no total desta sessão. Estamos nos aproximando da metade de 2017, que loucura isso, como a rotina do dia a dia não nos deixa perceber o quanto o tempo 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:

  • Check Constraint para Cálculo de Data;
  • CTE – Preenchendo dados de um registro com base no registro anterior;
  • Função para formatação de valores;
  • Função para retornar nome de tabelas e seus respectivos atributos(colunas);
  • Identificação do primeiro dia do mês corrente;
  • Monitoramento de Processos Bloqueados;
  • Movimentação de tabelas entre filegroups;
  • Obtendo o último valor Identity de uma coluna; e
  • Requisição de disco.

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:

1 – Material de Apoio – Maio – 2017 – Check Constraint – Cálculo de Data.sql

2 – Material de Apoio – Maio – 2017 – Preencher dados de um registro com dados do registro anterior.sql

3 – Material de Apoio – Maio – 2017 – Função – Formatação de Valores.sql

4 – Material de Apoio – Maio – 2017 – Função para retornar o nome e seus atributos – Utilizando operador Outer Apply.sql

5 – Material de Apoio – Maio – 2017 – Identificar o primeiro dia do mês com base na data atual.sql

6 – Material de Apoio – Maio – 2017 – Monitorando Processos Bloqueados no SQL Server.sql

7 – Material de Apoio – Maio – 2017 – Move Table between Filegroup – Stored Procedure.sql

8 – Material de Apoio – Maio – 2017 – Obtendo último valor identity.sql

9 – Material de Apoio – Maio – 2017 – Listando as querys que mais realizam requisição de disco.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.

Não deixe de acessar os outros posts das demais sessões, e o próximo post desta sessão será publicado em Agosto, um forte abraço.

Até mais.

#14 – Para que serve


Olá, boa noite….

Final de noite de domingo, véspera de feriado e nosso Brasil desde a última sexta – feira dia 28/04 vivendo fortes emoções na política, economia, esporte e principalmente cidadania. Alias dia 28/04/2017 uma das datas mais importantes da minha vida, neste dia comemorei mais uma primavera como gostam de dizer alguns dos meus familiares, já se vão 37 anos, muitos destes anos dedicados a minha esposa, filhos, filha, trabalho e principalmente a áreas de educação e tecnologia.

Aproveito para agradecer a todos os amigos, colegas, familiares, alunos, enfim pessoas que por algum momento passaram pela minha vida nestes últimos 37 anos.

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

Hoje vou destacar um conteúdo bastante simples e direto, mas muito interesse e bastante útil, que consiste basicamente em como através da linguagem Transact-SQL podemos identificar ou até mesmo descobrir quais portas de rede estão em uso em uma instância ou servidor SQL Server baseadas no protocolo TCP/IP e na versão IPV4 do protocolo IP.

Isso pode parecer algo bastante simples de ser feito, na verdade é mesmo, mas até a versão do SQL Server 2008 R2 SP1 era um pouco chato e até mesmo complexo para se obter esta simples informação, cenário que muito drasticamente a partir da versão 2012 e se mantem presente na versão 2016.

Desta forma, seja bem – vindo ao #14 – Para que serve – Identificando as portas de rede TCP/IP através da DMV – sys.dm_tcp_listener_states.

Introdução

Obter informações sobre as portas de rede utilizadas por uma instância ou servidor SQL Server, por mais simples que parece ser era considerada por muitos profissionais de bancos de dados uma das tarefas mais chatas e até mesmo tediosas pelo simples fato de não existir especificamente uma ferramenta da Microsoft dedicada para este cenário, mesmo assim existem algumas possibilidades que podemos ou não considerar práticas ou inseguras.

A seguir apresento as possibilidades mais conhecidas:

BPCheck: Não pode ser considerada dentre as possibilidades a mais conhecida, muito menos a mais simples, mas sim a mais completa no conjunto de dados retornados para o usuário. O BPCheck – Best Practices and Performance Check, criado em 28-07-2011 por Pedro Lopes (Senior Program Manager for the Microsoft SQL Server Product Group – Tiger Team), com base na versão 2005 do SQL Server e mantido até as versões atuais.

Posso dizer, que este é um daqueles scripts mágicos criados pelos maiores profissionais do SQL Server espalhados pelo mundo, dentre os quais o Pedro Lopes faz parte, o nível de complexidade existente no código fonte deste arquivo comprova o grau de conhecimento e capacidade técnica que este profissional apresenta.

Microsoft SQL Server 2008 e 2008 R2: Microsoft trabalhou e adicionou a partir da versão 2008 R2 SP1 uma forma não muito usual, nem muito interessante de se obter informações sobre as portas de rede fazendo uso da DMV – Dynamic Management View (Visão de Gerenciamento Dinâmico): sys.dm_server_registry, onde era possível coletar informações com base nas chaves de registro do Windows, o que sinceramente não podemos dizer que é algo muito indicado ou até mesmo seguro, mesmo assim era a única forma direta através do Management Studio de se encontrar estas informações. Esta DMV apresenta o seguinte conjunto de colunas:

Nome da coluna Tipo de dados Descrição
registry_key nvarchar(256) Nome da chave do Registro. Permitir valor nulo.
value_name nvarchar(256) Nome do valor da chave. Este é o item mostrado na coluna Nome do Editor do Registro. Permitir valor nulo.
value_data sql_variant Valor dos dados da chave. Este é o valor mostrado na coluna Dados do Editor do Registro para uma determinada entrada. Permitir valor nulo.

Microsoft SQL Server 2012: Talvez pode ser considerada até o presente momento a forma mais de se obter através de uma ferramenta gráfica neste caso o Management Studio as informações relacionadas a portas e protocolos de rede TCP/IP, fazendo-se uso da DMV – Dynamic Management View (Visão de Gerenciamento Dinâmico): sys.dm_tcp_listener_states, introduzida neste versão do SQL Server. Esta DMV apresenta o seguinte conjunto de colunas:

Nome da coluna Tipo de dados Descrição
listener_id int A ID interna do ouvinte. Não permite valor nulo.

Chave primária.

ip_address nvarchar48 O endereço IP do ouvinte que está online e está sendo escutando no momento. IPv4 ou IPv6 é permitido. Se um ouvinte possuir os dois tipos de endereços, eles serão listados separadamente. Um curinga de IPv4, exibido como “0.0.0.0”. Um curinga de IPv6, exibido como “::”.

Não permite valor nulo.

is_ipv4 bit Tipo de endereço IP

1 = IPv4

0 = IPv6

port int O número da porta na qual o ouvinte está escutando. Não permite valor nulo.
Tipo tinyint Tipo de ouvinte, um dos seguintes:

0 = Transact-SQL

1 = Service Broker

2 = Espelhamento do banco de dados

Não permite valor nulo.

type_desc nvarchar(20) Descrição do tipo, um dos seguintes:

TSQL

SERVICE_BROKER

DATABASE_MIRRORING

Não permite valor nulo.

state tinyint O estado do ouvinte do grupo de disponibilidade, um dos seguintes:

1 = Online. O ouvinte está escutando e processando solicitações.

2 = Reinício pendente. o ouvinte está offline, pendente de uma reinicialização.

Se o ouvinte do grupo de disponibilidade estiver escutando na mesma porta que a instância do servidor, esses dois ouvintes sempre terão o mesmo estado.

Não permite valor nulo.

Observação Observação
Os valores desta coluna são oriundos do objeto TSD_listener. A coluna não dá suporte a um estado offline porque, quando o TDS_listener está offline, ele não pode ser consultado para obter o estado.
state_desc nvarchar(16) Descrição do estado, um dos seguintes:

ONLINE

PENDING_RESTART

Não permite valor nulo.

start_time datetime Carimbo de data/hora que indica quando o ouvinte foi iniciado. Não permite valor nulo.

Bom, agora que já conhecemos as possibilidades de se coletar as informações relacionadas a portas e protocolos de rede, vamos colocar a mão na massa ou melhor no teclado e por em prática o uso das DMVs: sys.dm_server_registry e sys.dm_tcp_listener_states.

Exemplos

1 – Identificando a Default Port 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

 

 2 – Identificando a Dynamic Port através da sys.dm_server_registry:

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

 

3 – Obtendo a relação de Listeners, Ports, Protocols e demais dados relacionadas a network através da sys.dm_server_registry:

select Registry_key, Value_Name, Value_Data FROM sys.dm_server_registry

where registry_key like ‘%SuperSocketNetLib%’

Go

 

4 – Identificando a Default Port 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

 

5 – Obtendo a relação de Listeners, Ports e Protocols através da sys.dm_tcp_listener_states:

Select listener_id, ip_address, is_ipv4,

Port, Type, type_desc, state_desc,

start_time

from sys.dm_tcp_listener_states

Go

Show de bola, legal, legal, aqui estão os exemplos, se você obter realmente o uso da DMV sys.dm_server_registry em comparação com a DMV sys.dm_tcp_listener_states pode ser considerado bem mais complexo e confuso, pois torna-se necessário conhecer um pouco da estrutura de chaves de registro do Windows, bem como, o que representa a sequência de valores apresentados na coluna Registry_Key o que para muitos profissionais não é algo são comum de ser entendido.

Referências

https://blogs.msdn.microsoft.com/sql_server_team/programmatically-find-sql-server-tcp-ports/

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-tcp-listener-states-transact-sql

https://msdn.microsoft.com/en-us/library/hh204561.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/2017/03/25/13-para-que-serve/

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

Conclusão

Mesmo com todas as possíveis dificuldades, falta de ferramenta exclusiva ou facilidade para se conseguir obter uma simples informação relacionadas as portas de rede e protocolos, sempre vai existir alguma maneira de se conseguir encontrar o que deseja no Microsoft SQL Server, seja através de um script mágico como o destacado hoje neste post ou através de um recurso não muito usual, independente da maneira que possa ser dentro da estrutura, do coração do SQL Server em suas tabelas internar em conjunto com o uso das DMVs torna-se totalmente viável coletar qualquer tipo de dado desejado.

Neste post, você pode comprovar como é possível encontrar os dados relacionados á protocolos, portas, listeners e demais elementos envolvidos nos processos de network, onde uma simples aplicação, website, aplicativo ou ERP venha a necessitar acessar, consumir e trocar dados via pacotes de rede com o 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.
Nos encontramos em breve, até lá….

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.

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

Short Scripts – Dezembro 2016


Buenas tardes….. Comunidade.

Hoje um dia mais que especial na minha vida e da minha família, meu pai Pedro Galvão esta completando 60 anos de vida, meu deus, como o tempo nos supreende com a sua velocidade.

Quero aproveitar este post para agradeço por este privilégio de poder conviver com uma pessoa tão integra, humilde, trabalhadora, honesta, enfim existem milhares de adjetivos para definir o que meu pai e sua excência em pessoa representa.

O post de hoje

Falando do post de hoje, este é um último post dedicado a sessão Short Scripts no ano de 2016, mas com certeza em 2017 vai estar retornando com toda a força.

Como de costume, e não poderia ser diferente, estamos no final do ano, próximos ao Natal, momento de festa e troca de presentes, lógicamente vou deixar o meu presentinho para vocês, compartilhando os últimos scripts adquiridos neste ano, relacionados aos seguintes assuntos:

  • Calendário anual de feriados;
  • Conversão de Binários para String;
  • CTE e CTE Recursiva;
  • Funções;
  • Extended Stored Procedure XP_ServiceControl;
  • MaxRecursion;
  • Union e Union All; e
  • Monitoramento de Serviços.

Short Scripts

A seguir apresento a relação de Short Scripts de hoje:

— Short Script 1 – Monitorando os serviços do SQL Server através da Extended Stored Procedure – XP_ServiceControl

Set NoCount On
CREATE TABLE #ServicesStatus
(
myid int identity(1,1),
serverName nvarchar(100) default @@serverName,
serviceName varchar(100),
Status varchar(50),
checkdatetime datetime default (getdate())
)
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’MSSQLServer’
update #ServicesStatus set serviceName = ‘MSSQLServer’ where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’SQLServerAGENT’
update #ServicesStatus set serviceName = ‘SQLServerAGENT’ where myid = @@identity
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’msdtc’;
update #ServicesStatus set serviceName = ‘msdtc’ where myid = @@identity;
INSERT #ServicesStatus (Status)
EXEC xp_servicecontrol N’QUERYSTATE’,N’sqlbrowser’
update #ServicesStatus set serviceName = ‘sqlbrowser’ where myid = @@identity
Select * from #ServicesStatus
— Short Script 2 – Realizando a conversão de Binário para String e String para Binário —
— Declarando uma chave legível —
DECLARE @chave  VARCHAR(MAX) =’quechavemalfeita’
— Convertendo para Binário —
DECLARE @dadosBinary VARBINARY(MAX) = CONVERT(VARBINARY(MAX),@chave,0)
SELECT @chave,@dadosBinary
— Observando as diferenças —
SELECT CONVERT(VARCHAR(MAX),@dadosBinary,0) –o Tipo 0 tranforma em texto legivelSELECT CONVERT(VARCHAR(MAX),@dadosBinary,1) — transforma o valor BINARY EM VALOR VARCHAR DEIXANDO O MESMO CONTEUDO

SELECT CONVERT(VARCHAR(MAX),@dadosBinary,2)– transforma o valor BINARY EM VALOR VARCHAR RETIRANDO O 0x NO INICIO
Go

— Short Script 3 – Criando um calendário anual para feriados —
CREATE SCHEMA Calendar
Go
CREATE FUNCTION Calendar.Computus (@Y INT)
RETURNS DATETIME
AS
BEGIN
DECLARE @a INT, @b INT, @c INT, @d INT, @e INT, @f INT, @g INT, @h INT, @i INT, @k INT, @L INT, @m INT
SET @a = @Y % 19
SET @b = @Y / 100
SET @c = @Y % 100
SET @d = @b / 4
SET @e = @b % 4
SET @f = (@b + 8) / 25
SET @g = (@b – @f + 1) / 3
SET @h = (19 * @a + @b – @d – @g + 15) % 30
SET @i = @c / 4
SET @k = @c % 4
SET @L = (32 + 2 * @e + 2 * @i – @h – @k) % 7
SET @m = (@a + 11 * @h + 22 * @L) / 451
RETURN(DATEADD(month, ((@h + @L – 7 * @m + 114) / 31)-1, cast(cast(@Y AS VARCHAR) AS Datetime)) + ((@h + @L – 7 * @m + 114) % 31))
END
GO
CREATE TABLE Calendar.[Calendar] (
[Date] datetime NOT NULL,
[Year] int NOT NULL,
[Quarter] int NOT NULL,
[Month] int NOT NULL,
[Week] int NOT NULL,
[Day] int NOT NULL,
[DayOfYear] int NOT NULL,
[Weekday] int NOT NULL,
[Fiscal_Year] int NOT NULL,
[Fiscal_Quarter] int NOT NULL,
[Fiscal_Month] int NOT NULL,
[KindOfDay] varchar(10) NOT NULL,
[Description] varchar(50) NULL,
PRIMARY KEY CLUSTERED ([Date])
)
GO
ALTER TABLE Calendar.[Calendar]
ADD CONSTRAINT [Calendar_ck] CHECK (  ([Year] > 1900)
AND ([Quarter] BETWEEN 1 AND 4)
AND ([Month] BETWEEN 1 AND 12)
AND ([Week]  BETWEEN 1 AND 53)
AND ([Day] BETWEEN 1 AND 31)
AND ([DayOfYear] BETWEEN 1 AND 366)
AND ([Weekday] BETWEEN 1 AND 7)
AND ([Fiscal_Year] > 1900)
AND ([Fiscal_Quarter] BETWEEN 1 AND 4)
AND ([Fiscal_Month] BETWEEN 1 AND 12)
AND ([KindOfDay] IN (‘HOLIDAY’, ‘SATURDAY’, ‘SUNDAY’, ‘BANKDAY’)))
GO
SET DATEFIRST 1;
WITH Dates(Date)
AS
(
SELECT cast(‘1999’ AS DateTime) Date
UNION ALL
SELECT (Date + 1) AS Date
FROM Dates
WHERE
Date < cast(‘2021’ AS DateTime) -1
),
DatesAndThursdayInWeek(Date, Thursday)
AS
(
SELECT
Date,
CASE DATEPART(weekday,Date)
WHEN 1 THEN Date + 3
WHEN 2 THEN Date + 2
WHEN 3 THEN Date + 1
WHEN 4 THEN Date
WHEN 5 THEN Date – 1
WHEN 6 THEN Date – 2
WHEN 7 THEN Date – 3
END AS Thursday
FROM Dates
),
Weeks(Week, Thursday)
AS
(
SELECT ROW_NUMBER() OVER(partition by year(Date) order by Date) Week,
Thursday
FROM DatesAndThursdayInWeek
WHERE DATEPART(weekday,Date) = 4
)
INSERT INTO Auxiliary.Calendar
SELECT
d.Date,
YEAR(d.Date) AS Year,
DATEPART(Quarter, d.Date) AS Quarter,
MONTH(d.Date) AS Month,
w.Week,
DAY(d.Date) AS Day,
DATEPART(DayOfYear, d.Date) AS DayOfYear,
DATEPART(Weekday, d.Date) AS Weekday,
YEAR(d.Date) AS Fiscal_Year,
DATEPART(Quarter, d.Date) AS Fiscal_Quarter,
MONTH(d.Date) AS Fiscal_Month,
CASE
WHEN (DATEPART(DayOfYear, d.Date) = 1)          — New Year’s Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))-7)  — Palm Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-3)  — Maundy Thursday
OR (d.Date = Auxiliary.Computus(YEAR(Date))-2)  — Good Friday
OR (d.Date = Auxiliary.Computus(YEAR(Date)))    — Easter Sunday
OR (d.Date = Auxiliary.Computus(YEAR(Date))+39) — Ascension Day
OR (d.Date = Auxiliary.Computus(YEAR(Date))+49) — Pentecost
OR (d.Date = Auxiliary.Computus(YEAR(Date))+50) — Whitmonday
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      — Labour day
OR (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     — Constitution day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    — Cristmas day
OR (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    — Boxing day
THEN ‘HOLIDAY’
WHEN DATEPART(Weekday, d.Date) = 6 THEN ‘SATURDAY’
WHEN DATEPART(Weekday, d.Date) = 7 THEN ‘SUNDAY’
ELSE ‘BANKDAY’
END KindOfDay,
CASE
WHEN (DATEPART(DayOfYear, d.Date) = 1)            THEN ‘New Year”s Day’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-7)  THEN ‘Palm Sunday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-3)  THEN ‘Maundy Thursday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))-2)  THEN ‘Good Friday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date)))    THEN ‘Easter Sunday’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+39) THEN ‘Ascension Day’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+49) THEN ‘Pentecost’
WHEN (d.Date = Auxiliary.Computus(YEAR(Date))+50) THEN ‘Whitmonday’
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 1)      THEN ‘Labour day’
WHEN (MONTH(d.Date) = 5 AND DAY(d.Date) = 17)     THEN ‘Constitution day’
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 25)    THEN ‘Cristmas day’
WHEN (MONTH(d.Date) = 12 AND DAY(d.Date) = 26)    THEN ‘Boxing day’
END Description
FROM DatesAndThursdayInWeek d
inner join Weeks w
on d.Thursday = w.Thursday
OPTION(MAXRECURSION 0)
GO
CREATE FUNCTION Auxiliary.Numbers (@AFrom INT, @ATo INT, @AIncrement INT)
RETURNS @RetNumbers TABLE
([Number] int PRIMARY KEY NOT NULL)
AS
BEGIN
WITH Numbers(n)
AS
(
SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo
)
INSERT @RetNumbers
SELECT n from Numbers
OPTION(MAXRECURSION 0)
RETURN;
END
GO
CREATE FUNCTION Auxiliary.iNumbers( @AFrom INT, @ATo INT, @AIncrement INT)
RETURNS TABLE
AS
RETURN( WITH Numbers(n)
AS
(SELECT @AFrom AS n
UNION ALL
SELECT (n + @AIncrement) AS n
FROM Numbers
WHERE
n < @ATo)
SELECT n AS Number from Numbers
)
GO

Links

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

Agradecimento

Mais uma vez obrigado por sua visita, um forte abraço…

Feliz Natal com muita saúde, paz, alegria e esperança. Que 2017 você possa brilhar ainda mais e estar junto com a comunidade.