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

Откуда: Ростов-на-Дону
Сообщений: 344
Добрый день!

Есть таблица 1,3 млрд не партиционированная с большим кластерным индексом 184 Гб. Его перестройка (rebuild) при работающей системе с ONLINE=on занимает 2 часа 42 минуты. При этом следующий индекс по этой же таблице, который занимает 182 Гб сервер rebuildит за 48 минут. Хотелось бы понять почему возникает такая разница во времени. Спасибо!


CREATE CLUSTERED INDEX [IX_Table1_Date] ON [dbo].[Table1]
(
	[ForeignId] ASC,
	[DateUtc] 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 = 80) ON [PRIMARY]
GO

ForeignId INT
DateUTC DATETIME

Второй индекс, который быстро перестраивается (PK)

ALTER TABLE [dbo].[Table1] ADD  CONSTRAINT [PK_ID] PRIMARY KEY NONCLUSTERED 
(
	[IdRow] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

IdRow BIGINT
23 май 16, 12:12    [19206763]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Nimua,

смущает почему кластерный дольше некластерного?
23 май 16, 12:14    [19206782]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
o-o
Guest
надо было смотреть блокировки при онлайновом ребилде.
у меня было, что перестраивался мгновенно, но зато потом часами висел и ждал заключительное Sch-M.
пока не отвалятся все читатели, Sch-M не получает.
ну и желающих почитать кластерный видимо больше, чем желающих читать некластерный
23 май 16, 13:34    [19207442]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
TaPaK,

Да, то есть так и должно быть и мне бы какое-нибудь объяснение, конкретное
например при кластерном индексе также обновляются какие-нибудь ссылки в таблице поэтому это занимает больше времени.
23 май 16, 13:42    [19207514]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Nimua
TaPaK,

Да, то есть так и должно быть и мне бы какое-нибудь объяснение, конкретное
например при кластерном индексе также обновляются какие-нибудь ссылки в таблице поэтому это занимает больше времени.

вы знаете разницу между этими двумя видами индексов?
23 май 16, 13:42    [19207521]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
o-o,

спасибо! посмотрю, есть данные из SQL Sentry. Дело в том что делала 2 раза и данные по времени такие же (+- 10 минут)
23 май 16, 13:43    [19207525]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
TaPaK,

Исходя из того что я знаю - данные таблицы на диске лежат в порядке указанном в кластерном индексе. Но я не знаю деталей что происходит при его ребилде.
То есть вот индекс грохается и начинает делаться новый, что при этом происходит с данными в таблице - они тоже двигаются или нет. и как это посмотреть в хелпе/статье. А то я подспудно ощущаю что это норм, но доказать не могу :(
23 май 16, 13:45    [19207537]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
o-o
Guest
оффтоп:
а как это вы умудрились получить некластерный примерно того же размера, что и кластерный?
там что, абсолютно все поля в виде ?
23 май 16, 13:48    [19207558]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Nimua
TaPaK,

Исходя из того что я знаю - данные таблицы на диске лежат в порядке указанном в кластерном индексе. Но я не знаю деталей что происходит при его ребилде.
То есть вот индекс грохается и начинает делаться новый, что при этом происходит с данными в таблице - они тоже двигаются или нет. и как это посмотреть в хелпе/статье. А то я подспудно ощущаю что это норм, но доказать не могу :(


вообщем-то ответ прям определении:
https://msdn.microsoft.com/en-us/library/ms190457.aspx


автор
оффтоп:
а как это вы умудрились получить некластерный примерно того же размера, что и кластерный?
там что, абсолютно все поля в виде ?

разве что люто фрагментирован... а так да может размер не верно определён
23 май 16, 13:52    [19207583]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
o-o
Guest
Nimua
То есть вот индекс грохается и начинает делаться новый

только сперва новый строится, а потом старый грохается
23 май 16, 13:52    [19207587]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
o-o,

размер индекса определяла запросом:

SELECT i.[name] AS IndexName
,object_name(s.object_id) AS TableName
,SUM(s.[used_page_count]) * 8/ 1024/1024 AS IndexSizeGB
FROM sys.dm_db_partition_stats AS s
INNER JOIN sys.indexes AS i ON s.[object_id] = i.[object_id]
AND s.[index_id] = i.[index_id]
and i.name like 'IX_name'
GROUP BY i.[name],s.object_id
ORDER BY IndexSizeGB desc
23 май 16, 14:04    [19207654]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
TaPaK,

Нашла вот такую статью https://www.sql.ru/articles/mssql/03013101indexes.shtml
Я примерно так и думала, но в дискуссии с dba он сказал, что в таблице хранятся только ссылки на соответствующие части кластерного индекса. И поэтому кластерный индекс мы ребилдим или нет, нет разницы. Насколько я его поняла.

Исходя из части из хелпа

"Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order"

получается что прямо кластерный индекс это как раз часть данных таблицы. Так? и поэтому его перестройка занимает больше времени.
23 май 16, 14:27    [19207758]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Nimua
TaPaK,

Нашла вот такую статью https://www.sql.ru/articles/mssql/03013101indexes.shtml
Я примерно так и думала, но в дискуссии с dba он сказал, что в таблице хранятся только ссылки на соответствующие части кластерного индекса. И поэтому кластерный индекс мы ребилдим или нет, нет разницы. Насколько я его поняла.

Исходя из части из хелпа

"Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition. There can be only one clustered index per table, because the data rows themselves can be sorted in only one order"

получается что прямо кластерный индекс это как раз часть данных таблицы. Так? и поэтому его перестройка занимает больше времени.

если есть кластерный индекс - то он и есть таблица... если сильно грубо :)
23 май 16, 14:31    [19207768]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
автор
Я примерно так и думала, но в дискуссии с dba он сказал, что в таблице хранятся только ссылки на соответствующие части кластерного индекса. И поэтому кластерный индекс мы ребилдим или нет, нет разницы. Насколько я его поняла.

dba нынче не тот пошёл....
23 май 16, 14:32    [19207776]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2415
TaPaK

dba нынче не тот пошёл....

не все dba одинаково полезны )
23 май 16, 14:37    [19207806]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
StarikNavy
TaPaK

dba нынче не тот пошёл....

не все dba одинаково полезны )

кстати вопрос организации rebuild/reorganize это ж вроде как раз dba работа... или ТС тоже dba :)
23 май 16, 14:39    [19207822]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
o-o
Guest
Nimua
размер индекса определяла запросом:
...

нормальный запрос.
но некластерный индекс размером с кластерный подозрителен.
там все поля в include?
приведите скрипт таблицы и некластерного индекса
23 май 16, 14:41    [19207832]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
o-o
Guest
Nimua
получается что прямо кластерный индекс это как раз часть данных таблицы. Так? и поэтому его перестройка занимает больше времени.

это данные входят в кластерный индекс, а не индекс в данные.
но если я создам некластерный со всеми полями в include,
у меня получится некластерный индекс размером с кластерный
(похоже на ваш случай)
только по времени он ребилдиться будет столько же, сколько и кластерный
(берем идеальный случай, когда никто не мешает получить финальную -блокировку)
23 май 16, 14:46    [19207868]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
В некластерном индексе нет include. там только одно поле RowId BIGINT фрагментация 16%, почему то заполненность страниц (page fullness) 26% хотя fillfactor 80%.
может это надо учитывать в расчете кол-ва места в запросе? и он на самом деле меньше?

to TaPaK
я типа db engineer, с частичными функциями dba, пока сама не поняла :(
23 май 16, 14:57    [19207913]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
Nimua,

fillfactor не "упаковывает" ваши страницы во время вставок, только при создании индекса оставляет место... как то так
23 май 16, 15:06    [19207971]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
o-o
Guest
Заполненность страниц хранится только для куч, а у вас индекс.
Насоздавать некластерных размером больше кластерного можно и без include, запихав все поля в ключ индекса, если 900 байт не превысят.
Я подозреваю, вы не тот индекс смотрите, или не там, или используете sp_helpindex, а он не показывает included-поля.
Разве сложно выложить скоипт созданич таблицы и индекса?
Ну замените имя таблицы на T, поля на col1,... ColN.
Раз все такое супер-пупер секретное
23 май 16, 15:14    [19208034]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6802
o-o,
автор
Заполненность страниц хранится только для куч, а у вас индекс.

WAT?
23 май 16, 15:15    [19208044]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
Nimua
Member

Откуда: Ростов-на-Дону
Сообщений: 344
o-o,

Где проверяла что у индекса нет include полей - индекс - правой кнопкой Свойства Вкладка Included columns отсутствует.
В описании задачи приложен скрипт индкекса (второй, который некластерный) формировался так - на индексе правой кнопкой Scripts Index As - Create to

Вот ниже еще раз скрипт этого же индекса + таблицы

GO

CREATE TABLE [dbo].[Table1](
	RowId [bigint] IDENTITY(1,1) NOT NULL,
	ForeignId [int] NOT NULL,
	DateUtc] [datetime] NOT NULL CONSTRAINT [DF_1]  DEFAULT (getutcdate()),
	[TypeId] [tinyint] NOT NULL,
	Field1 [int] NULL,
	Field2 [int] NULL,
	Field3 [int] NULL,
	Field4 [int] NULL,
	Field5 [int] NULL,
	Field6  AS (dateadd(millisecond, -datepart(millisecond,[DateUtc]),[DateUtc])),
	Field7 [nvarchar](900) NULL,
	Field8 [bigint] NULL,
	Field9 [bigint] NULL,
	Field10 [datetime] NULL,
	Field11 [datetime] NULL,
	Field112 [int] NULL,
 CONSTRAINT [PK_ID] PRIMARY KEY NONCLUSTERED 
(
	[RowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO



ALTER TABLE [dbo].[Table1] ADD  CONSTRAINT [PK_ID] PRIMARY KEY NONCLUSTERED 
(
	[RowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO
23 май 16, 15:20    [19208090]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
o-o
Guest
TaPaK
o-o,
автор
Заполненность страниц хранится только для куч, а у вас индекс.

WAT?

Кому не нравится, приводит доказательства обратного. Или хотя бы объясняет, зачем бы серверу хранить заполненность страниц индекса.
23 май 16, 15:21    [19208097]     Ответить | Цитировать Сообщить модератору
 Re: Как понять почему Rebuild индекса делается столько времени  [new]
o-o
Guest
Nimua
o-o,

Где проверяла что у индекса нет include полей - индекс - правой кнопкой Свойства Вкладка Included columns отсутствует.
В описании задачи приложен скрипт индкекса (второй, который некластерный) формировался так - на индексе правой кнопкой Scripts Index As - Create to

Вот ниже еще раз скрипт этого же индекса + таблицы

GO

CREATE TABLE [dbo].[Table1](
	RowId [bigint] IDENTITY(1,1) NOT NULL,
	ForeignId [int] NOT NULL,
	DateUtc] [datetime] NOT NULL CONSTRAINT [DF_1]  DEFAULT (getutcdate()),
	[TypeId] [tinyint] NOT NULL,
	Field1 [int] NULL,
	Field2 [int] NULL,
	Field3 [int] NULL,
	Field4 [int] NULL,
	Field5 [int] NULL,
	Field6  AS (dateadd(millisecond, -datepart(millisecond,[DateUtc]),[DateUtc])),
	Field7 [nvarchar](900) NULL,
	Field8 [bigint] NULL,
	Field9 [bigint] NULL,
	Field10 [datetime] NULL,
	Field11 [datetime] NULL,
	Field112 [int] NULL,
 CONSTRAINT [PK_ID] PRIMARY KEY NONCLUSTERED 
(
	[RowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO



ALTER TABLE [dbo].[Table1] ADD  CONSTRAINT [PK_ID] PRIMARY KEY NONCLUSTERED 
(
	[RowId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

И где тут что-то кластерное?
23 май 16, 15:23    [19208121]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить