Задача проектирования структуры БД является ключевой с точки зрения производительности и масштабируемости ИТ систем. Под структурой можно также понимать структуру индексов. В зависимости от того насколько правильно расставлены индексы один и тот же запрос может формироваться различное время. Соотношение во времени выполнения может составлять порядки. Кроме этого, совокупная нагрузка на серверные ресурсы будет существенно отличатся. Именно поэтому технология правильной расстановки индексов является одной из приоритетных в общей технологии оптимизации.

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

---

Разместить статью на этом сайте

---

Рассмотрим среду 1С. В системе 1С невозможно стандартными средствами добавлять индексы. Это с одной стороны упрощает разработку. На начальном этапе не нужно задумываться, что такое индексы вообще. Ограничения по фильтрации и сортировке экранных форм(например в справочниках только сортировка по коду, наименованию или реквизитам с признаком отбора – т.е. по тем полям где есть по умолчанию индексы ) позволяют довольно длительный интервал времени после внедрения не беспокоится о проблеме оптимизации индексов. Но здесь есть и отрицательный момент. При увеличении объемов БД запросы с учетом стандартных индексов 1С отрабатывают все хуже и хуже. Нагрузка на сервер и время отчетов увеличивается до тех пор, пока эти значения не принимают критической величины. Как правило, после этого принимается решение об коренной перестройке структуры объектов 1С либо об апгрейде серверного оборудования. И первый, и второй вариант требуют существенных затрат. Например, иногда структура объектов в текущей реализации выглядит вполне нормальной, и вполне устраивала разработчиков, если бы была возможность, например, добавить индекс по шапке документа. Классическим примером здесь можно привести ситуацию, когда структура документов завязана иерархически и реквизит с документом родителем хранится в шапке документа. В данном случае конечно можно вынести этот реквизит в общие реквизиты но это будет неэффективное решение как с точки зрения структуры запросов так и с точки зрения общей производительности. (зачем всем документам хранить реквизит который нужен только для одного типа документов?).

Не смотря на ограниченность управления индексами в 1С 7,7. решение существует. Один из вариантов это использование процедуры обхода верификации. У этого метода есть все же определенные недостатки связанные с удобствами администрирования.

if app_name()=1CV7 /* это проверка какое приложение вызывает процедуру*/
begin /*этот вариант срабатывает если запущена процедура из 1С */
	SELECT
		TABLE_QUALIFIER,
		TABLE_OWNER,
		TABLE_NAME,
		NON_UNIQUE,
		INDEX_QUALIFIER,
		INDEX_NAME,
		TYPE,
		SEQ_IN_INDEX,
		COLUMN_NAME,
		COLLATION,
		CARDINALITY,
		PAGES,
		FILTER_CONDITION = convert(varchar(128),null)
	FROM #TmpIndex
	WHERE
		(INDEX_NAME like @index_name /* If matching name */
		or INDEX_NAME is null)		/* If SQL_TABLE_STAT row */
		and (substring(INDEX_NAME,1,3)<>P1C or INDEX_NAME is NULL)
		/*вот это проверка на префикс, если он начинается на P1C*/
		/*то в результат выполнения процедуры не попадает*/
	ORDER BY 4, 7, 6, 8
end
else 
begin /* это старый вариант реализации, стандартный*/
	SELECT
		TABLE_QUALIFIER,
		TABLE_OWNER,
		TABLE_NAME,
		NON_UNIQUE,
		INDEX_QUALIFIER,
		INDEX_NAME,
		TYPE,
		SEQ_IN_INDEX,
		COLUMN_NAME,
		COLLATION,
		CARDINALITY,
		PAGES,
		FILTER_CONDITION = convert(varchar(128),null)
	FROM #TmpIndex
	WHERE
		(INDEX_NAME like @index_name /* If matching name */
		or INDEX_NAME is null)		/* If SQL_TABLE_STAT row */
end

Предлагаем вашему вниманию следующую технологию обхода процедуры верификации с целью возможного расширения индексов. Сразу хочу отметить, что в 99-и процентов случаев индексы от 1С по умолчанию работают эффективно (конечно, кроме тех случаев, когда бездумно ставят сортировку и отбор на реквизиты). Поэтому варианты с удалением индексов 1С мы не рассматриваем.

После изменения процедуры sp_statistics мы можем беспрепятственно добавлять индексы. Эффект от правильной индексов может быть более чем большим. Например, один из отчетов который формировался ранее порядка 7-и часов, стал формироваться порядка 20-и минут. Это и не удивительно так по миллионной таблице осуществлялось множество запросов с фильтром по реквизиту без индекса, в результате чего осуществлялся full scan. Кроме длительно времени выполнения запроса еще большим минусом является в момент выполнения запроса большая загрузка сервера.

Теперь кратко рассмотрим, как пользоваться средством SQL-сервера для оптимизации индексов – Index Tuning wizard. Запустим профайлер и выберем пункт «Index Tuning wizard».

Откроется диалоговое окно Index Tuning wizard.

Перейдем на следующую страницу помощника. Откроется диалог авторизации.

Необходимо выбрать сервер и указать логин и пароль для входа.

После будет нужно выбрать анализируемую базу данных и режим анализа. Важно оставить пометку «keep all existing indexes» включенной, чтобы помощник не пытался удалить существующие индексы, даже если они не оптимальны. Режим «Fast» не следует использовать из-за его неточности. Режим «Thorough» может занять слишком много времени для анализа. Поэтому используем золотую середину. Переходим к следующему окну.

Здесь мы должны указать трассу, которую мы будем анализировать в виде файла на диске или таблицы в базе данных. Вместо трассы можно указать файл, содержащий SQL-запрос, который мы хотим проанализировать. После указания трассы нужно обязательно посмотреть «Advanced Options».

Это диалоговое окно содержит 3 важных параметра. Первый из них, важнейший, количество запросов для анализа. По-умолчанию там стоит цифра 200. Это означает, что из трассы случайным образом будут выбраны 200 запросов, которые и будут проанализированы. Если снять пометку, то будет проанализирована вся трасса. Это очень важный момент. Важно, чтобы анализировалась трасса, которая содержит как можно более репрезентативную выборку запросов, но не содержит в себе множества легковесных запросов. Если трасса небольшая, то можно снять пометку, чтобы анализировались все запросы. Второй параметр – это максимальный объем места на диске для индексов, которые будут добавляться. Индексы, которые превышают этот объем, добавляться не будут. Третий параметр – ограничение на количество колонок, входящих в индекс. После выбора значений и возврата в основную форму, переходим к следующей странице помощника.

На этой странице нам предстоит выбрать таблицы, индексы к которым будут анализироваться. Рекомендуется выбрать важные таблицы, остальные оставить не помеченными. Также, с учетом будущего роста базы можно установить значения в колонке «Projected Rows», указать, какое количество строк в данной таблице планируется с ростом базы. После нажатия на кнопку «Далее», будет осуществляться анализ.

Когда процесс анализа будет завершен, мы увидим таблицу результатов анализа.

В этой таблице будут представлены и существующие индексы, и новые, если помощник посчитал, что они необходимы. Новые индексы обозначены специальным значком (см. индекс с именем «RA11583»). Ниже в тексте написано, сколько процентов прироста производительности оттестированных запросов повлечет за собой создание рекомендуемых индексов. Перейдем дальше.

Здесь нам предстоит выбрать, что делать с полученными данными. Предлагается два варианта: применить изменения (сразу или в назначенное время), или сохранить сгенерированный SQL-скрипт в файл. Мы выбираем второй вариант, так как автоматически сгенерированные имена индексов нам не подходят, а также, потому что потом можно будет посмотреть на рекомендации и выбрать, что из них делать, а что нет. Кроме новых индексов может быть предложено создать и соответствующую статистику. Вот как выглядит пример файла рекомендаций.

/* Created by: Index Tuning Wizard	*/
/* Date: 18.05.2005			*/
/* Time: 10:50:10			*/
/* Server Name: VLADA			*/
/* Database Name: TestMain		*/
/* Workload File Name: D:WorkSQLMSSQLTRACEТрассировка.trc */
USE [TestMain] 
go
SET QUOTED_IDENTIFIER ON 
SET ARITHABORT ON 
SET CONCAT_NULL_YIELDS_NULL ON 
SET ANSI_NULLS ON 
SET ANSI_PADDING ON 
SET ANSI_WARNINGS ON 
SET NUMERIC_ROUNDABORT OFF 
go
DECLARE @bErrors as bit
BEGIN TRANSACTION
SET @bErrors = 0
CREATE NONCLUSTERED INDEX [RA11583] ON [dbo].[RA1158] ([SP1160] ASC )
IF( @@error <> 0 ) SET @bErrors = 1
IF( @bErrors = 0 )
 COMMIT TRANSACTION
ELSE
 ROLLBACK TRANSACTION
BEGIN TRANSACTION
SET @bErrors = 0
CREATE NONCLUSTERED INDEX [_1SJOURN10] ON [dbo].[_1SJOURN]
([IDDOCDEF] ASC, [DATE_TIME_IDDOC] ASC, [CLOSED] ASC )
IF( @@error <> 0 ) SET @bErrors = 1
IF( @bErrors = 0 )
 COMMIT TRANSACTION
ELSE
 ROLLBACK TRANSACTION
/* Statistics to support recommendations */
CREATE STATISTICS [hind_874486194_6A_1A] ON [dbo].[RA1158]
			([SP1160], [IDDOC])
CREATE STATISTICS [hind_429960608_9A_4A] ON [dbo].[_1SJOURN]
			([CLOSED], [IDDOCDEF])
CREATE STATISTICS [hind_429960608_9A_3A] ON [dbo].[_1SJOURN]
			([CLOSED], [IDDOC])
CREATE STATISTICS [hind_429960608_6A_4A] ON [dbo].[_1SJOURN]
			([DATE_TIME_IDDOC], [IDDOCDEF])
CREATE STATISTICS [hind_429960608_3A_4A] ON [dbo].[_1SJOURN]
			([IDDOC], [IDDOCDEF])
CREATE STATISTICS [hind_429960608_6A_3A] ON [dbo].[_1SJOURN]
			([DATE_TIME_IDDOC], [IDDOC])
CREATE STATISTICS [hind_429960608_9A_6A] ON [dbo].[_1SJOURN]
			([CLOSED], [DATE_TIME_IDDOC])
CREATE STATISTICS [hind_429960608_3A_6A] ON [dbo].[_1SJOURN]
			([IDDOC], [DATE_TIME_IDDOC])
CREATE STATISTICS [hind_429960608_3A_9A] ON [dbo].[_1SJOURN]
			([IDDOC], [CLOSED])
CREATE STATISTICS [hind_429960608_6A_9A_3A] ON [dbo].[_1SJOURN]
			([DATE_TIME_IDDOC], [CLOSED], [IDDOC])
CREATE STATISTICS [hind_429960608_4A_6A_3A] ON [dbo].[_1SJOURN]
			([IDDOCDEF], [DATE_TIME_IDDOC], [IDDOC])
CREATE STATISTICS [hind_429960608_6A_9A_4A] ON [dbo].[_1SJOURN]
			([DATE_TIME_IDDOC], [CLOSED], [IDDOCDEF])

Полный текст измененной процедуры sp_statistics для SQL-2000 sp3:

/*	Procedure for 8.0 server */
ALTER PROCEDURE sp_statistics (
				 @table_name		sysname,
				 @table_owner		sysname = null,
				 @table_qualifier	sysname = null,
				 @index_name		sysname = %,
				 @is_unique 		char(1) = N,
				 @accuracy			char(1) = Q)
