Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
 Re: Перекрестные индексы. Как лучше?  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28141
Гавриленко Сергей Алексеевич,

Вы наверное очень удивитесь, но если ...
CREATE INDEX IX_testindex ON testindex(a,b)
GO
set statistics IO ON
go
SELECT * FROM testindex WHERE a = 5 and b = 'X'
GO
set statistics IO off
GO
DROP INDEX IX_testindex ON testindex
GO
CREATE INDEX IX_testindex ON testindex(b,a)
GO
set statistics IO ON
go
SELECT * FROM testindex WHERE a = 5 and b = 'X'
GO
set statistics IO off
GO
DROP INDEX IX_testindex ON testindex
GO
--------------------------------------------------------------------------------------------
Table 'testindex'. 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.
Table 'testindex'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Разница в наших показаниях заключается в том что при использовании индекса №1 сервер использует статистики индекса №2.
В вашем случае для построения статистики по полю "b" был сделан FULLSCAN и значение "Х" попало в статистики.
Получается что-б ваш пример работал, вы должны иметь индексы по ВСЕМ полям, либо делать апдейт статистик с опцией FULLSCAN.

Но даже в вашем случае если вы запустите запрос БЕЗ указания индекса, то сервер выберет индекс №2.
12 окт 18, 04:32    [21702095]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28141
Mind,

Вообще-то планы разные, так как индекс по полю А "не видит" реального распределения значений по полю "Б" если по полю "Б" нет полной статистики.
12 окт 18, 04:38    [21702096]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28141
invm
Касаемого вашего теста

У меня получился такой-же результат как и у вас, когда я запустил тест в tempdb.

Обнаружилась интересная закономерность.
В tempdb статистика по полю "b" включает значение "X", а в пользовательской базе нет.

В этом и есть разница в IO.
Если надо, могу дать скриншоты статистик, хотя я уже и давал скриншоты разных планов.
12 окт 18, 04:51    [21702103]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
SandalTree
Обнаружилась интересная закономерность.
В tempdb статистика по полю "b" включает значение "X", а в пользовательской базе нет.
Вы опять фантазируете.
Статистика по (a,b) не может содержать распределение по b, ибо гистограмма хранится только для первого столбца. В любой БД.
Соответственно, статистика по (b,a) будет содержать такое распределение. Опять же в любой БД.
SandalTree
Если надо, могу дать скриншоты статистик, хотя я уже и давал скриншоты разных планов.
Ваши картинки мало кому интересны в качестве доказательств.
Репро давайте, подтверждающее ваши слова.
12 окт 18, 10:10    [21702226]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8711
Mind
invm
пропущено...
Попробуйте поразмышлять почему рекомендовано именно так.
Если честно я вообще не понимаю что именно они рекомендуют.
Какие еще варианты могут быть запихать колонку в WHERE кроме как = > < или BETWEEN? С функциями что-ли? Additional columns это что? Все те что кроме first? Или те что все еще в WHERE но непонятно с какими условиями поиска или те что даже не в WHERE? А зачем они вообще в индексе нужны? А какая тогда разница на их distinctness? И вообще получается что только начиная со второй колонки есть смысл в каком порядке запихивать в индекс, а первую как выбрать если там несколько в WHERE?
Или они хотели сказать "Additionally, columns...". Такое ощущение что эти рекомендации переводили индусы основываясь на доисторических свитках Sybase.

like ?

==================
PS Как вариант like 'АБэЦэ...%' (в идеале должен быть seek вместо scan)....
12 окт 18, 14:35    [21702571]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
SandalTree
Вообще-то планы разные, так как индекс по полю А "не видит"
При чем тут индекс "видит", "не видит"? Чушь какую то развели. Оптимизатор решает на основании статистики. Статистики могут быть как привязанные к индексу так и отдельно. И те и другие статистики могут создаваться/обновляться с FULLSCAN и без. Исключение составляет лишь создание/ребилд индекса потому что в этом случае статистика обновляется с FULLSCAN автоматически.
SandalTree
реального распределения значений по полю "Б" если по полю "Б" нет полной статистики.
Так при чем тут индекс то, если все дело в статистике? А особенно при чем тут селективность полей?
SandalTree
Если надо, могу дать скриншоты статистик, хотя я уже и давал скриншоты разных планов.
Все ваши тесты доказывают лишь то что при потере всех конечностей таракан теряет слух. Связи между селективностью и тем какой индекс сервер выбирает никакой, потому что решение оптимизатора в данном случае целиком и полностью зависит от того насколько дерьмовую статистику вы ему подсовываете.
SandalTree
Вы наверное очень удивитесь, но если ...
Удивляетесь здесь по-моему только вы... Готовы?
Если в ваш пример добавить селективности по полю A
DROP TABLE testindex 
GO
CREATE TABLE testindex (a INT, b CHAR(1))
GO
;WITH xx AS (SELECT * FROM 
	(VALUES (5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A')) AS x(a,b)
) 
INSERT INTO testindex(a,b) SELECT x1.*
FROM xx AS x1,xx AS x2,xx AS x3,xx AS x4,xx AS x5,xx AS x6,xx AS x7;
GO
INSERT INTO testindex(a,b) VALUES (1,'A')
INSERT INTO testindex(a,b) VALUES (2,'A')
INSERT INTO testindex(a,b) VALUES (3,'A')
INSERT INTO testindex(a,b) VALUES (4,'A')
INSERT INTO testindex(a,b) VALUES (5,'X')
GO

то сервер все равно продолжит выбирать индекс (b,a)!
SandalTree
Постройте 2 разных индекса и посмотрите что сервер предпочтёт тот по которуму выше селективность в первой колонке.
О ужас! Ваша псевдо-теория развалилась.


Можно построить оба индекса одновременно, чтобы исключить безлапых тараканов неполноценную статистику из нашего эксперимента.
+
DROP INDEX IX_testindex ON testindex;
CREATE INDEX IX_testindex_1 ON testindex(a,b)
CREATE INDEX IX_testindex_2 ON testindex(b,a)
go
set statistics profile on;
go
SELECT a, b FROM testindex WHERE a = 5 and b = 'X'
go
set statistics profile off;
go
Но сервер упорно продолжает выбирать индекс с меньшей селективностью!
|--Index Seek(OBJECT:([TEST].[dbo].[testindex].[IX_testindex_2]), SEEK:([TEST].[dbo].[testindex].[b]='X' AND [TEST].[dbo].[testindex].[a]=(5)) ORDERED FORWARD)
13 окт 18, 00:48    [21703115]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8711
Mind
SandalTree
Вообще-то планы разные, так как индекс по полю А "не видит"
При чем тут индекс "видит", "не видит"? Чушь какую то развели. Оптимизатор решает на основании статистики. Статистики могут быть как привязанные к индексу так и отдельно. И те и другие статистики могут создаваться/обновляться с FULLSCAN и без. Исключение составляет лишь создание/ребилд индекса потому что в этом случае статистика обновляется с FULLSCAN автоматически.
SandalTree
реального распределения значений по полю "Б" если по полю "Б" нет полной статистики.
Так при чем тут индекс то, если все дело в статистике? А особенно при чем тут селективность полей?
SandalTree
Если надо, могу дать скриншоты статистик, хотя я уже и давал скриншоты разных планов.
Все ваши тесты доказывают лишь то что при потере всех конечностей таракан теряет слух. Связи между селективностью и тем какой индекс сервер выбирает никакой, потому что решение оптимизатора в данном случае целиком и полностью зависит от того насколько дерьмовую статистику вы ему подсовываете.
SandalTree
Вы наверное очень удивитесь, но если ...
Удивляетесь здесь по-моему только вы... Готовы?
Если в ваш пример добавить селективности по полю A
DROP TABLE testindex 
GO
CREATE TABLE testindex (a INT, b CHAR(1))
GO
;WITH xx AS (SELECT * FROM 
	(VALUES (5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A')) AS x(a,b)
) 
INSERT INTO testindex(a,b) SELECT x1.*
FROM xx AS x1,xx AS x2,xx AS x3,xx AS x4,xx AS x5,xx AS x6,xx AS x7;
GO
INSERT INTO testindex(a,b) VALUES (1,'A')
INSERT INTO testindex(a,b) VALUES (2,'A')
INSERT INTO testindex(a,b) VALUES (3,'A')
INSERT INTO testindex(a,b) VALUES (4,'A')
INSERT INTO testindex(a,b) VALUES (5,'X')
GO

то сервер все равно продолжит выбирать индекс (b,a)!
SandalTree
Постройте 2 разных индекса и посмотрите что сервер предпочтёт тот по которуму выше селективность в первой колонке.
О ужас! Ваша псевдо-теория развалилась.


Можно построить оба индекса одновременно, чтобы исключить безлапых тараканов неполноценную статистику из нашего эксперимента.
+
DROP INDEX IX_testindex ON testindex;
CREATE INDEX IX_testindex_1 ON testindex(a,b)
CREATE INDEX IX_testindex_2 ON testindex(b,a)
go
set statistics profile on;
go
SELECT a, b FROM testindex WHERE a = 5 and b = 'X'
go
set statistics profile off;
go
Но сервер упорно продолжает выбирать индекс с меньшей селективностью!
|--Index Seek(OBJECT:([TEST].[dbo].[testindex].[IX_testindex_2]), SEEK:([TEST].[dbo].[testindex].[b]='X' AND [TEST].[dbo].[testindex].[a]=(5)) ORDERED FORWARD)


чуть меняем условие :

set statistics profile on;
go
SELECT a, b FROM testindex WHERE a =5 and b ! = 'X'
go
set statistics profile off;
go

Получаем картину:

StmtText:
  |--Index Seek(OBJECT:([tempdb].[dbo].[testindex].[IX_testindex_1]), SEEK:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b] < 'X' OR [tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b] > 'X') ORDERED FORWARD)

при
Argument:
OBJECT:([tempdb].[dbo].[testindex].[IX_testindex_1]), SEEK:([tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b] < 'X' OR [tempdb].[dbo].[testindex].[a]=(5) AND [tempdb].[dbo].[testindex].[b] > 'X') ORDERED FORWARD


Выбор в пользу другого индекса при НЕочевидном (для меня, по крайней мере) преобразовании...
14 окт 18, 14:10    [21703619]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SIMPLicity_
Member

Откуда: (((@)))
Сообщений: 8711
PS Вообще, вы куда-то слишком в дебри углубились...
PPS Мне так кажется....
14 окт 18, 14:18    [21703624]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
Mr. X
Member

Откуда:
Сообщений: 21
SIMPLicity_,

Да какие уж там дебри, все крутится (в основном) вокруг статистики. Из которой тут упоминали только селективность (selectivity), хотя в статистике присутствуют плотность (density), мощьность (cardinality) и гистограмма (histogram). Которые SQL так же использует для оценок.
14 окт 18, 18:13    [21703675]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28141
invm
SandalTree
Обнаружилась интересная закономерность.
В tempdb статистика по полю "b" включает значение "X", а в пользовательской базе нет.
Вы опять фантазируете.
Статистика по (a,b) не может содержать распределение по b, ибо гистограмма хранится только для первого столбца. В любой БД.
Соответственно, статистика по (b,a) будет содержать такое распределение. Опять же в любой БД.
SandalTree
Если надо, могу дать скриншоты статистик, хотя я уже и давал скриншоты разных планов.
Ваши картинки мало кому интересны в качестве доказательств.
Репро давайте, подтверждающее ваши слова.


Во первых: есть 2 статистики по обоим полям и когда скуль строит план, то он смотрит на обе. (закрыли этот вопрос?)

Во вторых: при запуске одного и того-же кода в разных базах происходит различная обработка алгоритма выборки сэмла. В одной базе последняя страница входит в сэмпл, а в другой нет, от этого различные статистики и планы. (какого рода репо вам предоставить? Потестируйте в разных базах и посмотрите на сэмплы и статистики.)

В третьих: При двух индексах a-b и b-a скуль почему-то выбирает второй индекс. Сможете ответить на вопрос "почему?"?
15 окт 18, 06:02    [21703818]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28141
Mind
дивляетесь здесь по-моему только вы... Готовы?
Если в ваш пример добавить селективности по полю A
то сервер все равно продолжит выбирать индекс (b,a)!

Да, вы правы. В случае выбора между двумя этими индексами скуль не видит разницы так как планы у них с одинаковой стоимостью.
15 окт 18, 07:33    [21703831]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
SandalTree
Во первых: есть 2 статистики по обоим полям и когда скуль строит план, то он смотрит на обе. (закрыли этот вопрос?)
Еще раз - для уровня оптимизации TRIVIAL статистика не используется для выбора плана. Только для оценки количества строк.
SandalTree
Во вторых: при запуске одного и того-же кода в разных базах происходит различная обработка алгоритма выборки сэмла. В одной базе последняя страница входит в сэмпл, а в другой нет, от этого различные статистики и планы.
Да? Вам был даден пример, из которого понятно из-за чего планы разные. Может для вас станет понятнее, когда отключите автосоздание статистики.
Но вы продолжаете фантазировать... Особо порадовала зависимость IO запроса от статистики...
SandalTree
В третьих: При двух индексах a-b и b-a скуль почему-то выбирает второй индекс. Сможете ответить на вопрос "почему?"?
Неужто из-за селективности?
Попробуйте поменять порядок создания индексов.

Ну и напоследок вопрос - каким образом для индексов (a, b) и (b, a), при предикате a = ... and b = ..., селективность столбцов индекса будет влиять на операцию поиска в нем?
15 окт 18, 13:43    [21704160]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28141
invm
SandalTree
Во вторых: при запуске одного и того-же кода в разных базах происходит различная обработка алгоритма выборки сэмла. В одной базе последняя страница входит в сэмпл, а в другой нет, от этого различные статистики и планы.
Да? Вам был даден пример, из которого понятно из-за чего планы разные. Может для вас станет понятнее, когда отключите автосоздание статистики.

Вы мне не верите, но сами проверить не желаете. Вот вам скриншот.

К сообщению приложен файл. Размер - 16Kb
15 окт 18, 17:34    [21704398]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28141
invm,

Разница в IO обусловлена тем что статистики не захватили FULLSAMPLE.
Прочитано только 7% всех записей и значения с последней страницы остались неучтёнными.

DECLARE @SQL VARCHAR(1000);

SELECT @SQL = 'DBCC SHOW_STATISTICS (''dbo.testindex'',''' + name + ''');' FROM sys.stats as s
INNER JOIN sys.stats_columns AS c
	ON c.object_id = s.object_id AND s.stats_id = c.stats_id
WHERE s.object_id = OBJECT_ID('testindex') AND c.column_id = 1
EXEC (@SQL)
SELECT @SQL = 'DBCC SHOW_STATISTICS (''dbo.testindex'',''' + name + ''');' FROM sys.stats as s
INNER JOIN sys.stats_columns AS c
	ON c.object_id = s.object_id AND s.stats_id = c.stats_id
WHERE s.object_id = OBJECT_ID('testindex') AND c.column_id = 2
EXEC (@SQL)


К сообщению приложен файл. Размер - 13Kb
15 окт 18, 17:39    [21704401]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28141
invm,

Как только мы перестроим статистики с полным сканированием то получим результат, который нам продемонстрировал Сергей Гавриленко.
DECLARE @SQL VARCHAR(1000);
SELECT @SQL = 'UPDATE STATISTICS dbo.testindex ' + name + ' WITH FULLSCAN;' FROM sys.stats as s
INNER JOIN sys.stats_columns AS c
	ON c.object_id = s.object_id AND s.stats_id = c.stats_id
WHERE s.object_id = OBJECT_ID('testindex') AND c.column_id = 1
EXEC (@SQL)
SELECT @SQL = 'UPDATE STATISTICS dbo.testindex ' + name + ' WITH FULLSCAN;' FROM sys.stats as s
INNER JOIN sys.stats_columns AS c
	ON c.object_id = s.object_id AND s.stats_id = c.stats_id
WHERE s.object_id = OBJECT_ID('testindex') AND c.column_id = 2
EXEC (@SQL)


К сообщению приложен файл. Размер - 16Kb
15 окт 18, 17:44    [21704404]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36965
Откуда появляется четвертое логическое чтение -- не понятно. (Не факт, что оно есть на самом деле и не врет statistics io.) Опять же, если выключить автосоздание статистик на базе, то чтений будет 4 в обоих вариантах, а если индекс прибить руками, то 3 в обоих вариантах.

Однако при глубине индекса в три уровня, четвертое чтение не может быть из дерева индекса -- в индексе просто читать больше нечего.

Короче, тут не в структуре индекса дело, а в каких-то сайд-эффектах от статистики.
15 окт 18, 17:47    [21704406]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
SandalTree
Вы мне не верите, но сами проверить не желаете. Вот вам скриншот.
Зачем мне ваш скриншот? В моем примере ровно то же самое.
Видимо вы так не удосожились хотя бы его результаты посмотреть.
SandalTree
Разница в IO обусловлена тем что статистики не захватили FULLSAMPLE.
Прочитано только 7% всех записей и значения с последней страницы остались неучтёнными.
И каким же образом "неучтенность значений" при компиляции повлияла на статистику IO при выполнении?
15 окт 18, 17:57    [21704408]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Гавриленко Сергей Алексеевич
Опять же, если выключить автосоздание статистик на базе, то чтений будет 4 в обоих вариантах, а если индекс прибить руками, то 3 в обоих вариантах.
А если обновить все статистики с FULLSCAN, то тоже становится 3 чтения вместо 4х.
15 окт 18, 21:12    [21704535]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
+ Тайна 4-й страницы
use master;
create database DBTest001;
alter database DBTest001 set auto_create_statistics off;
go

declare @sql nvarchar(max) = N'create event session DBTest001_TrackLatches on server 
add event sqlserver.latch_acquired
(
  action (sqlserver.session_id, sqlserver.sql_text)
  where ([database_id] = {dbid} and session_id = {spid})
)
add target package0.ring_buffer (set max_memory = 1000)
with (event_retention_mode=allow_single_event_loss, max_dispatch_latency = 3 seconds, track_causality=on, startup_state=off);';

if exists(select 1 from sys.server_event_sessions where name = N'DBTest001_TrackLatches')
 drop event session DBTest001_TrackLatches on server;

select
 @sql = replace(replace(@sql, N'{dbid}', cast(db_id('DBTest001') as nvarchar(10))), N'{spid}', cast(@@spid as nvarchar(10)));

exec(@sql);
go

use DBTest001;
go

CREATE TABLE testindex (a INT, b CHAR(1))
GO
;WITH xx AS (SELECT * FROM 
	(VALUES (5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A')) AS x(a,b)
) 
INSERT INTO testindex(a,b) SELECT x1.*
FROM xx AS x1,xx AS x2,xx AS x3,xx AS x4,xx AS x5,xx AS x6,xx AS x7;
GO
INSERT INTO testindex(a,b) VALUES (5,'X')
GO

create statistics ST_testindex__a on testindex (a) --with fullscan;
create statistics ST_testindex__b on testindex (b) --with fullscan;
go

create index IX_testindex on testindex (a, b);
go

create procedure spGetPagesRead
 @UpdateStatisticsMode varchar(10) = null

as
begin
 set nocount on;

 if @UpdateStatisticsMode = 'fullscan'
  begin
   update statistics testindex(ST_testindex__a) with fullscan;
   update statistics testindex(ST_testindex__b) with fullscan;
  end
 else
  begin
   update statistics testindex(ST_testindex__a);
   update statistics testindex(ST_testindex__b);
  end;

 alter event session DBTest001_TrackLatches on server state = start;
 set statistics io on; SELECT a, b FROM testindex WHERE a = 5 and b = 'X'  option (recompile); set statistics io off;

 declare @x xml;

 select
  @x = cast(t.target_data as xml)
 from
  sys.dm_xe_sessions s join
  sys.dm_xe_session_targets t on t.event_session_address = s.address
 where
  s.name = N'DBTest001_TrackLatches';

 alter event session DBTest001_TrackLatches on server state = stop;

 select distinct
  object_name(pa.object_id) as object, pi.file_id, pi.page_id, pa.index_id, pa.page_type_desc
 from
  @x.nodes('/RingBufferTarget/event[@name = "latch_acquired"][data[@name = "mode"][text = "SH"]]') t(n) cross apply
  (select t.n.value('(data[@name = "file_id"]/value)[1]', 'smallint'), t.n.value('(data[@name = "page_id"]/value)[1]', 'int')) pi(file_id, page_id) join
  sys.dm_db_database_page_allocations(db_id(), null, null, null, 'detailed') pa on pa.allocated_page_file_id = pi.file_id and pa.allocated_page_page_id = pi.page_id

end;
go

exec dbo.spGetPagesRead 'fullscan';
exec dbo.spGetPagesRead;
go

drop event session DBTest001_TrackLatches on server;
go

use master;
drop database DBTest001;
go

С fullscan
objectfile_idpage_idindex_idpage_type_desc
sysobjvalues1431INDEX_PAGE
sysobjvalues11281DATA_PAGE
sysobjvalues11321DATA_PAGE
syssingleobjrefs1971DATA_PAGE
testindex12904INDEX_PAGE
testindex1380974INDEX_PAGE
testindex1412504INDEX_PAGE

Без fullscan
objectfile_idpage_idindex_idpage_type_desc
sysmultiobjrefs11052INDEX_PAGE
sysobjvalues1431INDEX_PAGE
sysobjvalues11281DATA_PAGE
sysobjvalues11811DATA_PAGE
syssingleobjrefs1971DATA_PAGE
testindex12904INDEX_PAGE
testindex1380974INDEX_PAGE
testindex1412504INDEX_PAGE

Осталось понять какого лешего это попадает в статистику IO.
15 окт 18, 21:23    [21704544]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
В предыдущем примере накосячено.
+ Исправлено
use master;
create database DBTest001;
alter database DBTest001 set auto_create_statistics off;
go

declare @sql nvarchar(max) = N'create event session DBTest001_TrackLatches on server 
add event sqlserver.latch_acquired
(
  action (sqlserver.session_id, sqlserver.sql_text)
  where ([database_id] = {dbid} and session_id = {spid})
)
add target package0.ring_buffer (set max_memory = 1000)
with (event_retention_mode=allow_single_event_loss, max_dispatch_latency = 3 seconds, track_causality=on, startup_state=off);';

if exists(select 1 from sys.server_event_sessions where name = N'DBTest001_TrackLatches')
 drop event session DBTest001_TrackLatches on server;

select
 @sql = replace(replace(@sql, N'{dbid}', cast(db_id('DBTest001') as nvarchar(10))), N'{spid}', cast(@@spid as nvarchar(10)));

exec(@sql);
go

use DBTest001;
go

CREATE TABLE testindex (a INT, b CHAR(1))
GO
;WITH xx AS (SELECT * FROM 
	(VALUES (5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A'),(5,'A')) AS x(a,b)
) 
INSERT INTO testindex(a,b) SELECT x1.*
FROM xx AS x1,xx AS x2,xx AS x3,xx AS x4,xx AS x5,xx AS x6,xx AS x7;
GO
INSERT INTO testindex(a,b) VALUES (5,'X')
GO

create statistics ST_testindex__a on testindex (a) --with fullscan;
create statistics ST_testindex__b on testindex (b) --with fullscan;
go

create index IX_testindex on testindex (a, b);
go

create procedure spGetPagesRead
 @UpdateStatisticsMode varchar(10) = null

as
begin
 set nocount on;

 if @UpdateStatisticsMode = 'fullscan'
  begin
   update statistics testindex(ST_testindex__a) with fullscan;
   update statistics testindex(ST_testindex__b) with fullscan;
  end
 else
  begin
   update statistics testindex(ST_testindex__a);
   update statistics testindex(ST_testindex__b);
  end;

 alter event session DBTest001_TrackLatches on server state = start;
 set statistics io on; SELECT a, b FROM testindex WHERE a = 5 and b = 'X'  option (recompile); set statistics io off;

 declare @x xml;

 select
  @x = cast(t.target_data as xml)
 from
  sys.dm_xe_sessions s join
  sys.dm_xe_session_targets t on t.event_session_address = s.address
 where
  s.name = N'DBTest001_TrackLatches';

 alter event session DBTest001_TrackLatches on server state = stop;

 select
  pi.file_id, pi.page_id, pa.index_id, pa.page_type_desc
 from
  @x.nodes('/RingBufferTarget/event[@name = "latch_acquired"][data[@name = "mode"][text = "SH"]]') t(n) cross apply
  (select t.n.value('(data[@name = "file_id"]/value)[1]', 'smallint'), t.n.value('(data[@name = "page_id"]/value)[1]', 'int')) pi(file_id, page_id) join
  sys.dm_db_database_page_allocations(db_id(), null, null, null, 'detailed') pa on pa.allocated_page_file_id = pi.file_id and pa.allocated_page_page_id = pi.page_id
 where
  pa.object_id = object_id('testindex');

end;
go

exec dbo.spGetPagesRead 'fullscan';
exec dbo.spGetPagesRead;
go

drop event session DBTest001_TrackLatches on server;
go

use master;
drop database DBTest001;
go

С fullscan
file_idpage_idindex_idpage_type_desc
12904INDEX_PAGE
1380974INDEX_PAGE
1412504INDEX_PAGE

Без fullscan
file_idpage_idindex_idpage_type_desc
12904INDEX_PAGE
1380974INDEX_PAGE
1380974INDEX_PAGE
1412504INDEX_PAGE
15 окт 18, 22:35    [21704591]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28141
invm,

Ну и зачем оно 2 раза читает нижнюю страничку индекса?
15 окт 18, 23:25    [21704611]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
SandalTree
Member

Откуда: Перехлёсток восьми батог
Сообщений: 28141
Mind,

Вот нашёл более приближённый пример того что индекс с большей селективностью лучше работает:
CREATE TABLE dbo.testindex2(ID INT IDENTITY (0,5), i INT, a CHAR(1))
GO
CREATE INDEX ix_testindex1 ON dbo.testindex2(id,i)
GO
CREATE INDEX ix_testindex2 ON dbo.testindex2(i,id)
GO
DECLARE @l INT = 2010;
DECLARE @m INT = @l;
WHILE @m > 0
BEGIN
	INSERT INTO dbo.testindex2(i,a)
	SELECT TOP (@m) @l-@m, 'a' FROM sys.messages
	SET @m -= 5;
END
GO
SET STATISTICS IO ON
GO
SELECT * FROM dbo.testindex2 WITH (INDEX = ix_testindex1)
WHERE i BETWEEN 0 AND 1000 AND id BETWEEN 0 AND 1000
OPTION (RECOMPILE)
GO
SELECT * FROM dbo.testindex2 WITH (INDEX = ix_testindex2)
WHERE i BETWEEN 0 AND 1000 AND id BETWEEN 0 AND 1000
OPTION (RECOMPILE)
GO
SET STATISTICS IO OFF
автор
Table 'testindex2'. Scan count 1, logical reads 204, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'testindex2'. Scan count 1, logical reads 1035, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Распределение данных надумано и не реалистично, но это просто пример селективности.
В реальной жизни скорее всего могут быть более серьёзные случаи.
16 окт 18, 03:36    [21704670]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36965
SandalTree,

Ясен пень, что здесь будут разные seek-предикаты, в отличие от запроса с равенством.
16 окт 18, 10:18    [21704813]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
invm
Member

Откуда: Москва
Сообщений: 9344
SandalTree
Ну и зачем оно 2 раза читает нижнюю страничку индекса?
Нижнюю? Уверены? :)
А зачем - это вам объяснять. Это же ваша теория "О влиянии неучтенных значений в статистике на количество чтений во время выполнения запроса при идентичных планах выполнения".
SandalTree
Вот нашёл более приближённый пример того что индекс с большей селективностью лучше работает
Лед тронулся.
Не хотите переформулировать ваше "общее правило"?
16 окт 18, 11:11    [21704897]     Ответить | Цитировать Сообщить модератору
 Re: Перекрестные индексы. Как лучше?  [new]
msLex
Member

Откуда:
Сообщений: 8065
SandalTree
Mind,

Вот нашёл более приближённый пример того что индекс с большей селективностью лучше работает:
CREATE TABLE dbo.testindex2(ID INT IDENTITY (0,5), i INT, a CHAR(1))
GO
CREATE INDEX ix_testindex1 ON dbo.testindex2(id,i)
GO
CREATE INDEX ix_testindex2 ON dbo.testindex2(i,id)
GO
DECLARE @l INT = 2010;
DECLARE @m INT = @l;
WHILE @m > 0
BEGIN
	INSERT INTO dbo.testindex2(i,a)
	SELECT TOP (@m) @l-@m, 'a' FROM sys.messages
	SET @m -= 5;
END
GO
SET STATISTICS IO ON
GO
SELECT * FROM dbo.testindex2 WITH (INDEX = ix_testindex1)
WHERE i BETWEEN 0 AND 1000 AND id BETWEEN 0 AND 1000
OPTION (RECOMPILE)
GO
SELECT * FROM dbo.testindex2 WITH (INDEX = ix_testindex2)
WHERE i BETWEEN 0 AND 1000 AND id BETWEEN 0 AND 1000
OPTION (RECOMPILE)
GO
SET STATISTICS IO OFF

автор
Table 'testindex2'. Scan count 1, logical reads 204, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'testindex2'. Scan count 1, logical reads 1035, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Распределение данных надумано и не реалистично, но это просто пример селективности.
В реальной жизни скорее всего могут быть более серьёзные случаи.



Ваше различие в чтениях вызвано не селективностью индекса, а количеством записей, попадающим в диапазон between по одному и по второму полю


вот вам чуть измененных скрипт, в котором я просто "размазал" значение поля i, домножив его на 100, но не менял селективность ни одного из полей, и получил противоположный вашему результат


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

CREATE TABLE dbo.testindex2(ID INT IDENTITY (0,5), i INT, a CHAR(1))
GO
DECLARE @l INT = 2010;
DECLARE @m INT = @l;
WHILE @m > 0
BEGIN
	INSERT INTO dbo.testindex2(i,a)
	SELECT TOP (@m) @l-@m * 100, 'a' FROM sys.messages
	SET @m -= 5;
END
GO
CREATE INDEX ix_testindex1 ON dbo.testindex2(id,i)
GO
CREATE INDEX ix_testindex2 ON dbo.testindex2(i,id)
GO
SET STATISTICS IO ON
GO
SELECT * FROM dbo.testindex2 WITH (INDEX = ix_testindex1)
WHERE i BETWEEN 1 AND 100000 AND id BETWEEN 1 AND 100000
OPTION (RECOMPILE)
GO
SELECT * FROM dbo.testindex2 WITH (INDEX = ix_testindex2)
WHERE i BETWEEN 1 AND 100000 AND id BETWEEN 1 AND 100000
OPTION (RECOMPILE)
GO
SET STATISTICS IO OFF



STATISTICS IO
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'testindex2'. Scan count 1, logical reads 58, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(0 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'testindex2'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
16 окт 18, 13:58    [21705221]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить