Início / dba / Perguntas / 243746
Accepted
DForck42
Asked: 2019-07-25 19:48:48 +0800 CST

O que o SQL Server usa para fazer uma contagem(*) em uma tabela?

  • 772

Internamente, como o SQL Server determina o resultado de um select(*)? É diferente entre um heap e um índice clusterizado? É capaz de fazer coisas inteligentes com o índice clusterizado para não precisar carregar a tabela inteira?

sql-server index

1 respostas

  1. Best Answer

    Com uma consulta trivial SELECT COUNT(*) FROM dbo.YourTable;em uma tabela baseada em disco, o SQL Server conta as linhas verificando os nós de heap ou folha de um índice. O otimizador do SQL Server baseado em custo escolhe o mais estreito disponível para minimizar o número de páginas digitalizadas.

    Este exemplo mostra o comportamento com uma verificação de índice .

    CREATE TABLE dbo.YourTable(
      Column1 int NOT NULL
    );
--load 10K rows
WITH 
     t10 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) t(n))
    ,t1k AS (SELECT 0 AS n FROM t10 AS a CROSS JOIN t10 AS b CROSS JOIN t10 AS c)
    ,t1g AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS num FROM t1k AS a CROSS JOIN t1k AS b CROSS JOIN t1k AS c)
INSERT INTO dbo.YourTable WITH (TABLOCKX) (Column1) 
SELECT num
FROM t1g
WHERE num <= 10000;
GO
CREATE INDEX ncidx_YourTable_IntColumn ON dbo.YourTable(Column1);
GO
--index scan
SELECT COUNT(*) FROM dbo.YourTable;
GO
--show heap and index size
SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
    db_id(), object_id('dbo.YourTable'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name;

    Tamanhos de índice/heap:

    +---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         192 |
+---------------------------+-------------+

    Se fragmentarmos o índice, ele se tornará maior que o heap, de modo que o heap será verificado em vez do índice:

    --fragment index and update stats
UPDATE dbo.YourTable SET Column1 = 2;
UPDATE STATISTICS dbo.YourTable;
DBCC FREEPROCCACHE;
--heap scan
SELECT COUNT(*) FROM dbo.YourTable;
GO

    Heap e tamanho do índice com fragmentação:

    +---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         648 |
+---------------------------+-------------+

    Depois que o índice é reconstruído, o índice é verificado novamente porque é mais estreito:

    --rebuild index
ALTER INDEX ncidx_YourTable_IntColumn ON dbo.YourTable REBUILD;
DBCC FREEPROCCACHE;
--index scan
SELECT COUNT(*) FROM dbo.YourTable;
GO

    Heap e tamanho do índice após a reconstrução:

    SELECT
    i.name              AS IndexName,
    SUM(page_count * 8) AS IndexSizeKB
FROM sys.dm_db_index_physical_stats(
    db_id(), object_id('dbo.YourTable'), NULL, NULL, 'DETAILED') AS s
JOIN sys.indexes AS i
ON s.object_id = i.object_id AND s.index_id = i.index_id
GROUP BY i.name
ORDER BY i.name;
GO

+---------------------------+-------------+
|         IndexName         | IndexSizeKB |
+---------------------------+-------------+
| NULL                      |         272 |
| ncidx_YourTable_IntColumn |         192 |
+---------------------------+-------------+
    • 10

relate perguntas