Arquivo da tag: sys.tables

Material de Apoio – Junho 2018


Olá, boa tarde.

Tudo bem? E ai esta curtindo a Copa do Mundo de Futebol da Rússia? Posso dizer tranquilamente que eu estou curtindo muito todos os jogos e informações possíveis de serem acompanhadas.

Estou aqui mais uma vez procurando colaborar e compartilhar com a comunidade técnica em mais um post da sessão Material de Apoio dedicado exclusivamente ao meu blog.

Espero que você esteja gostando do conteúdo aqui disponibilizado, como também, possa me ajudar a cada vez mais melhorar ainda.

O post de hoje

Seja bem-vindo a mais um post da sessão Material de Apoio, sendo o terceiro do ano de 2018 e de número 157 no total desta sessão.

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 atualmente como Professor de Banco de Dados.

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

  • Acessos de Usuário;
  • Backup;
  • CheckList;
  • Comando Create Procedure;
  • Comando Declare;
  • Database Backup;
  • Disponibilidade;
  • DMV sys.dm_db_index_usage_stats;
  • DMV Sys.dm_os_sys_info;
  • Dymanic Management View;
  • Free Disk Space;
  • Função DateAdd;
  • Função Month;
  • Heap Table;
  • Índices Clustered; Reinicialização de Servidores;
  • Índices;
  • Junção de Tabelas;
  • Leitura e Escrita; System Stored Procedure;
  • Logins;
  • Set DateFirst;
  • Set Language;
  • Set NoCount;
  • System Stored Procedure SP_MSForeachTable;
  • System Table sys.allocation_units; System Table sys.indexes;
  • System Table sys.partitions;
  • System Table sys.schemas;
  • System Table sys.tables;
  • Usuários; e
  • Variáveis.

Espero que este conteúdo possa lhe ajudar em seus atividades profissionais e acadêmicas. 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.

Material de Apoio

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

1 – Material de Apoio – Junho – 2018 – Determine Free Space Prior to SQL Server Backup.sql

2 – Material de Apoio – Junho – 2018 – DMV – Sys.dm_os_sys_info.sql – Identificando o último restart realizado na instância ou servidor.sql

3 – Material de Apoio – Junho – 2018 – Identificando a relação de Heap Tables.sql

4 – Material de Apoio – Junho – 2018 – Identificando a última segunda – feira e o último dia do mês.sql

5 – Material de Apoio – Junho – 2018 – Identificando o último usuário que acesso a tabela.sql

6 – Material de Apoio – Junho – 2018 – Obtendo a relação de últimos acessos de leitura e escrita por banco de dados.sql

7 – Material de Apoio – Junho – 2018 – SP_msforeachtable – Criando índices clustered em todas as tabelas através de uma coluna específica.sql

Fique a vontade para copiar, editar, compartilhar e distribuir estes arquivos com seus contatos, aproveite se possível deixe seu comentário, críticas, sugestões e observações.

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:

https://pedrogalvaojunior.wordpress.com/2018/04/05/material-de-apoio-abril-2018/

https://pedrogalvaojunior.wordpress.com/2018/02/13/material-de-apoio-fevereiro-2018/

https://pedrogalvaojunior.wordpress.com/2017/11/04/material-de-apoio-novembro-2017/

https://pedrogalvaojunior.wordpress.com/2017/08/08/material-de-apoio-agosto-2017/

Agradecimento

Quero agradecer imensamente a sua visita, sinto-me honrado e orgulhoso de contar com a sua presença.

Não deixe de acessar os outros posts das demais sessões, o próximo post desta sessão será publicado no mês de agosto, até lá continue curtindo sua vida, aproveite este grande momento de confraternização mundial que a Copa do Mundo de Futebol nos proporciona a cada quatro anos.

Um forte abraço, muita saúde, sucesso e vamos em frente…

Anúncios

Dica do Mês – Microsoft SQL Server 2017 – SQL Graph Databases


Muito bom dia…… Salve amantes de banco de dados.

Tudo bem? Este é mais um post da sessão Dica do Mês, sessão dedicada a compartilhar mensalmente dicas, novidades, curiosidades e demais informações relacionadas ao Microsoft SQL Server, Banco de Dados e Tecnologias de Banco de Dados.

No post de hoje, quero compartilhar com você uma das maiores novidades implementadas na última versão do Microsoft SQL Server, neste caso, a versão 2017 lançada oficialmente em outubro de 2017 e muito destacada aqui no meu blog em diversos posts.

Destacando um pouco sobre o post, quando se referimos a banco de dados, normalmente pensamos em uma estrutura organizada basicamente em tabelas, colunas, chaves primárias, chaves estrangeiras e relacionamentos. Mas a partir do Microsoft SQL Server 2017 temos uma nova possibilidade de elaborar uma estrutura de banco de dados saindo um pouco deste tradicional cenário conhecimento como modelo relacional e sim partindo para o chamado modelo grafos ou banco de dados em grafos. Talvez em algum momento você já deve ter ouvido falar um pouco sobre esta forma de modelagem.

Pois bem, neste nova versão o time de engenheiros do SQL Server adicionaram ao conjunto de novas funcionalidades (features) o SQL Graph Databases ou simplesmente Banco de Dados em Grafos, isso mesmo banco de dados no formato de grafos, parece ser algo muito diferente do que estamos acostumados a fazer, na verdade não é bem assim, e você vai poder conhecer um pouco mais sobre este recurso, suas características e curiosidades no decorrer deste post.

Esta curioso em saber um pouco mais sobre esta nova feature? Eu estou, e não vejo a hora de poder dividir com você um pouco do vasto conteúdo relacionado com este recurso. Então, vamos em frente, vou tentar mitigar a sua curiosidade e ao mesmo também satisfazer os meus objetivos. Sendo assim, seja bem vindo ao post – Dica do Mês – Microsoft SQL Server 2017 – SQL Graph Databases.

Você vai se surpreender com este novo recurso e suas possibilidades relacionadas com modelagem de banco de dados.


Introdução

O SQL Graph Database é uma nova forma de se estruturar um banco de dados criado no Microsoft SQL Server 2017, tendo como base um estrutura formada por uma coleção de tabelas de nó (Node Table) e  tabelas de borda (Edge Table).

Basicamente um nó representa uma entidade — por exemplo, uma pessoa ou uma organização e uma borda representa uma relação entre os dois nós que ele conecta. As tabelas de nó ou borda podem ser criadas em qualquer esquema em um banco de dados, mas todas pertencem a uma estrutura de grafos representada de forma lógica.

Os bancos de dados na estrutura de grafos são úteis quando o aplicativo tem relacionamentos complexos de muitos para muitos e precisamos analisar as relações complexas.

Algumas das características importantes de um bancos de dados na estrutura de grafos:

  • Bordas (edge) ou relacionamentos(node) são entidades de primeira classe em um banco de dados de grafos e podem ter atributos ou propriedades associadas a eles;
  • Uma única edge table pode unir flexivelmente vários nós em um banco de dados de grafos;
  • Demonstrar a relação de padrões e consultas de navegação de vários saltos facilmente; e
  • Demonstrar o encerramento transitivo de dados e as consultas polimórficas facilmente.

A Figura 1 abaixo apresenta a estrutura básico do SQL Graph Databases e seus principais componentes:

Architecture of SQL Server 2017 Graph Database
Figura 1 – Estrutura básica do SQL Graph Databases.

Analisando a Figura 1 apresentada acima, podemos dizer que um banco de dados na estrutura de grafos é um tipo de banco de dados cujo conceito é baseado em nós e bordas. Este novo tipo de bancos de dados, denominada de grafos, baseiam-se na teoria dos grafos (um grafo é um diagrama de pontos e linhas conectados aos pontos), respeitando a seguinte estrutura:

  1. Os nós representam dados ou entidade e bordas representam conexões entre nós; e
  2. As bordas são propriedades que podem estar relacionadas a nós, essa capacidade nos permite mostrar interações mais complexas e profundas entre os nossos dados.

Elementos básicos

A seguir destaco os elementos básicos que compõem a estrutura do SQL Graph Databases:

Node Table
Representa uma entidade em um esquema de grafos. Sempre que criamos uma tabela de nós, juntamente com as colunas definidas pelo usuário, uma coluna implícita $node _id é criada, o que identifica exclusivamente um determinado nó no banco de dados.

Os valores na coluna $node _id são gerados automaticamente e são uma combinação de object_id dessa tabela de nós e um valor bigint gerado internamente. No entanto, quando a coluna $node _id é selecionada, um valor calculado na forma de uma cadeia de caracteres JSON é exibido.
Além disso, $Node _id é uma coluna pseudo, que mapeia para um nome interno com String hex nele. Quando selecionamos $node _id da tabela, o nome da coluna aparecerá como $node _id_ hex_string.

É recomendável que os usuários criem uma restrição ou índice exclusivo na coluna $node _id no momento da criação da tabela de nós, mas se um não for criado, um índice padrão exclusivo não clusterizado será criado automaticamente.

Edge Table
Como mencionado anteriormente, uma tabela de borda(Edge Table) representa uma relação em um grafos. As bordas são sempre direcionadas e conectam dois nós.

Uma tabela de borda permite que os usuários modelem relacionamentos muitos-para-muitos no grafos, esta mesma tabela pode ou não ter quaisquer atributos definidos pelo usuário.

$Edge _id
A primeira coluna na tabela de borda representa $Edge _id que identifica exclusivamente uma aresta fornecida no banco de dados. O valor da coluna edge_id é gerado com a combinação de object_id da tabela de borda e um valor bigint gerado internamente.

No entanto, quando selecionamos a coluna $Edge _id, ela é exibida como a seqüência de caracteres JSON que é calculada a partir do valor da coluna.

$from _id
Coluna que armazena o $node _id do nó, de onde a borda é originada. Semelhante ao $Edge _id este é também um pseduo e pode ser usado como $from _id no entanto, o nome da coluna inclui Strings hex nele.

$to _id
Armazena o $node _id do nó, no qual a borda termina. Comportamento desta coluna em também como por $Edge _id e $from coluna _id.

Funções

Existem algumas funções adicionadas a linguagem Transact-SQL, que visam ajudar os usuários a extrair informações das colunas geradas. Abaixo estão as funções:

OBJECT_ID_FROM_NODE_ID: Função que permite extrair o object_id de um node_id. Precisamos passar node_id para esta função e ele retornará o object_id, também podemos obter o nome do objeto do object_id.

GRAPH_ID_FROM_NODE_ID: Função que permite extrair o GRAPH_ID de um node_id. Precisamos passar node_id para esta função e ele retornará o object_id.

NODE_ID_FROM_PARTS: Através desta função podemos construir um node_id de um object_id e um graph_id.

OBJECT_ID_FROM_EDGE_ID: Função utilizada para extrair um object_id de um EDGE_ID.

GRAPH_ID_FROM_EDGE_ID: Função utilizada para identificar um GRAPH_ID de EDGE_ID.

EDGE_ID_FROM_PARTS: Função que nos permite identificar o id utilizada entre as conexões de EDGE_ID de object_id e identidade.

Tabelas de Sistemas e Metadados

Sys.Tables – Novas colunas foram adicionadas ao sys.tables para identificar se uma tabela é um nó ou uma borda, conforme apresenta a Tabela 1 abaixo:

Column Name Data Type Description
is_node bit 1 = this is a node table
is_edge bit 1 = this is an edge table

Tabela 1 – Novas colunas adicionadas a system table sys.tables.

Sys.Columns – Novas colunas foram adicionadas ao sys.tables para indicar o tipo da coluna em tabelas de nó e borda, permitindo o relacionamento entre as systems tables sys.columns e sys.tables. A Tabela 2 abaixo apresenta a relação de novas colunas adicionadas a sys.columns:

Column Name Data Type Description
graph_type int Internal column with a set of values.

The values are between 1-8 for graph columns and NULL for others:

1 – GRAPH_ID
2 – GRAPH_ID_COMPUTED
3 – GRAPH_FROM_ID
4 – GRAPH_FROM_OBJ_ID
5 – GRAPH_FROM_ID_COMPUTED
6 – GRAPH_TO_ID
7 – GRAPH_TO_OBJ_ID
8 – GRAPH_TO_ID_COMPUTED

graph_type_desc nvarchar(60) internal column with a set of values

Tabela 2 – Novas colunas adicionadas a system table sys.columns.

Nossa, quanta coisa nova foi adicionado ao Microsoft SQL Server a partir desta nova funcionalidades, como também, diversas mudanças internadas como de costume também foram realizadas no produto afim de possibilitar o uso destas e outras funcionalidades, algo comumente realizado a cada nova versão.

Vamos então conhecer de forma prática o SQL Graph Databases e entender como estes elementos podem ser utilizados através do cenário que estaremos implementando a partir de agora.

Implementando o SQL Graph Databases

Se estamos nos referindo ao um banco de dados de grafos, nada melhor do que representar a estrutura que será utilizada em nosso cenário de exemplo através de um Diagrama conforme ilustra a Figura 2 abaixo:

Figura 2 – Diagrama – Estrutura utilizada para o exemplo.

Os nós são SQL Server, Azure e Windows e segue (bordas) fornecem conexões entre nós. Este modelo de banco de dados não pode ser tratado como uma alternativa a um modelo de banco de dados relacional, mas confrontados com alguns problemas específicos, o modelo de banco de dados em grafos pode ser alternativo e efetivo.

Observações: Se você olhar para o diagrama de perto, talvez você pode projetar este modelo de dados em um banco de dados relacional por junções, mas imagine que se você tiver um monte de nós e bordas, em seguida, quantas junções você vai precisar? Outra consideração importante se relaciona na forma de como este projeto funcionaria? Por esta razão, ao lidar com alguns problemas de negócios, precisamos de um banco de dados representado na teoria de grafos.

Um cenário muito interessante para a adoção de um banco de dados em grafos a ser considerado são meios de comunicação social. Por exemplo, há um monte de ações sociais, cada ação social cria uma marca. Quando combinamos estas marcas, parece uma teia de aranha. O modelo de banco de dados em grafos é ideal para armazenar esse tipo de dados.

Criando o ambiente

Para nossa prática vamos trabalhar com o seguinte ambiente:

  • Database – SQLGraphDatabase;
  • Node Table – Products; e
  • Edge Table – Microsoft.

Vamos começar criando nosso banco de dados e a node table através do Bloco de Código 1 apresentado na sequência:

— Bloco de Código 1 – Criando Banco de Dados e Node Table —

— Criando o Banco de Dados —
Create Database SQLGraphDatabases
Go

— Acessando o Banco de Dados —
Use SQLGraphDatabases
Go

— Criando a Node Table Products —
Create Table Products
(ProductID TinyInt Primary Key,
ProductName Varchar(100)
) As Node
Go

Note que instrução Create Table especificamos ao final da mesma a palavra chave Node, desta forma, o Microsoft SQL Server vai entender que nossa tabela será um nó e posteriormente estará relacionada com no mínimo uma borda.

O próximo passo é realizar a inserção de dados na tabela Products, para isso vamos utilizar o Bloco de Código 2 apresentado a seguir:

— Bloco de Código 2 – Inserindo dados na Node Table Products —

— Inserindo dados na Node Table Products —
Insert Into Products
Values (1,’SQL Server’),
(2,’Azure’),
(3,’Windows’)
Go

— Consultando os dados —
Select ProductId, ProductName from Products
Go

Você poderá notar que após a execução do comando Select teremos o resultado das três linhas de registros lógicos adicionadas a node table Products apresentados em tela, para que seja possível apresentar toda estrutura da tabela incluindo o $Node_Id, vamos então utilizar um simples e conhecido Select * (técnica não recomendável, mas para este cenário será útil para apresentar de maneira rápida a estrutura da node table Products).

— Apresentando toda estrutura da node table Products e seus respectivos dados —

Select * From Products
Go

A Figura 3 abaixo ilustra o retorno dos dados obtidos após a execução do Select acima:

Figura 3 – Estrutura da node table Products, incluindo $Node_ID e dados.

Continuando a nossa caminhada, vamos agora criar nossa tabela borda (edge table) denominada Microsoft, sendo esta o elemento utilizado para estabelecer as ligações entre os dados da node table para com a edge table, desta forma utilizaremos o Bloco de Código 3 apresentado a seguir:

— Bloco de Código 3 – Criando a Edge Table Microsoft —

— Criando a Edge Table Microsoft —
Create Table Microsoft As Edge
Go

— Consultando a estrutura da Edge Table Microsoft —
Select * from Microsoft
Go

Pronto nossa tabela borda esta criada, nosso penúltimo passo será adicionar os dados oriundos da node table Products, estabelecem assim as relações entre ambos os elementos, para tal procedimentos utilizaremos o Bloco de Código 4 apresentado a seguir:

— Bloco de Código 4 – Inserindo os dados na Edge Table Microsoft —

— Azure com SQL Server —
Insert Into Microsoft ($from_id , $to_id )
Values ((Select $node_id from Products where ProductId=2),
(Select $node_id from Products where ProductId=1))
Go

— Windows com SQL Server —
Insert Into Microsoft ($from_id ,$to_id )
Values ((Select $node_id from Products where ProductId=3),
(Select $node_id from Products where ProductId=1))
Go

— Windows com Azure —
Insert Into Microsoft ($from_id ,$to_id )
Values ((Select $node_id from Products where ProductId=3),
(Select $node_id from Products where ProductId=2))
Go

— Consultando os dados inseridos na Edge Table Microsoft —
Select * from Microsoft
Go

Muito bom, acabamos de estabelecer as ligações entre nossa node table e a edge table, note que para realizar este vinculo entre os dados oriundas da node table Products para a edge table Microsoft, utilizamos os valores da coluna ProductID existente na tabela de Products, como elemente chave a ser inserido nas colunas $from_id e $to_id no momento da criação da edge table Microsoft.

Figura 4 a seguir ilustra os dados inseridos na edge table Microsoft e apresentados no formato de string JSON:

Figura 4 – Dados inseridos na edge Table Microsoft.

Agora falta muito pouco e chegamos ao último passo que consiste justamente em identificar as conexões estabelecidas com base no Bloco de Código 4 processado anteriormente, onde através da nova função Match() em conjunto com seus operadores ‘-‘ ou ‘->’ podemos definir o caminho para encontrar os dados respeitando a estrutura de nós e bordas. Para tal procedimento executaremos o Bloco de Código 5 apresentado abaixo:

— Bloco de Código 5 – Identificando as conexões entre os dados —

— Utilizando a função Match(), encontrando as conexões do ProductName = Azure —
Select Concat(Products.ProductName,’ –> ‘, ProductsDetails.ProductName) As Connections
From Products, Microsoft, Products ProductsDetails
Where Match(Products-(Microsoft)->ProductsDetails)
And Products.ProductName = ‘Azure’
Go

— Utilizando a função Match(), encontrando as conexões do ProductName = Windows —
Select Concat(Products.ProductName,’ –> ‘, ProductsDetails.ProductName) As Connections
From Products, Microsoft, Products ProductsDetails
Where Match(Products-(Microsoft)->ProductsDetails)
And Products.ProductName = ‘Windows’
Go

Após a execução do Bloco de Código 5 apresentado anteriormente o Management Studio deverá retornar um resultado similar ao apresentado pela Figura 5 abaixo:

Figura 5 – Conexões de dados estabelecidas com base nas colunas $from_id  e $to_id.

Incrível, chegamos ao final de mais um post da sessão Dica do Mês, tenho a certeza que você gostou do conteúdo apresentado hoje neste post.

Vale ressaltar que alguns pontos não foram abordados neste post, dentre eles como utilizar um índice clusterizado ou não clusterizado, como também, a maneira que uma Node Table e Edge Table são apresentadas na estrutura de um banco de dados através do Management Studio.

No que diz respeito ao novo modelo de banco de dados, tenha a certeza que a minha missão foi cumprida, principalmente no entendimento de como esta uma nova forma de representação baseada em grafos poderá nos ajudar a estabelecer uma nova técnica de relacionamento entre nossas tabelas e seus respectivos dados, indo muito além dos tradicionais conceitos dentre eles: chaves primárias e estrangeiras, caminhando para uma nova proposta com base no conceito de relacionamento lógico de dados.

Antes de encerrarmos, gostaria de contar com a sua participação neste post, respondendo a enquete abaixo:


Referências

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-sample

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-architecture

https://docs.microsoft.com/en-us/sql/relational-databases/graphs/sql-graph-overview

https://www.red-gate.com/simple-talk/sql/sql-development/sql-server-graph-databases-part-1-introduction/

https://www.sqlshack.com/implement-graph-database-sql-server-2017/

https://www.mssqltips.com/sqlservertip/4883/sql-server-2017-graph-database-example/

 

Posts Anteriores

https://pedrogalvaojunior.wordpress.com/2018/01/24/dicadomes-sqlservertoolsuiteintroduction/

https://pedrogalvaojunior.wordpress.com/2017/11/01/dicadomessql2017novascolunasinternas/

https://pedrogalvaojunior.wordpress.com/2017/08/26/dica-do-mes-simulando-a-insercao-de-uma-massa-de-dados-aleatoria/

https://pedrogalvaojunior.wordpress.com/2017/05/23/conhecendo-e-aplicando-o-uso-de-atualizacao-de-estatisticas-incrementais/

Conclusão

Neste post, conhecemos um pouco sobre este novo recursos SQL Graph Databases implementado a partir da versão 2017 do Microsoft SQL Server.

O SQL Server Graph é um recurso fantástico que nos permite implementar dentro da mesma estrutura de banco de dados existente no Microsoft SQL Server dois modelos de relacionamento de dados totalmente diferentes.  Através desta arquitetura híbrida temos a capacidade de utilizar recursos do mecanismo do SQL Server com um banco de dados na estrutura de grafos de maneira muito similar para não se dizer parecida com os recursos aplicados no modelo relacional, com base, na linguagem Transact-SQL nos dando todo suporte a consultas para este novo formato.

O banco de dados baseados na estrutura de grafos (SQL Graph Databases) apresentam como toda nova tecnologia algumas limitações técnicas neste momento, que tendem nas próximas versões do produto evoluírem ou até mesmo deixar de existir, algo que neste momento não podem ser consideradas limitações que impossibilitem a sua adoção ou uso em novos projetos de bancos de dados, como também, não se tornem  limitações a outros recursos excepcionais no existentes no Microsoft SQL Server 2017, mesmo assim podemos reconhecer que este novo modelo de banco de dados, pode ser considerada uma tecnologia convincente e promissora.

Agradecimentos

Agradeço a você por sua atenção e visita ao meu blog. Fique a vontade para enviar suas críticas, sugestões, observações e comentários.

Um forte abraço, nos encontramos no próximo post a ser publicado no mês de abril.

Viva o Microsoft SQL Server 2017 e suas novas funcionalidades…

Um forte abraço, sucesso e até o próximo post.

#16 – Para que serve


Fala galera, boa noite.

Último dia do mês de setembro e como costumo dizer o tempo esta passando a cada dia de uma maneira mais rápida que não conseguimos nos deparar com tudo o que acontece ao nosso redor.

São exatamente 22:07 hrs da noite de um sábado que já esta chegando ao fim e com ele o final também de mais um mês de muita luta e trabalho neste ano de 2017, eita ano complicado.

Mas se você esta lendo este post é porque de alguma maneira acabou chegando até aqui, muito obrigado por sua visita, sendo este o post de número 16 da sessão Para que serve, já estava passando da hora de publicar mais um conteúdo exclusivo para esta sessão.

O conteúdo de hoje é um assunto que eu pouco trabalhei em toda minha carreira de DBA, olha que já fazem 17 anos que venho me dedicando diariamente a esta função, mas o recurso que será destacado hoje não é assim “tão velhinho” como eu (kkkkk).

Hoje vou destacar um pouco sobre índices, ai você pode se perguntar, poxa vida você pouco trabalhou com índices, como assim? Pois é, a resposta tem uma pequena parte de verdade, o tipo de índice ao qual eu me refiro pois adicionado ao Microsoft SQL Server a partir da versão 2012, agora acredito que ficou fácil saber de qual índice estou me referindo, se você ainda não se lembrou ou não se atentou para a dica, estou me referindo ao ColumnStore Index. Agora, tenho a certeza que você se lembrou desta nova forma de armazenamento de dados criada pelo time de engenheiros do Microsoft SQL Server a partir da versão 2012 e que ao longo das próximas versões sofreu diversas atualizações e melhorias.

Se a sua resposta é não ou sinceramente você não conhece esta “nova maneira” de trabalhar com índice, não fique preocupado, como de costume vou fazer uma breve introdução sobre este recurso, como também, vamos fazer alguns comparativos em relação a espaço em disco ocupado e custo de processamento em relação aos tradicionais índices Clustered e NonClustered, sendo estes conhecidos como índices Row Store.

Então chegou a hora de conhecer um pouco mais sobre este recurso, desta forma, seja bem vindo ao #16 – Para que serve – Algumas características e comportamentos do ColumnStore Index.

Introdução

Segundo a documentação oficial do Microsoft SQL Server,  índice columnstore é o padrão para armazenar e consultar grandes tabelas de fatos de repositório de dados. 

Um columnstore index é uma tecnologia para armazenamento, recuperação e gerenciamento de dados usando um formato de dados colunar, chamado columnstore.

Ele usa armazenamento de dados baseado em coluna e processamento de consultas para alcançar um desempenho de consulta até 10 vezes melhor no data warehouse em relação ao armazenamento tradicional orientado por linha e até 10 vezes mais compactação de dados em relação ao tamanho dos dados descompactados.

A partir do SQL Server 2016, os índices columnstore permitem a análise operacional, a capacidade de executar análises de alto desempenho em tempo real em uma carga de trabalho transacional. 

ColumnStore Vs RowStore

Um columnstore são dados logicamente organizados como uma tabela com linhas e colunas e fisicamente armazenados em um formato de dados com reconhecimento de coluna.

Um rowstore são dados logicamente organizados como uma tabela com linhas e colunas e fisicamente armazenados em um formato de dados com reconhecimento de linha. No SQL Server, rowstore refere-se à tabela em que o formato de armazenamento de dados subjacente é um heap, um índice clusterizado ou uma tabela com otimização de memória.

Característica 1: Por padrão até a versão 2012 todo e qualquer índice do tipo Clustered e NonClustered ao ser criado era associado ao mesmo uma estrutura de armazenamento de dados utilizando a tecnologia de RowStore, neste caso, ao invés de conter somente a coluna relacionada ao índice o mesmo possuía a linha por completa o que acarretava um aumento considerável de espaço em disco, como também, um custo de processamento maior para o SQL Server quando o dado solicitado era pesquisado.

Seguindo em frente, já sabemos um pouco sobre esta nova maneira de se armazenar dados contidos em um índice, característica muito importante e que pode nos trazer grandes benefícios.

Quais podem ser os outros benefícios ou características nativas existentes no ColumnStore Index que podem nos fazer adotar este tipo de tecnologia? Esta e muitas outras perguntas que eu mesmo sempre fiz em relação á este tipo de índice serão respondidas a seguir, através do ambiente que estaremos utilizando neste post.

Ambiente

Para que possamos entender ainda mais sobre o ColumnStore Index, nada melhor do que colocar em prática este recurso, para tal vamos começar a montar nosso ambiente de teste, composto por três tabelas similares denominadas:

  • MassaDeDadosCompressaoPorRow;
  • MassaDeDadosCompressaoPorPage; e
  • MassaDeDadosCompressaoColumnStore.

Estes três tabelas serão formadas por duas simples colunas denominadas:

  • Record; e
  • Number.

Onde a coluna Record será utilizada como identificar das linhas de registros que estão sendo manipuladas, sendo seu valor preenchido de forma automática através da propriedade Identity().

A coluna Number corresponde ao número que será inserido de forma aleatório neste coluna, esta será a coluna que iremos utilizar para a construção dos respectivos índices em cada tabela.

Vamos então através do Bloco de Código 1 realizar a criação de cada tabela, suas colunas e seus respectivos índices. Vale ressaltar que além de índices nas tabelas MassaDeDadosCompressaoPorRow e MassaDeDadosCompressaoPorPage estamos também utilizando compressão de dados aplicada a linha e compressão de dados aplicada a página de dados respectivamente. 

A seguir apresento o Bloco de Código 1:

— Bloco de Código 1 —

Use TempDB
Go

Create Table dbo.MassaDeDadosCompressaoPorRow
(Record Bigint Identity(1,1),
 Number BigInt
 Index IND_MassaDeDadosCompressaoPorRow Clustered (Number))
WITH (DATA_COMPRESSION = ROW)
Go

Create Table dbo.MassaDeDadosCompressaoPorPage
(Record Bigint Identity(1,1),
 Number BigInt
 Index IND_MassaDeDadosCompressaoPorPage Clustered (Number))
WITH (DATA_COMPRESSION = PAGE)
Go

Create Table dbo.MassaDeDadosCompressaoPorColumnStore
(Record Bigint Identity(1,1),
 Number BigInt,

 INDEX IND_MassaDeDadosCompressaoPorColumnStore Clustered  ColumnStore)
Go

Por enquanto nada de muito inovador ou diferente do que você pode estar acostumado a fazer, mas uma segunda característica do ColumnStore Index pode ser identificada após a criação das tabelas.

Característica 2: Ao realizar a criação de um índice ColumnStore, o Storage Engine mecanismo utilizado pelo Microsoft SQL Server para armazenamento físico de dados, realiza a alocação de um espaço mínimo inicial para armazenar a estrutura base da coluna relacionada ao índice, ou seja, mesmo não possuindo não dado armazenado no ColumnStore um pequeno espaço em disco é ocupado por sua estrutura e respectiva coluna. Para comprovar esta característica utilizamos o Bloco de Código 2 apresentado abaixo:

— Bloco de Código 2 —

Select s.Name As SchemaName, t.Name As TableName, p.rows As RowCounts,
            Cast(Round((Sum(a.used_pages) / 128.00), 2) As Numeric(36,2)) As Used_MB,
             Cast(Round((Sum(a.total_pages) – SUM(a.used_pages)) / 128.00, 2) As Numeric(36, 2)) As Unused_MB,
            Cast(Round((Sum(a.total_pages) / 128.00), 2) As Numeric(36, 2)) As Total_MB
From sys.tables t Inner Join sys.indexes i
                                 On t.OBJECT_ID = i.object_id
                                Inner Join sys.partitions p
                                 On i.object_id = p.OBJECT_ID And i.index_id = p.index_id
                                Inner Join sys.allocation_units a
                                On p.partition_id = a.container_id
                                 Inner Join sys.schemas s
                                 On t.schema_id = s.schema_id
Where t.object_id >1
Group By t.Name, s.Name, p.Rows
Order By s.Name, t.Name
Go 

Agora que você executou o Bloco de Código 2, deve ter obtido como resultado ao similar ao apresentado na Figura 1 a seguir:


Figura 1 – Espaço ocupado pelo ColumnStore Index mesmo são a existência de linhas de registro.

Legal, mas ainda temos algumas coisas para analisar em relação ao ColumnStore Index, nosso próximo passo será realizar o armazenamento de uma massa de dados em cada tabela, aonde estará identificando o tempo de processamento decorrido para inserir um milhão de linhas de registro, valor hoje considerado pouco para se trabalhar com qualquer banco de dados e principalmente para estruturas ColumnStore, mas o suficiente para nossas análises e comparações.

Vamos então trabalhar com o Bloco de Código 3 a seguir:

— Bloco de Código 3 —

— Inserindo 1.000.000 na Tabela MassaDeDadosCompressaoPorRow –|

Declare @Contador Int = 1

While @Contador <=1000000
Begin
Insert Into dbo.MassaDeDadosCompressaoPorRow (Number)
Values (Rand()*1000000000000)

Set @Contador +=1
End
Go

— Inserindo 1.000.000 na Tabela MassaDeDadosCompressaoPorPage —

Declare @Contador Int = 1

While @Contador <=1000000
Begin
Insert Into dbo.MassaDeDadosCompressaoPorPage (Number)
Values (Rand()*1000000000000)

Set @Contador +=1
End
Go

— Inserindo 1.000.000 na Tabela MassaDeDadosCompressaoPorColumnStore —

Declare @Contador Int = 1

While @Contador <=1000000
Begin
Insert Into dbo.MassaDeDadosCompressaoPorColumnStore (Number)
Values (Rand()*1000000000000)

Set @Contador +=1
End
Go

Neste momento nossas tabelas já estão totalmente populadas com 1.000.000 de linhas de registros armazenadas em cada uma delas, vamos então identificar mais duas características presentes no ColumnStore Index, começando pela número 3.

Característica 3: ColumnStore Index apresentam em alguns cenários de armazenamento de dados dependendo do tipo de dados utilizado por ele uma ligeira perda de performance em comparação com o mesmo tipo de dados utilizado em índices que utilizam tecnologia RowStore.

Para comprovar e ajudar a entender esta diferença de comportamento apresento a seguir a Tabela 1 que exibe os dados coletados durante os três processos de inserir realizado pelo Bloco de Código 3 declarado anteriormente.

Tabela Operação Tempo Decorrido
MassaDeDadosCompressaoPorRow Insert 00:00:22
MassaDeDadosCompressaoPorPage Insert 00:00:22
MassaDeDadosCompressaoPorColumnStore Insert 00:00:28

Tabela 1 – Comparativo de tempo decorrido para inserção de 1.000.000 de linhas de registro em cada tabela.

Nossa próxima característica esta relacionada ao espaço físico ocupado em disco para cada índice de acordo com a tecnologia de armazenamento utilizada e a forma de compressão aplicada.

Característica 4: Tabelas que possuem índice do tipo ColumnStore ocupam um espaço físico em disco infinitamente menor que tabelas que possuem índices RowStore, o que representa que o espaço ocupado em disco por índices que utilizam tecnologia ColumnStore podem apresentar uma economia de espaço em disco entre 70 e 90%, algo que devemos considerar em muito quando vamos trabalhar com um volume consideravelmente grande de dados.

A Tabela 2 apresentada abaixo ilustra esta diferença de espaço em disco ocupada por cada tabela:

Dados coletados pelo SP_SpaceUsed
Tabela Linhas Espaço Reservado  Dados Índices Não Utilizado
dbo.MassaDeDadosCompressaoPorRow 1000000 18592 KB 18448 KB 104 KB 40 KB
dbo.MassaDeDadosCompressaoPorPage 1000000 17312 KB 17192 KB 96 KB 24 KB
dbo.MassaDeDadosCompressaoPorColumnStore 1000000 28904 KB 28696 KB 144 KB 64 KB

Tabela 2 – Dados coletados pela System Stored Procedure SP_SpaceUsed.

Observação: Se analisarmos os dados da Tabela 2 podemos notar que a compressão por página de dados para este cenário de 1.000.000 de linhas de registro foi um pouco superior em relação a economia de espaço em disco se comparada com a compressão por linha de dados, essa é uma característica interessante quando trabalhamos com compressão de dados fazendo uma análise de minimal space entre Row Compression e Page Compression.

Uma outra possibilidade para notar esta diferença considerável de espaço alocado, reservado e ocupado fisicamente em disco poderia ser obtido através de uma nova execução do Bloco de Código 2 apresentado anteriormente, para tal a Tabela 3 apresenta o resultado desta nova execução:

Dados coletados através do Bloco de Código 2
Tabela Linhas Espaço Ocupado em MBs Espaço Não Utilizado em MBs Total MBs
MassaDeDadosCompressaoPorRow 1000000 18.12 0.04 18.16
MassaDeDadosCompressaoPorPage 1000000 16.88 0.02 16.91
MassaDeDadosCompressaoPorColumnStore 1000000 0.02 0.05 0.07

Tabela 3 – Dados coletados através da execução do Bloco de Código 2.

Observação: No que diz respeito a minimal space quando trabalhamos com o ColumnStore Index em comparação com Row Compression ou Page Compression a diferença é extremamente assustadora, aonde o ColumnStore Index aplica uma técnica de armazenamento de dados muito interessante que possibilita esta ganho de espaço e consequentemente ganho de performance. Esta técnica é conhecida como Row Group.

Estamos quase no final, vamos agora identificar mais uma importante característica presente em um ColumnStore Index, relacionada a performance algo que este tipo de tecnologia apresenta como principal vantagem e benefícios em relação a índices RowStore ainda mais quando trabalhamos com grandes volumes de dados em ambientes relacionais.

Nossa análise será iniciada através da execução da Bloco de Código 4 declarado abaixo, observe que estaremos habilitando os contadores de estatísticas de processamento relacionados a Tempo e Input/Output denominados:

Através do uso destes contadores o Management Studio ira nos retornar na guia de mensagens um conjunto de dados relacionados ao tempo de processamento, quantidade de leituras lógicas e físicas demandadas por cada transação que estaremos processando, com base, nestes e outros dados poderemos identificar quando será o diferença do ColumnStore Index em comparação com os outros índices existentes em nossas tabelas.

Outro elemento importante que estaremos utilizando, consiste na quantidade de linhas pesquisadas em cada tabela, você poderá notar que para cada Select declarado no Bloco de Código 4 estaremos filtrando e limitando nosso result set na faixa de valores existentes entre as linhas de registro 525.000 e 789.000.

Vamos então executar o Bloco de Código 4, recomendo que faça a execução de cada select de forma separada, anote os dados retornados pelos contadores Time e IO:

— Bloco de Código 4 —

Set Statistics Time On
Set Statistics IO On
Go

Select * from dbo.MassaDeDadosCompressaoPorRow
Where Record Between 525000 And 789000
Go

Select * from dbo.MassaDeDadosCompressaoPorPage
Where Record Between 525000 And 789000
Go

Select * from dbo.MassaDeDadosCompressaoPorColumnStore
Where Record Between 525000 And 789000
Go

Set Statistics Time Off
Set Statistics IO Off
Go

Ufa, estamos quase lá, nosso Bloco de Código 4 foi apresentado, tenho a certeza que você executou cada Select de forma individual, anotando os respectivos valores de tempo de processamento e input/output retornados pelo SQL Server, muito bem, agora vamos analisar a Tabela 4 apresentada abaixo e identificar o comportamento apresentado pelo ColumnStore Index.

Tabela Operação Tempo Decorrido CPU Time Elapsed Time Scan Count Logical Reads Physical Reads
MassaDeDadosCompressaoPorRow Select 00:00:02 110 ms 769 ms 1 2316 0
MassaDeDadosCompressaoPorPage Select 00:00:02 203 ms 830 ms 1 2158 0
MassaDeDadosCompressaoPorColumnStore Select 00:00:01 125 ms 911 ms 2 3599 0

Tabela 4 – Conjunto de dados coletados e apresentados pelos contadores TIME e IO durante a execução dos selects declarados no Bloco de Código 4.

Característica 5: Índice do tipo ColumnStore sempre serão mais rápidos que índice RowStore mesmo apresentando alguns dados que valores superiores, dentre eles: Scan Count e Logical Reads.

Analisando os dados apresentados anteriormente na Tabela 4, fica mais fácil observar alguns comportamentos presentes em índices ColumnStore, nos quais a quantidade de leituras lógicas pode estar mais presente neste tipo de índice devido justamente a sua forma de armazenamento de dados, além disso, o número de contagem de varreduras conhecidos como Scan Count, outro fator que também esta relacionado ao armazenamento do dados no índice na forma de coluna ao invés de linha como no RowStore.

Mas o elemento mais importante esta vinculado ao tempo decorrido na busca dos dados solicitados, tanto a tabela MassaDeDadosCompressaoPorRow como também a MassaDeDadosCompressaoPorPage demandaram 00:00:02 segundos para identificar, filtrar e apresentar 264.001 mil linhas de registro.

Nesta mesma comparação, os dados pesquisados na tabela MassaDeDadosCompressaoPorRow foram retornados em tela em 00:00:01 segundo, ou seja, uma ligeira diferença no tempo de processamento, o que pode parecer muito insignificante para o atual volume de dados que estamos trabalhando, mas imagine este ganho de processamento quando estivermos manipulando bilhões, trilhões ou quatrilhões de linhas de dados, mesmo que o elapsed time do processamento tenha sido um pouco maior que os demais. 

Desta forma, os dados apresentados na Tabela 4 em conjunto com os demais dados presentes em cada análise realizada neste post, nos traz a seguinte certeza, que índices ColumnStore podem e devem ser analisados, o que nos permite também dizer que o uso deste tipo de tecnologia poderá ser de grande avaliar e benefícios no que se relaciona a armazenamento e processamento de dados.

Com isso chegamos ao final deste post, olha foi surpreende e desafiador….


Referências

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/row-compression-implementation

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/page-compression-implementation

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql-identity-property

https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-spaceused-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/indexes/columnstore-indexes-overview

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-time-transact-sql

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-statistics-io-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-tables-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-partitions-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-allocation-units-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/schemas-catalog-views-sys-schemas

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/06/28/15-para-que-serve/

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

Identificar a melhor forma de armazenar nossos dados em um Sistema Gerenciador de Banco de Dados não é um tarefa das mais fáceis e comum de se realizada, escolher qual tecnologia de armazenamento visando economia de espaço e performance muito menos.

A partir da versão 2012 do Microsoft SQL Server esta tarefa começou a ficar um pouco mais interessante e amigável com a adoção da tecnologia de armazenamento de dados existentes em um índice no formato de coluna ao invés de linha, tecnologia conhecida e denominada como ColumnStore Index.

O uso do ColumnStore Index deve ser avaliado, sua principal característica relaciona-se ao capacidade de processamento de um grande volume de dados com baixo custo de tempo, mesmo em ambientes relacionais, o que nos permite dizer que utilizar um índice ColumnStore poderá lhe trazer benefícios tanto no espaço ocupado em disco, bem como, no tempo de processamento na pesquisa de dados.

Agradecimentos

Antes de finalizar, são 00:46hrs da madrugada, já estamos no dia 01/10, ou seja, um novo mês esta começando neste domínio.

Chegou a hora do descanso, se preparar para um novo dia que daqui a pouco estará raiando, espero que você possa fazer o mesmo, aproveitar o seu dia ainda mais um domingo, tentar viver um pouco sem se preocupar com os problemas.

Mais uma vez obrigado por sua ilustre visita, sinto-me honrado com sua presença, 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, 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.