Resultados de busca

Fala galera, hoje quero iniciar uma serie de posts sobre estruturas internas do SQL Server, bom pra iniciar essa serie quero falar sobre HOBT, para você que já brinca bastante com SQL Server em algum momento já deve ter visto ou escutado falar sobre esse termo, e se você ainda não conhece vai conhecer agora. HOBT (Não tem relação com ‘The Hobbit’ o filme rs) é um acrônimo para Heap or BTree que são estruturas de dados composta por paginas de dados. Quando falamos de Heap estamos falando de uma tabela sem índice Cluster, onde não temos ordenação de dados, as paginas são mapeadas pela IAM(Index Allocation Map) e são lidas na ordem de alocação. Uma tabela sem índice Cluster, mas que possui índices Não Cluster também é considerada uma tabela Heap, pois sua estrutura principal esta desornada e sem a estrutura Btree, a tabela só deixa de ser Heap após a criação de um índice Cluster(Apenas um por tabela). USE master GO IF(SELECT DB_ID(‘DB_TESTE’)) IS NOT NULL BEGIN DROP DATABASE DB_TESTE END GO CREATE DATABASE DB_TESTE GO USE DB_TESTE GO IF(SELECT OBJECT_ID(‘DADOS’)) IS NOT NULL BEGIN DROP TABLE DADOS END GO CREATE TABLE DADOS (CAMPO1 INT ,CAMPO2 INT ,CAMPO3 INT) GO INSERT INTO DADOS VALUES ((SELECT CAST(RAND() * 1000 AS INT)),(SELECT CAST(RAND() * 1000 AS INT)),(SELECT CAST(RAND() * 1000 AS INT))) GO 10000 No script acima estamos preparando um ambiente para mostrar como funciona de verdade essas duas estruturas de armazenamento, criamos então um banco de dados DB_TESTE, e uma tabela DADOS com 3 campos inteiros sem nenhum índice, ou seja ela será uma estrutura Heap, logo após inserindo 10000 linhas. Como podemos ver nossa tabela DADOS possui 28 paginas de dados, e esta sobre uma estrutura Heap, na estrutura Heap não temos ordenação dos dados. Logo se os dados são desordenados a unica maneira de acesso a uma tabela na estrutura Heap é ‘Table Scan’, onde todas as linhas são lidas para qualquer operação de acesso a dados dessa tabela, sendo uma operação muito custosa para tabelas com muita informação, porém ‘Table Scan’ geralmente é mais rápido que um Clustered Index Scan, pois ele utiliza-se da pagina IAM para escanear a tabela. Como podemos ver no Select acima, estamos fazendo um filtro no ‘campo1’ para retornar apenas os valores 289, foram retornados 2 resultados para essa consulta, porém com o auxilio do ‘SET STATISTICS IO’ conseguimos ver que ele leu as 28 paginas de dados da nossa tabela. Através do comando DBCC IND podemos ver as paginas que estão alocadas para nossa tabela DADOS, destaque para primeira pagina com ‘PageType = 10’ essa é nossa pagina IAM, ‘Index Allocation Map’ é uma pagina de 8KB que contém ponteiros para as extents (conjunto de 8 paginas 64 kb), tabelas grandes podem conter mais de uma pagina IAM, uma pagina IAM pode mapear em torno de 4GB de extents, então se sua tabela for relativamente grande ela irá conter mais de uma pagina IAM e elas serão ligadas entre si, ou seja cada IAM tem o endereço da próxima IAM e o da anterior (Lista duplamente ligada). Bom como havia comentado anteriormente na estrutura Heap o unico metodo de acesso é ‘Table Scan’ onde ele irá ler as paginas de acordo com o mapeamento da pagina IAM, acima podemos ver que as paginas de dados não contém ponteiros para a próxima pagina e nem para anterior nos campos ‘NextPage’ e ‘PrevPage’, com isso podemos dizer que na estrutura Heap não temos fragmentação de dados, pois ela sempre irá ler na ordem da pagina IAM, sem fragmentação aumenta a chance de utilização do read-ahead. Imagem retirada do BOL(Books Online), essa é uma representação de uma tabela na estrutura HEAP. Falamos um pouco da estrutura Heap que é uma tabela sem índice Cluster e sem ordenação de dados, vamos falar um pouco da estrutura BTree(Arvore Balanceada e não Arvore Binária) onde temos a ordenação dos dados dentro das paginas de dados e temos uma estrutura diferente da Heap, onde os dados são armazenados em um formato de uma arvore com 3 níveis de navegação sendo Raiz, intermediário e nível folha, as paginas do índice são ligadas entre si, ou seja uma lista duplamente ligada, assim como as paginas IAM, como no exemplo da imagem abaixo. Imagem retirada do BOL(Books Online). Bom vamos incluir um índice Cluster na nossa tabela e verificar como ela vai ficar. CREATE CLUSTERED INDEX IDX_DADOS ON DADOS(CAMPO1) GO SELECT O.NAME AS TABLE_NAME,P.INDEX_ID, I.NAME AS INDEX_NAME , AU.TYPE_DESC AS ALLOCATION_TYPE, AU.DATA_PAGES, PARTITION_NUMBER,* FROM SYS.ALLOCATION_UNITS AS AU JOIN SYS.PARTITIONS AS P ON AU.CONTAINER_ID = P.PARTITION_ID JOIN SYS.OBJECTS AS O ON P.OBJECT_ID = O.OBJECT_ID JOIN SYS.INDEXES AS I ON P.INDEX_ID = I.INDEX_ID AND I.OBJECT_ID = P.OBJECT_ID WHERE O.NAME = N’DB_TESTE’ OR O.NAME = N’DADOS’ ORDER BY O.NAME, P.INDEX_ID; GO SELECT * FROM SYS.INDEXES WHERE object_id = object_id(‘DADOS’) Agora podemos ver que o tipo da nossa tabela é ‘Clustered’, rodando novamente o Select sem nenhuma clausula de ordenação já podemos ver que nossos dados agora estão ordenados pelo ‘CAMPO1’. Rodando novamente aquela consulta com o filtro buscando apenas o valor 289 podemos ver que o numero de paginas lidas são apenas 2(Pagina do Índice e a pagina de dados nível folha). Novamente rodando o DBCC IND, destacando as duas primeiras linhas, onde a primeira é nossa pagina IAM e a segunda com ‘PageType = 2’ é a pagina do nosso índice e podemos ver que nosso índice tem apenas 2 níveis pois nossa tabela contém poucos dados. Essa pagina de índice contem informações sobre a nossa chave e qual pagina determina informação se encontra. Note também que conforme a imagem la em cima da estrutura do Clustered Index, as paginas são uma lista duplamente ligada, então nas colunas ‘NextPage’ e ‘PrevPage’ temos um ponteiro indicando qual é a próxima pagina e a anterior, podemos ver também que elas estão ordenadas, mas isso pode mudar, caso venha a fragmentar essa tabela. Com a ajuda do DBCC PAGE conseguimos investigar melhor nossa pagina de índice(303), como mencionei acima através dessa  pagina conseguimos identificar onde esta determinada informação, por exemplo no nosso Select que esta la em cima, filtramos o ‘CAMPO1 = 289’, através dessa pagina de índice consigo identificar facilmente que meu registro 289 se encontra na pagina de dados 337 conforma acima, eu sei que meu registro 289 esta entre o registro 262 e o 301, portanto ele esta na pagina de dados 337, nesse nível do índice encontramos apenas as colunas que fazem parte da nossa chave, como passamos a clausula (*) no nosso Select precisaremos ler a pagina folha do índice também para recuperarmos os demais campos da tabela. Acima o DUMP da nossa pagina de índice Cluster(337), esse é nosso nível Folha aonde se encontram todas as colunas. Então podemos ver que na estrutura BTree com a ajuda das paginas intermediarias podemos encontrar facilmente uma informação especifica navegando pelos níveis superiores até chegar no nível folha onde se encontra a informação. Devemos tomar muito cuidado na escolha da chave do nosso índice, uma escolha ruim pode degradar a performance do seu ambiente. Então quando falamos de ‘Índice Cluster’ não estamos falando apenas de Índices, mas da própria tabela em si em uma estrutura BTree. Obs.: Os comandos ‘DBCC IND’ e ‘DBCC PAGE’ são comandos não documentados pela Microsoft. Bom galera essa é a primeira parte, quero fazer mais posts voltados a estrutura dos índices, a ideia aqui era mostrar a diferença entre as estruturas Heap or BTree(HOBT), não entrei em detalhes sobre as vantagens e desvantagens das suas utilizações, pois a minha ideia é fazer mais posts sobre isso, o que eu queria mostrar é a diferença entre as estruturas e dar um norte para quem quer estudar mais afundo, qualquer dúvida deixe seu comentário e espero que tenham gostado. Referencias: HEAP \ IAM https://blogs.msdn.microsoft.com/fcatae/2016/04/26/dbcc-ind/ Clustered Index Scan https://blogs.msdn.microsoft.com/fcatae/2016/05/03/dbcc-indexdefrag/ Clustered Index https://technet.microsoft.com/en-us/library/ms177443%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396 HEAP https://technet.microsoft.com/en-us/library/ms188270(v=sql.105).aspx Reginaldo  Silva

Fala pessoal, hoje vamos continuar na serie de estruturas internas, no ultimo post falei sobre as duas formas de organização de uma tabela Heap or BTree(HOBT), hoje vamos falar sobre as unidades de alocações(Allocation Units). No ultimo post falei que uma tabela pode estar estruturada no modelo HEAP ou Clustered Index (BTree), as unidades de alocações são utilizadas para gerenciar os dados dentro das estruturas HEAP ou BTree, as unidades de alocações dividem os dados de acordo com seu tipo em suas respectivas paginas, podendo ser dividida em três tipos de alocação: IN_ROW_DATA LOB_DATA ROW_OVERFLOW_DATA Imagem retirada do BOL(Books Online). Conforme a ilustração acima, uma tabela consiste de uma ou mais partições, cada partição pode ser organizada como uma HEAP ou Clustered Index (BTree) e os dados são divididos em 3 unidades dentro de uma HEAP ou BTree. IN_ROW_DATA – Conjunto de paginas de dados e índices, paginas com esse tipo armazenam os dados em si e informações de índices como os níveis intermediários de índices. LOB_DATA – LOB(Large Objects) Conjunto de paginas para armazenar grandes objetos, tipos de dados que utilizam essa unidade de alocação são: text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max), or CLR user-defined types (CLR UDT). ROW_OVERFLOW_DATA – Quando colunas de tamanho variáveis (varchar, nvarchar, varbinary, or sql_variant)  excedem o tamanho de 8060 bytes que é o tamanho suportado para uma linha, um ponteiro de 24 bytes é criado no lugar dessa linha e essa informação é jogada para outra pagina de dados do tipo ROW_OVERFLOW_DATA. Vamos ver isso na pratica então, abaixo script para preparar o ambiente de teste. USE master GO IF(SELECT DB_ID(‘DB_TESTE’)) IS NOT NULL BEGIN DROP DATABASE DB_TESTE END GO CREATE DATABASE DB_TESTE GO USE DB_TESTE GO IF(SELECT OBJECT_ID(‘DADOS’)) IS NOT NULL BEGIN DROP TABLE DADOS END GO CREATE TABLE DADOS (CAMPO1 INT PRIMARY KEY IDENTITY,CAMPO2 VARCHAR(5000) DEFAULT(REPLICATE(‘A’,5000)),CAMPO3 VARCHAR(5000) DEFAULT(REPLICATE(‘A’,5000)),CAMPO4 VARCHAR(MAX) DEFAULT(REPLICATE(‘B’,5000))) GO INSERT INTO DADOS DEFAULT VALUES GO 5 Criamos então um banco de dados chamado DB_TESTE e uma tabela chamada DADOS que possui 4 CAMPOS, essa tabela esta na estrutura de Clustered Index(BTree), logo após fazendo Insert de 5 linhas. Na estrutura da nossa tabela podemos notar que a nossa linha irá ultrapassar o limite de 8060 bytes, CAMPO1 = 4 Bytes CAMPO2 = 5000 bytes e CAMPO3 = 5000 bytes, total = 10004 bytes, nossa coluna CAMPO4 não entra no calculo por ela ser do tipo LOB(Large Objects) ela é automaticamente direcionada para sua unidade de alocação LOB_DATA, vale lembrar que nossos campos VARCHAR(5000) são variantes ou seja se eu inserir apenas 1 carácter ele irá ocupar apenas 1 byte e não iremos ver o split de IN ROW para OVERFLOW, por isso deixei como DEFAULT o valor de 5000 caracteres. Essa imagem acima mostra as unidades de alocação da nossa tabela logo após o comando CREATE TABLE, podemos ver que as unidades de alocação já são criadas mesmo sem nenhum dado para essa tabela. Após a inserção de 5 linhas já podemos ver que nossas unidades de alocação estão populadas, assim podemos ver que as paginas são agrupadas de acordo com seu tipo. Com a ajuda do ‘DBCC IND’ podemos ver mais a fundo, detalhe para as setas em vermelho onde mostra que temos uma pagina IAM para cada tipo de alocação, formando uma cadeia de paginas IAM, elas são duplamente ligadas entre si. Podemos ver que no tipo de IN_ROW_DATA temos 7 paginas, sendo 5 paginas de dados (nível folha do índice) 1 pagina IAM e 1 pagina de índice (Nível Raiz). No ROW_OVERFLOW_DATA temos 6 paginas, sendo 5 paginas que contem os dados e 1 pagina IAM. no LOB_DATA temos também 6 paginas, 1 IAM e 5 paginas com os dados. Com a ajuda do comando ‘DBCC PAGE’ olhando dentro de uma paginas de dados podemos ver que no CAMPO3 não temos os dados e sim um ponteiro de 24 bytes que aponta para a pagina 293 como podemos ver acima no ‘RowId = (1:293:0)’, através da coluna ‘TimeStamp’ ele poderá encontrar a informação nesta pagina que esta em uma unidade de alocação ROW_OVERFLOW_DATA lá iremos encontrar os dados do CAMPO3, a mesma coisa para o CAMPO4 do tipo LOB. Realizando um Select na tabela DADOS com um Where para trazer apenas a linha com CAMPO1 = 3, podemos ver o Plano de execução onde temos um CLUSTERED INDEX SEEK, olhando o numero de leituras com a ajuda do SET STATISTICS IO, temos 2 leituras logicas e 3 leituras de LOB. O que o SQL Server esta fazendo internamente é: Lê a pagina RAIZ do Índice (pagina 301), aqui ele é direcionado para a pagina folha. Lê a pagina FOLHA do índice (pagina 305), neste momento ele descobre que não possui todas as colunas aqui, pois estão em outra unidade de alocação, até aqui 2 leituras logicas. Lê a pagina 303(ROW_OVERFLOW_DATA ), 1 leitura LOB. Lê a pagina IAM 296 (LOB_DATA) e depois a pagina 304(LOB_DATA), 2 leituras LOB, assim formando as 3 leituras LOB. Bom mas se eu não precisar de todas as colunas posso economizar Reads. Podemos ver que colocando apenas o CAMPO1 no SELECT ele realiza apenas 2 leituras logicas que é referente ao índice(paginas RAIZ e FOLHA). Se eu colocar o CAMPO1 e CAMPO3 ele me retorna as 2 leituras logicas e mais 1 leitura LOB referente ao ROW_OVERFLOW_DATA. Então se atente aos SELECTS que possuem (*), na maioria das vezes você não precisa de todas as colunas, evitando leituras desnecessárias. Esses conjuntos de alocações (Allocation Units) permitem o SQL Server manter sua organização interna e manter um tamanho fixo padrão de 8 kb para suas paginas. Bom galera é isso, hoje vimos um pouco mais sobre a estrutura de uma tabela, entendendo como ela é organizada internamente e como o SQL faz para buscar as informações navegando pela estrutura BTree, espero que tenham gostado e até a próxima; Referencias: Table and Index Organization https://technet.microsoft.com/en-us/library/ms189051(v=sql.105).aspx Reginaldo Silva

Bom dia Galera, hoje tenho uma dica rápida e vou falar sobre uma curiosidade do comando Print do SQL Server, há algum tempo atrás me surgiu uma dúvida, porém não fui pesquisar muito afundo no momento, hoje eu consegui pesquisar melhor e entender como funcionava de verdade, a dúvida era a seguinte: As vezes precisamos depurar uma Store Procedure para identificarmos um determinado problema, para isso normalmente um dos primeiros passos da investigação é saber aonde esta ocorrendo o problema, em qual momento e qual a linha, para entendermos melhor o problema e pensar em uma solução, bom para quem já esta acostumado com a utilização de algum Debugger por exemplo do Visual Studio ou até mesmo do SQL Server sabe que podemos iniciar uma depuração e colocar alguns Breakpoints em pontos estratégicos para sabermos se o fluxo esta chegando até ali ou não, ou também podemos utilizar outra alternativa muito conhecida que é a função PRINT do SQL Server, colocar PRINTs em pontos estratégicos e acompanhar a saída durante a execução da procedure, bom é nesse ponto que queria chegar, vamos simular um ambiente e fazer alguns testes. USE MASTER GO IF(SELECT DB_ID(‘DB_TESTE’)) IS NOT NULL DROP DATABASE DB_TESTE GO CREATE DATABASE DB_TESTE GO USE DB_TESTE GO IF(SELECT OBJECT_ID(‘USP_TESTE’)) IS NOT NULL DROP PROC USP_TESTE GO CREATE PROC USP_TESTE AS WAITFOR DELAY ’00:00:05′ PRINT ‘MENSAGEM DE TESTE 1 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) WAITFOR DELAY ’00:00:05’ PRINT ‘MENSAGEM DE TESTE 2 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) WAITFOR DELAY ’00:00:05’ PRINT ‘MENSAGEM DE TESTE 3 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) No Script acima criei uma Store Procedure que me retorna 3 Prints em um intervalo de 5 segundos entre eles, a minha ideia aqui é executar essa procedure e acompanhar as mensagens sendo escritas na saída do Management Studio, vamos la. O que eu quero mostrar aqui é o seguinte, minha query esta em execução a 13 segundos como mostrado no circulo em vermelho e nenhuma mensagem foi escrita ainda, aonde mostra a seta vermelha. Depois da execução finalizada aparece nossos 3 prints com o tempo total de 15 segundos conforme esperado, ou seja os Prints só aparecem quando finaliza a execução. Não é bem isso que eu queria, na verdade eu queria acompanhar as mensagens em tempo de execução para saber quais partes da procedure já foi executado. O motivo para isso é que o SQL Server armazena as mensagens dessa transação em um Buffer de mensagens até que a execução tenha finalizado ou o Buffer alcance seu limite. O tamanho desse Buffer é de 8 kb, assim quando ele atinge esse tamanho ele manda todas as mensagens para o cliente e esvazia esse Buffer para continuar armazenando. Bom quer dizer então que se cada mensagem possuir 8 kb ele irá enviar cada mensagem no momento do PRINT e conseguiremos ver elas em tempo de execução?  Vamos ver isso acontecendo. ALTER PROC USP_TESTE AS WAITFOR DELAY ’00:00:05′ PRINT ‘MENSAGEM DE TESTE 1 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) + ‘ – ‘ + replicate(‘ ‘ ,8000) PRINT replicate(CAST(‘ ‘ AS CHAR(1)),100) WAITFOR DELAY ’00:00:05’ PRINT ‘MENSAGEM DE TESTE 2 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) + ‘ – ‘ + replicate(‘ ‘ ,8000) PRINT replicate(CAST(‘ ‘ AS CHAR(1)),100) WAITFOR DELAY ’00:00:05’ PRINT ‘MENSAGEM DE TESTE 3 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) + ‘ – ‘ + replicate(‘ ‘ ,8000) Alterando a procedure com o Script acima, estou utilizando a função REPLICATE para preencher a mensagem com 8000 caracteres ‘ ‘ (espaço em branco) assim enchendo o Buffer, logo após eu executo mais um Print com 100 caracteres para forçar a saída das mensagens no console e a limpeza do Buffer. Como na imagem acima olhando as 3 execuções podemos ver que agora o Print é retornado em tempo de execução, porém não é uma solução muito bonita de fazer, existe uma opção melhor para esta situação, o RAISERROR! O comando RAISERROR tem uma sintaxe um pouco mais complexa, porém ele possui uma opção que nos interessa bastante,’NOWAIT’ quando especificado junto com o RAISERROR ele retorna imediatamente as mensagens para a aplicação, então podemos utiliza-lo para nos ajudar nesse caso. ALTER PROC USP_TESTE AS DECLARE @MSG VARCHAR(1000) WAITFOR DELAY ’00:00:05′ SET @MSG = ‘MENSAGEM DE TESTE 1 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) RAISERROR (@MSG, 0, 1) WITH NOWAIT WAITFOR DELAY ’00:00:05’ SET @MSG = ‘MENSAGEM DE TESTE 2 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) RAISERROR (@MSG, 0, 1) WITH NOWAIT WAITFOR DELAY ’00:00:05’ SET @MSG = ‘MENSAGEM DE TESTE 3 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) RAISERROR (@MSG, 0, 1) WITH NOWAIT Alterando novamente a procedure agora trocando os PRINTs por RAISERROR. No primeiro parâmetro para o  RAISERROR passamos a mensagem a ser enviada, no segundo parâmetro a severidade do erro (se informado uma severidade abaixo de 10 é interpretado como mensagem e não como erro), no terceiro parâmetro  é o status da mensagem, bom executando novamente nossa procedure temos o resultado abaixo. Acima temos o resultado em tempo de execução sem precisar preencher com espaços em branco na nossa mensagem, isso é a mesma coisa quando você esta fazendo aquele WHILE e coloca PRINTs dentro dele, as mensagens só vão aparecer no final da execução ou quando encher os 8kb do Buffer que é utilizado para armazenar essas mensagens, você pode estar trocando também para RAISERROR. Obs: o RAISERROR irá limpar o Buffer utilizado para armazenar as mensagens, ou seja se você tiver executado algum PRINT antes, provavelmente sua mensagem ainda estará no Buffer, assim que você executar o RAISERROR todas as mensagens no Buffer serão enviadas juntas, exemplo: ALTER PROC USP_TESTE AS DECLARE @MSG VARCHAR(1000) WAITFOR DELAY ’00:00:05′ SET @MSG = ‘MENSAGEM DE TESTE 1 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) PRINT ‘TESTE 1′ RAISERROR (@MSG, 0, 1) WITH NOWAIT WAITFOR DELAY ’00:00:05’ SET @MSG = ‘MENSAGEM DE TESTE 2 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) PRINT ‘TESTE 2′ RAISERROR (@MSG, 0, 1) WITH NOWAIT WAITFOR DELAY ’00:00:05’ SET @MSG = ‘MENSAGEM DE TESTE 3 – ‘ + CONVERT(VARCHAR(20),GETDATE(),108) RAISERROR (@MSG, 0, 1) WITH NOWAIT Alterei minha procedure novamente e coloquei 2 Prints, o primeiro Print antes do RAISERROR. Veja que meu  primeiro Print já retornou junto com o RAISERROR mesmo o Buffer não estando cheio, após o RAISERROR nosso Buffer de mensagens esta vazio. Bom galera a dica era essa, espero que tenham gostado, qualquer duvida deixe seu comentário, e acompanhe os novos posts. Referencias: PRINT https://msdn.microsoft.com/en-us/library/ms176047.aspx?f=255&MSPPError=-2147217396 RAISERROR https://msdn.microsoft.com/en-us/library/ms178592.aspx?f=255&MSPPError=-2147217396 PRINT VS RAISERROR http://www.jimmcleod.net/blog/index.php/2010/07/19/print-vs-raiserror/ Reginaldo Silva