Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Как работает Clustered Index Scan?  [new]
f070214f
Member

Откуда:
Сообщений: 13
Добрый день. Случайно наткнулся на один интересный факт, которому пока не нашел объяснения. Для получения идентичного результата шаги нижеприведенных скриптов должны выполняться последовательно.

Подготовка данных:
+
use tempdb;

set ansi_nulls on;

if object_id( 'dbo.test' ) is not null drop table dbo.test;

create table dbo.test
  ( a char(  900 ) not null
  , b char( 7000 ) not null );
Тест с кучей:
+
insert dbo.test( a, b ) values( '', '' );
dbcc ind( 'tempdb', 'dbo.test', -1 );
-- 1 IAM страница, 1 страница с данными

set statistics io on;
select * from dbo.test;
set statistics io off;
-- Table 'test'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- с кучей вроде все понятно... сколько страниц с данными, столько и логических чтений, причем обращение к IAM-странице вроде бы не считается...
Тест 1:
+
create clustered index clix_test on test( a );

dbcc ind( 'tempdb', 'dbo.test', -1 );
-- 1 IAM страница, 1 страница с данными

set statistics io on;
select * from dbo.test;
set statistics io off;
-- Table 'test'. 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 logical reads?
Тест 2:
+
insert dbo.test( a, b ) values( '', '' );
dbcc ind( 'tempdb', 'dbo.test', -1 );
-- 1 IAM страница, 1 страница с индексом, 2 страницы с данными

set statistics io on;
select * from dbo.test;
set statistics io off;
-- Table 'test'. 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 logical reads?
Тест 3:
+
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
dbcc ind( 'tempdb', 'dbo.test', -1 );
-- 1 IAM страница, 1 корневая страница, 2 промежуточных страницы, 10 страниц с данными

set statistics io on;
select * from dbo.test;
set statistics io off;
-- Table 'test'. Scan count 1, logical reads 14, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- почему 14 logical reads?
Тест 4:
+
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
insert dbo.test( a, b ) values( '', '' );
dbcc ind( 'tempdb', 'dbo.test', -1 );
-- 1 IAM страница, 1 корневая страница, 4 промежуточных страницы, 18 страниц с данными

set statistics io on;
select * from dbo.test;
set statistics io off;
-- Table 'test'. Scan count 1, logical reads 24, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
-- почему 24 logical reads?
Удаление тестовой таблицы:
+
drop table dbo.test;

Мое видение:

1. Если у нас куча, то SQL Server находит IAM-страницу, по которой находит страницы с данными. В этом случае в logical reads указывается число, совпадающее с количеством страниц с данными, вроде бы все сходится.
2. Если у нас кластерный индекс, то у SQL Server есть два варианта выполнения сканирования: Index Ordered Scan и Allocation Ordered Scan ( http://sqlperformance.com/2015/01/t-sql-queries/allocation-order-scans ). В приведенных примерах скорее всего сервер выберет Index Ordered Scan. В этом случае, как я понимаю, ищется сначала корневая страница, потом по дереву находится страница с первой записью, далее скуль перебирает страницы по ссылкам в заголовках. Однако, я пока не могу объяснить то количество логических чтений которое возвращает сервер. Такое впечатление, что в logical reads указывается количество всех страниц, занятых таблицей - страницы с данными, все страницы индекса, IAM страница, хотя на текущий момент абсолютно не понятно, зачем серверу понадобилось читать все страницы индекса.

Уважаемое сообщество, подскажите, пожалуйста, где в своих рассуждениях я могу ошибаться?

Версия SQL:
+
Microsoft SQL Server 2014 - 12.0.2269.0 (X64) 
Jun 10 2015 03:35:45 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3 <X64> (Build 10240: )
2 мар 16, 16:23    [18888346]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
SET STATISTICS IO (Transact-SQL)



автор
Output item


Meaning


Table

Name of the table.


Scan count

Number of seeks/scans started after reaching the leaf level in any direction to retrieve all the values to construct the final dataset for the output.

•Scan count is 0 if the index used is a unique index or clustered index on a primary key and you are seeking for only one value. For example WHERE Primary_Key_Column = <value>.


•Scant count is 1 when you are searching for one value using a non-unique clustered index which is defined on a non-primary key column. This is done to check for duplicate values for the key value that you are searching for. For example WHERE Clustered_Index_Key_Column = <value>.


• Scan count is N when N is the number of different seek/scan started towards the left or right side at the leaf level after locating a key value using the index key.



logical reads

Number of pages read from the data cache.


physical reads

Number of pages read from disk.


read-ahead reads

Number of pages placed into the cache for the query.


lob logical reads

Number of text, ntext, image, or large value type (varchar(max), nvarchar(max), varbinary(max)) pages read from the data cache.


lob physical reads

Number of text, ntext, image or large value type pages read from disk.


lob read-ahead reads

Number of text, ntext, image or large value type pages placed into the cache for the query.

2 мар 16, 16:36    [18888438]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Может я чего-то недопонял... Но Вы прикалываетесь? Вы написали SELECT * FROM и спрашиваете почему у меня вычитываются все страницы.
2 мар 16, 16:42    [18888485]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Logical Reads
автор
•"Logical Reads
This value indicates the total number of page accesses needed to process the query. Every page is read from the data cache, whether or not it was necessary to bring that page from disk into the cache for any given read. This value is always at least as large and usually larger than the value for Physical Reads. The same page can be read many times (such as when a query is driven from an index), so the count of Logical Reads for a table can be greater than the number of pages in a table
2 мар 16, 16:47    [18888505]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
f070214f
Member

Откуда:
Сообщений: 13
Maxx, спасибо за пояснения. Да, я понимаю, что в зависимости от ситуации одна и та же страница может быть прочитана сервером несколько раз. Меня в большей степени интересует механизм доступа к данным на низком уровне, как сервер работает со страницами после того, как он выбрал Clustered Index Scan. Исходя из тех источников, с которыми я знакомился, у меня в голове сложился некоторый сценарий (приведен в исходном посте). Logical Reads я пытался использовать только как подтверждение или опровержение своих предположений. Пока, увы, не все сходится...
2 мар 16, 17:01    [18888578]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
o-o
Guest
о блин, тут тоже выискивание блох какое-то.
а вы не на таких объемах смотрите.
на миллионной табличке, где +-2 рида погоду не сделают.
2 мар 16, 17:08    [18888621]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
f070214f
Меня в большей степени интересует механизм доступа к данным на низком уровне

Вот Вам еще один пример для затравочки...

SET NOCOUNT ON;
GO
IF OBJECT_ID('dbo.tbl', 'U') IS NOT NULL
	DROP TABLE dbo.tbl
GO
CREATE TABLE dbo.tbl (
    a INT IDENTITY,
    b CHAR(500),
    PRIMARY KEY (a, b)
)
GO
INSERT INTO dbo.tbl
SELECT TOP(1000) 'a'
FROM [master].dbo.spt_values
GO

DBCC IND('master', 'dbo.tbl', -1)

SET STATISTICS IO ON
SELECT * FROM dbo.tbl -- logical reads 78

INSERT INTO dbo.tbl VALUES ('b')
GO

SELECT * -- сколько страниц прочитает? если вычитать нужно одну строку :)
FROM dbo.tbl
WHERE b = 'b'
--OPTION(QUERYTRACEON 9130)


Картинка с другого сайта.

Я это к тому, что пример нужно рассматривать в комплексе и на большой таблице... Там будут и read-ahead и прочие плюшки которые увеличивают число реального числа страниц которые нужно прочитать. Надеялся на заборный пример с Вашим репро... Эх...
2 мар 16, 17:26    [18888704]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
AlanDenton,

Не понял, к чему это вы...
Индекса же нет по колонке 'b' => скан индекса.
Если добавить индекс по колонке 'b', то будет 3 логических чтения.
3 мар 16, 06:38    [18890261]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
o-o
Guest
f070214f,
я предлагаю не страдать по поводу
"почему у меня не сходятся логические чтения" с абсолютной точностью.
да, в них может быть посчитана одна и та же страница, считанная несколько раз.
но после пары экспериментов на относительно больших таблицах,
где число строк не сравнимо с возможной "погрешностью" в 1-2 страницы,
у меня картинка ровно та же:
для кучи число лог. чтений совпадает с общим числом страниц данных и IAM page не учитывается.
для индекса, хотя казалось бы, для полного скана не нужны *все* страницы промежуточных уровней,
число лог.чтений упорно совпадает с общим числом всех вообще страниц.
ну т.е. вот у меня кластерный индекс, где 24391 data pages (leaf level),
а при сканировании его я получаю logical reads 24433.
и что у нас такое 24433?
это сумма всех страниц вообще, вот раскладка по страницам моего кластерного индекса:
pageType IndexLevel cnt
2 2 1
1 0 24391
2 1 40
10 NULL 1
3 мар 16, 11:17    [18891038]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
o-o
Guest
а убедиться, что он тащит *все* страницы в память,
можно, порывшись в sys.dm_os_buffer_descriptors.
например, вот так
select d.page_type, d.page_level, count(*) as cnt
from sys.dm_os_buffer_descriptors d
     join sys.allocation_units au 
        on d.allocation_unit_id = au.allocation_unit_id
     join sys.partitions p  
        on au.container_id = p.hobt_id 
where database_id = db_id() and object_name(p.object_id) = 'myorders'

ну т.е. сперва делаем CHECKPOINT; DBCC DROPCLEANBUFFERS;
потом запрос со сканом индекса,
потом проверяем, есть ли в памяти страницы 1-ого уровня.
они там ВСЕ и они считают IAM page индексной страницей тоже,
ибо что еще за 2 страницы 2-ого уровня, 2 корня что ли?

К сообщению приложен файл. Размер - 66Kb
3 мар 16, 11:57    [18891251]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
f070214f
Member

Откуда:
Сообщений: 13
o-o, спасибо. Действительно, про dm_os_buffer_descriptors я как-то не вспомнил...

А 2 страницы 2-ого уровня - это, похоже, действительно страницы 2-го уровня (IAM-страницы в представлении отображаются правильно). В моем случае таких страниц 2-го уровня аж целых 8...
+
use tempdb;

if object_id( 'dbo.test' ) is not null drop table dbo.test;

select c1.*
  into dbo.test
  from sys.columns c1 cross join sys.columns c2;

create clustered index clix_test on test( [object_id], [column_id] );

checkpoint;
dbcc dropcleanbuffers;

set statistics io on;
select * from dbo.test;
set statistics io off;

select d.page_type, d.page_level, count(*) as page_count
  from sys.partitions p with( nolock )
    inner join
      sys.allocation_units au with( nolock ) on au.container_id = p.hobt_id
    inner join
      sys.dm_os_buffer_descriptors d with( nolock ) on d.allocation_unit_id = au.allocation_unit_id
  where p.[object_id] = object_id( 'test' )
  group by d.page_type, d.page_level
  order by page_level desc;

page_typepage_levelpage_count
INDEX_PAGE28
INDEX_PAGE133
DATA_PAGE09334

Тут эти таинственные страницы показаны подробнее:

database_idfile_idpage_idpage_levelallocation_unit_idpage_typerow_countfree_space_in_bytesis_modifiednuma_noderead_microsecis_in_bpool_extension
211976222666130991371517952INDEX_PAGE3373350012254NULL
211979422666130991371517952INDEX_PAGE48004003003NULL
211982622666130991371517952INDEX_PAGE480040063799NULL
211985822666130991371517952INDEX_PAGE4800400312747NULL
211989022666130991371517952INDEX_PAGE3802900264465NULL
211992222666130991371517952INDEX_PAGE4800400124192NULL
211995422666130991371517952INDEX_PAGE57979005301NULL
211998622666130991371517952INDEX_PAGE48004005437NULL


dbcc ind про эти страницы ничего не говорит за исключением "настоящей" корневой страницы:

select * 
  from dbo.ind_data
  where PagePID in( 19762, 19794, 19826, 19858, 19890, 19922, 19954, 19986 );

PageFIDPagePIDIAMFIDIAMPIDObjectIDIndexIDPartitionNumberPartitionIDiam_chain_typePageTypeIndexLevelNextFIDNextPIDPrevPageFIDPrevPagePID
11976211461429580131112882303773602938880In-row data220000


Тем не менее они реально существуют, dbcc page подтверждает, что эти страницы принадлежат испытуемому объекту, являются индексными страницами и относятся ко второму уровню.
+
dbcc page( 'tempdb', 1, 19794, 0 );

/*
PAGE: (1:19794)


BUFFER:


BUF @0x000000018118FD80

bpage = 0x0000000165582000          bhash = 0x0000000000000000          bpageno = (1:19794)
bdbid = 2                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 38493                       bstat = 0x9
blog = 0xab21215a                   bnext = 0x0000000000000000          

PAGE HEADER:


Page @0x0000000165582000

m_pageId = (1:19794)                m_headerVersion = 1                 m_type = 2
m_typeFlagBits = 0x0                m_level = 2                         m_flagBits = 0x220
m_objId (AllocUnitId.idObj) = 182620m_indexId (AllocUnitId.idInd) = 9472
Metadata: AllocUnitId = 2666130991371517952                              
Metadata: PartitionId = 2882303773602938880                              Metadata: IndexId = 1
Metadata: ObjectId = 1429580131     m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 15                        m_slotCnt = 4                       m_freeCnt = 8004
m_freeData = 180                    m_reservedCnt = 0                   m_lsn = (932:11720:533)
m_xactReserved = 0                  m_xdesId = (0:39163)                m_ghostRecCnt = 0
m_tornBits = 1496452879             DB Frag ID = 1                      

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:16176) = 0x0   0_PCT_FULL
DIFF (1:6) = NOT CHANGED            ML (1:7) = NOT MIN_LOGGED           


DBCC execution completed. If DBCC printed error messages, contact your system administrator.
*/


А содержание этих страниц дублируют содержание "главной" корневой страницы.

В общем, чем дальше в лес, тем больше дров :)

К сообщению приложен файл. Размер - 40Kb
4 мар 16, 07:00    [18894958]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
o-o
Guest
f070214f,
Лишние логические чтения при скане индекса это в аккурат чтения страниц всех промежуточных уровней (казалось бы, хватило бы первой страницы каждого уровня, чтобы пройтись до листового уровня).
А вычитывает он все промежуточные уровни целиком, если собирается делать read ahead (тогда ему нужен список всех страниц листового, чтобы их не по одной, а пачками читать)
Итого, если хотите увидеть в логических чтениях только число всех страниц-листьев плюс по одному чтению на каждом промежуточном уровне, отключите read ahead, т.е. включите TF 652.
Идея не моя, подсказал Ben-Gan, но проканалось, при включенном флаге сходится с точностью до страницы.
У меня была мысль по этому поводу, но смущало все то же число логических чтений, когда уже все страницы в памяти и никакого read ahead не происходит, вот это мог бы объяснить только кто-то другой, не я :)
Поэтому никакой подтверждающей ссылки нет, есть только мнение Бен-Гана по этому поводу:
The way I see it, they can’t know ahead if the target leaf pages are already in memory because they don’t know ahead what the target pages are. So they have to scan the upper level pages to figure out which pages are needed at the leaf. So the upper level pages would always need to be read logically, with corresponding physical reads as needed. Then, after figuring out which pages are needed from the leaf, and whether they are cached already, they can determine whether to employ read ahead against them.
7 мар 16, 16:44    [18905816]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
o-o
Guest
Вернее, так.
Нет никакой ссылки, что в логические чтения пойдут вычитывания вышестоящего уровня для формирования списка листовых страниц для read ahead, но что сам механизм read ahead именно так организован, вот, можно тут убедиться:
The storage engine uses the information in the intermediate index page above the leaf level to schedule serial read-aheads for the pages that contain the keys. If a request is made for all the keys from ABC to DEF, the storage engine first reads the index page above the leaf page. However, it does not just read each data page in sequence from page 504 to page 556 (the last page with keys in the specified range). Instead, the storage engine scans the intermediate index page and builds a list of the leaf pages that must be read. The storage engine then schedules all the reads in key order. The storage engine also recognizes that pages 504/505 and 527/528 are contiguous and performs a single scatter read to retrieve the adjacent pages in a single operation. When there are many pages to be retrieved in a serial operation, the storage engine schedules a block of reads at a time. When a subset of these reads is completed, the storage engine schedules an equal number of new reads until all the required reads have been scheduled.
https://technet.microsoft.com/en-us/library/ms191475(v=sql.105).aspx
7 мар 16, 16:54    [18905839]     Ответить | Цитировать Сообщить модератору
 Re: Как работает Clustered Index Scan?  [new]
f070214f
Member

Откуда:
Сообщений: 13
o-o, спасибо за разъяснения! Нюансов много, но в целом картина стала гораздо понятнее.
9 мар 16, 07:11    [18909548]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить