Short Scripts – Outubro 2023

Olá pessoal, boa tarde…

Tudo bem? Estamos em pleno outono mas com cara de verão e inverno ao mesmo tempo, infelizmente a natureza esta dando diversos sinais que ela está sentindo muito todos os impactos gerados pela humanidade nos últimos anos.

Seja bem-vindo, em mais um post da sessão Short Scripts, o segundo post desta sessão em 2023 e o de número 48 em seu total.

Mantendo a tradição estou retornando com mais um conjunto dos “curtos ou pequenos” scripts catalogados e armazenados em minha biblioteca pessoal de códigos relacionados ao Microsoft SQL Server e sua fantástica linguagem de desenvolvimento Transact-SQL.

Como promessa é dívida e deve ser cumprida “ou melhor” compartilhada, vou pagar a minha mais uma vez hoje, feita no encerramento do post anterior publicado no mês de fevereiro de 2023, se por acaso você ainda não acessou o mesmo, calma, fique tranquilo, tudo vai dar certo, no encerramento deste post você terá a sua disposição a relação das últimas cinco publicações relacionadas aos Short Scripts.

Vamos caminhar mais um pouco então……..

O post de hoje

Para este post, vou compartilhar um conjunto diversificado de scripts dedicados ao ColumnStore Index, funcionalidade adicionada ao Microsoft SQL Server a partir da versão 2012.

Os exemplos de código aqui compartilhados se relacionam com os seguintes assuntos:

● Comando Alter Index;
● Comando Alter Table;
● Comando Create Database;
● Comando Create NonClustered ColumnStore Index;
● Comando Create NonClustered Index;
● Comando Create Sequence;
● Comando Create Table;
● Comando Exec;
● Comando Insert;
● Comando Select Next Value;
● Comando Use;
● Constraints;
● Database Command Console DBCC DropCleanBuffers;
● Database Command Console DBCC FreeProcCache;
● Diretiva Set Statitics Io;
● Diretiva Set Statitics Time;
● Objeto Sequence;
● Primary Key;
● Query Hint Ignore_NonClustered_ColumnStored_Index; e
● System Stored Procedure SP_Help;
● System Stored Procedure SP_HelpIndex;
● Tipo de Dados Date;
● Tipo de Dados Float;
● Tipo de Dados Time; e
● Tipo de Dados Varchar().

Chegou a hora, mãos nos teclados, a seguir apresento os códigos e exemplos selecionados para o Short Script – Outubro 2023.

---

Short Scripts

Vale ressaltar que todos os scripts publicados nesta sessão foram devidamente testados, mas isso não significa que você pode fazer uso dos mesmo em seu ambiente de produção, tenha todo cuidado possível para evitar maiores problemas.

Fase 1 – Preparando o Ambiente

-- Passo 1 -- Criando o Banco de Registros ColumnStoreIndex --
CREATE DATABASE ColumnStoreIndex
Go

-- Passo 1.1 -- Acessando o Banco de Registros ColumnStoreIndex --
Use ColumnStoreIndex
Go

-- Passo 2 -- 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 100 posições em cache
 NO CYCLE -- Não irá reciclar
Go

-- Passo 3 -- Criando a Tabela Registros com Primary Key --
Create Table Registros
  (Descricao VarChar(60) Not Null,
   Valor Float Null,
   Date Date Default GetDate(),
   Time Time Default GetDate())
Go

Alter Table Registros
 Add Constraint [PK_Registros] Primary Key Clustered (Descricao) On [Primary]
Go

-- Passo 3.1 -- Criando um Índice NonClustered para Tabela Registros --
CREATE NONCLUSTERED INDEX [IND_Registros_NonClustered]
ON Registros
 (Descricao, Valor, Date, Time)
Go

-- Passo 4 -- Listando a Relação de Índices da Tabela dbo.Registros --
Exec sp_helpindex 'Registros'
Go

Fase 2 – Trabalhando com os Dados

-- Passo 5 -- Inserindo a Massa de Registros --
Insert Into Registros (Descricao, Valor)
Values ('Ola...'+Convert(Varchar(100),Rand()),Rand())
Go 10000

-- Passo 6 -- Executando o Select em conjunto com Sequence e Verificar o Plano de Execução --
Select Next Value for Seq As Codigo, Descricao, Valor, Date, Time from Registros
Go

-- Passo 7 -- Limpando o Cache de Execução - Procedure e Buffer --
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
Go

Fase 3 – Utilizando o ColumnStore Index e Analisando a Performance

-- Passo 8 -- Criando o ColumnStore Index --
CREATE NONCLUSTERED COLUMNSTORE INDEX ColumnStoreIndex_Registros
ON dbo.Registros
(
     Descricao,
     Valor,
     Date,
     Time     
)
Go

-- Passo 9 -- Ativando as Estatísticas de Time e IO --
SET STATISTICS TIME ON
SET STATISTICS IO ON
Go

-- Passo 10 -- Executando 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 Registros 
Go

-- Passo 11 -- Executando 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 Registros 
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);
Go

-- Passo 12 -- Executando novamente o Select + 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 Registros 
Group By Descricao, Valor, Date
Go

-- Passo 13 -- Executando novamente o Select + 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 Registros 
Group By Descricao, Valor, Date
OPTION (IGNORE_NONCLUSTERED_COLUMNSTORE_INDEX);
Go

Fase 4 – Desativando o ColumnStore Index e Analisando a Performance

-- Passo 14 -- Desativando as Estatísticas de Time e IO --
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
Go

-- Passo 15 -- Tentando Inserir Registros com o ColumnStore Index em uso --
Insert Into Registros (Descricao, Valor)
Values ('Ola...'+Convert(Varchar(100),Rand()),Rand())
Go

-- Passo 16 -- Desativando o ColumnStore Index --
ALTER INDEX ColumnStoreIndex_Registros
ON Registros DISABLE
GO

-- Passo 17 -- Inserindo novos Registros com o ColumnStore Index em desabilitado --
Insert Into Registros (Descricao, Valor)
Values ('Ola...'+Convert(Varchar(100),Rand()),Rand())
Go 1000

-- Passo 18 -- Realizando o Rebuild do ColumnStore Index --
ALTER INDEX ColumnStoreIndex_Registros
ON Registros REBUILD PARTITION = ALL
GO

Fase 5 – Comparando Espaço Ocupado ColumnStore Index X Clustered Index

-- ColumnStore Index --
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
Go

-- Index --
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
Go

Fase 6 – Obtendo informações sobre o ColumnStore Index e Clustered Index

-- Exibindo informações sobre as Colunas utilizadas no ColumnStore Index --
Select * from sys.column_store_segments
Go

-- Exibindo informações sobre as Colunas com espaço ocupado no ColumnStore Index --
Select * from sys.column_store_dictionaries
Go

-- Comparativo de Informações entre Clustered Index X ColumnStore Index --
Select Name, status, indid, minlen, maxlen, dpages, rowcnt, used, rows from sysindexes
Where Name In ('ColumnStoreIndex_Dados','PK_Dados')
Go

** Evite utilizar o símbolo de asterisco em ambientes de homologação e produção.

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

---

Muito bem, missão mais que cumprida!

Uma super e especial relação de short scripts acaba de ser compartilhada, mesmo sendo denominados shorts entre aspas “curtos ou 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 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:

Short Scripts – Fevereiro 2023

Short Scripts – Setembro 2022

Short Scripts – Março 2022

Short Scripts – Junho 2021

Short Scripts – Janeiro 2021

Short Scripts – Setembro 2020

Agradecimento

Obrigado mais uma vez por sua visita, fico honrado com sua ilustre presença, desejo e espero que você possa ter encontrado algo que lhe ajudou.

Volte sempre, nos encontraremos mais uma vez na sessão Short Scripts no próximo post.

Tenha uma ótima semana.

Abraços.