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

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

Есть mssql 2005 и таблица 130 гигов. У нее есть штуки 3 некластерных индексов и еще индекс heap.
Задача их перестроить. Всё вроде просто, но есть одно НО. Места на диске 150 гигов свободного и лог раздувается так, что остается 5 гигов свободного места и приходится убивать процесс перестроения.

Пробовал:
1. перестроение любого из некластерных индексов
2. создать кластерный индекс по короткому столбцу, чтобы перестроить heap.

ничего не помогло. Кто что может предложить?
8 янв 16, 17:02    [18652895]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
komrad
Member

Откуда:
Сообщений: 5250
rinat mergenbaev
Привет

Есть mssql 2005 и таблица 130 гигов. У нее есть штуки 3 некластерных индексов и еще индекс heap.
Задача их перестроить. Всё вроде просто, но есть одно НО. Места на диске 150 гигов свободного и лог раздувается так, что остается 5 гигов свободного места и приходится убивать процесс перестроения.

Пробовал:
1. перестроение любого из некластерных индексов
2. создать кластерный индекс по короткому столбцу, чтобы перестроить heap.

ничего не помогло. Кто что может предложить?


если вам не хватает 150ГБ логов на перестроение одного индекса, то можно сделать следующее:
- добавить второй лог файл (временно), расположенный на сетке, где есть место и куда может писать учетка сиквела
- перестроить индексы
- удалить второй лог файл

как это сделать: включить трейс-флаг 1807

немного другая задача описана ниже, но идея та же :
http://blogs.lostincreativity.com/sqldba/how-to-restore-sql-database-on-network-share-unc-path-sql-server-2005/

однако, необходимо понимать, что это будет:
1) медленно - запись по сетке медленнней, чем на локальный диск
2) опасно - обрывы в сети приведут к потенциальному повреждению бд
8 янв 16, 17:15    [18652928]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
rinat mergenbaev
Member

Откуда:
Сообщений: 100
komrad
rinat mergenbaev
Привет

Есть mssql 2005 и таблица 130 гигов. У нее есть штуки 3 некластерных индексов и еще индекс heap.
Задача их перестроить. Всё вроде просто, но есть одно НО. Места на диске 150 гигов свободного и лог раздувается так, что остается 5 гигов свободного места и приходится убивать процесс перестроения.

Пробовал:
1. перестроение любого из некластерных индексов
2. создать кластерный индекс по короткому столбцу, чтобы перестроить heap.

ничего не помогло. Кто что может предложить?


если вам не хватает 150ГБ логов на перестроение одного индекса, то можно сделать следующее:
- добавить второй лог файл (временно), расположенный на сетке, где есть место и куда может писать учетка сиквела
- перестроить индексы
- удалить второй лог файл

как это сделать: включить трейс-флаг 1807

немного другая задача описана ниже, но идея та же :
http://blogs.lostincreativity.com/sqldba/how-to-restore-sql-database-on-network-share-unc-path-sql-server-2005/

однако, необходимо понимать, что это будет:
1) медленно - запись по сетке медленнней, чем на локальный диск
2) опасно - обрывы в сети приведут к потенциальному повреждению бд


боюсь это реально долго и опасно.

я думал еще над вариантом создать копию таблицы без индексов, ибо 20 гигов еще останется. потом старую таблицу удалить, шринкануть файл базы и логов и потом уже накатить индексы.
что думаете про такой вариант?
8 янв 16, 17:23    [18652957]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
komrad
Member

Откуда:
Сообщений: 5250
rinat mergenbaev
я думал еще над вариантом создать копию таблицы без индексов, ибо 20 гигов еще останется. потом старую таблицу удалить, шринкануть файл базы и логов и потом уже накатить индексы.
что думаете про такой вариант?


а что возвращает

select @@version
exec sp_spaceused 'имя_таблицы'
8 янв 16, 17:30    [18652973]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
автор
я думал еще над вариантом создать копию таблицы без индексов, ибо 20 гигов еще останется. потом старую таблицу удалить, шринкануть файл базы и логов и потом уже накатить индексы.
что думаете про такой вариант?


Так при построении индексов Вам опять места для тогов не хватит. Лучше создайте копию с индексами, и туда перелейте, а потом старую удалите.
8 янв 16, 17:41    [18652998]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
Зачем понадобилось перестраивать heap?
Какая модель восстановления у БД?

ЗЫ: Перестроение heap автоматом перестроит все индексы.
8 янв 16, 17:51    [18653022]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
komrad
Member

Откуда:
Сообщений: 5250
3unknown
Лучше создайте копию с индексами, и туда перелейте, а потом старую удалите.

добрый такой совет ;)
мало того, что это будет медленно, что вся переливка будет в логе, что новые индексы в итоге будут фрагментированы, так еще и базу раздует на неопределенную величину
8 янв 16, 18:35    [18653149]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
3unknown
Member

Откуда: New York
Сообщений: 140
автор
3unknown
Лучше создайте копию с индексами, и туда перелейте, а потом старую удалите.

добрый такой совет ;)
мало того, что это будет медленно, что вся переливка будет в логе, что новые индексы в итоге будут фрагментированы, так еще и базу раздует на неопределенную величину


А индексы потом создавать что, быстрее будет? Переливка да будет в логе, поэтому переливать надо в цикле, небольшими порциями, и лог бэкапить. Вариант конечно кривой, но лучше предложить я не знаю что можно. По хорошему, места на диске для логов должно хватать для перестроения индексов, иначе постоянно будут проблемы с дефрагментацией.
8 янв 16, 18:56    [18653221]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
komrad
Member

Откуда:
Сообщений: 5250
3unknown
автор
3unknown
Лучше создайте копию с индексами, и туда перелейте, а потом старую удалите.

добрый такой совет ;)
мало того, что это будет медленно, что вся переливка будет в логе, что новые индексы в итоге будут фрагментированы, так еще и базу раздует на неопределенную величину

А индексы потом создавать что, быстрее будет?

с большой вероятностью - да
кроме того, они будут свежие, с иголочки, нефрагментированные
и залить можно будет быстро (BCP)

3unknown
Вариант конечно кривой, но лучше предложить я не знаю что можно.

для этого не хватает данных, т.к. автор пока не указал:
- модель восстановления базы,
- степень фрагментации таблицы и индексов,
- филл-факторы,
- размер таблицы и индексов,
- версию и редакцию сиквела
8 янв 16, 19:19    [18653289]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
rinat mergenbaev
Member

Откуда:
Сообщений: 100
komrad
rinat mergenbaev
я думал еще над вариантом создать копию таблицы без индексов, ибо 20 гигов еще останется. потом старую таблицу удалить, шринкануть файл базы и логов и потом уже накатить индексы.
что думаете про такой вариант?


а что возвращает

select @@version
exec sp_spaceused 'имя_таблицы'



Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
Nov 24 2008 13:01:59
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

rows reserved data index_size unused
912276615 455865296 KB 141696512 KB 313931912 KB 236872 KB
8 янв 16, 19:40    [18653351]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
rinat mergenbaev
Пробовал:
1. перестроение любого из некластерных индексов
2. создать кластерный индекс по короткому столбцу, чтобы перестроить heap.
При создании кластерного индекса некластерные тоже перестраиваются, а у вас там больше 400 гигов, конечно места не хватает. Можно попробовать убить все некластерные, а потом построить кластерный и вернуть обратно некластерные, начиная с самого большого.
А вообще - покупайте диски уже или не перестраивайте вообще ничего.
8 янв 16, 20:06    [18653438]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
rinat mergenbaev
Member

Откуда:
Сообщений: 100
Mind
rinat mergenbaev
Пробовал:
1. перестроение любого из некластерных индексов
2. создать кластерный индекс по короткому столбцу, чтобы перестроить heap.
При создании кластерного индекса некластерные тоже перестраиваются, а у вас там больше 400 гигов, конечно места не хватает. Можно попробовать убить все некластерные, а потом построить кластерный и вернуть обратно некластерные, начиная с самого большого.
А вообще - покупайте диски уже или не перестраивайте вообще ничего.


Пробовал просто некластерный перестроить по одному полю -самому короткому - всё равно тот же итог.
А как можно расчитать сколько потребуется места, чтобы перестроить индекс?
8 янв 16, 21:49    [18653749]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
rinat mergenbaev,

Вот чем можно вам помочь, если на вопросы отвечать не желаете?
8 янв 16, 22:28    [18653865]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
rinat mergenbaev
Member

Откуда:
Сообщений: 100
invm
Зачем понадобилось перестраивать heap?


как раз для этого. чтобы не возиться с 3.

invm
ЗЫ: Перестроение heap автоматом перестроит все индексы.


invm
Какая модель восстановления у БД?


full
8 янв 16, 22:39    [18653931]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
rinat mergenbaev
как раз для этого. чтобы не возиться с 3.
rinat mergenbaev
full

Иногда стоит повозится, а не искать обманчиво простых решений.

Alter index rebuild может минимально журналироваться, поэтому:
1. Делаете бекап ЖТ.
2. Переводите БД в bulk-logged.
3. Перестраиваете ваши три индекса.
4. Переводите БД обратно в full.
5. Делаете бекап ЖТ.
8 янв 16, 22:58    [18653994]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
komrad
Member

Откуда:
Сообщений: 5250
данных всего 141ГБ
идея в том, чтобы
- вылить данные в файл на файловую систему
- партицировать таблицу (https://www.mssqltips.com/sqlservertip/1914/sql-server-database-partitioning-myths-and-truths/)
- залить данные обратно
- построить индексы

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

если есть тестовый сервер с копией базы (1:1 или 1:10), то можно примерно прикинуть сколько это займет как по времени, так и по пространству

rinat mergenbaev, покажите скрипты создания таблицы и индексов, плз

ссылки:
https://technet.microsoft.com/en-us/library/ms345146(v=sql.90).aspx
https://www.mssqltips.com/sqlservertip/1791/fixing-index-fragmentation-in-sql-server-2005-and-sql-server-2008/
8 янв 16, 23:16    [18654037]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
rinat mergenbaev
Member

Откуда:
Сообщений: 100
invm
rinat mergenbaev
как раз для этого. чтобы не возиться с 3.
rinat mergenbaev
full

Иногда стоит повозится, а не искать обманчиво простых решений.

Alter index rebuild может минимально журналироваться, поэтому:
1. Делаете бекап ЖТ.
2. Переводите БД в bulk-logged.
3. Перестраиваете ваши три индекса.
4. Переводите БД обратно в full.
5. Делаете бекап ЖТ.


а вы на практике делали так? какой выигрыш с обычным ребилдом?
с bulk-logged не имел дело.
Спасибо, почитаю
8 янв 16, 23:18    [18654046]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
komrad
Member

Откуда:
Сообщений: 5250
komrad
данных всего 141ГБ

даже 135ГБ, если считать честно
8 янв 16, 23:19    [18654051]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 205
rinat mergenbaev,

invm Вам дал толковый совет, я считаю.
Я на практике с таким имел дело - транлог используется гораздо меньше.
Можете еще вот с этой статьей ознакомиться: http://www.t-sql.ru/post/index_minimally_logged.aspx
8 янв 16, 23:24    [18654065]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
invm
Member

Откуда: Москва
Сообщений: 9400
rinat mergenbaev
какой выигрыш с обычным ребилдом?
+ Проверяем выигрыш
create database DBTestIndexRebuild;
go

alter database DBTestIndexRebuild set recovery full;
backup database DBTestIndexRebuild to disk = 'nul:';
go

use DBTestIndexRebuild;
go

create table dbo.t (id int identity primary key, s char(500));

insert into dbo.t
select top (100000)
 newid()
from
 master.dbo.spt_values a cross join
 master.dbo.spt_values b;

create index IX_t__s on dbo.t (s);
go

begin tran;

alter index IX_t__s on dbo.t rebuild;

select
 dt.database_transaction_log_bytes_used
from
 sys.dm_tran_current_transaction ct join
 sys.dm_tran_database_transactions dt on dt.transaction_id = ct.transaction_id and dt.database_id = db_id();

commit;
go

alter database DBTestIndexRebuild set recovery bulk_logged;
go

begin tran;

alter index IX_t__s on dbo.t rebuild;

select
 dt.database_transaction_log_bytes_used
from
 sys.dm_tran_current_transaction ct join
 sys.dm_tran_database_transactions dt on dt.transaction_id = ct.transaction_id and dt.database_id = db_id();

commit;
go

use master;
drop database DBTestIndexRebuild;
go

Ну и не лишне будет почитать - https://technet.microsoft.com/en-us/library/ms190203(v=sql.105).aspx
8 янв 16, 23:41    [18654110]     Ответить | Цитировать Сообщить модератору
 Re: Перестроение индекса большой таблицы  [new]
MasterZiv
Member

Откуда: Питер
Сообщений: 34620
rinat mergenbaev
Привет

Есть mssql 2005 и таблица 130 гигов. У нее есть штуки 3 некластерных индексов и еще индекс heap.
Задача их перестроить


ЗАЧЕМ? - вот главный вопрос, который ты должен был себе задать...
10 янв 16, 08:02    [18657905]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить