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

Откуда:
Сообщений: 59
Здравствуйте!

Версия сервера:
Microsoft SQL Server 2016 (SP1-GDR) (KB3207512) - 13.0.4199.0 (X64) Nov 18 2016 15:56:54 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: ) (Hypervisor)

Есть таблица вида:
CREATE TABLE [aptweb].[fzh_check1](
	[id_chk] [int] IDENTITY(1,1) NOT NULL,
	[date_create] [datetime] NOT NULL,
	[date_update] [datetime] NOT NULL,
 CONSTRAINT [PK_fzh_check1] PRIMARY KEY NONCLUSTERED 
(
	[id_chk] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


На таблице построен индекс


CREATE NONCLUSTERED INDEX [ix3] ON [aptweb].[fzh_check1]
(
	[date_update] ASC
)
INCLUDE ( [id_chk]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


При увеличении в таблице кол-ва строк до , примерно, 180-200 тысяч, запрос вида :

declare @b int, @e int;
declare @db datetime, @de datetime;

select @b = min(id_chk), @e = max(id_chk) from aptweb.fzh_check fc with (nolock)
where fc.date_update >=@db and fc.date_update < @de


перестает использовать существующий индекс.



Но при создании индекса, вида
CREATE NONCLUSTERED INDEX [idxTest] ON [aptweb].[FZH_Check]
(
	[id_chk] ASC
)
INCLUDE ([date_update]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, 
 ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


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

Картинки с планами приложены,
подскажите - почему перестает работать существующий индекс и происходит уточняющий запрос по RID в одном случае,
но используется idxtest в другом ?

Как влияет кол-во записей в таблице на использование того или иного индекса?
Буду благодарен за краткий ликбез и уточнения в документации. Приложение унаследовано, и есть возможность "оптимизации" только на уровне БД. создании индекса idxTest запрос всегда работает по нему, не важно сколько записей в таблице.

Запрос при ix3 работает то же достаточно быстро, но, периодически, подвисает, т.к. видимо происходит вытеснение из кэша.

К сообщению приложен файл. Размер - 118Kb
18 дек 16, 16:50    [20018130]     Ответить | Цитировать Сообщить модератору
 Re: Уточняющий RID и отсуствие выборки по индексу  [new]
iljy
Member

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

потому что оптимизатор считает, что при небольшом количестве записей выгодно выбрать часть записей по дате, а потом в них найти максимальное id. Если же записей много, то по дате их выберется слишком много, поэтому выгоднее взять индекс по id, и идти по нему до тех пор, пока не найдется удовлетворяющая условиям дата. Если в индексе по id даты нет - ее приходится поднимать ридлукапом, если же есть - то все тем более хорошо.
18 дек 16, 19:02    [20018426]     Ответить | Цитировать Сообщить модератору
 Re: Уточняющий RID и отсуствие выборки по индексу  [new]
o-o
Guest
ваши индексы созданы на разных таблицах,
что в коде, что на картинке:
CREATE NONCLUSTERED INDEX [ix3] ON [aptweb].[fzh_check1]
CREATE NONCLUSTERED INDEX [idxTest] ON [aptweb].[FZH_Check]

и запросы тоже к разным таблицам: fzh_check1 <> FZH_Check
поэтому непонятно, что вы сравниваете
18 дек 16, 19:48    [20018527]     Ответить | Цитировать Сообщить модератору
 Re: Уточняющий RID и отсуствие выборки по индексу  [new]
aleks2
Guest
o-o
ваши индексы созданы на разных таблицах,
что в коде, что на картинке:
CREATE NONCLUSTERED INDEX [ix3] ON [aptweb].[fzh_check1]
CREATE NONCLUSTERED INDEX [idxTest] ON [aptweb].[FZH_Check]

и запросы тоже к разным таблицам: fzh_check1 <> FZH_Check
поэтому непонятно, что вы сравниваете


Зачем же так, мордой то об стол?
19 дек 16, 05:52    [20019621]     Ответить | Цитировать Сообщить модератору
 Re: Уточняющий RID и отсуствие выборки по индексу  [new]
s2011
Member

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

Внесу ясности - обе таблицы в одной и той же БД, таблица fzh_check1 - копия основной таблицы, над которой делались опыты.
Структуры обеих таблиц идентичны. Просто из основной нельзя удалять записи.

Поведение проверено на ПК (система тиражируемая),
собственно, новые объекты, пока не наберут "критического" кол-ва записей проблем не вызывают. Именно поэтому сделано копия тбл и чистилась , что бы найти некий порог, когда начинается просадка производительности.
19 дек 16, 08:06    [20019693]     Ответить | Цитировать Сообщить модератору
 Re: Уточняющий RID и отсуствие выборки по индексу  [new]
s2011
Member

Откуда:
Сообщений: 59
Поведение проверено на несколькких ПК (система тиражируемая),
19 дек 16, 08:22    [20019716]     Ответить | Цитировать Сообщить модератору
 Re: Уточняющий RID и отсуствие выборки по индексу  [new]
s2011
Member

Откуда:
Сообщений: 59
iljy
s2011,

Если же записей много, то по дате их выберется слишком много, поэтому выгоднее взять индекс по id,.


Группировка id по датам показывает, что распределение по дням примерно одинаковое - 450-550 записей в день. Не совсем понял, как оптимизатор считает , что таких записей стало слишком много
19 дек 16, 08:32    [20019736]     Ответить | Цитировать Сообщить модератору
 Re: Уточняющий RID и отсуствие выборки по индексу  [new]
Mr. X
Guest
s2011,

Если под PK положить кластерный индекс, глядишь и загадок будет меньше.
19 дек 16, 08:54    [20019807]     Ответить | Цитировать Сообщить модератору
 Re: Уточняющий RID и отсуствие выборки по индексу  [new]
s2011
Member

Откуда:
Сообщений: 59
Mr. X,

При создании PK как кластерного оптимизатор использует idxTest
19 дек 16, 09:33    [20019975]     Ответить | Цитировать Сообщить модератору
 Re: Уточняющий RID и отсуствие выборки по индексу  [new]
iljy
Member

Откуда:
Сообщений: 8711
s2011
iljy
s2011,

Если же записей много, то по дате их выберется слишком много, поэтому выгоднее взять индекс по id,.


Группировка id по датам показывает, что распределение по дням примерно одинаковое - 450-550 записей в день. Не совсем понял, как оптимизатор считает , что таких записей стало слишком много


А если подумать? Сколько записей ему придется считать по условию where fc.date_update >=@db and fc.date_update < @de? Особенно учитывая, что это переменные? И сколько при поиске по индексу?

Я никак не пойму, вопрос-то в чем? Это спортивный интерес или какая-то практическая цель преследуется?



s2011
Mr. X,

При создании PK как кластерного оптимизатор использует idxTest


Естественно использует, поля все нужные есть и он тоньше. Но иметь в таблице кластерный индекс все равно полезно.
19 дек 16, 09:41    [20020020]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить