Скрипт для партицирования в Юконе нужен ?

[Torin]

В процессе работы появился ряд артефактов. Например, скрипты для Юкона, которые создают функцию и схему партициирования по DATAAREAID и скрипт, который пробегается по всей базе и раскидывает объекты по партициям - ручками замахаться, а Аксапта не может.
В результате, производительность на мультикомпаненых базах значительно возрастает - особенно снижается влияние конткуретности.
Схема для Аксапты остается прозрачной - она даже не замечает, что то-то изменилось
Короче, кому то надо ?

[raz]

Чего спрашивать! Конечно выкладывай!

[Torin]

Ага, ну вот.
Сразу, чтобы бы ввести понимание - работает только в Юконе (уровень совместимости неважен), причем Энтерпрайз версии (Или Девелопер - там где партиционирование разрешенно).
Сначала надо создать файловые группы с файлами данных (у меня DATAGROUP1, DATAGROUP2 и т.д.)
Потом функцию и схему

CREATE PARTITION FUNCTION axDataAreaPF (varchar(3))
AS RANGE LEFT FOR VALUES ('mce', 'mso', 'mea', 'mpc', 'pal', 'pgt', 'psa','pgh');
GO

CREATE PARTITION SCHEME axDataAreaPS
AS PARTITION axDataAreaPF
TO ([DATAGROUP1], [DATAGROUP2], [DATAGROUP3], [DATAGROUP4], [DATAGROUP5], [DATAGROUP6], [DATAGROUP7], [DATAGROUP8], [DATAGROUP9], [DATAGROUP10]);
GO

Где у нас 'mce', 'mso', 'mea', 'mpc', 'pal', 'pgt', 'psa','pgh' - это разные DataArea
По особенностям операторов лучьше почитать в хелпе - есть нюансы с LEFT и лишней группой в схеме.

Далее, само интересное. Этим скриптом я перебираю объекты, проверяю, что они еще не партициионированны и, собственно, do it. Скрипт можно запускать много раз - если ничего не изменилось или не было ошибок, то и делать ничего не будет.

USE AX64SP3;
SET LOCK_TIMEOUT -1;
GO

DECLARE @table_name varchar(100), @table_id int, @table_partition_type varchar(2), @table_partition_name  varchar(100)
DECLARE @index_name varchar(100), @index_type tinyint, @index_is_unique tinyint, @index_is_primary_key tinyint, @index_is_disabled tinyint, @index_allow_row_locks tinyint, @index_allow_page_locks tinyint, @index_partition_type varchar(2), @index_partition_name  varchar(100)
DECLARE @column_name varchar(100), @column_is_descending_key  tinyint, @column_first tinyint
DECLARE @SQL as nvarchar(4000)
	
DECLARE table_cursor CURSOR FOR
	select DISTINCT o.name, o.object_id, ds.type as type, ISNULL(pf.name, ds.name) as name 
	from 
		sys.partitions AS p
		INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
		INNER JOIN sys.objects AS o ON o.object_id = i.object_id
		INNER JOIN sys.data_spaces ds ON i.data_space_id=ds.data_space_id
		LEFT JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
		LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
	where o.type='U' and i.type=0 and SCHEMA_NAME(o.schema_id) = 'dbo'
	ORDER BY o.name

OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @table_name, @table_id, @table_partition_type, @table_partition_name

WHILE @@FETCH_STATUS = 0
BEGIN
	IF 
		EXISTS(select [name] FROM sys.columns WHERE object_id = @table_id AND UPPER(name) = 'DATAAREAID')
		AND NOT @table_name LIKE 'SYS%'
	BEGIN
		PRINT '--' + @table_name
		IF NOT EXISTS (Select [name] FROM sys.indexes WHERE [type] = 1 AND object_id = @table_id) AND @table_partition_type = 'FG'
		BEGIN
			PRINT '----MOVE TO PARTITION'
			SELECT @SQL = 'CREATE CLUSTERED INDEX tmp_cluster ON ' +@table_name+ '(DATAAREAID) ON axDataAreaPS(DATAAREAID);'
			--PRINT @SQL
			EXEC(@SQL)
			
			SELECT @SQL = 'DROP INDEX tmp_cluster ON ' + @table_name + ';'
			--PRINT @SQL
			EXEC(@SQL)
		END

		Declare index_cursor CURSOR FOR
			Select i.name, i.type, i.is_unique,  i.is_primary_key, i.is_disabled, i.allow_row_locks, i.allow_page_locks, ds.type as type, ISNULL(pf.name, ds.name) as name 
			FROM sys.partitions AS p
				INNER JOIN sys.indexes AS i ON p.object_id = i.object_id AND p.index_id = i.index_id
				INNER JOIN sys.objects AS o ON o.object_id = i.object_id
				INNER JOIN sys.data_spaces ds ON i.data_space_id=ds.data_space_id
				LEFT JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
				LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
			WHERE i.object_id = @table_id and i.type>0
			Order by i.type

		OPEN index_cursor
		FETCH NEXT FROM index_cursor INTO @index_name, @index_type, @index_is_unique, @index_is_primary_key, @index_is_disabled, @index_allow_row_locks, @index_allow_page_locks, @index_partition_type, @index_partition_name

		WHILE @@FETCH_STATUS = 0
		BEGIN
			IF @index_partition_type = 'FG'
			BEGIN
				PRINT '----' + @index_name
				SELECT @SQL = 
				CASE WHEN @index_is_primary_key = 1 THEN 
					'ALTER TABLE ' + @table_name + ' DROP CONSTRAINT ' + @index_name +' WITH (ONLINE = OFF); ' 
					+  ' ALTER TABLE ' + @table_name + ' WITH NOCHECK ADD CONSTRAINT ' + @index_name + ' PRIMARY KEY ' +CASE WHEN @index_type = 1 THEN ' CLUSTERED ' ELSE ' ' END 
				ELSE
					'CREATE ' + CASE WHEN @index_type = 1 THEN 'CLUSTERED' WHEN @index_is_unique =1 THEN  'UNIQUE' ELSE '' END +' INDEX ' + @index_name + ' ON '+ @table_name
				END

				Declare column_cursor CURSOR FOR
				Select t3.[name], t2.is_descending_key 
				FROM	sys.indexes t1
						INNER JOIN sys.index_columns t2 ON t1.object_id=t2.object_id AND t1.index_id = t2.index_id
						INNER JOIN sys.columns t3 ON t1.object_id=t3.object_id AND t2.column_id = t3.column_id
				WHERE t1.[name] =@index_name and t1.object_id = @table_id
				Order by t2.index_column_id
				
				SELECT @SQL = @SQL + '('
				SELECT @column_first = 1

				OPEN column_cursor
				FETCH NEXT FROM column_cursor INTO @column_name, @column_is_descending_key
				WHILE @@FETCH_STATUS = 0
				BEGIN
					SELECT @SQL = @SQL + CASE WHEN @column_first=1 THEN '' ELSE ', ' END + @column_name + CASE WHEN @column_is_descending_key = 1 THEN ' DESC' ELSE ' ' END
					SELECT @column_first = 0
					FETCH NEXT FROM column_cursor  INTO @column_name, @column_is_descending_key
				END
				CLOSE column_cursor
				DEALLOCATE column_cursor
				SELECT @SQL = @SQL +
					CASE WHEN @index_is_primary_key = 1 THEN 
						') WITH (FILLFACTOR = 75, ONLINE = OFF) ON axDataAreaPS(DATAAREAID);'
					ELSE
						') WITH (DROP_EXISTING = ON, ONLINE = OFF) ON axDataAreaPS(DATAAREAID);'
					END
				--PRINT @SQL
				EXEC(@SQL)
			END
		FETCH NEXT FROM index_cursor INTO @index_name, @index_type, @index_is_unique, @index_is_primary_key, @index_is_disabled, @index_allow_row_locks, @index_allow_page_locks, @index_partition_type, @index_partition_name
		END
		CLOSE index_cursor
		DEALLOCATE index_cursor
	PRINT 'GO' 	
	END
	FETCH NEXT FROM table_cursor INTO @table_name, @table_id, @table_partition_type, @table_partition_name
END

CLOSE table_cursor
DEALLOCATE table_cursor

Вместо EXEC можно расскомментировать PRINT - тогда результатом работы будет SQL скрипт, который можно разглядывать и выполнять по частям ручками.
AS IS, конечно

[fomenka]

Цитата:

Сообщение от Torin

Где у нас 'mce', 'mso', 'mea', 'mpc', 'pal', 'pgt', 'psa','pgh' - это разные DataArea

А виртуальные компании как обрабатывать? Так же?

[Vadik]

Перенесу-ка в Полезные материалы

[PavelX]

Хоть тема и древняя, но все же подниму вопрос...

Может ли кто либо пояснить как при использовании данного скрипта (да и партицирования вообще) обходится проблема синхронизации таблиц Аксаптой?

Провел следующий эксперимент (AX 2009, SQL Server 2008 R2):
На таблице InventTrans настроил простенькое партицирование по полю CreatedDateTime в сиквеле. 1,2 миллиона строк разбилось на разделы за 6 минут, все хорошо. Далее при попытке синхронизировать InventTrans из Аксапты, сначала предлагается удалить созданный индекс на таблице и затем при попытке это сделать вываливается ошибка:


То есть получается что синхронизация партицированной таблицы в принципе не возможна? Или я что то делаю не так?

Есть ли у кого либо опыт по партицированию таблиц Аксапты? Поделитесь пожалуйста. Также буду признателен за любые идеи на эту тему.

Спасибо!

[egorych]

Сделал партицирование на табличке sysdatabaselog - по полю table
По остальным - ну пока не вижу смысла. Делать партиции в таблице до 10млн. строк ИМХО смысла не имеет.
Правда все это на Оракле.

[PavelX]

Спасибо за информацию.

Похоже все оказалось проще. По всей видимости необходимо чтобы поле по которому производится партицирование (в моем случае InventTrans.CreatedDateTime) входило в первичный ключ этой таблицы InventTrans. Как только я его добавил - синхронизация выполнилась без ошибок.

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

[Damn]

Цитата:

Сообщение от PavelX

По всей видимости необходимо чтобы поле по которому производится партицирование (в моем случае InventTrans.CreatedDateTime) входило в первичный ключ этой таблицы InventTrans. Как только я его добавил - синхронизация выполнилась без ошибок.

Скорее всего не в первичный ключ, а в кластерный должно входить поле, по которому идёт секционирование.
Но у меня другой вопрос - у вас хорошо проходит реиндексация InventTrans после разбиения её по секциям ?
Я обнаружил что аксапта во время реиндексации в секционированной таблице пересоздаёт все индексы (кроме кластерного конечно) и, если раньше они хранились в одной файловой группе, то после реиндексации размазываются по нескольким файловым группам. Что приводит к тому что просто открыть таблицу в обозревателе таблиц становится практически невозможно. Я проводил тестирование на SysDatabaseLog, секционированной по CreatedDateTime. После реиндексации индекса TABLERECIDIDX таблица в обозревателе стала открываться по 30-40 минут. До реиндексации это происходило мгновенно.
Приходится в базе руками удалять индекс TABLERECIDIDX и создавать его снова, только в скрипте явно указывать в какой файловой группе он должен сидеть. При этом полностью согласен, что синхронизация таблицы проходит без ошибок. Именно синхронизация, но не реиндексация.

[vanokh]

Цитата:

Сообщение от Damn

Я проводил тестирование на SysDatabaseLog, секционированной по CreatedDateTime.

А можно подробности? Сколько записей в таблице, размеры индексов? На сколько групп разбивалось и что еще стало быстрее/медленнее?)

И кстати, индекс TABLERECIDIDX как раз и есть кластерный. Если руками реиндексировать, будет одна большая группа с кластерным индексом и несколько других, содержащих оставшиеся. Например, для таблицы в 5.5Гб будет одна группа на 5Гб с кластерным индексом и 500Мб индекс RECID, размазанный по остальным группам... Тогда смысл в разбиении, по-моему, теряется)

Или вы создавали другой кластерный индекс?

[Damn]

Цитата:

Сообщение от vanokh

Или вы создавали другой кластерный индекс?

Точно, я создал кластерный индекс по полю CreatedDataTime, а TableRecIdIdx сделал обычным индексом, к тому же удалил из него поле CreatedDateTime. И свойство SaveDataPerCompany у таблицы выставил в No. При открытии таблицы SysDatabaseLog в обозревателе таблиц аксапта пытается отсортировать её по полям из индекса TableRecIdIdx. Вот тут-то и происходит вся загвоздка, описанная мною в предыдущем посте.
В таблице около 30 миллионов записей, разбил я её на две не сильно отличающиеся по размеру части.
Я кстати так и не понял, у вас-то таблица SysDatabaseLog секционирована ?

[vanokh]

Цитата:

Сообщение от Damn

Я кстати так и не понял, у вас-то таблица SysDatabaseLog секционирована ?

Нет) Я, честно говоря, не вижу пока что надобности в секционировании. Нормальный сервер + raid 10 отлично справляются (база около 200г). Поэтому и спрашиваю, что вам дало секционирование?)

У вас увидел только минус в виде медленной сортировки по "размазанному" индексу

Кстати, мы не пользуемся реиндексацией из аксапты - работает sql-скрипт, который перестраивает тролько "проблемные" (по уровню дефрагментированности) индексы. Там индекс перестраивается (REBUILD/REORGANIZE) на каждой секции отдельно.

[Damn]

Цитата:

Сообщение от vanokh

Поэтому и спрашиваю, что вам дало секционирование?)

У вас увидел только минус в виде медленной сортировки по "размазанному" индексу

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