Estou tentando entender como o SQL Server acessa dados do índice clusterizado. Meu entendimento é que quando a tabela tem um índice clusterizado, o SQL deve ser capaz de buscar na única página que contém o registro usando o predicado de busca.
No entanto, meu teste me mostrando quando a consulta é executada, carrega mais algumas páginas de dados.
Configurar
CREATE TABLE t2(id INT IDENTITY PRIMARY KEY CLUSTERED,col2 VARCHAR(500),col3 VARCHAR(500));
INSERT INTO [dbo].[t2]([col2],[col3])
SELECT TOP 10010 REPLICATE('z',490),REPLICATE('*',490)
FROM sys.all_columns c1,
sys.all_columns c2
A seguinte consulta
select *
from sys.dm_db_index_physical_stats(db_id(),object_id(N'dbo.t2'),DEFAULT,null,'DETAILED');
mostra a saída como
Eu então limpei o cache com
CHECKPOINT
GO
DBCC DROPCLEANBUFFERS
E correu a seguinte SELECT
busca em uma única linha
SELECT [fplc].*,[t2].[col3] FROM [dbo].[t2] AS [t2]
CROSS APPLY sys.[fn_PhysLocCracker](%%physloc%%) AS [fplc]
WHERE id=4582
A consulta acima me diz que o registro está localizado na página 1061
Eu uso o código abaixo para verificar quantas páginas foram carregadas no buffer para obter o resultado do meu SELECT
SELECT buffers.* FROM sys.dm_os_buffer_descriptors buffers
INNER JOIN sys.allocation_units AS au
ON au.[allocation_unit_id] = buffers.[allocation_unit_id]
INNER JOIN sys.partitions AS p
ON au.[container_id] = p.[partition_id]
INNER JOIN sys.indexes AS i
ON i.[index_id] = p.[index_id] AND p.[object_id] = i.[object_id]
WHERE p.[object_id] > 100
and [database_id] = DB_ID () AND i.[object_id]=OBJECT_ID('t2')
ORDER BY [page_level] desc
Por que o SQL está carregando todas as páginas marcadas em verde? As páginas são carregadas por READ AHEAD ?
Isso é explicado em The Read Ahead que não conta como Read Ahead
No seu caso, você executou isso depois de limpar o cache do buffer com
DBCC DROPCLEANBUFFERS
. Portanto, o SQL Server está no estado em que tentaria aquecer o cache rapidamente.Você precisava ler três páginas para realizar a busca (uma em cada nível do índice). Eram páginas
1111
,1382
e1061
.Então você acabou trazendo três extensões inteiras com números de
1056 - 1063
página1104 - 1111
e1376 - 1383
.Se você fizer o mesmo experimento em algum outro SKU além do mencionado na cotação (e o traceflag 840 não estiver ativado), você deverá ver as três páginas esperadas (abaixo estava em relação ao Express LocalDB)