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

Откуда: Питер
Сообщений: 1938
Есть высоконагруженная система

Есть задача перестроить индекс

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

На тестовом стенде построение индексов (план выполнения его построения) показывает сканирование существующего индекса (назовём его IX_1) по совпадению индексного поля и поиск в кластерном для каждой записи. Что собственно очень долго. Если дропнуть сперва тот индекс, по которому сканирование, то ситуация меняется в лучшую сторону. Индекс строится гораздо быстрее, сканируя для своего построения только кластерный.

Есть ли способ подсказать оптимизатору лучший план? А то разница по времени построения существенна: пол-часа, если не дропать IX_1, и 30 секунд, если IX_1 сперва дропнуть

Если на пальцах, то картина такова: IX_1 - index on T(A), IX_new - index on T(A) include (B)
2 сен 14, 11:37    [16522621]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Glory
Member

Откуда:
Сообщений: 104751
CREATE INDEX ... DROP_EXISTING = { ON | OFF }

Specifies that the named, preexisting clustered, or nonclustered is dropped and rebuilt. The default is OFF.

ON
The existing index is dropped and rebuilt. The index name specified must be the same as a currently existing index; however, the index definition can be modified. For example, you can specify different columns, sort order, partition scheme, or index options.

OFF
An error is displayed if the specified index name already exists.
2 сен 14, 11:39    [16522643]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
+минимальное протоколирование заюзать, если есть возможность...можно существенно сократить время
2 сен 14, 11:42    [16522660]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Shurgenz
Member

Откуда: Питер
Сообщений: 1938
DROP_EXISTING

задачу не решает в моем случае. Ну и вопрос то в общем то не в этом... а в хреновом плане. На кой ляд оптимизатор лазит в IX_1, тогда как менее накладно сразу прочитать из кластерного?
2 сен 14, 11:43    [16522663]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
dedjiwed
Guest
Shurgenz
DROP_EXISTING

задачу не решает в моем случае. Ну и вопрос то в общем то не в этом... а в хреновом плане. На кой ляд оптимизатор лазит в IX_1, тогда как менее накладно сразу прочитать из кластерного?


в качестве бреда может попробовать plan guide?
2 сен 14, 11:52    [16522705]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Crimean
Member

Откуда:
Сообщений: 13147
maxdop можно попробовать явно указать, если на сервере задушен паралелизЬм
но если серьезно задран кост, то все равно может быть грустно
2 сен 14, 11:54    [16522715]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
dedjiwed
Guest
Crimean
maxdop можно попробовать явно указать, если на сервере задушен паралелизЬм
но если серьезно задран кост, то все равно может быть грустно


вы нам что-то не договариваете. не воспроизводится ваша ситуация.

Microsoft SQL Server 2012 - 11.0.5058.0 (X64) 
	May 14 2014 18:34:29 
	Copyright (c) Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

create database www1;
go
use www1;
go

create table big_table(id int primary key clustered, a int, b 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),
l5(n) as (select 1 from l4 t1, l4 t2),
rt(n) as (select row_number() over (order by (select 0)) from l5 t1, l5 t2)
insert into big_table(id, a, b)
select top(1000000)
	n, n as a, n as b
from
	rt
go

create index idx__big_table__a on big_table(a);
go


set statistics xml on;
go

create index idx__big_table_b on big_table(a) include(b);
go

set statistics xml off;
go

select @@version
go


К сообщению приложен файл (1111111111111111111.xml - 5Kb) cкачать
2 сен 14, 12:09    [16522789]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4831
Вам нужно перестроить индекс быстрее или так, чтобы не мешал работе системы? Можно сделать медленнее, но так, чтобы всё работало.

Есть ONLINE = ON, но он работает на ENTERPRISE 2005 и выше

см msdn

ALTER INDEX [IX_Test] ON [dbo].[Test] REBUILD WITH (ONLINE = ON);
2 сен 14, 13:44    [16523372]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
dedjiwed
вы нам что-то не договариваете. не воспроизводится ваша ситуация.
+1

Shurgenz,
Выкладывайте ваш план с лукапом по кластерному индексу. Иначе - не верю.
Ну и версию сервера озвучьте заодно.
3 сен 14, 01:18    [16526557]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
NickAlex66
Member

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

Можете не дропать, а задизейблить IX_1. Если он в запросах хинтуется, то не стоит.
3 сен 14, 07:50    [16526745]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Shurgenz
Member

Откуда: Питер
Сообщений: 1938
Индекс выглядит так:

set statistics xml on;

CREATE NONCLUSTERED INDEX [IX_Ticket_TopTicketID_Include14]
ON [dbo].[Ticket] ([TopTicketID])
INCLUDE ([TicketID],
[TicketFirmID],
[AuthorFirmID],
[AuthorUserID],
[StaffGroupID],
[TicketStatusID],
[AddDate],[ChangeDate],
[Subject],
[EMailAnswer],
[EMail],
[Attachment],
[PersonID],
[Viewed],
[InternalTypeID],
[Attributes],
[IntTicketID],
[ServerID],
[TicketNumber],
[Message_v2])

set statistics xml off;


индекс, который сканируется при построении так:

CREATE NONCLUSTERED INDEX [IX_Ticket_TopTicketID] ON [dbo].[Ticket]
(
[TopTicketID] ASC
)

select @@version:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4263.0 (X64) Aug 23 2012 15:56:56 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (Hypervisor)

план выполнения приложил

на примере dedjiwed действительно не воспроизводится.... плохой из меня описатель

ЗЫ: прошу извинить за молчание длительное... работы другой навалилось

К сообщению приложен файл (План выполнения.xml - 40Kb) cкачать
12 сен 14, 10:56    [16567117]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
случайно заглянул
Guest
Если позволяет место и tempdb находится на SSD диске, то включите параметр SORT_IN_TEMPDB = ON. Скорость построения индекса может увеличиться.
12 сен 14, 11:09    [16567200]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
хмхмхм
Guest
Shurgenz
На тестовом стенде построение индексов (план выполнения его построения) показывает сканирование существующего индекса (назовём его IX_1) по совпадению индексного поля и поиск в кластерном для каждой записи.


Так все очень просто: дизейблите индекс IX_1 на время создания вашего индекса, затем его включаете.
Этот вариант не подходит, если индекс используется в хинтах.
12 сен 14, 11:14    [16567223]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Shurgenz
Member

Откуда: Питер
Сообщений: 1938
хмхмхм
Shurgenz
На тестовом стенде построение индексов (план выполнения его построения) показывает сканирование существующего индекса (назовём его IX_1) по совпадению индексного поля и поиск в кластерном для каждой записи.


Так все очень просто: дизейблите индекс IX_1 на время создания вашего индекса, затем его включаете.
Этот вариант не подходит, если индекс используется в хинтах.


Не годится

этот индекс на продакшене тоже активно используется

пробовал строить с ONLINE=ON, но прерывал, потому как количество заблокированных запросов начинало расти... Интересно, поправили ли поведения CREATE INDEX хотя бы в новых версия сиквела
12 сен 14, 11:33    [16567342]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Shurgenz
Member

Откуда: Питер
Сообщений: 1938
автор
затем его включаете.


кстати... просто его включаете это тоже индекс ребилд
12 сен 14, 11:35    [16567358]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
хмхмхм
Guest
Shurgenz
автор
затем его включаете.


кстати... просто его включаете это тоже индекс ребилд


Зато это избавит от сканирования при создании другого индекса.

Shurgenz
Не годится

этот индекс на продакшене тоже активно используется
пробовал строить с ONLINE=ON, но прерывал, потому как количество заблокированных запросов начинало расти...


Тогда надо запрашивать сервисное окно или что-то придумывать с переливкой данных в другую таблицу, создании индекса там, переименованием таблицы и "дозаливки" данных.
12 сен 14, 14:46    [16568695]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37225
Как вариант, заменить один индекс другим через drop_existing = on + online = on.

В таблице всего 3кк записей. Оффлайново он вообще должен за минуту-другу сделаться.
12 сен 14, 15:20    [16568961]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Shurgenz
Member

Откуда: Питер
Сообщений: 1938
Гавриленко Сергей Алексеевич
Как вариант, заменить один индекс другим через drop_existing = on + online = on.

В таблице всего 3кк записей. Оффлайново он вообще должен за минуту-другу сделаться.


drop_existing = on + online = on не решает задачу тоже.... план выполнения не меняется никак
оффлайново у меня выполнялось минут 10 с планом, что я приложил. За это время сайт упадёт нафиг... (ну так написан, тут я ничо поделать не могу)
12 сен 14, 15:41    [16569111]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381
Shurgenz,

а если попробовать какой-то из столбцов из include переместить в ключ (вторым), не поправится план ?
12 сен 14, 15:58    [16569241]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
хмхмхм
Так все очень просто: дизейблите индекс IX_1 на время создания вашего индекса, затем его включаете.
Запредельно просто. Напомните команду для "включения" индекса?
12 сен 14, 20:09    [16570236]     Ответить | Цитировать Сообщить модератору
 Re: Как заставить построиться индекс быстрее?  [new]
Mind
Member

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

Весьма интересный случай.
Вот код для воспроизведения, если кому интересно. За основу взял пример dedjiwed и немного модифицировал. А именно, добавил поле c varchar(8000) и включил его в include индекса.

+
create database www1;
go
use www1;
go

create table big_table(id int primary key clustered, a int, b int, c varchar(8000));
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),
l5(n) as (select 1 from l4 t1, l4 t2),
rt(n) as (select row_number() over (order by (select 0)) from l5 t1, l5 t2)
insert into big_table(id, a, b)
select top(1000000)
	n, n as a, n as b
from
	rt
go

create index idx__big_table__a on big_table(a);
go


set statistics xml on;
go

create index idx__big_table_b on big_table(a) include(b, c);
go

set statistics xml off;
go


Так как данных становится достаточно много, то стоимость сортировки взлетает до небес. Серверу нужно сортировать весь набор данных (включая поля INCLUDE), а не только одно поле, поэтому для того чтобы избежать дорогостоящей (по его мнению) сортировки, он выбирает вариант со сканированием некластерного индекса, так как он уже отсортирован. Но при этом недостающие данные надо вытаскивать через лукап.

Вариантов исправить ситуацию я вижу не много:

Вариант 1: можно не строить такой огромный индекс. Оптимизатор SQL любит предлагать индексы где все-все поля в INCLUDE, но при этом он как то не особо задумывается о размерах такого индекса, и стоимости его обслуживания. Так что для начала убедитесь что для вашего запроса действительно нужно столько полей в INCLUDE.

Вариант 2: Хинты. Набор хинтов для CREATE INDEX весьма ограничен. По сути у нас есть MAXDOP и SORT_IN_TEMPDB, ни то ни другое не окажет никакого влияния на план в нашем случае. Остается вариант с хинтами не на конкретную опреацию, а на всю сессию, естественно недокументированный, а именно отключение правил оптимизации.

Вы можете поиграть на тестовом сервере. у меня получилось исключить nested loops join и lookup выключив одно или все из следующих 4-х правил:

drop index idx__big_table_b on big_table
go

dbcc ruleoff('FetchToApply')
dbcc ruleoff('SelIdxToRng')
dbcc ruleoff('GetToIdxScan')
dbcc ruleoff('ImplRestrRemap')

set statistics xml on;
go

create index idx__big_table_b on big_table(a) include(b, c);
go

set statistics xml off;
go

dbcc ruleon('FetchToApply')
dbcc ruleon('SelIdxToRng')
dbcc ruleon('GetToIdxScan')
dbcc ruleon('ImplRestrRemap')


Что именно эти правила означают я не знаю, ибо документации нет. Можете попробовать спросить об этом у Paul White или somewheresomehow.

Использовать или нет эти хинты в продакшн - решать вам.
12 сен 14, 21:43    [16570641]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить