Short Script – Novembro – 2013 – Especial – ColumnStore Index

Salve, Salve…..

Bom dia, Comunidade.

Estou retornando com a nossa Sessão – Short Script, mas neste mês com um Especial sobre o ColumnStore Index, recurso que foi implementado no Microsoft SQL Server 2012 e que esta crescendo em muito na sua utilização.

Basicamente o ColumnStored Index é uma nova forma de armazenamento de dados no SQL Server que permite um ganho de até 10x no processamento de Querys.

Não vou me extender neste conceito, que também já foi abordado no meu Blog, como de costume vou compartilhar com você este Script que poderá ser utilizado em seus ambientes de estudo.

Um detalhe importante que também estou fazendo uso de uma nova funcionalidade que foi introduzida no SQL Server 2012, conhecida com Sequence.

O Short Script esta organizado em cinco partes:

  1. Criando o Ambiente para uso do ColumnStore Index;
  2. Criando o ColumnStore Index;
  3. Simulando cenários de utilização do ColumnStored Index;
  4. Obtendo informações sobre o ColumnStore Index; e
  5. Comparativo entre o ColumnStore Index x ClusteredIndex.

 

Segue abaixo o Short Scripts – Especial – ColumnStore Index

 

— Parte 1 – Criando o Ambiente —

— Criando o Banco de Dados Armazenamento —

CREATE DATABASE Armazenamento

Go

 

— Acessando o Banco de Dados Pitagoras —

Use Armazenamento

Go

 

— Criando uma nova Sequência de Valores —

CREATE SEQUENCE Seq As INT — Tipo

 START WITH 1 — Valor Inicial (1)

 INCREMENT BY 1 — Avança de um em um

 MINVALUE 1 — Valor mínimo 1

 MAXVALUE 100000 — Valor máximo 100000

 CACHE 100 — Mantém 10 posições em cache

 NO CYCLE — Não irá reciclar

Go

 

— Criando a Tabela Dados com Primary Key —

Create Table Dados

  (Descricao VarChar(60) Primary Key,

   Valor Float Null,

   Date Date Default GetDate(),

   Time Time Default GetDate())

 

— Listando a Relação de Índices da Tabela dbo.Dados —

Exec sp_helpindex ‘Dados’

Go

 

— Parte 2 – Criando o ColumnStore Index –-

CREATE NONCLUSTERED COLUMNSTORE INDEX ColumnStoreIndex_Dados

ON dbo.Dados

(

     Descricao,

                 Valor,

                 Date,

                 Time    

)

Go

 

— Parte 3 – Simulando cenários de utilização do ColumnStore Index –-

— Inserindo a Massa de Dados —

Insert Into Dados (Descricao, Valor)

Values (‘Ola…’+Convert(Varchar(100),Rand()),Rand())

Go 10000

 

— Limpando o Cache de Execução —

DBCC FREEPROCCACHE

Go

 

— Ativando as Estatísticas de Time e IO —

SET STATISTICS TIME ON

SET STATISTICS IO ON

Go

 

— Cenário 1 —

— Executando o novamente o Select com ColumnStore Index e Verificar o Plano de Execução —

Select Next Value for Seq As Codigo, Descricao, Valor, Date, Time from Dados

Go

 

— Executando o novamente o Select ignorando o ColumnStore Index e Verificar o Plano de Execução —

Select Next Value for Seq As Codigo, Descricao, Valor, Date from Dados

OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

Go

 

— Cenário 2 —

— Executando o novamente o Select + Sequence + Group By com ColumnStore Index e Verificar o Plano de Execução —

Select Next Value for Seq As Codigo, Descricao, Valor, Date, Count(Time) As Contagem from Dados

Group By Descricao, Valor, Date

Go

 

— Executando o novamente o Select + Sequence + Group By ignorando o ColumnStore Index e Verificar o Plano de Execução —

Select Next Value for Seq As Codigo, Descricao, Valor, Date, Count(Time) As Contagem from Dados

Group By Descricao, Valor, Date

OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

Go

 

— Cenário 3 —

— Executando o novamente o Select + Sem Sequence + Group By com ColumnStore Index e Verificar o Plano de Execução —

Select Descricao, Valor, Date, Count(Time) As Contagem from Dados

Group By Descricao, Valor, Date

Go

 

— Executando o novamente o Select + Sem Sequence + Group By ignorando o ColumnStore Index e Verificar o Plano de Execução —

Select Descricao, Valor, Date, Count(Time) As Contagem from Dados

Group By Descricao, Valor, Date

OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

Go

 

— Cenário 4 —

— Executando o novamente o Select + Sem Sequence + Sem Group By com ColumnStore Index e Verificar o Plano de Execução —

Select Next Value for Seq As Codigo, Descricao, Valor, Date, Time from Dados

Go

 

— Executando o novamente o Select + Sem Sequence + Sem Group By ignorando o ColumnStore Index e Verificar o Plano de Execução —

Select Next Value for Seq As Codigo, Descricao, Valor, Date, Time from Dados

OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);

Go

 

— Parte 4 – Obtendo informações sobre o ColumnStore Index –-

— Exibindo informações sobre as Colunas utilizadas no ColumnStore Index —

Select * from sys.column_store_segments

 

— Exibindo informações sobre as Colunas com espaço ocupado no ColumnStore Index —

Select * from sys.column_store_dictionaries

 

— Parte 5 –  Comparativo de Informações entre Clustered Index X ColumnStore Index —

— Exemplo – 1 —

Select Name, status,

           indid, minlen,

           maxlen, dpages,

           rowcnt, used,

           rows

from sysindexes

Where Name In (‘ColumnStoreIndex_Dados’,’PK_Dados’)

Go

 

— Exemplo 2 –

SELECT ‘ColumnStoreIndex’As IndexName, SUM(on_disk_size_MB) AS TotalSizeInMBColumnStoredIndex

  FROM

  (

     (SELECT ‘ColumnStoreIndex’As IndexName, SUM(css.on_disk_size)/(1024.0*1024.0) * 8 on_disk_size_MB

      FROM sys.indexes AS i

      JOIN sys.partitions AS p

          ON i.object_id = p.object_id

      JOIN sys.column_store_segments AS css

          ON css.hobt_id = p.hobt_id

      WHERE i.object_id = object_id(‘Dados’)

      AND i.type_desc = ‘NONCLUSTERED COLUMNSTORE’)

    UNION ALL

     (SELECT ‘ColumnStoreIndex’As IndexName, SUM(csd.on_disk_size)/(1024.0*1024.0) * 8 on_disk_size_MB

      FROM sys.indexes AS i

      JOIN sys.partitions AS p

          ON i.object_id = p.object_id

      JOIN sys.column_store_dictionaries AS csd

          ON csd.hobt_id = p.hobt_id

      WHERE i.object_id = object_id(‘Dados’)

      AND i.type_desc = ‘NONCLUSTERED COLUMNSTORE’)

  ) AS InformationColumnStoredIndex

 

SELECT ‘PK_Dados’As IndexName, SUM(on_disk_size_MB) AS TotalSizeInMBIndex

  FROM

  (

     (SELECT SUM(i.rowcnt)/(1024.0*1024.0) * 8 on_disk_size_MB

      FROM sysindexes AS i

      JOIN sys.partitions AS p

          ON i.id = p.object_id

      WHERE i.id = object_id(‘Dados’))

    UNION ALL

     (SELECT SUM(i.rowcnt)/(1024.0*1024.0) * 8 on_disk_size_MB

      FROM sysindexes AS i

      JOIN sys.partitions AS p

          ON i.id = p.object_id

      WHERE i.id = object_id(‘Dados’))

  ) AS InformationIndex

————————————————————————————————————————————————————————————————–

Mais uma vez agradeço a sua visita.

Espero que este simples Short Script possa lhe ajudar em suas atividades profissionais e acadêmicas.

Nos encontramos em breve.

Até mais.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

3 comentários em “Short Script – Novembro – 2013 – Especial – ColumnStore Index”

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s