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

Откуда:
Сообщений: 38
Доброе время суток.

Система:
Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
	May 14 2014 18:34:29 
	Copyright (c) Microsoft Corporation
	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)


Исходные данные :
CREATE TABLE [dbo].[Data](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[DataDateTime] [datetime2](7) NOT NULL,
	[Field1] [smallint] NULL,
	[Field2] [tinyint] NOT NULL,
	[Field3] [tinyint] NULL,
	[Field4] [real] NULL,
	[Field5] [bit] NOT NULL,
	[Field6] [bit] NOT NULL DEFAULT ((0)),
	[Field7] [tinyint] NULL,
	[FullTextSearchColumn] [varchar](20) NOT NULL,
	[FullTextSearchColumnReverse] [varchar](20) NOT NULL,
	[Field8] [nvarchar](1000) NULL,
 CONSTRAINT [PK_DATA] PRIMARY KEY NONCLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [fgDataPrimaryIndex]
)


CREATE CLUSTERED INDEX [CIX_Data_DataDateTime] ON [dbo].[Data]
(
	[DataDateTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)
on [fgData]

CREATE NONCLUSTERED INDEX [IX_Data_Field3] ON [dbo].[Data]
(
	[Field3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100)



Число записей ~2 млрд. Таблица секционирована по полю DataDateTime по дням, среднее число записей в секции ~5 млн - то число записей которое попадает в систему за день (вставка производится BULK INSERT).

скрипт:
select max(Field3) from [dbo].[Data] with(nolock)


Вопрос :
Скрипт выполнялся очень долго(>20мин), не дождавшись его выполнения, я его остановил. Прикладываю предполагаемый план выполнения запроса. Подскажите, в чем может быть причина столь необычно долгово выполнения запроса?


К сообщению приложен файл (StrangeMax.sqlplan - 10Kb) cкачать
19 янв 15, 16:58    [17139203]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
_djХомяГ
Guest
Запрос во вложении не соотв описанию приведенному в сообщении выше
select max(CameraID) from Traffic]

ЗЫ происходит сканирование кластерного индекса на большом количесвте Esimated Number Rows
19 янв 15, 17:06    [17139274]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
rere1234
Member

Откуда:
Сообщений: 38
_djХомяГ,

В описании указано другое имя чтобы не отвлекать лишней информацией. А вот почему происходит сканирование я никак понять не могу.
19 янв 15, 17:15    [17139371]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37050
А если так?

select max(Field3) from [dbo].[Data] with ( nolock, index = IX_Data_Field3 )
19 янв 15, 17:19    [17139396]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
_djХомяГ
Guest
Попробуйте указать в запросе хинт на нужный индекс
19 янв 15, 17:20    [17139406]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
rere1234
Member

Откуда:
Сообщений: 38
Гавриленко Сергей Алексеевич,

Происходит сканирование некластеризованного индекса

К сообщению приложен файл (StrangeMaxWithIndex.sqlplan - 10Kb) cкачать
19 янв 15, 18:01    [17139427]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
rere1234
Member

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

Хотел еше уточнить что некластеризованный индекс IX_Data_Field3 также секцеонирован, а в ежедневном плане обслуживания настроен ребилд только последней партиции для CIX_Data_DataDateTime и IX_Data_Field3. Обновление статистики по плану обслуживания не производится.
19 янв 15, 18:19    [17139488]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
rere1234
rere1234,

Хотел еше уточнить что некластеризованный индекс IX_Data_Field3 также секцеонирован, а в ежедневном плане обслуживания настроен ребилд только последней партиции для CIX_Data_DataDateTime и IX_Data_Field3. Обновление статистики по плану обслуживания не производится.
Ну так ничего удивительного. У вас по сути индекс физически построен не на Field3, а на DataDateTime + Field3. Соответственно поиск по индексу для определения максимального значения невозможен. Только скан.

А скан кластерного сервер решает использовать потому что дешевле чем скан некластерного. Видать он каким то образом получился меньше чем некластерный. Возможно некластерный так "раздуло" из-за внутренней фрагментации, а может просто статистики устарели.
20 янв 15, 01:23    [17140770]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31420
Mind
Ну так ничего удивительного. У вас по сути индекс физически построен не на Field3, а на DataDateTime + Field3. Соответственно поиск по индексу для определения максимального значения невозможен. Только скан.

А скан кластерного сервер решает использовать потому что дешевле чем скан некластерного. Видать он каким то образом получился меньше чем некластерный. Возможно некластерный так "раздуло" из-за внутренней фрагментации, а может просто статистики устарели.
Непонятно, почему сервер так делает.

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

А внутри секции секционированный индекс всё таки строится не как "DataDateTime + Field3", а просто по Field3, правильно? Так что можно искать?
20 янв 15, 01:43    [17140790]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
alexeyvg
Mind
Ну так ничего удивительного. У вас по сути индекс физически построен не на Field3, а на DataDateTime + Field3. Соответственно поиск по индексу для определения максимального значения невозможен. Только скан.

А скан кластерного сервер решает использовать потому что дешевле чем скан некластерного. Видать он каким то образом получился меньше чем некластерный. Возможно некластерный так "раздуло" из-за внутренней фрагментации, а может просто статистики устарели.
Непонятно, почему сервер так делает.

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

Ну а вот так сервер будет выполнять поиск, при условии что индекс партиционирован?
CREATE NONCLUSTERED INDEX [IX_id] ON [dbo].[Data](SomeVerySelectiveId)
select * from [dbo].[Data] with(nolock) where SomeVerySelectiveId = 12345
Нет не будет. Хотя тоже казалось бы что сложного.

Mind
А внутри секции секционированный индекс всё таки строится не как "DataDateTime + Field3", а просто по Field3, правильно? Так что можно искать?

Да даже и вот так можно сделать весьма оптимальный поиск (индекс не партиционирован):
CREATE NONCLUSTERED INDEX [IX_id] ON [dbo].[Data](NonSelectiveId, SomeVerySelectiveId)
select * from [dbo].[Data] with(nolock) where SomeVerySelectiveId = 12345


В итоге все сводится к вот этому:

Status : Closed as Won't Fix
https://www.sql.ru/forum/902076/oracle-s-index-skip-scan-in-sql-server
https://connect.microsoft.com/SQLServer/feedback/details/695044/implement-index-skip-scan
20 янв 15, 02:58    [17140828]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31420
Mind
Да даже и вот так можно сделать весьма оптимальный поиск (индекс не партиционирован):
Теоретически да, но тут не всегда это оптимально, усложняется алгоритм построения плана.

Всё таки партицированные индексы - ярко выраженный случай. Могли бы и сделать.

А в данном случае, о проблеме ТС, согласен, все поиски нужно делать с учётом секционирования.
20 янв 15, 09:45    [17141248]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
хмхмхм
Guest
Mind
В итоге все сводится к вот этому:

Status : Closed as Won't Fix
https://www.sql.ru/forum/902076/oracle-s-index-skip-scan-in-sql-server
https://connect.microsoft.com/SQLServer/feedback/details/695044/implement-index-skip-scan


Заранее извиняюсь за глупый вопрос, но в вашем примере на коннекте почему не испольщовать ключ по полю Firstname и Gender как включаемое поле? Gender ведь неселективен. Будет и index seek и поиск по запросам, где встречаются условия по двум этим полям.
20 янв 15, 10:13    [17141415]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31420
хмхмхм
Mind
В итоге все сводится к вот этому:

Status : Closed as Won't Fix
https://www.sql.ru/forum/902076/oracle-s-index-skip-scan-in-sql-server
https://connect.microsoft.com/SQLServer/feedback/details/695044/implement-index-skip-scan


Заранее извиняюсь за глупый вопрос, но в вашем примере на коннекте почему не испольщовать ключ по полю Firstname и Gender как включаемое поле? Gender ведь неселективен. Будет и index seek и поиск по запросам, где встречаются условия по двум этим полям.
Смысл в том, что бы не делать 2 индекса там, где достаточно одного, и при этом обеспечить эффективный поиск по сочетанию Gender и Firstname. Теоретически это сделать возможно, о чём и сообщение на коннекте.

Хотя в принципе согалсен, по полю Firstname, + Gender как включаемое поле тоже будет хорошо, раз Gender имеет только 2 значения.
20 янв 15, 10:41    [17141545]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
rere1234
Member

Откуда:
Сообщений: 38
Посмотрел как хранится секцеонированный индекс с помощью команд DBCC IND и DBCC PAGE
+
FileId PageId Row Level ChildFileId ChildPageId Field3 (key) DataDateTime (key) UNIQUIFIER (key) KeyHashValue Row Size
9 2017459 0 2 9 2012550 NULL NULL NULL NULL 17
9 2017459 1 2 9 2017460 1 2013-12-10 08:14:24.0000000 16 NULL 28
9 2017459 2 2 9 2025849 11 2013-12-10 13:32:06.0000000 39 NULL 28
9 2017459 3 2 9 2025853 16 2013-12-10 15:18:39.0000000 4 NULL 28
9 2017459 4 2 9 2031017 21 2013-12-10 14:51:09.0000000 18 NULL 28
9 2017459 5 2 9 2031021 25 2013-12-10 00:11:16.0000000 2 NULL 28
9 2017459 6 2 9 12684224 30 2013-12-10 10:10:44.0000000 32 NULL 28
9 2017459 7 2 9 12684225 33 2013-12-10 08:44:20.0000000 22 NULL 28
9 2017459 8 2 9 12777328 36 2013-12-10 07:57:00.0000000 30 NULL 28
9 2017459 9 2 9 12777329 43 2013-12-10 16:52:33.0000000 4 NULL 28
9 2017459 10 2 9 12270080 55 2013-12-10 11:01:01.0000000 55 NULL 28
9 2017459 11 2 9 12775776 60 2013-12-10 20:11:56.0000000 16 NULL 28
9 2017459 12 2 9 12775777 67 2013-12-10 14:31:52.0000000 52 NULL 28
9 2017459 13 2 9 12852480 68 2013-12-10 15:13:22.0000000 36 NULL 28
9 2017459 14 2 9 12852481 73 2013-12-10 18:43:10.0000000 52 NULL 28
9 2017459 15 2 9 12854920 75 2013-12-10 20:21:11.0000000 24 NULL 28
9 2017459 16 2 9 12854921 79 2013-12-10 18:18:35.0000000 2 NULL 28
9 2017459 17 2 9 12857200 84 2013-12-10 18:20:10.0000000 55 NULL 28
9 2017459 18 2 9 12857201 87 2013-12-10 21:18:06.0000000 29 NULL 28
9 2017459 19 2 9 12859440 95 2013-12-10 00:01:03.0000000 20 NULL 28
9 2017459 20 2 9 12859441 96 2013-12-10 20:04:22.0000000 32 NULL 28
9 2017459 21 2 9 12872816 103 2013-12-10 22:30:14.0000000 2 NULL 28
9 2017459 22 2 9 12872817 109 2013-12-10 15:31:45.0000000 52 NULL 28
9 2017459 23 2 9 12884520 115 2013-12-10 19:29:55.0000000 51 NULL 28
9 2017459 24 2 9 12884521 120 2013-12-10 08:28:45.0000000 28 NULL 28
9 2017459 25 2 9 12941016 125 2013-12-10 09:10:06.0000000 9 NULL 28
9 2017459 26 2 9 12941017 128 2013-12-10 19:49:03.0000000 31 NULL 28
9 2017459 27 2 9 12966200 132 2013-12-10 16:14:14.0000000 3 NULL 28
9 2017459 28 2 9 12966201 135 2013-12-10 17:26:26.0000000 49 NULL 28
9 2017459 29 2 9 12976408 139 2013-12-10 21:58:29.0000000 14 NULL 28
9 2017459 30 2 9 12977656 151 2013-12-10 07:31:44.0000000 19 NULL 28
9 2017459 31 2 9 12977657 153 2013-12-10 10:52:32.0000000 24 NULL 28
9 2017459 32 2 9 13013616 155 2013-12-10 17:51:08.0000000 69 NULL 28
9 2017459 33 2 9 13013617 159 2013-12-10 12:37:58.0000000 44 NULL 28
9 2017459 34 2 9 13043368 167 2013-12-10 03:23:21.0000000 2 NULL 28
9 2017459 35 2 9 13043369 169 2013-12-10 04:32:17.0000000 0 NULL 20
9 2017459 36 2 9 13054648 171 2013-12-10 14:57:00.0000000 35 NULL 28
9 2017459 37 2 9 13054649 173 2013-12-10 14:51:14.0000000 54 NULL 28
9 2017459 38 2 9 13147392 176 2013-12-10 07:51:43.0000000 7 NULL 28
9 2017459 39 2 9 13147393 179 2013-12-10 13:17:21.0000000 8 NULL 28
9 2017459 40 2 9 13239296 183 2013-12-10 09:50:00.0000000 0 NULL 20
9 2017459 41 2 9 13239297 191 2013-12-10 12:24:51.0000000 1 NULL 28
9 2017459 42 2 9 13263280 196 2013-12-10 17:24:13.0000000 7 NULL 28
9 2017459 43 2 9 13263281 197 2013-12-10 21:52:35.0000000 26 NULL 28
9 2017459 44 2 9 13286064 206 2013-12-10 08:39:12.0000000 55 NULL 28
9 2017459 45 2 9 13286065 214 2013-12-10 11:27:13.0000000 37 NULL 28
9 2017459 46 2 9 12992744 219 2013-12-10 20:19:30.0000000 14 NULL 28
9 2017459 47 2 9 13366272 227 2013-12-10 19:13:10.0000000 23 NULL 28
9 2017459 48 2 9 13366273 229 2013-12-10 20:35:15.0000000 24 NULL 28
9 2017459 49 2 9 13592872 233 2013-12-10 09:35:59.0000000 28 NULL 28
9 2017459 50 2 9 13592873 235 2013-12-10 17:16:23.0000000 74 NULL 28
9 2017459 51 2 9 13594984 237 2013-12-10 20:19:12.0000000 25 NULL 28
9 2017459 52 2 9 13594985 241 2013-12-10 10:32:40.0000000 54 NULL 28
9 2017459 53 2 9 13611112 246 2013-12-10 17:58:33.0000000 33 NULL 28
9 2017459 54 2 9 13611113 248 2013-12-10 21:38:40.0000000 35 NULL 28
9 2017459 55 2 9 13613504 256 2013-12-10 18:53:17.0000000 54 NULL 28
9 2017459 56 2 9 13613505 260 2013-12-10 15:16:50.0000000 43 NULL 28
9 2017459 57 2 9 13615872 263 2013-12-10 16:12:23.0000000 1 NULL 28
9 2017459 58 2 9 13615873 266 2013-12-10 13:09:54.0000000 9 NULL 28
9 2017459 59 2 9 13618048 273 2013-12-10 20:48:48.0000000 22 NULL 28
9 2017459 60 2 9 13618049 281 2013-12-10 08:37:15.0000000 22 NULL 28
9 2017459 61 2 9 13622704 283 2013-12-10 00:55:09.0000000 2 NULL 28
9 2017459 62 2 9 13622705 285 2013-12-10 08:13:57.0000000 25 NULL 28


те в индексе два ключа Field3, DataDateTime, причем Field3 идет вначале

Я правильно понимаю что сканирование используется именно из-за того что индекс секционирован, и это следствие каких то недоработок в Sql Server?
20 янв 15, 11:30    [17141853]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
rere1234
Member

Откуда:
Сообщений: 38
Подсмотрел на https://connect.microsoft.com/SQLServer/feedback/details/240968/partition-table-using-min-max-functions-and-top-n-index-selection-and-performance обходное решение
select top (1) o.*
from (select partition_number from sys.partitions where object_id = object_id('dbo.Data') 
and index_id = 1) as p(a) cross apply
     (select max(Field3) m
        from Data t
        where $partition.pfSplitToDate(t.DataDateTime) = p.a) as o
order by o.m desc;
20 янв 15, 17:12    [17144095]     Ответить | Цитировать Сообщить модератору
 Re: Долгая сортировка при наличии индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
rere1234
Посмотрел как хранится секцеонированный индекс с помощью команд DBCC IND и DBCC PAGE
те в индексе два ключа Field3, DataDateTime, причем Field3 идет вначале
Это ни о чем не говорит. Вы посмотрели только одну ветку (B-tree), а у вас их там столько сколько секций. По сути там физически, вместо одного индекса - несколько и у каждого своя root page, поэтому сквозной поиск не возможен. Можно было бы сделать поиск по каждой секции, а потом объединить результаты, но в MS решили не заморачиваться.

rere1234
Подсмотрел на https://connect.microsoft.com/SQLServer/feedback/details/240968/partition-table-using-min-max-functions-and-top-n-index-selection-and-performance обходное решение
Прикольно, но все таки это костыль, хотелось бы чтобы сервер сам так делал.
20 янв 15, 20:54    [17145081]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить