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

Откуда:
Сообщений: 217
Ребят, помогите с реорганизацией и перестроением индекса.

Есть несколько критических баз, доступ к которым 24на7, SQL2008r2. Нужно время от времени обновлять статистику, индексы и процедурный Кеш.
1. Проблема - я возможно ошибаюсь, но, перестроение индекса создает проблемы с доступом, поскольку ведется в монопольном режиме. Как выкручиваться? Можно ли использовать только реорганизацию и никогда не делать перестроение?
2. Реорганизация тут тоже возникли проблемы – процедура затянулась до утра и в итоге я ее отменил. Насколько я знаю в отличии от перестроения после отмены результат реорганизации сохраняется.
3. Время выполнения реорганизации несколько удивило 7 часов, как и размер индекса 1,7гб, размер базы 193гб – возможно реиндексация сжала индекс?
“Кроме того, реорганизация сжимает страницы индекса. Их сжатие производится в соответствии с текущим значением коэффициента заполнения. Увидеть коэффициент заполнения можно в таблице sys.indexes.”
Посмотрел динамику роста базы – нет, размер примерно такой-же. Тогда почему так долго выполнялась реорганизация?
4. Лог вырос на 80гб. Хорошо что отключил, процедуру – до переполнения диска оставалось 24гб. В связи с этим вопрос, возможно ли базу в 200гб, реиндексировать, стандартными sql средствами, при том, что на диске свободно около 100гб.
22 май 14, 10:34    [16052923]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
tsdos,

1. А вы перестроение индекса делаете из принципа или по каким то его характеристикам ориентируетесь?
2. Зависит от нагрузки на сервер и его характеристик.
3. не понял в чем состоит вопрос, кореляции между размером одного индекса и размером всей базы не вижу никакой, дефрагметация индекса собственно тем и занимается, что приводит страницы индекса к "текущим значениям коэффициента заполнения"
4. А вы знаете какие то не sql средства для реиндексации?:)
22 май 14, 11:04    [16053188]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
Visibus
Member

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

1. Лучше делать реиндекс в режиме онлайн. Накладные расходы - потребуется дополнительно около 50 % свободного места от текущего размеры БД. В вашем случае, при размере БД в 200 Гб потребуется почти 100 Гб. А, да, если возможно, то лучше перевести
БД в простую модель восстановления. В этом случае лог журнала транзакций расти не будет. При полной модели восстановления - растет и довольно активно. По окончанию - модель восстановления вернуть, как было изначально)
2. С реорганизацией. Тут вообще не понятно - лог транзакционный растет при любой модели восстановления. Может и 100 Гб не хватить, если индексы сильно фрагментированы.
22 май 14, 11:21    [16053345]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8584
ALTER INDEX ... WITH (ONLINE = ON)
22 май 14, 11:21    [16053352]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2423
Владислав Колосов
ALTER INDEX ... WITH (ONLINE = ON)


это если у ТС enterprise
22 май 14, 11:26    [16053393]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
Visibus
Member

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

Точно - автор забыл версию указать)
22 май 14, 11:28    [16053415]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
tsdos
Member

Откуда:
Сообщений: 217
Visibus,WarAnt,Владислав Колосов

Ребят, спасибо за подсказки, буду медитировать, читать, пробовать)))...
22 май 14, 11:33    [16053472]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
Alexander Us
Member

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

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

REBUILD WITH (ONLINE = ON) это если энтерпрайз и в таблице нет блоб полей.
SORT_IN_TEMPDB = ON, но вначале убедитесь что TEMPDB на другом диске и там есть достаточно места.
22 май 14, 12:10    [16053818]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
tsdos
Member

Откуда:
Сообщений: 217
Alexander Us,

Время выполнения реорганизации = 7 часов: это для одного индекса оли Вы всей базы?

Для всей базы, размер индексов всего 1,7гб, меня удивил такая долгая их обработка.

Как насчёт того, чтобы обрабатывать только те индексы, которые в этом нуждаются?

Видимо к этому и пришел.

REBUILD WITH (ONLINE = ON) это если энтерпрайз и в таблице нет блоб полей.

Буду читать про online

SORT_IN_TEMPDB = ON, но вначале убедитесь что TEMPDB на другом диске и там есть достаточно места.

В моем случае на одном(((.

Спасибо за подробные рекомендации.
22 май 14, 12:59    [16054280]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
Alexander Us
Member

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

Вот пара готовых решений:

от Alexander Gladchenko и
SQL Server Index and Statistics Maintenance

я пробую такой путь:
создаю таблицу с перечнем индексов, данными фрагментации, флагом rebuild online 1/0 итд.
Замем ночью стартует джоб который выполняет строчку за строчкой.
При этом можно задать ограничение на общее время выполнения и максимальное время offline.

Хоть ещё не готово на 100%, но мож. быть Вам пригодится:

Создание таблицы индексов и иx параметров:
create table [Wartung: Indexes] -- это Maintaince: Indexes 
(
 	 Rnr int identity(1,1) primary key
	,object_name sysname
	,index_name sysname
	,cols varchar(200)
	,cols_included varchar(200)
	,type_desc varchar(50)
	,record_count bigint
	,fragment_count int
	,page_count int
	,avg_fragmentation_in_percent decimal(5,2)
	,index_depth int
	,user_seeks bigint
	,user_scans bigint
	,user_lookups bigint
	,user_updates  bigint
	,blobs_cols_count int
	,SQL_Rebuild varchar(500)
	,Rebuild_online int
	,SQL_Reorganize varchar(500)
	,[skip] int
	,datetime_erzeugt datetime
	,datetime_start datetime
	,datetime_end datetime
	,dauer_sec as (datediff(ss,datetime_start,datetime_end)) --это duration
	,spid int
	,[error_number] int
	,[error_state] int
	,[error_message] varchar(4000)
)


---------------------------------------------------------------------



--http://technet.microsoft.com/ru-ru/library/ms188917.aspx

SELECT * INTO #T
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'SAMPLED') --with(nolock)
WHERE  index_id > 0 AND avg_fragmentation_in_percent > 5.0; 

---------------------------------------------------------------------
WITH QO
AS
(
	SELECT 
	I.Name as index_name, I.index_id,
	(
		select count(*) from sys.columns sc join sys.types st on sc.system_type_id = st.system_type_id where sc.object_id=T.object_id and (st.name in ('text','ntext','image','xml') or (st.name in ('varchar','nvarchar','varbinary') and sc.max_length=-1))
	) CntBlobsOnTabs, 	
	(
		select case when ic.is_included_column=0 then sc.name + ', ' else '' end from sys.index_columns ic join sys.columns sc on ic.object_id=sc.object_id and ic.column_id=sc.column_id  where ic.object_id =T.object_id and ic.index_id = I.index_id for xml path('')
	) ColsBase,
	(
		select case when ic.is_included_column=1 then sc.name + ', ' else '' end from sys.index_columns ic join sys.columns sc on ic.object_id=sc.object_id and ic.column_id=sc.column_id where ic.object_id =T.object_id and ic.index_id = I.index_id for xml path('')
	) ColsIncl,
	
	I.type_desc,T.alloc_unit_type_desc,T.index_depth,
	object_name(T.object_id) [object_name], 
	record_count, Fragment_count, Page_count, avg_fragmentation_in_percent,
	S.user_seeks,S.user_scans,S.user_lookups,S.user_updates  	 
	FROM
	#T T 
	join sys.indexes I on T.object_id=I.object_id and T.Index_id = I.Index_id
	left join SYS.DM_DB_INDEX_USAGE_STATS S on S.Database_id=db_id() and T.object_id=S.object_id and T.Index_id = S.Index_id -- SYS.DM_DB_INDEX_USAGE_STATS liefrt über alle DBs
	where record_count > 1000 and T.index_depth > 1 --  1 = Heap / LOB_DATA / ROW_OVERFLOW_DATA
)

INSERT [Wartung: Indexes] ([object_name], index_name, cols, cols_included, type_desc, record_count, fragment_count, page_count, avg_fragmentation_in_percent, index_depth, user_seeks ,user_scans ,user_lookups,user_updates,blobs_cols_count, SQL_Rebuild, Rebuild_online, SQL_Reorganize, [skip], datetime_erzeugt) 
SELECT 
[object_name],
index_name, 
substring(ColsBase, 1, len(ColsBase)-1) ColsBase, 
case when ColsIncl <> '' then substring(ColsIncl, 1, len(ColsIncl)-1) else '' end ColsIncl,
type_desc,
record_count,
Fragment_count,
Page_count,
avg_fragmentation_in_percent,
index_depth,
user_seeks,
user_scans,
user_lookups,
user_updates,
CntBlobsOnTabs,
'ALTER INDEX [' + Index_Name + '] on [' + [object_name] + '] REBUILD WITH(SORT_IN_TEMPDB=ON, ONLINE=' + CASE WHEN CntBlobsOnTabs=0 then 'ON)' ELSE 'OFF)' END  as SQLRebuild,
CASE WHEN CntBlobsOnTabs=0 then 1 ELSE 0 END as Rebuild_online,
'ALTER INDEX [' + Index_Name + '] on [' + [object_name] + '] REORGANIZE;' as SQLAlter,
0 as [skip],
getdate()			   DateTimeErzeugt
from QO
order by page_count desc



А это в джобе:

!!! У меня тут заряжено REBUILD. По уму надо бы делать в зависимости от значения avg_fragmentation_in_percent
!!! EXEC sp_updatestats это тоже не по уму. Надо бы только на отдельные индексы. Только после reorganize.

SET QUOTED_IDENTIFIER ON; --erforderlich beim Start in Agent f&#252;r Ind auf berechenete Cols unsw. Sonst Fehler 1934

declare @SQL nvarchar(4000), @BatchStart datetime, @CommandStart datetime, @ErrText varchar(8000);
declare @rnr int, @cnt int, @dauer_batch_sec int, @dauer_offline_sec int, @rebuild_online int, @ErrCnt int;
declare @cnt_max int, @dauer_offline_sec_max int, @dauer_batch_sec_max int, @ErrCnt_max int

--------------------- Einstellungen ---------------------------------------------- 
set @cnt_max			= 200;		--bis 200 Indexes
set @dauer_offline_sec_max		=  450;		--max 450 sec offline
set @dauer_batch_sec_max		= 20*60;	                 --max 20 min max. Jobdauer
set @ErrCnt_max			= 3;		--max. 3 Fehler bis Abbruch
----------------------------------------------------------------------------------

SELECT @BatchStart=getdate(), @cnt=1, @dauer_offline_sec=0, @dauer_batch_sec=0, @ErrCnt=0

WHILE @cnt <= @cnt_max and @dauer_offline_sec <= @dauer_offline_sec_max and @dauer_batch_sec <= @dauer_batch_sec_max and @ErrCnt <= @ErrCnt_max
BEGIN

	select top 1 @SQL = SQL_Rebuild, @rnr=rnr, @Rebuild_online=Rebuild_online from [Wartung: Indexes] where [Skip]=0 and datetime_start is NULL order by rnr
	
	IF @@ROWCOUNT=0
		BREAK
					
	set @cnt=@cnt+1	
		
	select @CommandStart=getdate()

	BEGIN TRY

		if (@SQL is not NULL)
		begin
			update [Wartung: Indexes] set datetime_start=getdate(), spid=@@spid where rnr=@rnr
			set @CommandStart=getdate()
			exec sp_executesql @SQL
		      
			update [Wartung: Indexes] 
			set 
			[error_number]	=0,
			[error_state]	=0,
			[error_message]	='Ok',
			datetime_end	=getdate() 
			where rnr=@rnr    
		end
				
	END TRY
	BEGIN CATCH

		update [Wartung: Indexes] 
		set 
		[error_number]	=ERROR_NUMBER(),
		[error_state]	=ERROR_STATE(),
		[error_message]	=ERROR_MESSAGE(),
		datetime_end	=getdate() 
		where rnr=@rnr
	       
	    set @ErrCnt = @ErrCnt + 1
	       
	END CATCH
	
	
	set @dauer_batch_sec = DATEDIFF(mi, @BatchStart, getdate())
	
	
	if @rebuild_online=0
	   set @dauer_offline_sec = @dauer_offline_sec + DATEDIFF(second, @CommandStart, getdate())


END


EXEC sp_updatestats
22 май 14, 16:48    [16056603]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
Crimean
Member

Откуда:
Сообщений: 13147
Alexander Us
создаю таблицу с перечнем индексов, данными фрагментации, флагом rebuild online 1/0 итд.
Замем ночью стартует джоб который выполняет строчку за строчкой


+1. так же сделал в свое время
22 май 14, 16:56    [16056704]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
Gosha
Member

Откуда: Odessa
Сообщений: 217
Вопрос в тему, ответ пока нигде не нашел: Если во время перестроения или реорганизации индексов закончится место на диске, понятно, что будет ошибка и т.д., но меня интересует, что произойдет с логом при модели восстановления simple? он просто останется в том же размере, что во время ошибки?
2 окт 14, 15:35    [16651081]     Ответить | Цитировать Сообщить модератору
 Re: реорганизация и перестроение индекса 24на7  [new]
Crimean
Member

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

а что ему еще остается делать? самому "догадаться" и шринкнуться? нет, не умеет он этого
2 окт 14, 15:39    [16651107]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить