/*==================================================================================

Curso: SQL SERVER, do Básico ao Avançado.
Para ter acesso ao curso completo: 
https://www.udemy.com/course/draft/3957796/?referralCode=FB10D369E786D9FE8A48

Instrutor: Sandro Servino
https://www.linkedin.com/in/sandroservino/?originalSubdomain=pt
https://filiado.wixsite.com/sandroservino

ATIVIDADES ROTINEIRAS BÁSICAS DO DBA
PERFORMANCE: PARTITIONS

==================================================================================*/

-- Antes de partirmos para PARTITIONS, vamos aprender a mudar o local fisico de uma 
-- tabela para um outro disco, com a delecao e criacao de um indice CLUSTERED da
-- respectiva tabela e sem interromper o serviço. Isto não é particionamento 
-- mas é uma forma eficaz de mudar a tabela de disco sem parar o servico e 
-- os sistemas que acessam esta tabela, desde que utilize o SQL SERVER ENTERPRISE.

-- Se uma tabela não tiver um índice CLUSTERED, seus dados serão chamados de heap. Neste
-- caso, precisa criar um campo com indice clustered mesmo que seja int, identity(1,1). Depois
-- de movida a tabela, pode deletar este campo, se for o caso.
-- Se uma tabela tiver um índice clusterizado, esse índice será efetivamente a própria 
-- tabela, com todos os campos, ordenado com o campo indicado na criaçao do indice. 
-- Então se mover o índice CLUSTERED também moverá a tabela e os dados da mesma.

-- Primeiro passo é verificar a tabela que quer mover de disco:
sp_help 'your table name'
go
-- A saída mostrará uma coluna intitulada 'Data_located_on_filegroup.'

-- Depois, apenas para ratificar, rode este procedimento para saber se a tabela tem indice 
-- CLUSTERED ou é uma HEAP
sp_helpindex 'your table name'
go
-- Para mover o índice, anote o index_name e index_keys mostrados nos resultados da consulta 
-- para poder mudar o indice.


---- VAMOS AO LAB

USE AUDITORIADBA
GO
sp_help NOIndex
GO
-- Verificar Data_Located_on_Filegroup e index_description

sp_helpindex NOIndex
GO
-- Só para confirmar o valor em index_description

USE AUDITORIADBA
GO  
select COUNT(*) from AUDITORIADBA..Noindex
go
-- Vamos verificar as propriedades da tabela NoIndex no SSMS, propriedade storage

-- Crie o grupo de arquivos TransactionsFG1 no banco de dados AUDITORIADBA
ALTER DATABASE AUDITORIADBA
ADD FILEGROUP TransactionsFG1;  
GO 
 
-- Adicione o arquivo TransactionsFG1dat3 ao grupo de arquivos TransactionsFG1. 
-- Observe que você terá que alterar o parâmetro do nome do arquivo nesta instrução para executá-lo sem erros.
 
ALTER DATABASE AUDITORIADBA
ADD FILE   
(  
    NAME = TransactionsFG1dat3,  
    FILENAME = 'C:\TABELANOINDEX\TransactionsFG1dat3.ndf',  -- NAO É UMA BOA PRATICA CRIAR EM C: APENAS PARA O LAB.
    SIZE = 100MB,  
    MAXSIZE = 1000MB,  
    FILEGROWTH = 50MB  
)  
TO FILEGROUP TransactionsFG1;  
GO  
-- Verifique em C:\TABELANOINDEX\TransactionsFG1dat3.ndf, o arquivo criado e veja o tamanho 100MB

/* Recrie o índice idx_NONONclusterednumero
  no grupo de arquivos TransactionsFG1 e assim será descartado o índice idx_NONONclusterednumero original.
*/  
CREATE CLUSTERED INDEX idx_NONONclusterednumero 
    ON AUDITORIADBA..Noindex(numero)  
    WITH (DROP_EXISTING = ON, ONLINE = ON)  -- NECESSARIO DELETAR O INDICE CLUSTERED E CRIAR DE FORMA ONLINE PARA MITIGAR DESCONTINUIDADE
    ON TransactionsFG1;  
GO  

-- Enquanto estiver movendo os dados, abra uma nova sessao
-- execute sp_whoisactive e tente pesquisar um dado da tabela e
-- realizar insert
USE AuditoriaDBA
GO
Insert Into Noindex (Numero,nomecliente,endereco, cidade, estado, pais )
Values (Rand()*100000000000,'NOVOCLIENTE','rua teste 123','vitoria','espirito santo','brasil' )
GO
select * from AUDITORIADBA..Noindex
where nomecliente = 'NOVOCLIENTE'
GO

-- e 

-- Novamente execute o mesmo comando para verificar se foi mudado o local da tabela
sp_help 'NOIndex'
GO 
select COUNT(*) from AUDITORIADBA..Noindex
go

-- Verifique em C:\TABELANOINDEX\TransactionsFG1dat3.ndf, o arquivo criado e veja o novo tamanho 
-- tamanho do arquivo apos a movimentacao da tabela e indice. Veja ainda a propriedade
-- da tabela Noindex no SSPS em storage.

----------------------------------------------------------------------------------FIM


-- AGORA SIM, VAMOS AO PARTITIONS

/*

- Os dados de tabelas e índices particionados são divididos em unidades que podem ser difundidas 
por mais de um grupo de arquivos em um banco de dados.
- Todas as partições de um único índice ou de uma única tabela devem residir no mesmo banco de dados.
- A tabela ou o índice é tratado como uma única entidade lógica quando são executadas consultas ou atualizações nos dados. 
- SQL Server oferece suporte a até 15.000 partições por padrão.
- Só tem sentido para tabelas e indices muito grandes, de centenas de GBytes (bilhoes de linhas), pois aumenta a complexidade de gestão para o DBA.
  Na maioria dos casos, um bom plano de indices, bons codigos, resolvem o problema para tabelas com centenas de milhóes de linhas e até bilhoes
  de linhas, sem a necessidade de PARTITIONS, mas é um excelente recurso para tratar particionamento de dados dentro de tabelas muito grandes.

Principais Benefícios do particionamento:

- Você pode transferir ou acessar subconjuntos gidantesco de dados de forma rápida e eficaz e, 
  ao mesmo tempo, manter a integridade de uma coleção de dados, como exemplo uma exportação
  de dados de um sistema OLTP para OLAP.
- Você pode executar operações de manutenção mais rapidamente em uma ou mais partições. 
  As operações são mais eficientes porque elas visam apenas estes subconjuntos de dados, 
  e não a tabela inteira. Por exemplo, você pode optar por compactar dados em uma ou mais 
  partições ou recriar uma ou mais partições de um índice, ou 
  realizar backup e restore por partições.
- Poderá colocar conjunto de dados especificos, por data por exemplo, em um disco separado
  ou mesmo por nome do cliente, e possibilitar consultas em paralelo em discos separados ou mesmo 
  ordenações em discos separados através do uso das partições com mais performance do que
  em apenas 1 disco de dados. Um join com duas tabelas grandes que estavam no mesmo disco
  pode ganhar performance porque o mesmo tempo o sql server vai realizar a leitura ao mesmo
  tempo nos dois discos.
- Além disso, você pode melhorar o desempenho habilitando o escalonamento de bloqueios em 
  nível de partição e não em uma tabela inteira. Isso pode reduzir a contenção de bloqueio 
  na tabela.
- Pode colocar dados históricos e menos acessados em discos mais lentos, deixando
  os dados mais recentes e muitos acessados nos discos mais rápidos e caros.

Principais Dsvantagens:

- Aumento da complexidade de gestão e necessidade de DBA's senior.
- Existem procedimentos de SQL que podem perder performance, quando por exemplo
  particiona uma tabela por uma coluna e faz buscas por outra coluna em varias particoes. 
  importante simular em QA.
- Com arquivos em vários discos aumenta a possibilidade de problemas de crash de disco.
  A infraestutura precisa ter alto grau de maturidade para se manter um ambiente complexo
  com base de dados particionada em vários discos.
- Processo de carga inicial, com bases grandes, pode trazer indisponibilidade da aplicação.
- Maior consumo de memória e cores. Separe pelo menos 16gb de RAM e mais cores para rodar 
- um banco com muitas partições.
- DBCC CHECKDB constuma demorar mais tempo para rodar.

A criação de uma tabela ou um índice particionado ocorre quatro etapas

- Crie um grupo ou grupos de arquivos e os arquivos correspondentes que terão as partições especificadas pelo esquema de partição.
- Cria uma função de partição que mapeia as linhas de uma tabela ou de um índice em partições com base nos valores de uma coluna especificada.
- Cria um esquema de partição que mapeia as partições de uma tabela particionada ou índice para os novos grupos de arquivos.
- Crie ou modifique uma tabela ou um índice e especifique o esquema de partição como local de armazenamento.

-- VAMOS AO LAB

Criando partições

*/  

USE AUDITORIADBA
GO  

-- Adicione 6 novos grupos de arquivos ao banco de dados AUDITORIADBA
ALTER DATABASE AUDITORIADBA
ADD FILEGROUP test1fg;  
GO  
ALTER DATABASE AUDITORIADBA
ADD FILEGROUP test2fg;  
GO  
ALTER DATABASE AUDITORIADBA
ADD FILEGROUP test3fg;  
GO  
ALTER DATABASE AUDITORIADBA
ADD FILEGROUP test4fg;  
 
ALTER DATABASE AUDITORIADBA
ADD FILEGROUP test5fg; 

ALTER DATABASE AUDITORIADBA
ADD FILEGROUP test6fg;   

-- Adicione 1 arquivo para cada grupo de arquivos.
ALTER DATABASE AUDITORIADBA
ADD FILE   
(  
    NAME = test1dat1,  
    FILENAME = 'C:\TABELANOINDEX\t1dat1.ndf',  
    SIZE = 500MB,  
    MAXSIZE = 1000MB,  
    FILEGROWTH = 50MB  
)  
TO FILEGROUP test1fg; 
GO
 
ALTER DATABASE AUDITORIADBA
ADD FILE   
(  
    NAME = test2dat2,  
    FILENAME = 'C:\TABELANOINDEX\t2dat2.ndf',  
    SIZE = 500MB,  
    MAXSIZE = 1000MB,  
    FILEGROWTH = 50MB  
)  
TO FILEGROUP test2fg;  
GO  

ALTER DATABASE AUDITORIADBA
ADD FILE   
(  
    NAME = test3dat3,  
    FILENAME = 'C:\TABELANOINDEX\t3dat3.ndf',  
    SIZE = 500MB,  
    MAXSIZE = 1000MB,  
    FILEGROWTH = 50MB  
)  
TO FILEGROUP test3fg;  
GO 
 
ALTER DATABASE AUDITORIADBA
ADD FILE   
(  
    NAME = test4dat4,  
    FILENAME = 'C:\TABELANOINDEX\t4dat4.ndf',  
    SIZE = 500MB,  
    MAXSIZE = 1000MB,  
    FILEGROWTH = 50MB  
)  
TO FILEGROUP test4fg;  
GO  

ALTER DATABASE AUDITORIADBA
ADD FILE   
(  
    NAME = test4dat5,  
    FILENAME = 'C:\TABELANOINDEX\t4dat5.ndf',  
    SIZE = 500MB,  
    MAXSIZE = 1000MB,  
    FILEGROWTH = 50MB  
)  
TO FILEGROUP test5fg;  
GO 

ALTER DATABASE AUDITORIADBA
ADD FILE   
(  
    NAME = test4dat6,  
    FILENAME = 'C:\TABELANOINDEX\t4dat6.ndf',  
    SIZE = 500MB,  
    MAXSIZE = 1000MB,  
    FILEGROWTH = 50MB  
)  
TO FILEGROUP test6fg;  
GO 

-- Cria uma função de partição chamada myRangePF1 que irá particionar uma tabela em 6 partições
-- particao 1 <=100000
-- particao 2 > 100001 <=300000
-- particao 3 > 300001 <=500000
-- particao 4 > 500001 <=700000
-- particao 5 > 700001 <=900000
-- particao 6 > 900001
CREATE PARTITION FUNCTION myRangePF1 (bigint)  
    AS RANGE LEFT FOR VALUES (100000, 300000, 500000, 700000, 900000) ;  
GO  

-- Cria um esquema de partição chamado myRangePS1 que aplica myRangePF1 aos 6 grupos de arquivos criados acima
CREATE PARTITION SCHEME myRangePS1  
    AS PARTITION myRangePF1  
    TO (test1fg, test2fg, test3fg, test4fg, test5fg, test6fg) ;  
GO  

-- Cria uma tabela particionada chamada PartitionTable que usa TABELAPARTICIONADA para particionar numero
CREATE TABLE [dbo].[TABELAPARTICIONADA](
	[Registro] [bigint] NULL,
	[Numero] [bigint] NOT NULL,
	[nomecliente] [nvarchar](100) NULL,
	[endereco] [nchar](200) NULL,
	[cidade] [nchar](30) NULL,
	[estado] [nchar](30) NULL,
	[pais] [nchar](20) NULL
) ON myRangePS1 (Registro)
GO
------

-- Fazendo insert na tabela particionada.

-- Vamos mover os dados da tabela CompressColumnStore para esta TABELAPARTICIONADA.
-- Trabalhar com tabelas compactadas com a tecnologia ColumnStore particionada pode ser
-- bem interessante para ambientes de DW, para minimizar o tempo das cargas.

-- OBS> Na ultima aula nós deletamos os dados da tabela CompressColumnStore. 
-- Então antes do proximo insert, insira os dados novamente na tabela CompressColumnStore 

Declare @Cont Int = 1
While @Cont <=333333
Begin
   Insert Into CompressColumnStore (Numero,nomecliente,endereco, cidade, estado, pais )
   Values (Rand()*100000000000,'Pedro','rua teste 123','vitoria','espirito santo','brasil' )
   Insert Into CompressColumnStore (Numero,nomecliente,endereco, cidade, estado, pais )
   Values (Rand()*100000000000,'maria','rua teste 789','vila nova de gaia','porto','portugal')
   Insert Into CompressColumnStore (Numero,nomecliente,endereco, cidade, estado, pais)
   Values (Rand()*100000000000,'jose','rua teste 456','rio de janeiro','rio de janeiro','brasil' )
   Set @Cont +=1
End
Go

use AUDITORIADBA
GO
INSERT INTO AUDITORIADBA..TABELAPARTICIONADA ([Registro], [Numero], [nomecliente], [endereco], 
[cidade], [estado] , [pais])
SELECT *
FROM AUDITORIADBA..CompressColumnStore 

Select count(*) from AUDITORIADBA..TABELAPARTICIONADA 
GO

------

-- Para determinar se uma tabela é particionada
-- A consulta a seguir retornará uma ou mais linhas se a tabela PartitionTable 
-- for particionada. Se a tabela não for particionada, nenhuma linha será retornada.

SELECT *   
FROM sys.tables AS t   
JOIN sys.indexes AS i   
    ON t.[object_id] = i.[object_id]   
    AND i.[type] IN (0,1)   
JOIN sys.partition_schemes ps   
    ON i.data_space_id = ps.data_space_id   
WHERE t.name = 'TABELAPARTICIONADA';   
GO 

------

-- Para determinar os valores de limite para uma tabela particionada
-- A consulta a seguir retorna os valores de limite para cada partição 
-- na tabela PartitionTable .

SELECT t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue   
FROM sys.tables AS t  
JOIN sys.indexes AS i  
    ON t.object_id = i.object_id  
JOIN sys.partitions AS p  
    ON i.object_id = p.object_id AND i.index_id = p.index_id   
JOIN  sys.partition_schemes AS s   
    ON i.data_space_id = s.data_space_id  
JOIN sys.partition_functions AS f   
    ON s.function_id = f.function_id  
LEFT JOIN sys.partition_range_values AS r   
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number  
WHERE t.name = 'TABELAPARTICIONADA' AND i.type <= 1  
ORDER BY p.partition_number;  

-------

-- Verificar onde ficaram os dados
SELECT $PARTITION.myRangePF1(a.Registro) AS Partition,   
COUNT(*) AS [COUNT] FROM  auditoriadba..TABELAPARTICIONADA a
GROUP BY $PARTITION.myRangePf1(a.Registro)
ORDER BY Partition ;  
GO

-- Recuperar os dados da particao 2
SELECT * FROM auditoriadba..TABELAPARTICIONADA a 
WHERE $PARTITION.myRangePf1(a.Registro) = 2 ;  

-------

-- Para determinar a coluna de partição para uma tabela particionada
-- A consulta a seguir retorna o nome da coluna de particionamento 
-- para a tabela. PartitionTable.

SELECT   
    t.[object_id] AS ObjectID   
    , t.name AS TableName   
    , ic.column_id AS PartitioningColumnID   
    , c.name AS PartitioningColumnName   
FROM sys.tables AS t   
JOIN sys.indexes AS i   
    ON t.[object_id] = i.[object_id]   
    AND i.[type] <= 1 -- clustered index or a heap   
JOIN sys.partition_schemes AS ps   
    ON ps.data_space_id = i.data_space_id   
JOIN sys.index_columns AS ic   
    ON ic.[object_id] = i.[object_id]   
    AND ic.index_id = i.index_id   
    AND ic.partition_ordinal >= 1 -- because 0 = non-partitioning column   
JOIN sys.columns AS c   
    ON t.[object_id] = c.[object_id]   
    AND ic.column_id = c.column_id   
WHERE t.name = 'TABELAPARTICIONADA ' ;   
GO  

-- MOVENDO DADOS ENTRE PARTICÕES
-- Imagina que queira mover dados históricos que estão na minha tabela corrente
-- para uma tabela histórica. O que teria que fazer se não existir partições?
-- Terá que dar insert em uma tabela e delete na horiginal. Dependendo da 
-- quantidade de dados, este procedimento pode demorar muito tempo e trazer
-- impacto para a produção.

-- Primeiro, vamos criar uma tabela histórica usando o mesmo modelo de particionamento
-- Como a tabela original ja está particionada, vai ser mouito rápido todo o processo.

USE AuditoriaDBA
GO

DROP TABLE IF EXISTs TABELAPARTICIONADA_HIST 
GO

CREATE TABLE TABELAPARTICIONADA_HIST (
	[Registro] [bigint] NULL,
	[Numero] [bigint] NOT NULL,
	[nomecliente] [nvarchar](100) NULL,
	[endereco] [nchar](200) NULL,
	[cidade] [nchar](30) NULL,
	[estado] [nchar](30) NULL,
	[pais] [nchar](20) NULL
) ON myRangePS1 (Registro)
GO


-- Assim estão distribuidos os dados nas partições
SELECT $PARTITION.myRangePF1(a.Registro) AS Partition,   
COUNT(*) AS [COUNT] FROM  auditoriadba..TABELAPARTICIONADA a
GROUP BY $PARTITION.myRangePf1(a.Registro)
ORDER BY Partition ;  
GO

-- Antes de mover, vamos deletar 98.000 linhas apenas na partição 1.
SELECT * FROM auditoriadba..TABELAPARTICIONADA a 
WHERE $PARTITION.myRangePF1(a.Registro) = 1 ;
go
DELETE FROM auditoriadba..TABELAPARTICIONADA 
WHERE $PARTITION.myRangePF1(Registro) = 1 
and registro < 98000;
go
SELECT * FROM auditoriadba..TABELAPARTICIONADA a 
WHERE $PARTITION.myRangePF1(a.Registro) = 1 ;
go

SELECT $PARTITION.myRangePF1(a.Registro) AS Partition,   
COUNT(*) AS [COUNT] FROM  auditoriadba..TABELAPARTICIONADA a
GROUP BY $PARTITION.myRangePf1(a.Registro)
ORDER BY Partition ;  
GO

-- Agora vamos deslocar os dados que estão em uma partição em uma tabela
-- para outra partição de outra tabela

ALTER TABLE TABELAPARTICIONADA SWITCH PARTITION 1 TO TABELAPARTICIONADA_HIST PARTITION 1

-- Vamos verificar como ficou agora as duas tabelas.

SELECT $PARTITION.myRangePF1(a.Registro) AS Partition,   
COUNT(*) AS [COUNT] FROM  auditoriadba..TABELAPARTICIONADA a
GROUP BY $PARTITION.myRangePf1(a.Registro)
ORDER BY Partition ;  
GO

SELECT $PARTITION.myRangePF1(a.Registro) AS Partition,   
COUNT(*) AS [COUNT] FROM  auditoriadba..TABELAPARTICIONADA_HIST a
GROUP BY $PARTITION.myRangePf1(a.Registro)
ORDER BY Partition ;  
GO

-- Foi instantaneo. Será porque eram poucas linhas? 
-- Vamos fazer agora com uma partição com 200.000 linhas

ALTER TABLE TABELAPARTICIONADA SWITCH PARTITION 2 TO TABELAPARTICIONADA_HIST PARTITION 2
GO

SELECT $PARTITION.myRangePF1(a.Registro) AS Partition,   
COUNT(*) AS [COUNT] FROM  auditoriadba..TABELAPARTICIONADA a
GROUP BY $PARTITION.myRangePf1(a.Registro)
ORDER BY Partition ;  
GO

SELECT $PARTITION.myRangePF1(a.Registro) AS Partition,   
COUNT(*) AS [COUNT] FROM  auditoriadba..TABELAPARTICIONADA_HIST a
GROUP BY $PARTITION.myRangePf1(a.Registro)
ORDER BY Partition ; 
GO 

-- Agora vamos retornar as duas partições para a tabela original
ALTER TABLE TABELAPARTICIONADA_HIST SWITCH PARTITION 1 TO TABELAPARTICIONADA PARTITION 1
GO
ALTER TABLE TABELAPARTICIONADA_HIST SWITCH PARTITION 2 TO TABELAPARTICIONADA PARTITION 2
GO

SELECT $PARTITION.myRangePF1(a.Registro) AS Partition,   
COUNT(*) AS [COUNT] FROM  auditoriadba..TABELAPARTICIONADA a
GROUP BY $PARTITION.myRangePf1(a.Registro)
ORDER BY Partition ;  
GO

SELECT $PARTITION.myRangePF1(a.Registro) AS Partition,   
COUNT(*) AS [COUNT] FROM  auditoriadba..TABELAPARTICIONADA_HIST a
GROUP BY $PARTITION.myRangePf1(a.Registro)
ORDER BY Partition ; 
GO 

-- E VAMOS DELETAR TODOS OS DADOS APENAS DA PARTICAO 3
DELETE FROM auditoriadba..TABELAPARTICIONADA 
WHERE $PARTITION.myRangePF1(Registro) = 3
go

SELECT $PARTITION.myRangePF1(a.Registro) AS Partition,   
COUNT(*) AS [COUNT] FROM  auditoriadba..TABELAPARTICIONADA a
GROUP BY $PARTITION.myRangePf1(a.Registro)
ORDER BY Partition ;  
go

-- E Vamos aprender a inserir dados em uma determinada
-- particao. Partidao 3 da tabela TABELAPARTICIONADA.
-- Basta usar os valores dentro da faixa estabelecida
-- particao 3 > 300001 <=500000
-- Vamos verificar que agora vai aparecer novamente a 
-- particao 3 com 3 linhas

Insert Into TABELAPARTICIONADA(Registro,Numero,nomecliente,endereco, cidade, estado, pais )
Values (300001,Rand()*100000000000,'Pedro','rua teste 123','vitoria','espirito santo','brasil' )
Insert Into TABELAPARTICIONADA(Registro,Numero,nomecliente,endereco, cidade, estado, pais )
Values (300002,Rand()*100000000000,'maria','rua teste 789','vila nova de gaia','porto','portugal')
Insert Into TABELAPARTICIONADA(Registro,Numero,nomecliente,endereco, cidade, estado, pais)
Values (300003,Rand()*100000000000,'jose','rua teste 456','rio de janeiro','rio de janeiro','brasil' )

SELECT $PARTITION.myRangePF1(a.Registro) AS Partition,   
COUNT(*) AS [COUNT] FROM  auditoriadba..TABELAPARTICIONADA a
GROUP BY $PARTITION.myRangePf1(a.Registro)
ORDER BY Partition ;  
go

-- Segue outros exemplos de manipulação das Partitions
https://docs.microsoft.com/en-us/previous-versions/technet-magazine/cc162478(v=msdn.10)?redirectedfrom=MSDN