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

Откуда:
Сообщений: 52
Привет!

Есть таблица Table. Упрощенно она выглядит так:

ID | Klient | Hz

По ней построен кластерный индекс по ID и индекс по полям Klient, ID.

Делаю запрос вида:

SELECT
T1.ID
FROM Table T1
WHERE T1.Klient = @P1

Происходит Index Seek. Все нормально. Теперь я хочу получить получить дополнительное поле:

SELECT
T1.ID,
T1.Hz
FROM Table T1
WHERE T1.Klient = @P1

в результате получается Clustered Index Scan. Я не особо силен в этом всем и думал, что SQL сервер сделает какой-то Index Seek, отберет ID, а потом уже "точечно" по нужным ID по кластерному индексу выберет поле Hz.

Объясните, пожалуйста, так и должно быть и мое предположение было не верное?
3 окт 13, 10:52    [14916725]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Ennor Tiegael
Member

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

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

Сгенерите данных, чтоб таблица хотя бы сотню страниц занимала, план должен измениться.
3 окт 13, 10:58    [14916767]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Glory
Member

Откуда:
Сообщений: 104751
Raskolnikov
в результате получается Clustered Index Scan.

И вы можете предоставить оба плана выполнения ?
3 окт 13, 11:00    [14916784]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Raskolnikov
Привет!

Есть таблица Table. Упрощенно она выглядит так:

ID | Klient | Hz

По ней построен кластерный индекс по ID и индекс по полям Klient, ID.

Делаю запрос вида:

SELECT
T1.ID
FROM Table T1
WHERE T1.Klient = @P1

Происходит Index Seek. Все нормально. Теперь я хочу получить получить дополнительное поле:

SELECT
T1.ID,
T1.Hz
FROM Table T1
WHERE T1.Klient = @P1

в результате получается Clustered Index Scan. Я не особо силен в этом всем и думал, что SQL сервер сделает какой-то Index Seek, отберет ID, а потом уже "точечно" по нужным ID по кластерному индексу выберет поле Hz.

Объясните, пожалуйста, так и должно быть и мое предположение было не верное?

Добавьте в некластерный индекс поле ID через INCLUDE и будет вам IndexSeek
3 окт 13, 11:02    [14916799]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Сергей Викт.
Member

Откуда: Москва
Сообщений: 888
Сергей Викт.,

прошу прощения, не ID, а Hz
3 окт 13, 11:02    [14916801]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Гость333
Member

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

А какова селективность условия T1.Klient = @P1?
3 окт 13, 11:28    [14917016]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Raskolnikov
Member

Откуда:
Сообщений: 52
Ennor Tiegael, я нагенерировал в таблице 11132 записи, количество страни 656.

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

Сергей Викт., изменить индекс так, как вы предлагаете не представляется возможным. Это БД системы 1С. И изменения в структуре базы нужно делать из-под 1С. А сделать из-под 1С то, что предлагаете вы нельзя.

Гость333, селективность таблицы по этому условию ~0,44
3 окт 13, 11:54    [14917242]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Glory
Member

Откуда:
Сообщений: 104751
Так у вас там 2 индекса. А не один, как вы уверяете

Сообщение было отредактировано: 3 окт 13, 11:55
3 окт 13, 11:55    [14917253]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Raskolnikov
Member

Откуда:
Сообщений: 52
Glory, индексов на самом деле больше 1. Я этого не указал, т.к. подумал, что к сути дела это отношения особо не имеет, т.к. индексы есть совсем по другим полям.
3 окт 13, 12:04    [14917334]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
wqhdquiwdhqi
Guest
Raskolnikov,

Fld3243_RRef - не является ключем таблицы Doc1333, кроме того, вы тянете еще одно поле в запросе, потому ClusteredIndex Scan. По чем он искать-то будет?

Во втором запросе используется поиск по другому индексу, по полю Fld3243_RRef и так как выбирается только оно. Оптимизатор не использует этот индекс в первом запросе, потому что считает что lookup'ы по RID дорого.
3 окт 13, 12:05    [14917349]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
wqhdquiwdhqi
Guest
wqhdquiwdhqi,
поправлюсь

Очевидно IDRRef - кластерный ключ. Значит в листовых блоках некластерных индексов он содержится.

1 запрос: идет clustered index scan, поскольку в where указано не ключевое поле.
2 запрос: идет поиск по некластерному индексу по полю Fld3243_RRRef, а так как в листовых блоках такого индекса вместо RID содержится clustered key и вы его выбираете в запросе
, то lookup по RID не нужен и оптимизатор считает поиск очень эффективным.
3 окт 13, 12:15    [14917445]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Raskolnikov
селективность таблицы по этому условию ~0,44

Тогда clustered index scan получается гораздо выгоднее, чем index seek+key lookup.
Index seek+key lookup обычно выгоден, когда селективность не превышает нескольких процентов.
3 окт 13, 12:18    [14917470]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
ziktuw
Member

Откуда:
Сообщений: 3552
Raskolnikov
индекс по полям Klient, ID


Здесь нет опечатки? Именно ID в этом индексе?
3 окт 13, 12:24    [14917520]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Glory
Member

Откуда:
Сообщений: 104751
Raskolnikov
Glory, индексов на самом деле больше 1. Я этого не указал, т.к. подумал, что к сути дела это отношения особо не имеет, т.к. индексы есть совсем по другим полям.

Очень даже имеет.
Даже из плана видно используется PrimaryKey. Который неизвестно как создан.
Почему вы считаете, что сервер должен выбирать только между двумя индексами, если индексов гораздо больше ?
3 окт 13, 12:29    [14917576]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
zxc1257
Member

Откуда:
Сообщений: 71
+ автор, я это хотел сказать

drop table tt1;
go
create table tt1(id int primary key clustered, id_tt2 int, data int);
go
;with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
rt(n) as (select row_number() over (order by (select 0)) from l4 t1, l4 t2)
insert into tt1(id, id_tt2, data)
select top(1000000) n, n % 3, n * rand(checksum(newid()))
from rt
go
create nonclustered index idx_id_tt2 on tt1(id_tt2);
go
-- распределение id_tt2 таково что значений 1 много, потому, оптимизатор выбирает сканирование clustered PK_IDX
-- вместо idx_id_tt2 + keylookup
select id, data
from tt1
where id_tt2 = 1;

-- а здесь выбирает поиск idx_id_tt2
-- т. к. во первых в where id_tt2 = 1, а во вторых все необходимые данные содержатся в листах
-- индекса idx_tt2 (nonclustered key + clustered key вместо RID)
select id
from tt1
where id_tt2 = 1;



планы в аттаче

К сообщению приложен файл (plplan.sqlplan - 15Kb) cкачать
3 окт 13, 12:43    [14917702]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Raskolnikov
Member

Откуда:
Сообщений: 52
wqhdquiwdhqi, спасибо! Вроде понятно стало. Только непонятно это:
wqhdquiwdhqi
то lookup по RID не нужен и оптимизатор считает поиск очень эффективным

lookup по RID или key lookup? Суть ответа (для меня) от этого особо не изменится, просто для себя хочу уяснить.

Гость333, ваш ответ - это, наверное, ответ на вопрос этой темы :) . Спасибо!

Глеб, опечатки нету. Индексы такие, как я в первом посте описал.

Glory, в таблице есть первичный ключ по полю ID (ещё раз повторю, что ID - это я для простоты обозвал, физически в базе поле называется _IDRRef.
Glory
Даже из плана видно используется PrimaryKey. Который неизвестно как создан.

Это используется кластерный индекс (который строится по ID), который называется так же, как называется первичный ключ.
Картинка с другого сайта.
Glory
Почему вы считаете, что сервер должен выбирать только между двумя индексами, если индексов гораздо больше ?

Я это по плану запроса вижу.

zxc1257,
zxc1257
автор, я это хотел сказать

Этовы выше под ником wqhdquiwdhqi отвечали? Если да, то я понял, спасибо! Если это были не вы - то спасибо за ответ: мне это тоже было полезно, наряду с другими ответами в этой теме :) .

В общем, всем большое спасибо! Вроде стало понятно. Если ещё кто-то что-то захочет добавить - я с удовольствием почитаю.
3 окт 13, 14:29    [14918610]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Glory
Member

Откуда:
Сообщений: 104751
Raskolnikov
Glory
Почему вы считаете, что сервер должен выбирать только между двумя индексами, если индексов гораздо больше ?

Я это по плану запроса вижу.

Еще раз вопрос. Почему сервер _должен_ выбирать тот индекс, который нравится вам ?
Вы можете принудительно указать использование индекса и сравнить количество чтений. Скорее всего окажется, что план сервера все же выгоднее вашего.
3 окт 13, 14:41    [14918727]     Ответить | Цитировать Сообщить модератору
 Re: Растолкуйте, пожалуйста, поведение SQL сервера  [new]
Raskolnikov
Member

Откуда:
Сообщений: 52
Glory,
Glory
Почему сервер _должен_ выбирать тот индекс, который нравится вам ?

В этом у меня и был вопрос :) , ответ на который вот:
Гость333
Тогда clustered index scan получается гораздо выгоднее, чем index seek+key lookup.
Index seek+key lookup обычно выгоден, когда селективность не превышает нескольких процентов.


Glory
Вы можете принудительно указать использование индекса и сравнить количество чтений. Скорее всего окажется, что план сервера все же выгоднее вашего.

Нет, я не хочу принудительно. Я просто хотел для себя уяснить почему сервер отрабатывает именно так.
4 окт 13, 16:21    [14925139]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить