Fala galera estou aqui novamente trazendo mais um post pra vocês, hoje o assunto é sobre TSQL, a ideia é falar sobre alguns mitos e curiosidades por volta da função COUNT() do SQL Server e mostrarei algo interessante durante a montagem do cenário esse não irei a fundo fica como um bônus rs , quem mexe bastante com a linguagem TSQL com certeza esta acostumado a ver muito em consultas o famoso Count(1) não é mesmo ? E pra você que não viu irei explicar as diferenças e o funcionamento dessa função muito utilizada no dia-a-dia.
Para começar vamos simular um ambiente para realizarmos os testes:
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 INT NOT NULL,CAMPO3 INT) GO CREATE NONCLUSTERED INDEX IDX_DADOS ON DADOS(CAMPO2) GO INSERT INTO DADOS VALUES ((SELECT CAST(RAND() * 1000 AS INT)),(SELECT CAST(RAND() * 1000 AS INT))) GO 10000 GO
Bom no Script acima estou criando meu banco de dados ‘DB_TESTE’ e criando uma tabela chamada ‘DADOS’ com 3 campos, o primeiro campo da tabela irá ser minha PK (índice cluster por padrão) e irá ser um campo inteiro e incremental, o segundo e o terceiro campo são campos do tipo inteiro.
Bom repare que logo após eu criei um índice não cluster na segunda coluna e inseri 10000 mil linhas com valores aleatórios utilizando a função RAND nessa minha tabela.
Ok, agora vamos fazer um count nessa tabela e ver os planos de execução:
SELECT COUNT(*) FROM DADOS SELECT COUNT(1) FROM DADOS SELECT COUNT(CAMPO3) FROM DADOS
Bom até aqui sem novidades os três SELECTs fizeram um SCAN no meu índice Cluster como podemos ver acima.
Bom aqui deixa eu explicar um pouco sobre o funcionamento da função Count(), ele serve basicamente para retornar uma contagem de linhas da sua tabela de acordo com o filtro que você especificar, para isso ele precisa fazer uma varredura em sua tabela para encontrar o numero de linhas existentes, para fazer isso de uma forma mais performática o SQL Server vai tentar ler o menor numero de paginas de dados possível, como assim ? Como sabemos o SQL Server é baseado em custo, por traz dos panos ele vai pensar em uma maneira de te entregar uma determinada requisição da forma menos custosa, ou seja, no nosso exemplo temos uma tabela chamada ‘DADOS’ essa tabela possui dois índices, um índice cluster (a própria tabela e, aqui possui todos os campos) e um índice não cluster(possui apenas o campo ‘CAMPO2’ + os campos da chave do meu cluster, neste caso nosso índice ficará com dois campos), então o SQL Server pode escolher qualquer índice da tabela pra fazer a varredura (desde que o índice não seja filtrado) e retornar o resultado de volta para a aplicação.
Bom chegado até aqui eu já sei que o SQL Server vai ler o menor índice da minha tabela DADOS, e como eu sei qual o tamanho de cada índice da minha tabela ? Vamos la.
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;
Essa query acima me retorna a quantidade de paginas que cada índice tem alocado, então podemos ver que a nossa PK que é nosso índice cluster esta menor do que o índice não cluster, ele tem 9 paginas a menos, então se você voltar na imagem la em cima do plano de execução você vai ver que realmente meu Count() varreu meu índice cluster que era o menor e menos custoso no momento.
Até aqui tudo normal certo ? Não, pera ai, tem algo estranho nesse resultado ai não acha ? Por que nosso índice Cluster esta menor do que nosso índice Não Cluster ? Pense bem, meu índice Cluster possui todas as colunas da tabela e meu índice Não cluster possui apenas duas colunas (CAMPO3 + CAMPO1 que é a nossa chave do cluster) não é? Então pela lógica meu índice Não Cluster deveria ser menor, também estranhei quando vi isso e esse era o bônus que falei no inicio, a reposta pra isso é Page Split!
Acima são eventos do Page Split que ocorreram durante o Insert capturados pelo Extended Events.
Repare que no meu Insert eu utilizo a função RAND para gerar valores aleatórios para os campos CAMPO2 e CAMPO3, porém meu campo 2 tem um índice Não Cluster, meu indice precisa garantir que esse campo estará ordenado pelo seu tipo de dados que é Inteiro, por estar sendo inserido valores aleatórios meu índice ficara cheio de GAPs em sua sequencia, de forma resumida quando sua pagina de dados já esta cheia e você precisa inserir uma linha no meio dessa pagina para garantir a sequencia do seu índice, SQL Server irá criar uma nova pagina de dados e empurrar metade dos dados para essa nova pagina assim liberando espaço para essa nova linha. Não vou entrar muito a fundo para não perder o foco até porque é um assunto extenso e complexo pois envolve também o fator de preenchimento da pagina (FILL FACTOR), bom então devido a esses Splits meu indice Não Cluster ficou com mais paginas de dados.
Como resolver isso ? Simples sempre insira valores incrementais, então sempre tome muito cuidado na escolha dos campos que irá criar índices, principalmente para o índice Cluster. Talvez em um novo Post falaremos mais sobre alguns assuntos mencionados aqui como o Page Split e FILL FACTOR.
No meu caso utilizei uma SEQUENCE para inserir valores ordenados.
IF(SELECT COUNT(*) FROM SYS.SEQUENCES WHERE NAME = ‘SEQ’) > 0 DROP SEQUENCE DBO.SEQ CREATE SEQUENCE DBO.SEQ START WITH 1 INCREMENT BY 1 ; GO TRUNCATE TABLE DADOS GO ALTER TABLE DADOS ADD CONSTRAINT TESTE DEFAULT NEXT VALUE FOR DBO.SEQ FOR CAMPO2 GO INSERT INTO DADOS(CAMPO3) SELECT (SELECT CAST(RAND() * 1000 AS INT)) GO 10000 GO
Inserindo novamente as 10000 linhas na tabela, agora com valores incrementais, não iremos mais ver os eventos de Page Split ocorrendo e verificando novamente o tamanho dos índices.
Agora sim, nosso índice Não cluster esta menor do que nosso índice Cluster, então vamos rodar os Counts novamente ?
Agora já podemos ver que nos dois primeiros SELECTs estão varrendo meu índice Não Cluster IDX_DADOS e o terceiro Count continua varrendo meu índice Cluster.
Qual é a diferença então desses três Counts ?
No primeiro SELECT o COUNT(*) vai contar realmente o numero de linhas dentro do indice ignorando numero de colunas e se elas são nulas ou não.
Bom e no segundo SELECT COUNT(1) ? Um mito esta por trás deste, já ouvi dizerem que ele é mais rápido que ele utiliza menos recursos e essas coisas, mas na verdade o que ele realiza por traz dos panos é COUNT(*) como podemos ver nas propriedades do plano de execução abaixo:
Por trás dos panos o SQL Server faz isso, troca seu COUNT(1) por COUNT(*), então isso realmente é um Mito na verdade os dois primeiros Selects são praticamente iguais, bom e o terceiro SELECT que utiliza o COUNT(CAMPO3) ? Bom esse tem algumas particularidades, como podemos ver no plano de execução ele continua varrendo meu indice Cluster mesmo sabendo que ele é maior, o motivo pra isso é que na estrutura da minha tabela esse campo esta permitindo aceitar nulo, e quando você faz um COUNT especificando uma coluna o SQL Server só ira retornar campos que realmente tem valores ignorando assim os campos nulos, então o SQL Server precisa varrer linha a linha perguntando se existe valor naquele campo, porém se na criação da minha tabela eu falar que esse campo não aceita nulo quando eu fizer o COUNT(CAMPO3) internamente o SQL Server vai fazer a mesma coisa que ele faz para o COUNT(1) irá trocar por (*) e ai o SQL Server irá escolher o menor índice para varrer.
Depois de rodar o Alter Table e informar que meu CAMPO3 não permite nulo, veja que agora minhas três consultas tem o mesmo plano de execução, então mesmo eu especificando a coluna que eu quero contar o SQL Server é sábio o suficiente para saber que aquela coluna não permite nulo, sendo assim a quantidade de linhas tem que ser a mesma para qualquer um dos meus índices desde que ele não seja um índice filtrado.
Bom galera é isso ai, espero que tenham gostado e entendido as diferenças entre os Count() e os mitos por traz dele, qualquer duvida deixem seu comentário, não entrei muito a fundo em alguns assuntos para o Post não ficar muito extenso e virar uma leitura muito cansativa, mas quero fazer mais Posts sobre alguns assuntos como Índices, falamos muito dele hoje mas não entrei muito em detalhes mas espero la na frente fazer Posts dedicados a ele.
Referencia:
COUNT()
Page Split
Extended Events
Reginaldo Silva
Por trás dos panos o SQL Server faz isso, troca seu COUNT(1) por COUNT(*), então isso realmente é um Mito na verdade os dois primeiros Selects são praticamente iguais, bom e o terceiro SELECT que utiliza o COUNT(CAMPO3) ? Bom esse tem algumas particularidades, como podemos ver no plano de execução ele continua varrendo meu indice Cluster mesmo sabendo que ele é maior, o motivo pra isso é que na estrutura da minha tabela esse campo esta permitindo aceitar nulo, e quando você faz um COUNT especificando uma coluna o SQL Server só ira retornar campos que realmente tem valores ignorando assim os campos nulos, então o SQL Server precisa varrer linha a linha perguntando se existe valor naquele campo, porém se na criação da minha tabela eu falar que esse campo não aceita nulo quando eu fizer o COUNT(CAMPO3) internamente o SQL Server vai fazer a mesma coisa que ele faz para o COUNT(1) irá trocar por (*) e ai o SQL Server irá escolher o menor índice para varrer.
Depois de rodar o Alter Table e informar que meu CAMPO3 não permite nulo, veja que agora minhas três consultas tem o mesmo plano de execução, então mesmo eu especificando a coluna que eu quero contar o SQL Server é sábio o suficiente para saber que aquela coluna não permite nulo, sendo assim a quantidade de linhas tem que ser a mesma para qualquer um dos meus índices desde que ele não seja um índice filtrado.
Bom galera é isso ai, espero que tenham gostado e entendido as diferenças entre os Count() e os mitos por traz dele, qualquer duvida deixem seu comentário, não entrei muito a fundo em alguns assuntos para o Post não ficar muito extenso e virar uma leitura muito cansativa, mas quero fazer mais Posts sobre alguns assuntos como Índices, falamos muito dele hoje mas não entrei muito em detalhes mas espero la na frente fazer Posts dedicados a ele.
Referencia:
COUNT()
Page Split
Extended Events
Reginaldo Silva