Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Есть таблица на миллион записей. На таблице кластерный индекс.
По sys.dm_db_index_physical_stats кластерныый индекс имеет три уровня: 1,79 и 25001 страниц на каждом уровне.

Почему при выборке всех данных из таблицы(кластеред индекс скан), SET STATISTICS IO показывает чтение 25082 страниц, хотя должно быть 1+79+25001 = 25081 ?

Почему при выводе одной записи из таблицы(кластеред индекс сик по ключу), logical reads = 5, хотя должно быть 3?

Если селектить из хипа(тейбл скан), то показания sys.dm_db_index_physical_stats и SET STATISTICS IO по количеству страниц сходятся.
-----------------
open your mind
3 дек 12, 16:00    [13569369]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
автор
хотя должно быть 1+79+25001 = 25081 ?
А зачем для скана нужные еще какие-то страницы, кроме листового уровня?
3 дек 12, 16:09    [13569475]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Я так понимаю, что бы найти первую страницу листового уровня, а дальше уже двигаемся по двух-связному списку.
Тогда кол-во страниц сходится:
Одна из sys.system_internals_allocation_units + 1+79+25001.
Но из sys.system_internals_allocation_units можно сразу попасть на первую страницу.


Гавриленко Сергей Алексеевич
По-вашему, при кластеред индекс скан в logical reads мы должны видеть только кол-во страниц на листовом уровне кластерного индекса?
3 дек 12, 16:23    [13569625]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
--__Александр__--
Я так понимаю, что бы найти первую страницу листового уровня, а дальше уже двигаемся по двух-связному списку.
Тогда кол-во страниц сходится:
Одна из sys.system_internals_allocation_units + 1+79+25001.
Сканить средние уровни нет смысла никакого. Либо seek на первую страницу и далее скан (будет плохо для select top 1 каких-нибудь) либо напрямую через листовой уровень.
--__Александр__--
Но из sys.system_internals_allocation_units можно сразу попасть на первую страницу.
Да. И смысла делать по-другому особого нет (есть в некоторых специфических случаях).
--__Александр__--
Гавриленко Сергей Алексеевич
По-вашему, при кластеред индекс скан в logical reads мы должны видеть только кол-во страниц на листовом уровне кластерного индекса?
В моем понимании - да. Хотя могут быть ньюансы, типа ghost records (и страниц, состоящих из этих рекорд полностью).
3 дек 12, 16:30    [13569677]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Сергей, простой тест показывает несколько другое:
USE tempdb
GO
drop table test_Clustered_Index_Scan

CREATE TABLE test_Clustered_Index_Scan
(id int identity(1,1), ch varchar(3000))

declare @id int =1 

WHILE @id <= 100
begin
	insert into test_Clustered_Index_Scan(ch)  VALUES(REPLICATE('a',3000))
	set @id = @id + 1
end

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('test_Clustered_Index_Scan'), NULL, NULL , 'DETAILED');
-- 54 page_count

SET STATISTICS IO ON
select * from test_Clustered_Index_Scan
--- logical reads 54

CREATE CLUSTERED INDEX IXcls on test_Clustered_Index_Scan(id)
SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('test_Clustered_Index_Scan'), NULL, NULL , 'DETAILED');
--1+50 page_count


select * from test_Clustered_Index_Scan
--logical reads 52


Как видите, листовой уровень у нас из 50 страниц, а logical reads в последнем запросе 52.
Как вы это объясните?
3 дек 12, 16:43    [13569784]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
--__Александр__--
Как видите, листовой уровень у нас из 50 страниц, а logical reads в последнем запросе 52.
Элементарной логикой. Чтобы достать все страницы с последнего уровня, промежуточные нафиг не нужны. А что там еще сервер читает при выполнении запроса и потом показывает в статистике - это надо разбираться.
3 дек 12, 16:53    [13569907]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 16943
автор
Хотя могут быть ньюансы, типа ghost records (и страниц, состоящих из этих рекорд полностью).

point forward еще есть. и версионность.
3 дек 12, 17:50    [13570400]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Нюансы конечно хорошо, но кто-нибудь знает, какие еще страницы читаются, помимо листового уровня, при кластеред индекс скан в простейшем случаи(см пример выше)?
3 дек 12, 17:54    [13570446]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37068
--__Александр__--
Нюансы конечно хорошо, но кто-нибудь знает, какие еще страницы читаются, помимо листового уровня, при кластеред индекс скан в простейшем случаи(см пример выше)?
Могут элементарно считаться страницы системного каталога. Или статистика еще какая. И еще 100500 вещей.

Сообщение было отредактировано: 3 дек 12, 17:56
3 дек 12, 17:56    [13570466]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34621
--__Александр__--,

Таблица — связный список страниц, ссылка на первую страницу в sysindex.
Если это кластерный индекс, то страницы в цепочке — листы кластерного индекса.
Если это heap, то это просто страницы.
3 дек 12, 18:20    [13570666]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
MasterZiv
--__Александр__--,

Таблица — связный список страниц, ссылка на первую страницу в sysindex.
Если это кластерный индекс, то страницы в цепочке — листы кластерного индекса.
Если это heap, то это просто страницы.


Спасибо конечно, но теория мне известно.
По-приведенному мною примеру, можете сказать из каких страниц состоит logical reads = 52 страницам ?
4 дек 12, 09:36    [13572790]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34621
Почему при выборке всех данных из таблицы(кластеред индекс скан), SET STATISTICS IO показывает чтение 25082 страниц, хотя должно быть 1+79+25001 = 25081 ?

Даже в теории должно быть 25001

Почему при выводе одной записи из таблицы(кластеред индекс сик по ключу), logical reads = 5, хотя должно быть 3?

Тут согласен..

Если селектить из хипа(тейбл скан), то показания sys.dm_db_index_physical_stats и SET STATISTICS IO по количеству страниц сходятся.



Есть страницы переполнения, если индекс не уникален.

Возможно есть еще row forwarding какой то, в современном Ms я не знаю как все устроено досконально.
4 дек 12, 12:08    [13574101]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Хорошая статья по сабжу
4 дек 12, 12:12    [13574137]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
Гость333
Member

Откуда:
Сообщений: 3683
MasterZiv
Возможно есть еще row forwarding какой то, в современном Ms я не знаю как все устроено досконально.

Это явление возможно только для таблицы-кучи (heap), для индексов (в т.ч. кластерных) применяется расщепление страниц.
4 дек 12, 15:17    [13575889]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Мне кажется, дело в том, что представление sys.dm_db_index_physical_stats, в случае кучи, считает количество страниц включая IAM, в случае индекса, с первой страницы индекса по уровням.
А когда вы смотрите статистику скана по индексу, то читается страница индекса + страница IAM, которая добавляет 1 чтение к 51.
4 дек 12, 16:14    [13576420]     Ответить | Цитировать Сообщить модератору
 Re: Из чтения каких страниц состоит clustered index scan/clustered index seek  [new]
Эмпирист
Guest
Немножко проанализировал как ведет себя скан кластерного индекса всей таблицы в разных условиях и вот что получилось

set nocount on
if OBJECT_ID ('dbo.t') is not null 
	drop table dbo.t

create table dbo.t(
	id int primary key, 
	c char(6000) -- одна запись - одна страница
) 

insert t(id)
select 1

-- Одна страница и один уровень, как такового B-tree нет
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.t'), 1, null, 'detailed')


set statistics io on
select * from t
-- Table 't'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- считано 2 странницы. IAM + единственная страница с данными 
set statistics io off



insert t(id)
select 2


-- Добавляем вторую странницу и получаем полноценно B-tree из трех страниц и двух уровней
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.t'), 1, null, 'detailed')



set statistics io on
select COUNT(*) from t
-- Table 't'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- считано 4 странницы. IAM + две страницы с данными + ? (еще 1 страница, возможно рут)
set statistics io off



-- добьем листовой уровень до 622-х страниц (максимальное кол-во страниц листового уровня при котором дерево остается двухуровневым) 
declare @id int = 3
while 1=1
begin
	if @id > 622
		 break
		 
	insert t(id)
	values(@id)
	

	set @id	 += 1 
end


-- Проверяем, действительно 2 уровня с 622-мя страницами на листовом уровне
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.t'), 1, null, 'detailed')


set statistics io on
select COUNT(*) from t option (maxdop 1)
-- Table 't'. Scan count 1, logical reads 624, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- считано 624 странницы. IAM + 622 страницы с данными + ? (еще 1 страница, возможно рут)
set statistics io off


-- теперь сделаем дерево трехуровневым 
insert t(id)
values(623)



-- Проверяем
select * from sys.dm_db_index_physical_stats(db_id(), object_id('dbo.t'), 1, null, 'detailed')
-- действительно 3 уровня 
-- 623 страницы на листовом уровне
-- 2 страницы на промежуточном
-- 1 страница рут

set statistics io on
select COUNT(*) from t option (maxdop 1)
-- Table 't'. Scan count 1, logical reads 627, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- считано 627 странницы. IAM + 623 страницы с данными + ? (еще 3 страница, похоже на рут + промежуточный уровень)
set statistics io off


очень похоже, что при скане кластерного индекса таблицы считывается не только листовой уровень но и промежуточные и рут.
зачем - х/з
4 дек 12, 17:53    [13577028]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить