| ||||||||||||||||
| ||||||||||||||||
| ||||||||||||||||
Управление индексами в 1С-SQL Задача проектирования структуры БД является ключевой с точки зрения производительности и масштабируемости ИТ систем. Под структурой можно также понимать структуру индексов. В зависимости от того насколько правильно расставлены индексы один и тот же запрос может формироваться различное время. Соотношение во времени выполнения может составлять порядки. Кроме этого, совокупная нагрузка на серверные ресурсы будет существенно отличатся. Именно поэтому технология правильной расстановки индексов является одной из приоритетных в общей технологии оптимизации. Технология создания индексов отнюдь не тривиальна. Тем не менее, существуют некоторые относительно не сложные походы. Также фирма Microsoft предлагает специализированный мастер эффективных настроек индексов. Концепция его предполагает снятия истории запросов (трасс) и дальнейший анализ этих запросов этим мастером после чего будет предложен отчет в котором будет информация по оптимизации индексов. К данным этого отчета надо относится, тем не менее, с некоторой опаской и окончательные решения по созданию того или иного индекса принимать только после экспертного анализа. Рассмотрим среду 1С. В системе 1С невозможно стандартными средствами добавлять индексы. Это с одной стороны упрощает разработку. На начальном этапе не нужно задумываться, что такое индексы вообще. Ограничения по фильтрации и сортировке экранных форм(например в справочниках только сортировка по коду, наименованию или реквизитам с признаком отбора – т.е. по тем полям где есть по умолчанию индексы ) позволяют довольно длительный интервал времени после внедрения не беспокоится о проблеме оптимизации индексов. Но здесь есть и отрицательный момент. При увеличении объемов БД запросы с учетом стандартных индексов 1С отрабатывают все хуже и хуже. Нагрузка на сервер и время отчетов увеличивается до тех пор, пока эти значения не принимают критической величины. Как правило, после этого принимается решение об коренной перестройке структуры объектов 1С либо об апгрейде серверного оборудования. И первый, и второй вариант требуют существенных затрат. Например, иногда структура объектов в текущей реализации выглядит вполне нормальной, и вполне устраивала разработчиков, если бы была возможность, например, добавить индекс по шапке документа. Классическим примером здесь можно привести ситуацию, когда структура документов завязана иерархически и реквизит с документом родителем хранится в шапке документа. В данном случае конечно можно вынести этот реквизит в общие реквизиты но это будет неэффективное решение как с точки зрения структуры запросов так и с точки зрения общей производительности. (зачем всем документам хранить реквизит который нужен только для одного типа документов?). Не смотря на ограниченность управления индексами в 1С
7,7. решение существует. Один из вариантов это
использование процедуры обхода верификации. У этого метода есть все же определенные
недостатки связанные с удобствами администрирования.
Предлагаем вашему вниманию следующую технологию обхода процедуры верификации с целью возможного расширения индексов. Сразу хочу отметить, что в 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 doesn't 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» имеет штат высококлассных специалистов, которые проводят работы, в том числе и по оптимизации индексов, которые могут качественно выполнить эту работу. |
|
| ||||||||||||||||
|