AS
	set nocount on
	DECLARE @indid				int
	DECLARE @lastindid			int
	DECLARE @table_id			int
	DECLARE @full_table_name	nvarchar(257)
	create table #TmpIndex(
		TABLE_QUALIFIER sysname collate database_default NULL,
		TABLE_OWNER 	sysname collate database_default NULL,
		TABLE_NAME		sysname collate database_default NOT NULL,
		INDEX_QUALIFIER sysname collate database_default null,
		INDEX_NAME		sysname collate database_default null,
		NON_UNIQUE		smallint null,
		TYPE			smallint NOT NULL,
		SEQ_IN_INDEX	smallint null,
		COLUMN_NAME 	sysname collate database_default null,
		COLLATION		char(1) collate database_default null,
		index_id		int null,
		CARDINALITY 	int null,
		PAGES			int null,
		status			int NOT NULL)
	if @table_qualifier is not null
	begin
		if db_name() <> @table_qualifier
		begin	/* If qualifier doesnt match current database */
			raiserror (15250, -1,-1)
			return
		end
	end
	if @accuracy not in (Q,E)
		begin
			raiserror (15251,-1,-1,accuracy,Q or E)
			return
		end
	if @table_owner is null
	begin	/* If unqualified table name */
		SELECT @full_table_name = quotename(@table_name)
	end
	else
	begin	/* Qualified table name */
		if @table_owner = 
		begin	/* If empty owner name */
			SELECT @full_table_name = quotename(@table_owner)
		end
		else
		begin
			SELECT @full_table_name = quotename(@table_owner) +
				. + quotename(@table_name)
		end
	end
	/*	Get Object ID */
	SELECT @table_id = object_id(@full_table_name)
	/*	Start at lowest index id */
	SELECT @indid = min(indid) FROM sysindexes
	WHERE not (@table_id is null)
		AND id = @table_id
		AND indid > 0
		AND indid < 255
	/* Create a temp table to correct the ordinal position of the columns */
	create table #TmpColumns
	(ordinal int identity(1,1),
	 colid smallint not null)
	/* Load columns into the temp table */
	insert into #TmpColumns (colid)
	select c.colid
	from syscolumns c
	where c.id = @table_id
	order by c.colid
	
	WHILE @indid is not NULL
	BEGIN
		INSERT #TmpIndex	/* Add all columns that are in index */
		SELECT
			DB_NAME(),				/* TABLE_QUALIFIER*/
			USER_NAME(o.uid),			/* TABLE_OWNER*/
			o.name, 				/* TABLE_NAME*/
			o.name, 				/* INDEX_QUALIFIER*/
			x.name, 				/* INDEX_NAME*/
			case					/* NON_UNIQUE*/
				WHEN x.status&2 <> 2 then 1	/* Nonunique index*/
				else 0				/* Unique index*/
			end,
			case					/* TYPE*/
				when @indid > 1 then 3		/* Non-Clustered*/
				else 1				/* Clustered index*/
			end,
			tc.ordinal,				/* SEQ_IN_INDEX*/
			INDEX_COL(@full_table_name, indid, tc.ordinal),/* COLUMN_NAME*/
			A,					/* COLLATION*/
			@indid, 				/* index_id*/
			case					/* CARDINALITY*/
				when @indid > 1 then NULL	/* Non-Clustered*/
				else x.rows 			/* Clustered index*/
			end,
			case					/* PAGES*/
				when @indid > 1 then NULL	/* Non-Clustered*/
				else x.dpages			/* Clustered index*/
			end,
			x.status				/* status*/
		FROM sysindexes x, syscolumns c, sysobjects o, #TmpColumns tc
		WHERE
			not (@table_id is null)
			AND x.id = @table_id
			AND x.id = o.id
			AND x.id = c.id
			AND tc.colid = c.colid
			AND tc.ordinal < keycnt+(x.status&18)/18
			/* all but Unique Clust indices have an extra key */
			AND INDEX_COL(@full_table_name, indid, tc.ordinal) IS NOT NULL
			AND indid = @indid
			AND (x.status&2 = 2
			OR @is_unique <> Y)
			AND (x.status&32) = 0
		/*
		**	 Now move @indid to the next index.
		*/
		SELECT @lastindid = @indid
		SELECT @indid = NULL
		SELECT @indid = min(indid)
		FROM sysindexes
		WHERE not (@table_id is null)
			AND id = @table_id
			AND indid > @lastindid
			AND indid < 255
 END
	/* now add row for table statistics */
	INSERT #TmpIndex
	SELECT
		DB_NAME(),			/* TABLE_QUALIFIER*/
		USER_NAME(o.uid),		/* TABLE_OWNER	 */
		o.name, 			/* TABLE_NAME	 */
		null,				/* INDEX_QUALIFIER*/
		null,				/* INDEX_NAME	 */
		null,				/* NON_UNIQUE	 */
		0,				/* SQL_TABLE_STAT */
		null,				/* SEQ_IN_INDEX	 */
		null,				/* COLUMN_NAME	 */
		null,				/* COLLATION	 */
		0,				/* index_id 	 */
		x.rows, 			/* CARDINALITY	 */
		x.dpages,			/* PAGES	 */
		0				/* status	 */
	FROM sysindexes x, sysobjects o
	WHERE not (@table_id is null)
		AND o.id = @table_id
		AND x.id = o.id
		AND (x.indid = 0 or x.indid = 1)/*If there are no indexes*/
						/*then table stats are in*/
						/*a row with indid =0*/
	if app_name()=1CV7 
	begin
		SELECT
			TABLE_QUALIFIER,
			TABLE_OWNER,
			TABLE_NAME,
			NON_UNIQUE,
			INDEX_QUALIFIER,
			INDEX_NAME,
			TYPE,
			SEQ_IN_INDEX,
			COLUMN_NAME,
			COLLATION,
			CARDINALITY,
			PAGES,
			FILTER_CONDITION = convert(varchar(128),null)
		FROM #TmpIndex
		WHERE
			(INDEX_NAME like @index_name /* If matching name*/
			or INDEX_NAME is null)	/* If SQL_TABLE_STAT row*/
			and (substring(INDEX_NAME,1,3)<>P1C or INDEX_NAME is NULL)
		ORDER BY 4, 7, 6, 8
	end
	else 
	begin 
		SELECT
			TABLE_QUALIFIER,
			TABLE_OWNER,
			TABLE_NAME,
			NON_UNIQUE,
			INDEX_QUALIFIER,
			INDEX_NAME,
			TYPE,
			SEQ_IN_INDEX,
			COLUMN_NAME,
			COLLATION,
			CARDINALITY,
			PAGES,
			FILTER_CONDITION = convert(varchar(128),null)
		FROM #TmpIndex
		WHERE
			(INDEX_NAME like @index_name /* If matching name */
			or INDEX_NAME is null)/* If SQL_TABLE_STAT row */
	end
	DROP TABLE #TmpIndex, #TmpColumns
GO

Компания «SoftPoint» предлагает вашему вниманию продукт, который позволит облегчить выполнение задачи оптимизации индексов. Кроме того, компания «SoftPoint» имеет штат высококлассных специалистов, которые проводят работы, в том числе и по оптимизации индексов, которые могут качественно выполнить эту работу.