Archive

Posts Tagged ‘SQL Server’

Desafio T-SQL

Ola pessoal, tudo bem?

Essa semana presenciei um caso aonde era necessário ajudar a desenvolver uma instrução T-SQL que fugia um pouco do nosso dia-a-dia, devido a isso resolvir postar no blog um desafio para compartilhar com todos a solução depois. Espero que muitas pessoas participem.

Vamos ao cenário.

Imagine que o Sistema X realiza o “track” de contratos de uma instituição financeira que são gerados pelo sistema Y, no entanto,o sistema Y gera mais de UMA LINHA para cada contrato toda vez que esse contrato é renegociado. Isso significa que, cada conjunto de contrato no sistema Y terá mais de uma linha baseando-se no número de contrato mais codigo de movimentação do mesmo (Contrato + Mov). Vejam um sample dos dados, conforme a seguir:

O sistema X precisa transformar o resulte set da imagem anterior em um único registro levando as seguintes regras em consideração:

  1. O status 0 significa – Precisa ser avaliado. O Status 1 significa: Registro Válido e o Status 2 significa que o registro pode ser descartado.
  2. Se o os valores em Dt1 e Dt2 for NULOS para para um conjunto de registro (Contrato + Processo) utilizo o registro com a MENOR DtReal.
    1. Obs: O print não atende essa condição, mas, consideraria asegunda linha da minha tabela:

       

       

  3. Se algum dos valores em Dt1 e Dt2 não for NULO para um conjunto de registro (Contrato + Processo) utilizo o registro com a MAIOR DtReal como base.
  4. Quando a regra de número 3 for atendida é necessário “montar” o registro com a seguinte “sub-regra”:
    1. Se o MAX VALUE da coluna Dt1 do conjunto de registro (Contrato + Processo) for maior ou igual a minha DtReal da linha atual, considero o MAX (no caso o valor 25/08/2013) para essa coluna. (Nesse caso minha coluna DtReal seria 22/08/2013 + Dt1 = 25/08/2013). No entanto, temos outra “regrinha” que no caso a regra “4.b”.
    2. Se o MAX VALUE da coluna Dt1 do conjunto de registro (Contrato + Processo) for maior ou igual ao MAX VALUE da DtReal do conjunto de registro (Contrato + Processo) o valor da coluna DtReal DEVE ser a menor data.O registro deve ser como a seguir:

       

  5. Se o MAX VALUE da coluna Dt1 do conjunto de registro (Contrato + Processo) NÃO for maior que a minha linha BASE (no caso a DtReal=22/08/2013, seguindo a regra de número 3) , conforme print:

    Nesse caso minha linha deve ser montada da seguinte maneira:

  6. Minha última regra consiste no seguinte: Se o MAX VALUE da coluna Dt1 do conjunto de registro (Contrato + Processo) NÃO for maior que a minha linha BASE (no caso a DtReal=22/08/2013, seguindo a regra de número 3) E o valor da Coluna Dt1 da linha BASE (no caso a linha que possi o valor DtReal = 22/08/2013) NÃO for NULA, minha linha consiste na DtReal + Dt1 daquela linha. Veja o cenário:

     

    Para a regra de número 6 o meu registro deve ser algo mais o menos assim:

     

     

Observações:

  • Os registros que devem aparecer na solução no final devem ter o valor da coluna Status modificado para 1
  • A solução pode ser escrita nas seguintes versões do SQL Server: 2008,200R2, 2012 e 2014.
  • A solução pode ser feita em mais de uma consulta para atender as regras.
  • Não é permitido a ulização de abordagens linha-a-linha (While e CUSRSOR)

Estou disponibilizando o script que monta os cenários com todos os registros. A ideeia inicial é desenvolver um pouco a lógica e fazer exercícios de T-SQL. No futuro podemos utilizar o mesmo cenário e avaliar o desempenho das sugestões que serão enviadas e discutir todas elas.

use tempdb

go

–Create sample datas

CREATE
TABLE #T
(Contrato VARCHAR(50), Mov INT, DtReal date, Dt1 date, Dt2 date,[Status] tinyint)

–DROP TABLE #T

INSERT
INTO #T(Contrato, Mov,DtReal,Dt1,Dt2, [Status])


VALUES


(’00XXXXX.25.0000′, 80193, ‘20130701’,
NULL        ,NULL,0)


,(’00XXXXX.25.0000′, 80193, ‘20130617’,
NULL        ,NULL,0)


,(’00XXXXX.25.0000′, 80193, ‘20130821’, ‘20130825’    ,NULL,0)


,(’00XXXXX.25.0000′, 80193, ‘20130822’,
NULL        ,NULL,0)

 

            ,(’00YYYYY.26.0000′, 80192, ‘20130701’, ‘20130820’    ,NULL,0)


,(’00YYYYY.26.0000′, 80192, ‘20130617’,
NULL        ,NULL,0)


,(’00YYYYY.26.0000′, 80192, ‘20130821’,
NULL        ,NULL,0)


,(’00YYYYY.26.0000′, 80192, ‘20130822’,
NULL        ,NULL,0)

 


,(’00ZZZZZ.27.0000′, 80194, ‘20130701’,
NULL        ,NULL,0)


,(’00ZZZZZ.27.0000′, 80194, ‘20130617’, ‘20130825’    ,NULL,0)


,(’00ZZZZZ.27.0000′, 80194, ‘20130821’,
NULL        ,NULL,0)


,(’00ZZZZZ.27.0000′, 80194, ‘20130822’, ‘20130821’    ,NULL,0)

 


,(’00WWWWW.28.0000′, 80195, ‘20130701’,
NULL        ,NULL,0)


,(’00WWWWW.28.0000′, 80195, ‘20130617’, ‘20130820’    ,NULL,0)


,(’00WWWWW.28.0000′, 80195, ‘20130821’,
NULL        ,NULL,0)


,(’00WWWWW.28.0000′, 80195, ‘20130822’, ‘20130821’    ,NULL,0)

 

            ,(’00KKKKK.29.0000′, 80196, ‘20130701’,
NULL        ,NULL,0)


,(’00KKKKK.29.0000′, 80196, ‘20130617’,
NULL        ,NULL,0)


,(’00KKKKK.29.0000′, 80196, ‘20130821’,
NULL        ,NULL,0)


,(’00KKKKK.29.0000′, 80196, ‘20130822’,
NULL        ,NULL,0)

 

            ,(’00AAAAA.30.0000′, 80196, ‘20130701’,
NULL        ,NULL,0)


,(’00AAAAA.30.0000′, 80196, ‘20130602’,
NULL        ,NULL,0)


,(’00AAAAA.30.0000′, 80196, ‘20130821’,
NULL        ,NULL,0)


,(’00AAAAA.30.0000′, 80196, ‘20130822’,
NULL        ,NULL,0)

 

            ,(’00BBBBB.19.0000′, 80196, ‘20130701’,
NULL        ,NULL,0)


,(’00BBBBB.19.0000′, 80196, ‘20130502’,
NULL        ,NULL,0)


,(’00BBBBB.19.0000′, 80196, ‘20130821’,
NULL        ,NULL,0)


,(’00BBBBB.19.0000′, 80196, ‘20130822’,
NULL        ,NULL,0)

 

Aguardo o script com a solução por e-mail. Fiquem a vontade para discutirmos. O Resultado da consulta deve ser o da imagem abaixo:

Thiago Carlos [TC] de Alencar

Implementing Microsoft Azure Infrastructure Solutions – Rumo a NUVEM !!!

Olá pessoal, quanto tempo neh!? Pois é, bastante correria. Desta vez eu vim para falar um assunto não muito técnico, mas, que com certeza vai vingar muito em breve no meu ponto de vista. Este post vai falar sobre minha experiência referente ao exame 70-533 da Microsoft que realizei na última quinta-feira (19/02/2015) e foi concluído com sucesso e uma visão que EU tenho do que está por vir com o Azure. Posso estar errado? Com certeza!!! Mas o que vale é compartilhar…rs.

Nos primeiros momentos estudando para a prova, veio na minha cabeça uma coisa que sempre conversei com o meu amigo Marcelo Fernandes e que acho que foi bastante cobrado dos DBAs quando existia o extinto título MCDBA. Pra quem não sabe, para possuir esse exame era necessário possuir DUAS provas de SQL Server 2000 e UMA de Windows Server 2003, já que o SQL Server deve ser instalado em cima de um SO é necessário que conheça um pouco dele. No meu ponto de vista existem diversos tipos de DBAs e com perfis diferentes que cada um se encaixa perfeitamente no seu dia-a-dia de trabalho, no entanto, o DBA deve conhecer um pouco de Rede, Infra, SO, Storage, Desenvolvimento, Servidores de Aplicação e algo que pode influenciar o seu SGBD indiretamente. Pois, como diz o Luti DBA significa “Default Blame Accept”. Faz sentido não é!?

No meu ponto de vista, no Microsoft Azure vejo que não existirá os rótulos: o “cara” do SO, o “cara” do SQL, o “cara” da Rede e o “cara” disso e daquilo, mas, sim o Arquiteto de Soluções pra Cloud, pois, esse é o que se espera da pessoa que se especializará nesse segmento. Por outro lado isso não isenta de se especializar, mas, torna as coisas muito mais interessantes e competitivas. Acredita que essa é a palavra que melhor define o Microsoft Azure, Competitivo.

Em uma época onde as empresas são bastante dinâmicas e precisam sempre estar à frente dos seus concorrentes e de forma que necessitem reduzir custo, o Microsoft Azure traz tudo isso de forma simples e objetiva tornando as empresas mais competitivas. Não é isso que a sua área de negócio e o seu negócio espera de você!? Por que gastar uma fortuna em Infra Estrutura, Armazenamento, Comunicação, Licenças, Renovações, Equipes “infladas”, plantões infinitos e etc. Embora, isso não significa que não precisamos de pessoas, elas ainda são e serão muito importantes nas organizações, mas, agora com o foco mais direcionado que é “Business”.

Imagine o seguinte cenário: Você precisa criar um site que venderá ingressos para o show dos Beatles. Estima-se que o local do show abrigará mais que duzentas mil pessoas. O site pode ser acessado 24X7 e apenas ficará disponível por 2 dias para quem quiser comprar. Seria um grande desafio conseguir o data center, comprar licenças, prever alta disponibilidade, monitoramento, equipe e etc. Com o Microsoft Azure essa situação muda um pouco! Se quiser saber mais, deixo no final do artigo referências sobre o assunto.

Quanto o exame 70-533 volta um pouco a ser exigido o que era exigido no exame MCDBA, não com tantos detalhes técnicos, mas, requer que o candidato tenha conceitos de Storage, Rede, Infra e etc. E com certeza uma das coisas mais importante como ter tudo isso reduzindo custo.

Vamos a lista do que é cobrado no exame:

  • Implement Web Sites
  • Implement Virtual Machines
  • Implement Cloud Services
  • Implement Storage
  • Implement Azure Active Directory
  • Implement Virtual Networks

No modulo de Web sites é importante entender como fazer a implantação de um web site, configurá-lo e monitorar. Também entender quais são as opções para restringir o acesso e forma de deixa-lo seguro com protocolos como SSL e certificados.

Em implementando máquinas virtuais são basta apenas saber o que é uma máquina virtual, mas, como utilizar de forma inteligente e o que se pode fazer com máquinas virtuais na nuvem. Uma das coisas mais fantásticas e a opção de você criar um “template” e fazer o upload da imagem para a sua galeria. Claro que outras coisas como configurar, monitorar, como separar por “subnets” e como criar maquinas para você ter disponibilidade em caso de desastres ou qualquer aplicação de patch.

Em Implementando o Cloud Services requer a exigência de entender como se pode implementar aplicações rodando na nuvem, como fazer o “scale up/down”, configurar, monitorar. Entender o que é um “Web Role” e “Worker Role”,saber como tirar proveitos desses recursos e como dimensiona-los da melhor forma reduzindo custos.

Em implementando Storage que na minha opinião é um dos assuntos com o aspecto mais importantes no Azure. Pois, lhe traz opções de como armazenar aquivos, pastas, fazer backups, sql database, blob storage, site recovery e etc. Sabendo configurações, opções de planos e alguns outros pontos chaves tenho certeza que sua vida será mais tranquila quando migrar pra nuvem.

Está o requisito que pessoas apenas acostumada com seu “quadrado” pode ter dificuldade. Para a pessoa que vem de SQL ou de qualquer tecnologia especifica pouco se preocupa de como é feito/ou deveria ser o acesso as aplicações, recursos, objetos e integrações seja ela no on-premises ou no Microsoft Azure. Esse quesito é bastante exigido, pois, no exame existem diversos cenários aonde se fala em integrar ambiente on-premises com Azure.

Finalmente implementando redes virtuais, se você é aquela pessoa que não possui bastante afinidade com redes de computadores, bem-vindo ao time. Nsse item é requerido que o candidato possua conhecimento de redes, protocolos, comunicação, subnets, vpn e algumas “coisitas” mais. Entender fases, passo-a-passo e componentes para entender como criar redes: Point-to-site, site-to-site, multi-site e fazer com que as redes hoje locais possam estender para a nuvem é um dos aspectos chaves desse módulo.

Caminho que segui para os estudos:

Utilizei o livro:  Exam Ref 70-533 Implementing Microsoft Azure Infrastructure Solutions, MSDN e TechNet e alguns links abaixo:

http://azure.microsoft.com/pt-br/

http://azure.microsoft.com/pt-br/pricing/

http://azure.microsoft.com/pt-br/overview/what-is-azure/

Também o que foi bastante importante para a consolidação do aprendizado foi criar uma conta no Microsoft Azure, que é disponibilizada free no portal.

http://azure.microsoft.com/pt-br/pricing/free-trial/

Espero que tenham gostado!!!

Até a próxima

Especificação de Capacidade Máxima no SQL Server

Pessoal, boa tarde.

Hoje apenas apareci para dar uma dica rápida e acredito que seja de interesse em geral…rsrs. Quem nunca se pegou pensando: Qual a capacidade máxima de colunas em uma instrução SELECT ? Qual a quantidade de linhas por tabela? Ou até mesmo tabelas por database? “Passeando” pelo BING uma pesquisa me direcionou para um link no MSDN que gostaria de compartilhar com vocês. O link se refere a Capacidade máxima de especificação do SQL Server:

http://msdn.microsoft.com/en-us/library/ms143432.aspx

Muito Obrigado.

Abs

SQL Server Locks – SP – 24/31 de Maio.

Galera, bom dia.

É com um imenso prazer que venho anunciar um treinamento específico sobre locks no SQL Server. A ideia de criá-lo veio enquanto analisava os materiais e conteúdos que existem por ai. Tem bastante coisa, porém, ainda não vim nenhum especifico de lock e isso é muito comum no dia-a-dia do DBA / Desenvolvedor.

Segue mais detalhes:

Agenda –

Parte I – Fundamentals About Locks, Transactions and Concurrency

  • Concurrency and Transactions
  • Properties
  • Scope
  • Isolation
  • Pessimistic Vs Optimistic Lost Update Problem
  • Lock Basic Concept
  • Lock Overview
  • Lock Resources
  • Lock Duration
  • Lock Ownership
  • Lock Metadata

Parte II – Advanced Lock

  • Lock Compatibility
  • Lock Conversion
  • Special Intent Locks
  • SIX, UIX, SIU,Key RangeS-S, RangeS-U, RangeX-X
  • Lock Scalation
  • Controlling Concorrency
  • Lock Timeout
  • Lock hints

Parte III – Advanced Troubleshooting

  • Troubleshooting Locking
  • Troubleshooting Blocking
  • Troubleshooting DeadLocking
  • Optimistic Concurrency
  • Row Versionning
  • Snapshot Isolations Level
  • RCSI
  • SI
  • Version Store

Parte IV –Examples

  • Examples of all earlier issues spoken.

Obs: A data pode ser reagendada no caso do quorum minimo não ter atingido o esperado.

Muito Obrigado.

T-SQL – Simulando o FN_SPLIT sem uso da função

Galera, boa tarde.

Para os meus amigos SQL Geeks, dedico esse post para Paula Patricia, uma super DBA SQL Server e uma grande amiga.

Hoje eu vim aqui falar de um assunto “inacabado” que apareceu em uma das turmas em que ministrava um treinamento sobre SQL Server. Falando sobre de como o SQL Server é otimizado para trabalhar com o que chamamos de “set-based” ao invés de processamento linha-a-linha.

Um aluno me disse: Thiago tem um cursor que executa um processamento “X” para um processo que processa algumas informações de estoque de forma hierárquica. A primeira sugestão que fiz foi: A alteração de um cursor para utilizar um While que lhe traria o mesmo resultado sem muita decodificação, porém, eu sabia que o resultado, poderia ser alcançado utilizando outro recurso foi que lhe pedi para que ele me trouxesse o código para analisarmos e mudar o While para uma instrução baseada em lote.

Depois de alguns minutos olhando o código cheguei à conclusão do que ele realmente precisava e de como poderíamos chegar ao resultado com uma CTE Recursiva (Para quem está lendo, irei levar em consideração que você conhece CTEs. No final desse post existem links para todos os assuntos tratados aqui).

Algum tempo de codificação por parte do aluno e… BAZINGA. A query estava retornando os valores, porem, a string que foi montada era utilizada como parâmetro de entrada para outra procedure. No que resultou em uma variável do tipo varchar(max)

Ex:

DECLARE @ListaParametrosEntrada varchar(max)= ‘18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853

Obs: Existem muitas outras soluções para resolver esse problema, para esse artigo apenas será abordado uma delas, mas, farei outros que abordam diversos resultados considerando performance.

Agora que começa a ficar interessante…

Imagine o cenário: Para cada Valor separado por vírgula dentro da variável @ListaParametrosEntrada deve ser desmembrado para que seja utilizado dentro de uma variável do tipo de dados inteiro em uma clausula WHERE com o operador de igualdade (=).

Ex:

where id_operacao_origem= @I

No código que encontrei, existia um While que verificava o seguinte: “enquanto houver valores separados por vírgula…quebra esse valor e coloque em uma variável,conforme demonstrado acima.

Então podemos avaliar que: Se eu tiver Um milhão de linhas, vou fazer isso Um milhão de vezes? A resposta é sim. Pode parecer estranho, mas, muitos programados têm uma forma de pensar serializada para resolver os problemas e essa é uma forma equivocada quando falamos de SGBD, pois, estamos nos referindo a um conjunto de dados (No fim do post tem um ótimo artigo do Fabiano Amorim sobre o assunto).

O código abaixo demonstra a lógica utilizada para o que foi visto até aqui:

DECLARE @ListaParametrosEntrada varchar(max)= ‘18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853’

DECLARE @I VARCHAR(MAX)

if right(@ListaParametrosEntrada, 1)<> ‘,’

begin

set @ListaParametrosEntrada= @ListaParametrosEntrada + ‘,’

end

while charindex(‘,’, @ListaParametrosEntrada)<> 0

BEGIN

set @I= ltrim(rtrim(substring(@ListaParametrosEntrada, 1 , charindex(‘,’, @ListaParametrosEntrada)- 1)))

set @ListaParametrosEntrada= ltrim(rtrim(right(@ListaParametrosEntrada,len(@ListaParametrosEntrada)- charindex(‘,’, @ListaParametrosEntrada))))

SELECT @I

END

Resultado:

Print2

Procurei na internet e achei ma função chamada fn_Split, que parece ser bem famosa quando estamos falando desse cenário. A função realmente atende, porém existem diversos problemas de performance relacionado a funções que não serão cobertos nesse post. Quem dizer da uma olhada de como pode ser feito com a função.

http://geekswithblogs.net/AngelEyes/archive/2007/04/12/111504.aspx

Para evitar esse processamento linha-a-linha que não é a melhor opção, o ideal seria que cada valor fosse um registro armazenado em uma coluna de uma tabela, para que possamos realizar um JOIN com a coluna id_operacao_origem.

O código abaixo atende a este requisito

DECLARE @T TABLE (Cod int NOT NULL)

DECLARE @ListaParametrosEntrada varchar(max), @Split char(1), @X xml

SELECT @ListaParametrosEntrada = ‘18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,7853965,15239,12578,145236,7853,18965,15239,12578,145236,7853,18965,15239,12578,145236,785318965,15239,12578,145236,7853’,

@Split= ‘,’

SELECT @X = CONVERT(xml,'<root><s>’+ REPLACE(@ListaParametrosEntrada,@Split,'</s><s>’)+ ‘</s></root>’)

insert into @T(Cod)

SELECT CAST(T.c.value(‘.’,’varchar(20)’)AS INT)

FROM @X.nodes(‘/root/s’) T(c)

SELECT * FROM @T

Print3Galera, ate o feriado eu edito algumas coisas, arrumo a formatação e vejo os erros de portugues, por enquanto o importante é que o código “funfa”.

Abs

Create Index não é Tuning II

Esse post é dedicado a um grande amigo que me ensinou muito sobre Tuning e Performance um dos melhores DBAs que conheci, Sergio Bonsague.

Esse artigo é continuação do último post lançado aqui no blog. A coisa mais divertida no SQL Server é saber, porque ele fez isso ou aquilo? Por que “tal” decisão foi tomada, entender um pouco desse mundo obscuro…rs. Vamos parar de tagarelar e colocar a mão na massa.

Se analisarmos o plano de execução do post print, o SQL Server escolheu utilizar um Clustered Index Seek para ler toda a tabela. Porém, fica a pergunta, Ele não poderia ter escolhido um Clustered Index Scan? Como vou ler tudo, um Scan não é melhor que um Seek? A resposta é: Depende.

Executando duas vezes a mesma query, uma com o HINT WITH(INDEX=0) que força a Não utiliza do Index da tabela e uma sem utilização de hint, para que possamos comparar o antes e depois.

 

Executando a query forçando um Clustered Index Scan Vs um Clustered Index Seek podemos ver que no plano de execução o custo de ambos é  de 50% (Atual e Estimado).

ambosPlano

 

Thiago, que dizer que é a mesma coisa? A resposta é não. Quando se está avaliando performance, o ideal também é utilizar como parâmetro os tempos de execução, para isso sempre uso o Profiler. A quantidade de leitura é muito menor quando utilizamos um Clustered Index Seek, Vejamos o resultado:

EvidenciaProfiler

 

O SQL Server realizou uma quantidade menor de leituras utilizando o Index Seek. Mas, porque?

No Seek temos duas propriedades que não aparecem no Scan que no caso é a “Direção da Leitura”, como pode ser visto utiliza o “FORWARD” e está marcada como “ORDERED” como True.

ClusteredIndexSeek

 

Já o Clustered Index Scan, essas propriedades não são apresentadas. É a única questão que acredito ser o motivo.

ClusteredIndexScan

 

 

Esperam que vocês tenham gostado.

 

Encontrando Índices Duplicados no SQL Server

Galera, boa noite.

Geralmente nós profissionais do mercado SQL Server sempre estamos procurando, desenvolvendo e criando maneiras de automatizar o trabalho do dia-a-dia. Com isso, podemos usar o nosso precioso tempo para poder desenvolver técnicas e recursos para agir com mais pró-atividade. E isso não seria diferente com os “scripts” que carregamos. A partir de hoje colocarei  nesta seção de scripts, alguns scripts que criei, outros que peguei na internet ou copiei de livros.  Não vou conseguir lembrar o nome de todos os autores do scripts, mas, geralmente serão os nomes dos profissionais mais conhecidos e ativos na comunidade (Se o autor ver seu script aqui, por favor, me mande um e-mail para que eu faça a referência). Até hoje uso os scripts que foram utilizados nas seções “30 Scripts que não podem faltar no pen driver de um DBA (parte 1/2). Que foi apresentada pelo Vitor Fava, Alexandre Lopes e Rodrigo. Simplesmente incrível.

Vamos ao que interessa! O script abaixo foi criado pelo Paul Nielsen para poder encontrar índices duplicados em um banco de dados SQL Sever. O script avalia apenas as chaves dos índices e não as colunas que foram adicionados ao Cover Index e ao Filtered Index:

/*
SELECIONAR INDICES DUPLICADOS ( KEYS INDEX ONLY )

http://sqlblog.com/blogs/paul_nielsen/archive/2008/06/25/find-duplicate-indexes.aspx

*/
— exact duplicates
with indexcols
as (
select
object_id as id
, index_id as indid
, name
, (
select
case keyno
when 0 then null
else colid
end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by
keyno
, colid
for xml path(”)
) as cols
, (
select
case keyno
when 0 then colid
else null
end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by
colid
for xml path(”)) as inc
from sys.indexes as i)
select
object_schema_name(c1.id) + ‘.’ + object_name(c1.id) as ‘table’
, c1.name as ‘index’
, c2.name as ‘exactduplicate’
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

 

Obrigado Alisson Cardoso

O que é o DTA?

Amigos da comunidade, blz? Esse post é dedicado a um outro amigo de trabalho. O DBA Felipe Melo , conhecimento SQL Geek em replicação, tunning e T-SQL. Sempre trocamos uma “figurinha” nos desafios profissionais, afinal, sempre é bom ter uma outra opinião. A certeza é: Nunca estamos totalmente certos! Mão na massa!?

O Database Engine Tuning Advisor é uma das “novas” features do SQL Server, ele analisa arquivos de carga de trabalho e propõe alterações no banco de dados, a fim de melhorar seu desempenho geral.

Para as alterações propostas, o Tuning Advisor também mostra o impacto que causará cada modificação.

Entre as suas capacidades, estão:

  • Query Optimazer, para propor índices e visões indexadas;
  • Recomendação de particões;
  • Análise de impacto das recomendações;
  • Fornecimento de informações sobre o número de consultas e o número de índices.

Opções de ajuste:

  • Quais objetos o Tuning Advisor poderá recomendar;
  • Quais partições analisará;
  • Quais estruturas serão mantidas no banco de dados;
  • Espaço máximo para recomendações;
  • Número máximo de columas por índice.

Criando analises com o DTA

Para iniciar o DTA, vá em StartAll / Programs / Microsoft SQL Server 2008 / Performance Tools / DataBase Engine Tunning Advisor.

Quando o programa for iniciado, clique em File/New Connection.

Aparecerá a tela de conexão para qual servidor você deseja criar a análise

Obs: Para o exemplo será usado um ambiente de testes.

O servidor será mostrado no canto superior direito da tela, clique com o botão direito e escolha a opção New Session.

A seguinte tela será mostrada:

Clique na guia Tunning Options e será mostrado a seguinte tela:

Physical Design Structure(PDS) to use in database: Esta opção avalia o que a Engine do DTA irá analisar referente a objetos de design na estrutura do banco(índex,índex views e etc). A opção de Índex views é desabilitada no SQL Server STD.

Partitioning strategy to employ: A opção de particionamento, verifica se existem objetos que podem ser particionados e avalia o particionamento que já existente. Disponível somente na versão Enterprise do SQL Server.

Physical Design Structure(PDS) to keep in database: Avalia a estrutura fisica do banco, abalia se existem índices clustereds e nonclustereds devem ser deletados(ele não deleta os indices), ou se existe algo divergente na no modelo como um todo. O padrão é Keep All existing PDS.

Voltando a guia General, aonde:

  • Session Name: Nome da Sessão
  • WorkLoad: A origem de ondes virão os dados que serão analisados(no caso do profiler o mesmo pode ser um arquivo ou uma tabela)
  • DataBase for WorkLoad Analysis: Aonde serão gaurdadas as analises temporarias.
  • Select DataBase and tables to tune: Lista de banco de dados e de tabelas que serão parte da analise. (sempre coloque os bancos de dados que [*] foram filtrados no profiler)

Coloque o nome no profiler e escolha o caminho aonde está o arquivo de trace do profiler. Ao clicar no Radio Button File, clique no binóculo a esquerda e será aberta a tela do Windows. Escolha o profiler que deseja analisar.

Clique em abrir.

Caso o trace tenha sido guardado em uma tabela, marque o Radio Button Table, clique no binóculo a esquerda e será aberta a tela de conexão:
Escolha qual banco, schema e tabelas estão os profilers que foram armazeandos.

Obs:  O login qual abriu a sessão no DTA deve ter acesso as tabelas que estão os traces, caso contrário, uma mensagem de erro será retornada.

 

O preenchimento deve ficar parecido com a tela abaixo:

 

Clique no botão Start Analysis. Após clicar em Star Analysis, uma tela de progresso será exibida.

 

Na guia recomendações, é apresenta a porcentagem de melhoria da análise.

 

No menu Actions aparecerá a opção de aplicar as recomendações ou savá-las.
Clique em Save Recommendations

Será gerado um arquivo .sql com as recomendações.

Espero Ter ajudado

 

Atualizando a Edição do SQL Server 2008

Caros, bom dia.

Este post é dedicado ao meu amigo Lucas Souza, um DBA com quem venho aprendendo muito. Está sempre me acompanhando nos processos aqui na empresa e me instruindo.

Hoje vamos falar sobre um assunto bastante simples, porém, foi a primeira vez que tive que realizar o procedimento, atualizar a edição do SQL Server. Hoje temos aqui na empresa o ambiente de desenvolvimento, produção, testes e homologação. Junto com a equipe começamos uma iniciativa de padronizar a instalação do SQL Server, isso inclui Edição utilizada, service pack e cumulative updates. Após baixar o SP2 da versão do SQL Server 2008 R2, comecei a aplicar a instalação no ambiente de desenvolvimento, testes e homologação. Quando me deparei com um ambiente de homologação que está com a versão Standard do produto. E agora? Preciso reinstalar o SQL Serve novamente? Claro que não…

Vamos ao passo-a-passo

Inicialmente você deve copiar o instalador do SQL Server para a máquina qual deseja atualizar. De um duplo clique no setup.exe a seguinte tela será apresentada:

Clique em Yes e aparecerá o SQL Server Installation Center. No lado esquerdo clique em Maintenance.

Clique no Edition Upgrade, após isso, apenas devemos seguir os passos. Vamos fazer juntos?

O SQL Server irá fazer uma verificação, se todos os itens verificados passarem por essa verificação, prossiga. Caso algum item falhar, recomendo que seja ajustadoo item em questão e o processo seja refeito.

É altamente recomendado que todos os itens sejam aprovados no check que o SQL Server faz, só assim podemos garantir que não teremos problema futuramente.

Clique em OK.

Esse processo é bem parecido com o processo de instalação, após o OK confirmado na tela acima, aparecerá a opção da chave do produto, Clique em Next:

Marque o checkbox de “Aceito os termos” e clique em next:

Na próxima tela, podemos perceber que o instalador traz a versão atual do produto e para qual instancia será feito o “upgrade”.

Clique em next.O SQL Server fará uma verificação antes de lhe dar a opção de Update.

Clique em next. Você verá um sumario bem importante, que diz para qual versão está sendo modificado o SQL Server, qual ação está sendo tomada (No caso Edition Upgrade) e a lista de features que sofrerão com esta atualização:

Clique em Upgrade. Após isso é só aguardar o SQL Server terminar de atualizar todos os binários necessários para essa atualização. Quando isso acontecer, reinicie a máquina.

Obs: No post não foi focado cuidados que devem ser tomados no caso dessa atualização. É uma boa prática sempre realizar backup dos databases de sistema, quando for realizar alguma alteração que afeta as configurações da instancia do SQL Server

Espero ter ajudado

Abs

Trabalhando com Certificados no SQL Server 2008

Boa tarde pessoal.

Este post é dedicado a um amigo da empresa, Rogério Santini, extremamente inteligente, sempre me da os conselhos certos.No post anterior falamos sobre a importância de utilizar database roles para controlar o acesso aos servidores de banco de dados através das aplicações. Hoje iremos falar sobre um caso real que foi aplicado aqui na nossa empresa. Temos nossas aplicações executando as procedures através de database roles, porém, existe uma migração acontecendo que estamos trabalhamos com informações se sistemas legados.

Vamos partir do nosso exemplo anterior onde tínhamos uma tabela de clientes com a coluna IdCliente que tem a propriedade identity de 1,1. Imagine que essa tabela será populado pelo nosso sistema atual. A cada inserção, será atribuído um valor incremental, até ai nenhuma novidade, comportamento normal. Agora aparece uma variável em nossa implantação. O sistema legado que temos, ainda ficará em funcionamento, sendo assim, ele precisará realizar inserções em nossa tabela de clientes. Thiago não entendi!? Aonde você quer chegar? Vamos lá meu caro blog…rs.

O sistema legado já existe os seus IdClientes, com uma sequencia própria dele, neste caso, as procedures farão uso da opção SET IDENTITY_INSERT. E dai? Como já tenho permissão de execução de procedures no sistema, nada muda certo? ERRADO. Para realizar a operação de SET IDENTITY_INSERT o usuário deve ter permissão de DDL_ADMIN. No momento que liberar essa permissão, o login da aplicação pode criar qualquer objeto dentro do banco de dados e esse é um risco muito grande. Thiago então qual a solução? Vamos lá bloguinho. A partir do SQL Server 2005, aprimorou-se as features de segurança do produto. Uma das “novidades” é o uso de certificados e máster key. Podemos realizar o uso dessas features, para fazer com que o login que chamou a procedure possa executar a mesma com o comando set identity_insert sem ter permissão de DDL_ADMIN. Vamos ver como isso funciona?

Vamos se conectar ao banco de dados dbPermissoes e alterar a procedure stp_ins_cliente que usamos no exemplo anterior para pode adicionar o comando SET_IDENTITY_INSERT.

alter  PROCEDURE dbo.stp_ins_cliente(@Id int, @Nome VARCHAR(50), @Cpf CHAR(11))

AS

begin

       SET IDENTITY_INSERT dbo.Clientes ON

            INSERT INTO dbo.Clientes(IdCliente, NomeCliente,CpfCliente) VALUES(@id, @Nome, @Cpf)

      SET IDENTITY_INSERT dbo.Clientes OFF

 END

go

Executando a procedure após de alterada, temos a seguinte mensagem de erro:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 8 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

Msg 1088, Level 16, State 11, Procedure stp_ins_cliente, Line 5

Cannot find the object “dbo.Clientes” because it does not exist or you do not have permissions.

A mensagem acima é clara, ou não tenho permissão ou o objeto não existe. Segundo que a hipótese da permissão é verdadeira. Vamos dar permissão de DDL_ADMIN para o login e testar a execução, para garantir que é isso mesmo.

USE [dbPermissoes]

GO

EXEC sp_addrolemember N’db_ddladmin’, N’AplSistema’

GO

Executando novamente:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 8 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

Vejamos o resultado e o ID 8 foi inserido.

Iremos remover o login do database role do DD_ALMIN e faremos a solução “Elegante”.

Criaremos o database máster key dentro da base dbPermissoes:

use dbPermissao

go
CREATE MASTER KEY ENCRYPTION BY password = ‘MkE2012@#’

Criamos o certificado:

CREATE CERTIFICATE CrtPermissoes WITH SUBJECT = ‘Schema user Certificate’

Agora precisamos criar um usuário para a base de dados em questão. Esse usuário é o que chamamos de “without login”. Quando criamos um usuário no banco de dados que não está atribuído a nenhum login, a sintax é completamente simples. Na criação do usuário, atribuo ele aio certificado:

CREATE USER UsrCert FROM CERTIFICATE CrtPermissoes

No momento da mágica, apenas realizamos o comando abaixo:

GRANT ALTER ANY SCHEMA TO UsrCert ADD SIGNATURE TO dbo.stp_ins_cliente BY CERTIFICATE CrtPermissoes

GO

Esse comando da permissão de “ANY SCHEMA” para o usuario UsrCert que criamos, e essa permissão é atribuida pelo certificado CrtPermissoes. Como o Usuario AplSistema tem permissão de execução de procedures a solução torna-se viável e “clean”.  Iremos remover o login do DDL_ADMIN e realizar uma execução de insert, inserindo o IdCliente 666.

USE [dbPermissoes]

GO

EXEC sp_droprolemember N’db_ddladmin’, N’AplSistema’

GO

Executamos a procedure:

EXECUTE AS LOGIN = ‘AplSistema’

go

EXEC dbo.stp_ins_cliente @id = 666 ,@Nome = ‘Alencar’,@Cpf =’133xxx63215′

GO

REVERT

E BAZINGA!!!!!