Arquivos

Archive for the ‘SQL Server’ Category

Database cast – Log de transações

2 de julho de 2014 3 comentários

Fala galera, tive a honra em participar de mais um databasecast com os brothers Mauro, Wagner e Ricardo Rezende (a.k.a. Wolverine ;P)… Desta vez falamos sobre Log de transações… Segue o link:

http://imasters.com.br/banco-de-dados/databasecast-log-de-banco-de-dados/

Hope you like it!

Abs.

Fabiano

Arquivos, SQLSat284 e Fórum Tecnico MS Brasil

28 de abril de 2014 1 comentário

Fala galera, os últimos dias foram corridos porém extremamente prazerosos.

Fiz duas apresentações, uma na MS Brasil onde falei sobre Otimizador de Consultas do SQL2014 e outra no SQLSat284 em POA onde falei sobre Tuning do Início ao Fim.

Segue o link para download dos arquivos utilizados nas minhas sessões:

Fórum Tecnico na MS Brasil “Novidades para otimização e performance no SQL Server 2014”: http://1drv.ms/1fmpsuI

SQLSat284 – “Tuning do Início ao Fim”: http://1drv.ms/1m2Hv7z

Algumas imagens dos ppts:

Fórum técnico na Microsoft Brasil:

clip_image002

clip_image004

SQLSat284 em POA:

clip_image006

clip_image008

Databasecast–Indexação

16 de setembro de 2013 2 comentários

Galera, está no ar o database cast que gravei com o pessoal do Imasters.

http://imasters.com.br/banco-de-dados/sql-server/databasecast-indices/

Espero que gostem… O bate papo foi bem legal…

Abs.

Fabiano

Fabiano vs Me empresta um livro ?

2 de abril de 2013 6 comentários

Ontem um amigo meu disse que queria estudar sobre performance e se aprofundar no SQL Server e perguntou se eu tinha algum livro para emprestar pra ele…

Separei os seguintes livros de minha biblioteca:

image

Esses são livros que considero essenciais para todo profissional SQL Server que quer se aprofundar no assunto performance. É importante dizer que existe uma GRANDE diferença entre alguém que sabe a melhor prática e alguém que sabe porque a melhor prática é a melhor prática… Como nós já sabemos no SQL Server quase tudo depende, ou seja, depende do cenário, depende da tabela, depende do índice, depende do número de modificações que a tabela sofre, depende do collate, depende do número de CPUs, depende do número de memória disponível, depende das estatísticas disponíveis, depende do trafego de rede, depende do perfil (OLTP ou OLAP), depende de diversas configurações… ou seja, depende.

Entenda como funciona para saber onde usar, quando usar e quando não usar.

Fica a dica!

E como diria o Sir. Itzik na assinatura do livro que ele me deu…

image

Winking smile

CategoriasLivro, SQL Server

Sorteio, SQL DBA Bundle ($1.895) de graça! Red-Gate!

14 de março de 2013 43 comentários

E ai galera beleza ?

Como alguns de vocês já sabem eu faço parte do programa Friends of Red-Gate e realmente eu gosto MUITO das ferramentas deles.

Atualmente tenho trabalhado muito com o SQL Monitor, e simplesmente não consigo mais viver sem o SQL Prompt, quando vou trabalhar em uma máquina sem ele, ficou maluco!

A Red-Gate também patrocina o meu blog, por isso tem o logozinho deles ai em cima! Smile Se vocês ainda não conhecem os produtos deles, de verdade, vale a pena dar uma olhada.

Tenho um relacionamento muito bom com eles e recentemente eles me enviaram uma licença do SQL DBA Bundle pra eu sortear no blog Smile, exato, são $1.895 doletas em produtos deles.

Mano, é sério… isso significa, SQL Prompt + SQL Monitor + SQL Compare + SQL Backup PRO e várias outras ferramentas de graça… imperdível…

Pra participar do sorteio é fácil:

Publique o seguinte texto no seu facebook ou twitter:

“Eu quero ganhar um SQL DBA Bundle da Red-Gate (http://redg.at/Zma34t). Se você também quer leia mais no http://blogfabiano.com

Coloque um link comprovando sua de sua publicação aqui no meu blog!

Se você não tem facebook, pode usar o twitter, orkut, ou mesmo fazer um reblog desse post no seu blog, o importante é não esquecer de incluir um comentário comprovando sua publicação da promoção.

No mês que vem eu vou fazer o sorteio usando algum serviço de sorteio online qualquer, gravar um video com o sorteio e publicar o resultado no meu blog.

Regras:

  • Se eu achar que tenho que mudar alguma coisa no sorteio, eu mudo e pronto e acabo, quem manda nessa porra sou eu. Hot smile
  • Se o vencedor não se manifestar em até 4 dias, farei outro sorteio.
  • Caso eu não tenha o e-mail do vencedor, eu vou avisar no dia da publicação do resultado, este deve me enviar seu e-mail no prazo dos 4 dias.
  • Só tenho 1 licença, ou seja, haverá apenas 1 ganhador.
  • 1 dia antes de fazer o sorteio aviso aqui no meu blog.
    That’s all follks, good luck!

Top 5 Hard-earned Lessons of a DBA
Read lesson two, ‘Beating Backup Corruption’ by Shawn McGehee, and learn how to:
- Avoid subtle failings that could end up defeating your recovery plans.
- Ensure your data pages are corruption free.
- Put together a multi-pronged defence against data corruption.
Learn to beat backup corruption.

red

Artigo–DBCC OPTIMIZER_WHAT IF

4 de fevereiro de 2013 Deixe um comentário

Artigo novo publicado no Simple-Talk, check it out:

 

“SQL Server’s Query optimiser judges the best query plan from the data in the relevant tables and the server’s hardware. How, then, can you investigate the query plans being generated for slow-running queries on a customer’s production server when you can neither access the server, nor recreate the database from a backup?”

http://www.simple-talk.com/sql/database-administration/using-optimizer_whatif-and-statsstream-to-simulate-a-production-environment/

Abs.

Free Execution Plans e-book, e é meu :-)

31 de outubro de 2011 6 comentários

Galera, essa notícia eu vinha esperando por um bom tempo. O Simple-Talk publicou um e-book com vários artigos que escrevi sobre os operadors dos planos de execução.

Você pode fazer o download do livro aqui, é “di gratis”: http://www.simple-talk.com/books/sql-books/complete-showplan-operators/

Espero que gostem, eu realmente adorei escrever os article, e agora fico hiper mega feliz de ver que isso virou um livro Open-mouthed smile.

image

Tem vários textos que eu gosto muito, mas eu acho que meu predileto é o seguinte:

To show the functionality of the Eager Spool we’ll go back in time a bit, to the time when I was just a project being planned, but some other geeks working intensively with databases.

It was Halloween; the cold winter’s night was black as pitch, (I really don’t know if was winter, but I thought it would sound more thrilling) and the wind howled in the trees. It was 1976 and the children was demanding “tricks or treat” in the houses. The full moon shone and illuminated the whole city when suddenly, some clouds crossed the moon making the night even more dark and gloomy. It was possible to smell and taste the tension in the air like a stretched rubber band so close to burst. People walking in the street felt that someone was observing them, and when they looked closely in their back, they see two red eyes waiting and looking out for a prey unprotected.

Was that their imagination? Or just the wrong night to work with databases?

Meanwhile in a place not far away, an update was started on a database by a skeleton staff, to update the salary by 10% of all employees who earned less than $ 25.000,00 dollars.  Their feelings of impending doom increased as the query failed to complete in the expected time. When, at length, it did, they found to their horror that every employee had their pay increased to $ 25.000,00. It was the stuff of DBA nightmares.

 

Agora uma boa notícia para quem vai no SQLSaturday 100, a Red-Gate me enviou alguns livros impressos pra eu poder distribuir pra galera Laughing out loud. É isso mesmo, vou distribuir alguns livros na minha sessão, portanto compareça.

2011-10-31_12-12-37_279

Não ficou muito grande, portanto você consegue ler rápidinho Winking smile.

Thanks so much for Red-Gate and Simple Talk to make this possible, I loved!

Abs.

Como remover um Plano de execução do CachePlan

5 de janeiro de 2010 5 comentários

Pessoal depois de alguns bons dias de férias, volto com uma dica bem legal que li.

Para limpar o PlanCache de uma instância SQL utilizamos o DBCC FREEPROCCACHE, para limpar o cache de apenas um banco específico utilizamos o DBCC FLUSHPROCINDB (<DBID_DO_BANCO>). Mas fica uma pergunta, tem alguma forma de limpar o cache de apenas um determinado comando?

Sim. Podemos utilizar o DBCC FREEPROCCACHE passando o [sys.dm_exec_cached_plans].PlanHandle, por ex:

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st

O comando acima retorna o PlanHandle dos planos, depois e só passar ele como parâmetro para o FREEPROCCACHE

DBCC FREEPROCCACHE(0x06000500AF643E18B840C234000000000000000000000000)

Créditos para o GlennBerry.

Abraços

CategoriasSQL Server

Query Optimizer VS Foreign Keys

10 de novembro de 2009 Deixe um comentário

Pessoal, depois da WebCast que fiz falando sobre o Query Optimizer, quero compartilhar um caso onde na minha opinião ele infelizmente não foi tão esperto assim :-(

Por sinal o vídeo já esta disponível para download…. é só acessar o link de cadastro para a webcast e baixar…

Veja bem, no meu banco de dados tenho 2 tabelas assim…

CONCC030 – Tabela de propostas, e CONCC036 tabela que relaciona uma proposta com uma Cota.

As chaves das duas tabelas são, ID_Empresa, ID_Documento, ID_Tipo_Documento. Na tabela CONCC036 eu tenho uma foreign key forçando a integridade entre as tabelas. A foreign key é formada pelas 3 colunas que acabei de mencionar.

Tenho uma consulta assim:

SELECT ID_Bem
  FROM CONCC030
INNER JOIN CONCC036
    ON CONCC036.ID_Tipo_Documento = CONCC030.ID_Tipo_Documento
   AND CONCC036.ID_Documento = CONCC030.ID_Documento
WHERE CONCC036.ID_Empresa = 1
   AND CONCC036.ID_Cota = 123456

Bom, como podemos observar, o join não esta sendo feito pelas 3 colunas da foreign key… esta faltando especificar a coluna ID_Empresa, porém ela esta no WHERE, fazendo um filtro por ID_Empresa = 1.

O que podemos concluir com isso, ora, o QO sabe que existe uma foreign key entre as tabelas e sabe quais são as colunas da FK, porque então não utilizar o filtro da tabela CONCC036.ID_Empresa = 1 (WHERE) e também aplicar este filtro na CONCC030.ID_Empresa ?

Com certeza o desenvolvedor que escreveu esta consulta não especificou o ID_Empresa no join justamente porque ele sabia que a coluna seria utilizada no where. Mas isso causou um problema para o QO, pois ele não conseguiu utilizar um índice que temos para fazer o link entre as duas tabelas, já que o índice esta ordenado justamente pela ordem, ID_Empresa, ID_Documento, ID_Tipo_Documento….

O QO poderia muito bem aplicar o filtro em CONCC030.ID_Empresa = 1 e utilizar as outras colunas no join para fazer o filtro…

Outra coisa triste, a funcionalidade que mencionei na WebCast sobre ele descartar as tabelas caso a existência de uma foreign key, só funciona para foreign Keys ligadas por apenas uma coluna… ou seja, se você tiver uma foreign key que liga uma tabela por mais de uma coluna e tentar utilizar a funcionalidade que mencionei, não vai conseguir…

Bom, fica a seguinte dica, ao escrever uma consulta, SEMPRE(does not matter what!) especifique todas as colunas no seu join…

Abraço…

CategoriasSQL Server

Date Correlation Optimization Internals

14 de outubro de 2009 1 comentário

Introdução

No artigo passado vimos como o Date_Correlation_Optimization funciona, e vimos que se bem utilizado, ele pode proporcionar um ganho considerável de performance em nossas consultas. Desta vez vamos analisar esta feature mais profundamente, para entender melhor a lógica utilizada para conseguir descobrir a correlação entre os campos de data.

A primeira pergunta que devemos fazer é: Como e o que o SQL fez para descobrir os valores a serem utilizados no filtro da coluna Data_Entrega?

Bom vamos começar do começo :-). E separando em partes separadas :-).

Analisando o código gerado pelo Query Optimizer

Lembra que eu disse que o SQL pegava informações sobre as colunas correlatas e guardava como se fossem estatísticas? Pois é, o SQL cria uma view indexada com informações sobre as colunas.

Vamos criar novamente a estrutura apresentada como exemplo, mas desta vez vamos executar o script em partes para ver os comandos gerado pelo SQL Server. Vá para o SSMS, execute apenas a parte de criação da estrutura de tabelas, índice.

IF OBJECT_ID(‘Pedidos’) IS NOT NULL

BEGIN

DROP TABLE Items

DROP TABLE Pedidos

END

GO

CREATE TABLE Pedidos(ID_Pedido Integer Identity(1,1),

Data_Pedido DateTime NOT NULL,–The columns cannot accept null values

Valor Numeric(18,2),

CONSTRAINT xpk_Pedidos PRIMARY KEY (ID_Pedido))

GO

CREATE TABLE Items(ID_Pedido Integer,

ID_Produto Integer,

Data_Entrega DateTime NOT NULL,–The columns cannot accept null values

Quantidade Integer,

CONSTRAINT xpk_Items PRIMARY KEY NONCLUSTERED(ID_Pedido, ID_Produto))

GO

– At least one of the DATETIME columns, must belong to a cluster index

CREATE CLUSTERED INDEX ix_Data_Entrega ON Items(Data_Entrega)

GO

Após criar as tabelas e o índice clustered sobre a tabela Items, vamos configurar um Trace para capturar algumas informações sobre os comandos que o SQL Server gera internamente para criação e controle da view, e contem as informações sobre as colunas correlatas. Crie um novo trace e selecione os eventos SP: StmtCompleted e SP: StmtStarted, são eles que irão nos mostrar os comandos gerados pelo SQL.

clip_image001

Após iniciar o trace, execute o comando abaixo:

– There must to be a foreign key relationship between the tables that contain correlation date

ALTER TABLE Items ADD CONSTRAINT fk_Items_Pedidos FOREIGN KEY(ID_Pedido) REFERENCES Pedidos(ID_Pedido)

GO

No Profiler serão capturadas as seguintes linhas:

clip_image002

Segue o código gerado pelo SQL Server.

CREATE UNIQUE CLUSTERED INDEX [i__MPStats_Sys_fk_Items_Pedidos_8e035f2] ON [dbo].[_MPStats_Sys_08E035F2_{39D85729-E411-4A66-BC1E-1184EF97837A}_fk_Items_Pedidos](ParentPID,ChildPID)

insert [dbo].[_MPStats_Sys_08E035F2_{39D85729-E411-4A66-BC1E-1184EF97837A}_fk_Items_Pedidos] select * from [dbo].[_MPStats_Sys_08E035F2_{39D85729-E411-4A66-BC1E-1184EF97837A}_fk_Items_Pedidos]

SELECT @MaxParPID = MAX(ParentPID),

@MinParPID = MIN(ParentPID),

@MaxChdPID = MAX(ChildPID),

@MinChdPID = MIN(ChildPID),

@countPID = COUNT(*)

FROM [dbo].[_MPStats_Sys_08E035F2_{39D85729-E411-4A66-BC1E-1184EF97837A}_fk_Items_Pedidos] WITH(NOEXPAND)

WHERE C > 0

Como podemos ver, o SQL gerou 3 comandos, primeiro um create index em uma view chamada _MPStats…, um insert nesta view e depois um select nesta view. Primeiro vamos entender o seguinte, de onde surgiu esta view?

Entendendo a View criada pelo SQL Server

No profiler não pegamos o código de criação da view mas ela foi criada internamente pelo SQL Server, podemos comprovar isso efetuando um select na sys.views.

clip_image003

Repare que existe uma coluna na sys.views que informa se a view é utilizada pelo date correlation. Neste caso vemos o valor preenchido com 1.

O nome da view é um pouco estranho a primeira vista, mas ele tem uma lógica que é a seguinte:

_MPStats_Sys_<constraint_object_id>_<GUID>_<FK_constraint_name>

Seguindo esta regra temos o seguinte:

_MPStats_Sys_15460CD7_{628BDBBC-4E23-4C9E-A8EA-CE08C7C4F3EA}_fk_Items_Pedidos

Onde:

· Roxo - Valor Fixo.

· Vermelho - Hexadecimal do Object_ID da Foreign Key. Apenas para conhecimento, se você quiser confirmar se o valor esta correto (como eu), você pode transformar o valor de Object_ID em hexadecimal e verificar se ele bate com o nome utilizado pelo SQL Server. Por ex:

clip_image004

clip_image006 clip_image008

· Verde – GUID gerado internamente pelo SQL Server.

· Azul – Nome da foreign key que liga as duas tabelas.

Se você tentar efetuar um select nesta view para ver o que ela retorna, receberá a seguinte mensagem.

clip_image009

Para efetuar o select e verificar os dados que são armazenadas na view vamos fazer o seguinte, criar uma nova view com base no código fonte desta view do sistema.

Pergunta, como saber para ver o código fonte dela? Fácil, só rodar um sp_helptext na view.

clip_image010

Não se preocupe em entender o que a view está fazendo agora, vamos ver daqui a pouco.

Com o código, vamos criar outra view, chamada vw_test.

CREATE VIEW [dbo].vw_test

WITH SCHEMABINDING

AS

SELECT DATEDIFF(day, convert(datetime2, ’1900-01-01′, 121), LEFT_T.[Data_Pedido]) / 30 as ParentPID,

DATEDIFF(day, convert(datetime2, ’1900-01-01′, 121), RIGHT_T.[Data_Entrega]) / 30 as ChildPID,

COUNT_BIG(*) AS C

FROM [dbo].[Pedidos] AS LEFT_T

JOIN [dbo].[Items] AS RIGHT_T

ON LEFT_T.[ID_Pedido] = RIGHT_T.[ID_Pedido]

GROUP BY DATEDIFF(day, convert(datetime2, ’1900-01-01′, 121),LEFT_T.[Data_Pedido]) / 30,

DATEDIFF(day, convert(datetime2, ’1900-01-01′, 121), RIGHT_T.[Data_Entrega]) / 30

Agora já conseguimos efetuar o select na view, porém as tabelas Pedidos e Items estão vazias, vamos inserir alguns dados nelas.

DECLARE @i Integer

SET @i = 0

WHILE @i < 1000

BEGIN

INSERT INTO Pedidos(Data_Pedido,

Valor)

VALUES(CONVERT(VarChar(10),GetDate() – ABS(CheckSum(NEWID()) / 10000000),112),

ABS(CheckSum(NEWID()) / 1000000))

SET @i = @i + 1

END

GO

INSERT INTO Items(ID_Pedido, ID_Produto, Data_Entrega, Quantidade)

SELECT ID_Pedido,

ABS(CheckSum(NEWID()) / 10000000),

CONVERT(VarChar(10),Data_Pedido + ABS(CheckSum(NEWID()) / 100000000),112),

ABS(CheckSum(NEWID()) / 10000000)

FROM Pedidos

GO

INSERT INTO Items(ID_Pedido, ID_Produto, Data_Entrega, Quantidade)

SELECT ID_Pedido,

ABS(CheckSum(NEWID()) / 10000),

CONVERT(VarChar(10),Data_Pedido + ABS(CheckSum(NEWID()) / 100000000),112),

ABS(CheckSum(NEWID()) / 10000000)

FROM Pedidos

GO

Vamos consultar a view apenas para ver o que o SQL guarda nela. Lembrando apenas que criamos a vw_test para poder ver o que está na view criada internamente pelo SQL Server, como o código de ambas é igual temos exatamente o que esta salvo na view original.

clip_image011

É importante destacar que esta é uma view indexada, ou seja, ela ocupa espaço em disco e tem de ser atualizada durante os inserts, updates e deletes nas tabelas Pedidos e Items. Portanto se você identificar que suas operações de manutenção na tabela estão lentas, você pode analisar se é a view que está causando este problema.

Podemos ver o espaço ocupado pela view utilizando a proc sp_spaceused.

clip_image012

Entendendo a Mágica

Bom, agora que entendemos quando a view é criada e como ela é atualizada, vamos entender o principal. Qual a lógica utilizada para identificar os valores das colunas correlatas.

Na view temos duas colunas uma chamada ParentPID e outra chamada ChildPID, a seguinte regra esta sendo utilizada para retornar seus valores.

ParentPID = DATEDIFF(day, convert(datetime, ’1900-01-01′, 121), LEFT_T.[Data_Pedido]) / 30

ChildPID = DATEDIFF(day, convert(datetime, ’1900-01-01′, 121), RIGHT_T.[Data_Entrega]) / 30

Traduzindo:

ParentPID e ChildPID = Me retorne a quantidade de dias que faltam para que 1900-01-01 chegue na data gravada em Data_Pedido, e divida este resultado por 30. Por ex: De 1900-01-01 até 2009-01-01 se passaram 39812 dias. Dividindo estes dias por 30 temos 1327.

A divisão por 30 é efetuada para que os valores agrupados sejam poucos, e neste caso temos sempre uma referência de alguns poucos meses entre uma data e outra.

Quando construímos uma consulta utilizando a coluna Data_Pedido, o SQL pega o valor que usamos no where e aplica a mesma fórmula para poder chegar no valor de dias dividido por 30. O Query Optimizer vai na view e procurando pelo valor de ChildPID(Data_Entrega) passando como filtro o valor de ParentPID (que é a coluna Data_Pedido) que ele calculou. Desta forma o SQL irá retornar o valor coluna ChildPID que esta relacionada com a ParentPID(lembre-se de que o SQL sabe que existe o relacionamento entre ID_Pedido, veja na view que ele faz o join.). Com o valor do ChildPID em mãos o SQL aplica a regra reversa para poder achar as datas que ele vai utilizar como predicate na coluna Data_Entrega. Vamos ver isso na prática e seguindo passo a passo para ficar mais claro.

Suponha que eu crie a seguinte consulta.

SELECT *

FROM Pedidos

INNER JOIN Items

ON Pedidos.ID_Pedido = Items.ID_Pedido

WHERE Pedidos.Data_Pedido = ’20090801′

O filtro foi aplicado na coluna Data_Pedido, o SQL precisa identificar quais os valores ele deve informar como predicate na tabela Items.Data_Entrega. Vamos no passo a passo:

1. O Query Optimizer vai na view identificar qual é o maior e menos valor de ChildPID para poder fazer o calculo reverso. Você pode capturar esta consulta na view no profiler.

a. A seguinte consulta foi executada, eu alterei um pouco para ficar mais claro, mas é isso que você irá capturar no profiler.

clip_image013

2. Com os valores de 1334 e 1335 em mãos o SQL aplica a regra inversa para poder obter os valores do filtro por Data_Entrega.

clip_image014

Traduzindo, a partir de 1900-01-01 some (1334 * 30), neste caso teremos o valor de 2009-07-28 como valor mínimo.

Para o valor máximo, o SQL soma mais 1 como margem de segurança para não pegar o valor incorreto.

3. Aplica os valores como filtro na tabela Items. Veja que o plano de execução passou exatamente os valores que calculamos acima.

clip_image015

Conclusão

É pessoal, vimos internamente como a feature funciona, e pode ser que você tenha aprendido alguns truques que ainda não conhecia, como capturar consultas feitas pelo SQL utilizando o Profiler, converter valores de hexa para decimal, sp_helptext, sp_spaceused…

Quero deixar claro que por enquanto não faço parte do desenvolvimento do produto, portanto posso estar equivocado em alguma afirmação que eu tenha feito. Não confirmei com o time de desenvolvimento do SQL se a lógica que apresentei acima está 100% valida, mas se você seguiu meu raciocínio viu que tudo faz pleno sentido.

Pra terminar deixo uma pergunta. Da pra usar esta mesma lógica em outros cenários concorda?… Vale a pena perder um tempo pensando sobre isso.

Espero que tenham gostado todo feedback é bem vindo, estejam a vontade para comentar ou se preferirem me escrever para dar opinião sobre meus artigos, você pode me enviar um e-mail no fabiano_amorim(at)bol.com.br.

That’s all folks.

CategoriasSQL Server
Seguir

Obtenha todo post novo entregue na sua caixa de entrada.

Junte-se a 55 outros seguidores