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

Откуда:
Сообщений: 166
MSSQL 2008R2

Есть таблица на 100 млн записей, 45гб дискового пространства.

Уровень фрагментации кластерного индекса 92%
Делаю его ребилд, размер базы резко начинает расти до размеров, которые превышают дисковое пространство (примерно до 80гб).

Какие есть альтернативы?
1 авг 12, 15:07    [12946849]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Гм... А на каком типе данных построен кластерный индекс? Он суррогатный или естественный? Что происходит с записями, что возникла такая фрагментация?

ЗЫ. Т.е. нужен ли здесь вообще кластреный индекс.
1 авг 12, 15:42    [12947139]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Ребилд онлайн/офлайн? Что растет, база или лог?
1 авг 12, 15:43    [12947155]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
zanderman
Member

Откуда:
Сообщений: 166
pkarklin
Гм... А на каком типе данных построен кластерный индекс? Он суррогатный или естественный? Что происходит с записями, что возникла такая фрагментация?

ЗЫ. Т.е. нужен ли здесь вообще кластреный индекс.


Построен на

  [id] int IDENTITY(1, 1) NOT NULL
  ...
  PRIMARY KEY CLUSTERED ([id])


Был инсерт данных, примерно 10 млн строк...
1 авг 12, 15:49    [12947220]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
zanderman
Member

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

Онлайн,
Растет база,
правда лог тоже,но не так значительно...
1 авг 12, 15:50    [12947233]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
Для онлайн ребилда понадобится столько же места, сколько весит таблица, плюс еще чуток.

Какой командой вы ребилдите таблицу?
1 авг 12, 15:52    [12947253]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Сделайте offline, раз места нет.
1 авг 12, 15:52    [12947256]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
zanderman
Member

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

Пользуюсь таким скиптом:
USE DB

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

DECLARE @dbid smallint;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SET @dbid = DB_ID();

SELECT

    [object_id] AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag, page_count

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation

AND index_id > 0 -- Ignore heaps

AND page_count > 25; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N'Executed: ' + @command;

END

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO


Совсем забыл об отключении индексов.
1 авг 12, 16:41    [12947652]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37155
По дефолту ONLINE = { ON | OFF }
Показания не сходятся.
1 авг 12, 16:47    [12947706]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
zanderman
Member

Откуда:
Сообщений: 166
Гавриленко Сергей Алексеевич
По дефолту ONLINE = { ON | OFF }
Показания не сходятся.


Я уже понял, спасибо.
1 авг 12, 16:59    [12947799]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
pkarklin
Сделайте offline, раз места нет.
Я что-то не совсем понимаю, а что оффлайн ребилд не требует места?

автор
When a new index structure is created, disk space for both the old (source) and new (target) structures is required in their appropriate files and filegroups. The old structure is not deallocated until the index creation transaction commits.
2 авг 12, 20:14    [12955179]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
Mind,

Требует, но ONLINE операция требует больше места:

BOL
When you perform index operations online, additional temporary disk space is required.

If a clustered index is created, rebuilt, or dropped online, a temporary nonclustered index is created to map old bookmarks to new bookmarks.
2 авг 12, 20:55    [12955271]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
Mind
Member

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

Требует, но ONLINE операция требует больше места:

BOL
When you perform index operations online, additional temporary disk space is required.

If a clustered index is created, rebuilt, or dropped online, a temporary nonclustered index is created to map old bookmarks to new bookmarks.
Я боюсь что 800 лишних мегабайт не спасут ТС.
2 авг 12, 22:29    [12955446]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
zanderman
Member

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

Да вы правы мои свободные 20гб были съедены, так же быстро,как и в онлайн режиме...
3 авг 12, 01:06    [12955815]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
Mind
Member

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

Да вы правы мои свободные 20гб были съедены, так же быстро,как и в онлайн режиме...
Попробуйте ALTER INDEX REORGANIZE, хотя результат может быть далеко не таким хорошим как после REBUILD, к тому же потребует много места под лог, даже если БД в SIMPLE .

Или создать новую таблицу, создать кластер, и копировать данные в новую порциями, из старой удалять, потом создать все некластерные индексы, старую таблицу убить, новую переименовать. Гимору много, но от фрагментации избавитесь.

А вообще, для автоинкрементного индекса какая-то жуткая фрагментация. Скорее всего было много сплитов, которые при таком кластерном индексе могут возникнуть только при апдейте уже существующих строк бОльшими значениями. Если это так то советую пересмотреть вставки/обновления данных или уменьшить fillfactor для кластерного индекса.
3 авг 12, 02:57    [12955919]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 34760
Блог
можно секционировать таблицу и перестраивать отдельные секции
3 авг 12, 10:49    [12956801]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
HandKot
Member

Откуда: Sergiev Posad
Сообщений: 3019
Критик
можно секционировать таблицу и перестраивать отдельные секции


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

пока видится один вариант, как говорил
Mind
создать новую таблицу, создать кластер, и копировать данные в новую порциями, из старой удалять, потом создать все некластерные индексы, старую таблицу убить, новую переименовать


а вот новую уже можно и секционировать
3 авг 12, 11:07    [12956949]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
zanderman
Member

Откуда:
Сообщений: 166
[quot Mind]
zanderman
Mind,
Или создать новую таблицу, создать кластер, и копировать данные в новую порциями, из старой удалять, потом создать все некластерные индексы, старую таблицу убить, новую переименовать. Гимору много, но от фрагментации избавитесь.


Как раз переезжаю на новый сервак,
думаю количество действий будет упрощено
4 авг 12, 16:47    [12962326]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
vlas
Member

Откуда: Москва
Сообщений: 112
Критик
можно секционировать таблицу и перестраивать отдельные секции


Секционирование доступно только в Enterprise.
Может сервер Standard Edition.
4 авг 12, 17:51    [12962415]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
zanderman
Member

Откуда:
Сообщений: 166
Перенес базу на новый сервер, сделал ребилд кластерного индекса (ресурсы повзоляют). Гуд.
Однако бешено возраcло Unallocated Space примерно на 85%.
Решил сделать Shrink базы.
Уровень дефрагментации кластерного индекса снова подскочил с 0% до 99%
Это замкнутый круг?
4 сен 12, 15:32    [13112236]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
gang
Member

Откуда:
Сообщений: 1394
zanderman,
Примерно да. Реиндекс сначала занимает, а потом высвобождает место. Шринк выжимает фри спейс, но фрагментирует данные. Попробуйте далть шринк с транкейт-онли. Места может освободить гораздо меньше, но тасовать данные не станет.
4 сен 12, 15:41    [13112314]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
Ozerov
Member

Откуда: Москва
Сообщений: 3637
zanderman
Перенес базу на новый сервер, сделал ребилд кластерного индекса (ресурсы повзоляют). Гуд.
Однако бешено возраcло Unallocated Space примерно на 85%.
Решил сделать Shrink базы.
Уровень дефрагментации кластерного индекса снова подскочил с 0% до 99%
Это замкнутый круг?


А место то свободное где было ? Может в логах ? И шринк лог файла только достаточно (только в след раз при ребилде все равно вырастет)
4 сен 12, 15:44    [13112352]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
zanderman
Member

Откуда:
Сообщений: 166
Ozerov
А место то свободное где было ? Может в логах ? И шринк лог файла только достаточно (только в след раз при ребилде все равно вырастет)


Нет. Вырос размер файла базы, лог вырос, но не значительно по сравнению с базой.
Надеюсь до ребилда следующего не доведу. Да и сейчас то толку, или 50 гб места на дисках или важный кластерный индекс.
4 сен 12, 15:54    [13112436]     Ответить | Цитировать Сообщить модератору
 Re: Ребилд кластерного индекса  [new]
zanderman
Member

Откуда:
Сообщений: 166
А как такой вариант:

на новом сервера создать копию нужной таблицы с теми же индексами, а потом сделать импорт данных из текущей таблицы в новую.
Тогда кластерный индекс останется с нормальным уровнем фрагментации?
И база не разрастется
14 сен 12, 11:48    [13163348]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить