[SQL Code] Mapeando tabelas e seus respectivos Filegroups

Olá pessoal,

Aqui na empresa nós temos um controle manual de todo o crescimento dos bancos de dados e seus arquivos, processo que chamamos de Volumetria. O tamanho máximo de cada banco de dados é controlado manualmente, ou seja, não deixamos habilitada a propriedade AUTOGROW dos bancos de dados.

Nosso cliente pediu para expandir o tamanho de um banco de dados que possui 4 Filegroups e para isso perguntamos quais tabelas iriam crescer para expandir os arquivos correspondentes ao Filegroup com a(s) tabela(s) que iriam crescer. A resposta do cliente foi: Quê?! Hã!?! ou poderia ser ainda um tremendo WTF!!!!???!?

Em resumo, os analistas da aplicação não sabiam dizer onde cada tabela estava localizada então, vamos ao nosso script.

Uma maneira bem simplista de verificar em que Filegroup uma tabela está localizada é executando a procedure sp_help, assim:

USE <seu database>
EXEC sp_help <sua tabela>

O único problema de se fazer isto é quando se tem 317 tabelas para verificar! Logo, é hora de fazer um script para isto. =D

Depois de pesquisar as System Views do SQL Server, cheguei neste resultado:

SELECT
    T.NAME AS TABLE_NAME,
    P.ROWS,
    FG.NAME AS FILEGROUP_NAME,
    FG.IS_DEFAULT
FROM
    SYS.PARTITIONS P
    JOIN SYS.TABLES T
        ON P.OBJECT_ID = T.OBJECT_ID
    JOIN SYS.ALLOCATION_UNITS AU
        ON AU.CONTAINER_ID = P.PARTITION_ID
    JOIN SYS.FILEGROUPS FG
        ON AU.DATA_SPACE_ID = FG.DATA_SPACE_ID        
WHERE
    P.INDEX_ID < 2
    AND T.NAME <> 'SYSDIAGRAMS'        
ORDER BY T.NAME

O resultado é o nome da tabela, quantidade de linhas, nome do Filegroup onde a tabela está alocada e se este Filegroup é ou não o default do banco de dados.

Curioso como sou, pensei, caramba, se a procedure sp_help retorna essa informação, de onde ela traz isso?!

EXEC sp_helptext sp_help

O trecho da procedure onde é retornada a informação do Filegroup é este:

EXEC sys.sp_objectfilegroup @objid

Fazendo um sp_helptext na procedure sys.dp_objectfilegroup chegamos finalmente à consulta realizada pelo SQL Server:

    if exists (select * from sysobjects
            where id = @objid
            and type in ('S ','U '))
        begin
            select Data_located_on_filegroup = d.name
            from sys.data_spaces d
            where d.data_space_id =
            (select i.data_space_id from sys.indexes i
             where i.object_id = @objid and i.index_id < 2)
        end

Um pouco diferente da que fiz “na unha”, deu menos voltas e chegou no mesmo resultado… Live and Learn!!!

Abraços,
Erickson

Anúncios

[Basic SQL Code] Split Numbers

Olá pessoal,

Este é um artigo rápido onde vou compartilhar um código para fazer o split de uma lista de valores em um formato tabular. A idéia deste trecho de código surgiu em uma de minhas aulas na Impacta, como dúvida de um aluno. Veja abaixo o código na íntegra:

DECLARE
      @COUNT INT,
      @NUMS VARCHAR(100),
      @NUMS_AUX1 VARCHAR(100),
      @NUMS_AUX2 VARCHAR(100),     
      @POS INT,
      @SEPARATOR CHAR(1)
 
— lista de valores    
SET @NUMS = ’10;2;7;55;6;94;8;36;45;88;25′
— separador de cada valor
SET @SEPARATOR = ‘;’
 
SET @COUNT = LEN(@NUMS) –> total de caracteres da lista
SET @POS = 1                  –> posição de retorno da lista
SET @NUMS_AUX1 = ”           –> variável auxiliar 1
SET @NUMS_AUX2 = ”           –> variável auxiliar 2
 
— validação de existência da tabela temporária auxiliar
IF EXISTS(SELECT OBJECT_ID(‘TEMPDB..#NUMS’,’U’))
      DROP TABLE #NUMS
CREATE TABLE #NUMS (NUM INT)
 
— lógica para realizar o split dos números
WHILE @COUNT >= 0
BEGIN
      — variável auxiliar captura primeira posição da sequência
      SELECT @NUMS_AUX1 = SUBSTRING(@NUMS,@POS,1)
     
      — caso o valor capturado seja o separador ou eu tenho lido todos os caracteres,
      — insere o valor na tabela temporária auxiliar
      IF @NUMS_AUX1 = @SEPARATOR OR @COUNT = 0
      BEGIN
            INSERT INTO #NUMS VALUES (@NUMS_AUX2)
            SET @NUMS_AUX2 = ”
      END
      — caso contrário, atribui o valor para a variável auxiliar 2 e continua o loop
      ELSE
            SET @NUMS_AUX2 = @NUMS_AUX2 + ” + @NUMS_AUX1
 
      — decremento e incremento das variáveis @COUNT e @POS, que controla a posição
      — que está sendo capturada na lista de valores
      SET @COUNT -= 1  
      SET @POS += 1
END
 
— Exibe o resultado da lista de valores em formato tabular
SELECT * FROM #NUMS

Bons Estudos!
Erickson

Master the Basics – Queries

Depois de muito pensar sobre temas para falar aqui no blog, decidi me inspirar no conselho do guru de T-SQL, Itzik Ben-Gan:

“Observing true experts in different fields, you find a common practice that they all share – mastering the basics. (…) If you want to master a profession, you need to build your knowledge upon strong foundations.
Put a lot of effort into perfecting your techniques, master the basics, and you’ll be able to solve any problem.”

Uma idéia simples: criar uma série de posts que irão tratar de temas do nosso dia-a-dia como DBAs, entretanto, explorando aspectos normalmente não explorados. Cada tema seria introduzido, na sua forma mais simples e didática possível, e então vamos aumentando o grau de complexidade sobre a discussão do assunto em questão.

Inicialmente vou falar de queries. Vejamos um exemplo de consulta:

SELECT database_id, name
FROM sys.databases
WHERE database_id > 4;

Nada além do que uma simples consulta onde irá exibir as colunas database_id e name da tabela de sistema sys.databases, visualizando o resultado apenas de bancos de dados cujo database_id seja maior que 4.

Não vou explorar nenhum aspecto sobre performance ou sobre boas práticas na escrita de consultas. O que quero tratar aqui é de como o SQL Server resolve esta consulta.
O que acontece quando submetemos esta consulta para o SQL Server executar? A resolução desta query pelo SQL Server é tão simples quanto abrirmos o Management Studio, digitarmos a query, apertamos F5 e pronto, temos o resultado?! Creio que não… Há muito além disto, vamos ver…

A primeira etapa que é realizada para executar uma consulta é estabelecer uma conexão com nosso banco de dados. Para tal, temos a SQL Server Network Interface (SNI), que é uma camada que fará o “meio de campo” entre o client que solicitou a conexão e a nossa instância do SQL Server. Normalmente o protocolo de comunicação utilizado através da SNI é o TCP/IP, mas existem também outros protocolos como Shared Memory, Named Pipes e VIA, mas não entrarei em detalhes sobre eles agora…

Nossa query será enviada para o servidor como um pacote de dados TDS (Tabular Data Stream – outro assunto para falarmos num outro momento… =D). Em nosso servidor temos novamente a SNI para recepcionar os pacotes enviados para serem executados. Ela recebe o nosso pacote, e vendo que se trata de uma query, submete a query para um componente do Relational Engine do SQL Server, o Command Parser.

O papel do Command Parser é fazer o parse da query, validando se a sintaxe está correta, se os objetos existem, e também realizar pequenas simplificações na query. Ao realizar estas validações, o Command Parser irá gerar um hash da nossa query e irá buscar no Plan Cache se existe um plano de execução para a nossa consulta.

Se existir, o plano de execução é carregado e submetido para execução, caso contrário, o Command Parser irá gerar uma sequence
tree (ou query tree), que é uma estrutura interna que será passada para o próximo componente envolvido no nosso processo, o Optimizer.

Falar do Optimizer é tão empolgante quanto complexo, logo, não entrarei em muitos detalhes sobre este componente neste momento… Para ser breve, o Optimizer é baseado em custo, ou seja, para otimizar uma consulta ele se baseia no custo daquela otimização e quem tiver o menor custo, seria escolhido. Um ponto muito importante para se dizer do Optimizer é que ele procura montar um plano de execução “bom o suficiente” para a consulta. Percebeu a sutileza da expressão “bom o suficiente”? Ele não tenta fazer o melhor plano possível, o “the best”. Isso seria muito demorado, muito caro pra se fazer… É bom sempre termos isso em mente.

Vamos considerar então que o Optimizer criou um bom plano para nossa query. Agora, este plano de execução criado é enviado para o Query Executor, que vai conversar com outro grande componente do SQL Server, o Storage Engine. Uma vez que o Query Executor submete nossa query para o Storage Engine, ela é tratada pelos Access Methods, que são, basicamente, estruturas que dirão ao SQL Server como buscar a informação (dados) necessária para atender a nossa consulta. Será papel do Access Methods verificar se os dados necessários estão no Buffer Cache. Se estiverem, os dados serão retornados para o Access Methods que fará o caminho de volta com os dados para o client que fez a consulta.

Considerando que os dados não estão em cache o SQL Server precisa ler estes dados do disco. Vamos lembrar o seguinte, o SQL Server trabalha somente com dados em memória, ou seja, ele irá ler os dados necessários do disco, irá carregá-los no Buffer Cache e posteriormente vai retorná-los para o Access Methods.

Uma vez que nossos dados foram carregados do disco para o Buffer Cache e estes foram entregues para o Access Methods, os dados são enviados de volta para o Relational Engine que se encarrega de retornar estes dados para o client solicitante.

Vejamos abaixo como ficou o fluxo completo de execução da nossa query.

O que podemos concluir com tudo o que vimos. Para um simples mortal que utiliza um sistema e clica num botão “Enviar” para gerar um relatório, o SQL Server nada mais é do que uma caixa preta, onde de alguma forma ele busca os dados que ele pediu. Para nós, especialistas em SQL Server, ele é uma complexa ferramenta que vai muito além de dados e consultas.

As referências utilizadas para este artigo foram as seguintes:

Artigo da Kalen Delaney – Inside SQL Server: Parse, Compile, and Optimize
http://www.sqlmag.com/article/tsql3/inside-sql-server-parse-compile-and-optimize

Livro Inside Microsoft SQL Server 2008 – T-SQL Querying, Capítulo 1 – Logical Querying Processing
http://www.amazon.com/Inside-Microsoft%C2%AE-SQL-Server%C2%AE-2008/dp/0735626030/ref=sr_1_2?ie=UTF8&qid=1311032858&sr=8-2

Livro Professional SQL Server 2008 Internals and Troubleshooting, Capítulo 1 – SQL Server Architecture
http://www.amazon.com/Professional-Server-2008-Internals-Troubleshooting/dp/0470484284/ref=sr_1_1?s=books&ie=UTF8&qid=1311032906&sr=1-1

Espero que seja útil. Sugestões e comentários são muito bem vindos.

Até mais!

Recursividade e funções

Olá,

Esses dias fui submetido a um teste em que tive que responder à seguinte pergunta: Dado um número x de pessoas, quantas vezes estas pessoas irão se cumprimentar entre si? A imagem abaixo deixa claro a situação.

Cinco pessoas se encontram. Quantos cumprimentos eles farão? (Considerando que não vão haver repetições nos cumprimentos).

Vamos iniciar a contagem por uma das pessoas e seguir em sentido horário. Veja abaixo:

A primeira pessoa, conforme o esperado, cumprimenta as outras 4. Ao passar para a segunda pessoa, ela não irá cumprimentar novamente a primeira uma vez que já se cumprimentaram. Logo, a seu sequência fica assim:

Seguinte esta linha de raciocínio, percebe-se uma lógica na quantidade de cumprimentos realizados. A primeira pessoa realizou 4 cumprimentos, que em outras palavras é a quantidade de pessoas (@elements) menos 1. A segunda pessoa realizou 3 cumprimentos, ou seja, @elements – 2, e esta lógica é seguida até o final onde todos se cumprimentam.

Observando a solução do problema percebi que existe uma recursividade acontecendo. Dito isto, montei a função abaixo que realiza a chamada dela mesma para retornar a quantidade total de cumprimentos realizados.

CREATE FUNCTION Fn_Combinatoria(@elements int)
RETURNS int
AS
BEGIN
DECLARE @n INT
SET @n = 0
 IF @elements > 0
SELECT @n = dbo.Fn_Combinatoria( @elements-1 ) + @elements-1

RETURN @n
END
GO

A recursividade está no fato da função realizar uma chamada a ela mesma. Observem que vi a necessidade de colocar uma validação (IF @elements > 0) para evitar um loop infinito na função, gerando o erro:

Msg 217, Level 16, State 1, Line 1
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Enfim, com base em um teste identifiquei um exemplo simples e didático para explicar como funciona a recursividade e consegui demonstrar utilizando uma função dentro do SQL Server.

É isso.

Abraços!

Transações, como utilizar?

Olá,

Aproveitando o embalo do estudos para a prova 70-433, vou falar um pouco de transações e como utilizar o comando SAVE TRANSACTION, que pode ser bem útil em alguns cenários.

As transações podem ser definidas como um meio para garantir que um conjunto de ações (ou uma ação individual) tenha sucesso ou falhe por completo, de forma a garantir as características ACID do banco de dados. No SQL Server existem transações implícitas e explícitas e por padrão o SQL Server trabalha com transações implícitas. O que significa isso? Cada statement executada na base de dados é considerado como uma transação individual, sendo sua execução “commitada” ao terminar.

Para termos maior controle sobre o que estamos executando nós trabalharemos com transações explícitas, utilizando seguintes comandos:

  • BEGIN TRANSACTION
  • COMMIT TRANSACTION
  • ROLLBACK TRANSACTION

Um exemplo:

BEGIN TRANSACTION

insert into t1 values (1, ‘Text1’);

COMMIT TRANSACTION

Na sequência vou executar um script com transações alinhadas, ou seja, vamos trabalhar com mais de uma transação por vez:

 

— Início da transação 1

begin tran

 

— Inserindo alguns valores…

insert into t1 values (1, ‘Text1’);

insert into t1 values (2, ‘Text2’);

insert into t1 values (3, ‘Text3’);

 

— Verificando os dados inseridos

select * from t1;

 

— Início da transação 2

begin tran

 

— Vou realizar um update pra demonstrar o resultado.

update t1

set col2 = ‘nonononono’

where col1 = 2;

 

— Só pra confirmar…

select * from t1;

 

— Aplicar o COMMIT. Este commit será feito somente para a Transação 2.

commit

 

— Validando quantas transação ainda temos abertas. Temos de ter pelo menos uma.

select @@trancount

 

— Fazendo um rollback para depois ver qual resultado vou obter…

rollback;

select * from t1;

 

Executem passo a passo e acompanhem os resultados. Foi aberta uma transação e foram inseridos alguns registros na tabela de testes. Depois abrimos uma nova transação e atualizamos um registro desta tabela. O commit confirma o update realizado mas na sequência, perto do término do script é feito um rollback. Façam novamente um select na tabela para ver qual será o resultado.

Tudo foi desfeito!!!

Porque isto acontece? Não importa quantas transações sejam abertas numa sessão, se for realizado um rollback, todas as transações serão desfeitas. Mesmo aquelas que foram commitadas, lembrando que isto vale somente para transações da mesma seção. Apesar de ter sido feito o commit de uma das transações, ainda havia uma transação aberta, que era externa a transação que foi “commitada”, logo, com o rollback tudo foi desfeito.

Podemos evitar este problema utilizando o comando SAVE TRANSACTION.

O script abaixo executa o mesmo que o acima mas agora com a inclusão de um SAVE TRANSACTION para garantir que uma parte da execução seja realmente salva. Vejam:

 

begin tran

 

insert into t1 values (1, ‘Text1’);

insert into t1 values (2, ‘Text2’);

insert into t1 values (3, ‘Text3’);

 

select * from t1;

 

— Aqui o “x” da questão.

save tran step1;

 

begin tran

select @@trancount

 

update t1

set col2 = ‘nonononono’

where col1 = 2;

 

select * from t1;

 

commit

select @@trancount

 

— Neste ponto eu vou fazer rollback somente para o ponto que eu criei…

rollback tran step1;

 

— Confirmando que voltei ao ponto salvo antes de fazer o update.

select * from t1;

 

— Ainda tenho uma transação aberta …

select @@trancount

 

— Fazendo o último commit e validando o resultado.

commit

select * from t1;

 

Uma observação: Reparem que a utilização do comando SAVE TRANSACTION implica em se nomear a transação salva. O mesmo vale na hora de fazer o commit/rollback.

Já vi casos onde o desenvolvedor teve dificuldades de trabalhar um determinado processo pois haviam diversas transações sendo trabalhadas e quando ocorria um erro que não era tratado e era feito um rollback, tudo o que ele havia feito era perdido. Com a utilização do comando SAVE TRANSACTION você pode aumentar a forma de como você controla o seu processo e manter o controle sobre o que está sendo executado.

Espero que seja útil.

 

Até a próxima!

Erickson Ricci