Ajuste de desempenho de consulta – Infográfico

Site: Solarwinds

Palestra aprovada no Microsoft Insights 2015

Você já viu neste blog que submeti (junto com meus amigos mega geeks Ivan Lima, Edvaldo Castro e Fabiano Amorim) uma série de palestras para o Microsoft Insights 2015 (antigo TechEd) e que a maioria foi recusada… Mas não todas! :-)

E se você me ouviu falar no último ano ou está um pouco antenado nesse blog, deve ter notado que estou indo um pouco além do relacional, estudando diversas outras coisas que são muito interessantes e importantes para pensar em uma miríade de soluções que podemos criar para tratar grandes questões de dados.

E no TechEd eu e Ivan Lima não estaremos falando de SQL Server on-premisse, internals, troubleshooting ou performance…

Ciência de dados com o Microsoft Azure (Big Data)

Ciência dos dados é uma das grandes frentes de investimento de todo grande executivo e, devido a sua escalabilidade, a nuvem se tornou o ambiente ideal para a implementação de soluções de forma ágil. Nessa sessão abordaremos o desenvolvimento de soluções escaláveis utilizando os serviços disponíveis no Microsoft Azure como HDInsight, Machine Learning e Data Factory.

Link: https://www.microsoftinsights.com.br/trail/TrailDetail/TrailId/2#lecture437

Esperamos que quem esteja no Insight possa participar da nossa sessão e que consigamos quebrar mitos, passar definições mais claras e, claro, fazer demonstrações com aplicações interessantes.

Muito feliz em estar palestrando neste grande evento da Microsoft no Brasil, que contando com os antigos TechEds, será pela sexta vez!

PS: Não estou deixando o SQL Server de lado, ou outro banco de dados relacional, só acho que hoje um arquiteto de dados, para se considerar bom e maduro, tem muito mais problemas à sua frente, e que precisam de respostas diferentes do que se propunha alguns anos atrás...

Abraços

Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
www.srnimbus.com.br

Vídeo 02 – Certificações II

Mais um vídeo, continuando a falar sobre certificações, só que dessa vez falando do path de certificação para SQL Server, do finado programa MCM e até das provas de DB2.


Abraços,

Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
www.srnimbus.com.br

Piecemeal restore com filestream

    Parte do trabalho de consultoria não é só trabalhar para definir as melhores soluções arquiteturais, mas trabalhar dentro dos limites do cliente (ex.: dinheiro, tempo, necessidade e efetividade) e uma vez definido o caminho a ser seguido, pensar em questões delicadas como, desempenho, recuperação de desastres, entre outros.
    O problema: em um cliente vamos utilizar o SQL Server filestream para armazenamento de imagens e espera-se receber para um único evento em torno de 16 milhões de imagens, com tamanho médio de 230 KB. Fazendo o cálculo nós temos aproximadamente 3,5 TB de imagens, fora os dados relacionados.
    Então considerando um eventual desastre com o banco de dados, não queremos esperar X horas com o sistema off-line até o restore finalizar, então foi proposto dividir os arquivos em regiões diferentes, permitindo a recuperação parcial do banco de dados. Para fins didáticos eu montei o script abaixo, que mostra como podemos fazer um piecemeal restore com filestream no SQL Server, e coloco nesse post para seguir como guia.
    Observação importante: não vou entrar no mérito de onde deve ser armazenado as imagens, não estou preocupado com alinhamento dos índices particionados, partition switching ou retirada dos dados desse banco, evitando sua obesidade. Claro que são questões importantes e que devem ser pensadas, mas isso faço junto com o cliente e colocar tudo aqui ia deixar esse artigo muito grande.

Estrutura do banco de dados

    O banco de dados possui o filegroup (FG) primário só com os objetos definidos na model e um filegroup para armazenar o restante dos dados do banco, que não sejam imagens. Até o momento não houve necessidade de particionar outros objetos ou dividir esse arquivo de dados. Além disso temos 5 FGs, uma para cada região que será definida, responsável por armazenar as imagens.

Script 01 – Definição do banco de dados

USE master
GO

exec sp_configure 'filestream access level', 2
RECONFIGURE

-- 2) Cria banco de dados com Filestream e tabela que utiliza recurso
IF EXISTS (SELECT * FROM sys.databases WHERE name = N'NimbusFS')
  DROP DATABASE NimbusFS
GO

CREATE DATABASE NimbusFS ON PRIMARY
  ( NAME = NimbusFS_Primary, 
    FILENAME = N'D:\Temp\SQLData\NimbusFS.mdf',
    SIZE = 15MB,
    MAXSIZE = 50MB, 
    FILEGROWTH = 15MB),
FILEGROUP FGNimbusFSData DEFAULT
  ( NAME = NimbusFS_Data, 
    FILENAME = N'D:\Temp\SQLData\NimbusFS_Data.mdf',
    SIZE = 100MB,
    MAXSIZE = 10GB, 
    FILEGROWTH = 100MB) ,
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM
  ( NAME = NimbusFS_FSG01, 
    FILENAME = N'D:\Temp\SQLData\FSG01'),
FILEGROUP FileStreamGroup2 CONTAINS FILESTREAM
  ( NAME = NimbusFS_FSG02, 
    FILENAME = N'D:\Temp\SQLData\FSG02'),
FILEGROUP FileStreamGroup3 CONTAINS FILESTREAM
  ( NAME = NimbusFS_FSG03, 
    FILENAME = N'D:\Temp\SQLData\FSG03'),
FILEGROUP FileStreamGroup4 CONTAINS FILESTREAM
  ( NAME = NimbusFS_FSG04, 
    FILENAME = N'D:\Temp\SQLData\FSG04'),
FILEGROUP FileStreamGroup5 CONTAINS FILESTREAM
  ( NAME = NimbusFS_FSG05, 
    FILENAME = N'D:\Temp\SQLData\FSG05')
LOG ON 
  ( NAME = 'NimbusFS_log', 
    FILENAME = N'D:\Temp\SQLLog\NimbusFS.ldf',
    SIZE = 50MB, 
    MAXSIZE = 1GB, 
    FILEGROWTH = 10MB);
GO

USE NimbusFS
GO

ALTER DATABASE NimbusFS
SET RECOVERY FULL
GO

    A tabela região é definida pensando nas 5 regiões do Brasil, mas notem que ela é um termo abstrato e, caso seja necessário, podemos definir nossas “pseudo-regiões”, separando São Paulo ou outros estados com maior volume de dados, ou como o negócio ditar.
    Uma função de particionamento para as regiões é utilizada em 2 esquemas. O primeiro não separa os dados em filegroups diferentes, já o segundo separa os arquivos em diferentes FGs que contém filestream. Depois a tabela Documento é criada, seguindo as definições do filestream e particionando pelo atributo IDRegiao, seguindo os esquemas previamente definidos.
    Talvez você deva estar se perguntando porque não utilizamos o tempo no particionamento, o que é mais comum. Porém neste problema o tempo não é significativo, pois potencialmente um conjunto muito grande de imagens estarão dentro de uma mesma partição, que seria a ativa para o negócio, o que não ajudaria a diminuir o tempo de recuperação.

Script 02 – Definição do banco de dados

IF (OBJECT_ID('dbo.Regiao') IS NOT NULL)

       DROP TABLE Regiao

GO


-- Filegroup default

CREATE TABLE dbo.Regiao

(ID INT IDENTITY NOT NULL PRIMARY KEY

 , Nome VARCHAR(100))

go


INSERT INTO dbo.Regiao (Nome) values ('Norte'), ('Sul'), ('Nordeste'), ('Sudeste'), ('Centro-Oeste')

go


IF (OBJECT_ID('dbo.Documento') IS NOT NULL)

       DROP TABLE Documento

GO


CREATE PARTITION FUNCTION MyPartFunction (INT) AS RANGE LEFT FOR VALUES (1,2,3,4);

CREATE PARTITION SCHEME MyPartScheme AS PARTITIONMyPartFunction ALL TO (FGNimbusFSData);

CREATE PARTITION SCHEME MyPartSchemeFS AS PARTITIONMyPartFunction TO (FileStreamGroup1, FileStreamGroup2,FileStreamGroup3, FileStreamGroup4, FileStreamGroup5);


CREATE TABLE dbo.Documento

(ID INT IDENTITY NOT NULL

 , ChaveDoc UNIQUEIDENTIFIERNOT NULL DEFAULT NEWID() ROWGUIDCOL UNIQUE ONFGNimbusFSData

 , IDFK INT NOT NULL

 , IDRegiao INT NOT NULL

 , Doc VARBINARY(MAX) FILESTREAM

) ON MyPartScheme(IDRegiao)

FILESTREAM_ON MyPartSchemeFS;

go


ALTER TABLE Documento

ADDCONSTRAINT FK_Documento_Regiao_IDRegiao

FOREIGN KEY (IDRegiao)

REFERENCES dbo.Regiao (ID)


go


Inserindo dados e realizando backups

    Uma vez com a estrutura criada, os próximos passos são auto explicativos. Vou inserindo registros no banco de dados, em diferentes regiões, seguidas de um backup full, inserts, backup do filegroup e por fim, um backup do fim do log, que coloquei lá para representar um cenário onde perdemos os dados mas ainda consigo um “tail do log”’.
    Enquanto você estiver executando os passos desse script, recomendo dar uma olhada nos diretórios do filestream. Assim que um registro da região é inserido, o arquivo aparece no filegroup da partição, mostrando claramente o “roteamento” dos dados.

Script 03 – Dados e backups

INSERTINTO Documento (IDFK, IDRegiao, Doc) VALUES (111, 1, CAST('Doc 111 - Regiao 1' ASvarbinary(MAX)));

INSERTINTO Documento (IDFK, IDRegiao, Doc) VALUES (222, 2, CAST('Doc 222 - Regiao 2' ASvarbinary(MAX)));

INSERTINTO Documento (IDFK, IDRegiao, Doc) VALUES (333, 3, CAST('Doc 333 - Regiao 3' ASvarbinary(MAX)));

INSERTINTO Documento (IDFK, IDRegiao, Doc) VALUES (444, 4, CAST('Doc 444 - Regiao 4' ASvarbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (555, 5, CAST('Doc 555 - Regiao 5' AS varbinary(MAX)));


BACKUP DATABASE NimbusFS

TO DISK = 'D:\Temp\SQLBackup\NimbusFSFull.bak'

WITH INIT


INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (112, 1, CAST('Doc 112 - Regiao 1' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (223, 2, CAST('Doc 222 - Regiao 2' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (334, 3, CAST('Doc 333 - Regiao 3' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (445, 4, CAST('Doc 444 - Regiao 4' AS varbinary(MAX)));

INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (556, 5, CAST('Doc 555 - Regiao 5' AS varbinary(MAX)));

GO


SELECT * FROM Documento

GO


BACKUP DATABASE NimbusFS

FILEGROUP = 'PRIMARY'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_Primary.bak'

WITH INIT

go


BACKUP DATABASE NimbusFS

FILEGROUP = 'FGNimbusFSData'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSData.bak'

WITH INIT

go


INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (113, 1, CAST('Doc 113 - Regiao 1' AS varbinary(MAX)));


BACKUP DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup1'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG1.bak'

WITH INIT

GO


INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (224, 2, CAST('Doc 222 - Regiao 2' AS varbinary(MAX)));


BACKUP DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup2'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG2.bak'

WITH INIT

GO


INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (335, 3, CAST('Doc 333 - Regiao 3' AS varbinary(MAX)));


BACKUP DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup3'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG3.bak'

WITH INIT

GO


INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (446, 4, CAST('Doc 444 - Regiao 4' AS varbinary(MAX)));


BACKUP DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup4'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG4.bak'

WITH INIT

GO


INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (557, 5, CAST('Doc 555 - Regiao 5' AS varbinary(MAX)));


BACKUP DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup5'

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG5.bak'

WITH INIT

GO


INSERT INTO Documento (IDFK, IDRegiao, Doc) VALUES (558, 5, CAST('Doc 555 - Regiao 5' AS varbinary(MAX)));


SELECT * FROM Documento

GO


-- "Tail do log"

BACKUP LOG NimbusFS

TO DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'


WITH INIT;


Recuperando de um desastre

    Supondo que neste momento você perdeu seu banco de dados e tem que partir para o restore. Você não gostaria de esperar todo o restore terminar para indicar ao negócio que todos podem voltar ao trabalho, mas sim permitir que as pessoas voltem a trabalhar aos poucos, minimizando o impacto para as regiões mais críticas. Certo?
    Então se você fez seu dever de casa certinho, pode fazer um piecemeal restore...

Script 04 – Iniciando o piecemeal restore

USE master

GO


-- Simula o desastre... Ou um DBA brilhando... :-)

DROPDATABASE NimbusFS;

GO


-- Início do piecemeal restore

RESTOREDATABASE NimbusFS

FILEGROUP= 'PRIMARY'

FROMDISK = 'D:\Temp\SQLBackup\NimbusFS_Primary.bak'

WITH PARTIAL, NORECOVERY


RESTORE DATABASE NimbusFS

FILEGROUP = 'FGNimbusFSData'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSData.bak'

WITH NORECOVERY


RESTORE LOG NimbusFSFROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

    Temos restaurado o filegroup primário e o FG com os dados (sem as imagens), colocando-os em um estado consistente. A partir deste momento você já tem acesso ao banco e alguma funcionalidade pode ser utilizada.
    Consultas às tabelas de sistemas, tabelas que não usam o filestream e até as colunas da tabela que não são filestream, podem ser manipuladas. Porém quando você tentar acessar um dado que está em filegroup ainda não restaurado, vai ver a mensagem 670, conforme abaixo.

Script 05 – Utilizando parcialmente o banco

SELECT * FROM NimbusFS.sys.objects WHERE TYPE = 'U';

SELECT * FROM NimbusFS.dbo.Regiao;

SELECT * FROM NimbusFS.dbo.Documento;


/*

Msg 670, Level 16, State 1, Line 177

Large object (LOB) data for table "dbo.Documento" resides on an offline filegroup ("FileStreamGroup1") that cannot be accessed.

*/



SELECT id, ChaveDoc, IDRegiao FROMNimbusFS.dbo.Documento;


    A partir deste momento sua escolha é decidir qual região deve voltar primeiro (em geral isso está definido no plano de DR que você escreveu, mas enfim...). O exemplo abaixo deixa online a região 2 e faz consulta filtrando pela região, que funciona perfeitamente, mas não para as outras regiões.

Script 06 – Restore da região 02

-- Regiao 02

RESTORE DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup2'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG2.bak'

WITH NORECOVERY


RESTORE LOG NimbusFS

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

GO


-- Erro

SELECT* FROM NimbusFS.dbo.Documento;

-- E...


SELECT * FROM NimbusFS.dbo.Documento WHEREIDRegiao = 2;


    Continua-se o procedimento de recuperação até que todos os filegroups estejam online. Vale notar também que a última inserção, que estava no backup do transaction log, também é recuperada com sucesso.

Script 07 – Restore das outras regiões

-- Regiao 01

RESTORE DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup1'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG1.bak'

WITH NORECOVERY


RESTORE LOG NimbusFS

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

GO


-- Erro

SELECT* FROM NimbusFS.dbo.Documento;

-- E...

SELECT * FROM NimbusFS.dbo.Documento WHEREIDRegiao in (1,2);


-- Regiao 04

RESTORE DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup4'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG4.bak'

WITH NORECOVERY


RESTORE LOG NimbusFS

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

GO


-- Erro

SELECT* FROM NimbusFS.dbo.Documento;

-- E...

SELECT * FROM NimbusFS.dbo.Documento WHEREIDRegiao in (1,2,4);



-- Regiao 03

RESTORE DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup3'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG3.bak'

WITH NORECOVERY


RESTORE LOG NimbusFS

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

GO


-- Erro

SELECT* FROM NimbusFS.dbo.Documento;

-- E...

SELECT * FROM NimbusFS.dbo.Documento WHEREIDRegiao in (1,2,3,4);



-- Regiao 05

RESTORE DATABASE NimbusFS

FILEGROUP = 'FileStreamGroup5'

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_FSG5.bak'

WITH NORECOVERY


RESTORE LOG NimbusFS

FROM DISK = 'D:\Temp\SQLBackup\NimbusFS_LOG01.trn'

GO


SELECT * FROM NimbusFS.dbo.Documento;

-- E...


SELECT * FROM NimbusFS.dbo.Documento WHEREIDRegiao in (1,2,3,4,5);


    Neste momento o seu banco de dados está todo online! Vale ressaltar que o processo de recuperação como um todo vai levar mais tempo, porém você consegue ir aos poucos permitindo que regiões da sua empresa volte a trabalhar, sem ter que esperar todo o processo de DR.

    Gostou? Espero que sim! E foi bom voltar a postar posts puramente técnicos... Que agora virão com maior frequência, prometo.

Quer o script? Pode fazer o download do OneDrive.

Abraços,

Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
www.srnimbus.com.br

Recoverying Model Database

Recentemente tive algumas discussões a respeito de como proceder em caso a base de dados Model seja corrompida. A primeira coisa que escutei foi: Nunca tivemos a base de dados model corrompida! É tão pequena que não teriamos problema Bom, ai é que surge o problema! Se estamos pensando em um verdadeiro cenário de Disaster […]

Material de Apoio – Março 2015

Salve, salve, bom dia. Olá você, bordão bastante conhecido dos amantes de futebol!!! Estou retornando minhas atividades após 20 dias de descanso (férias), ainda em um ritmo bem abaixo do normal, mas vamos em frente. Mesmo de férias, procurei tirar alguns dias para estudar um pouco mais sobre o SQL Server e o fantástico mundo […]

[SQLServerDF] Material XXV – Paralelismo no SQL Server

Faz alguns dias eu apresentei no SQLServerDF sobre paralelismo no SQL Server, então deixo aqui o material (PDF e scripts).
Conforme informei para o pessoal, a sessão em breve vai virar um treinamento on-demand da Nimbus, com um pouco mais conteúdo, então reaproveitei o PPT, pois não tive tempo para alterar.

Você pode baixar o material que está no OneDrive.

Abraços,

Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.br
www.twitter.com/luticm
www.srnimbus.com.br

Site guia do DBA e 50% de desconto em um treinamento Nimbus

Ontem recebi um e-mail com uma iniciativa recente voltada para banco de dados e DBAs, o http://guiadba.com.br/.
Não conhecia o site, mas como disse a eles, toda iniciativa de divulgar conhecimento é válida, e espero que eles consigam manter a regularidade e postar conteúdo de qualidade.

Como forma de apoio a Sr. Nimbus forneceu 50% de desconto em qualquer treinamento em 2015, que eles vão sortear na próxima semana, então é uma boa oportunidade para você participar. Espero que quem receber o desconto possa participar da próxima turma e entender porque todos falam tão bem dos treinamentos da Nimbus (http://www.srnimbus.com.br/turmas/)! Sim, aqui a modéstia passou longe…

Abraços,

Luciano Caixeta Moreira - {Luti}
luciano.moreira@srnimbus.com.brwww.twitter.com/luticm
www.srnimbus.com.br

Novo artigo simple-talk–Sort warnings…

Depois de quase 1 ano sem escrever pro Simple-Talk… here we go com um novo artigo ! https://www.simple-talk.com/sql/performance/never-ignore-a-sort-warning-in-sql-server/ Abs. Fabiano

Azure SQLDatabase v12 Premium Editions and Available Cores

A couple of days ago, during the great SQL Unplugged live show, I have asked a couple of questions the leadership trio consisting of Shawn Bice, Nigel Ellis, and Rohan Kumar and was lucky enough to have them answered. The…
Go to Top