Как переиндексировать базу данных 1C+MS-SQL?
Иногда (крайне редко) возникает необходимость переиндексировать пользовательские таблицы базы данных 1C+MS-SQL . Так как конфигуратор 1С этого не позволяет, мы можем запустить хранимую процедуру _1sp_DBReindex , либо выполнить аналогичный код в SQL Query Analizer ‘е, если такой процедуры в базе нет (не 1С база):
DECLARE @TableName char (32)
DECLARE SysCur CURSOR FOR SELECT name FROM sysobjects WHERE type= ‘U’
OPEN SysCur
FETCH NEXT FROM SysCur INTO @TableName
WHILE @@FETCH_STATUS =0 BEGIN
DBCC DBREINDEX(@TableName)
FETCH NEXT FROM SysCur INTO @TableName
END
CLOSE SysCur
DEALLOCATE SysCur
Массовое перестроение индексов в Microsoft SQL Server (переиндексация БД)
Всем привет! Сейчас мы с Вами напишем процедуру на языке Transact-SQL, которая будет осуществлять массовое перестроение индексов или их реорганизацию в зависимости от степени фрагментации этих индексов.
Ранее мы с Вами поговорили о том, что такое индексы, зачем и как их обслуживать и проектировать, при этом мы рассматривали примеры перестроения и реорганизации отдельно взятых индексов, так сказать вручную, а сегодня мы автоматизируем этот процесс путем написания специальной процедуры переиндексации всех пользовательских индексов в базе данных, другими словами, немного автоматизируем процесс обслуживания, в результате чего вся работа будет заключаться в запуске одной процедуры и то ее запуск также можно запланировать и запускать автоматически.
Итак, нам понадобится инструмент разработки и запуска сценариев на T-SQL, в Microsoft SQL Server таким инструментом является среда SQL Server Management Studio (SSMS), но Вы это, конечно же, уже должны знать.
Открываем Management Studio, создаем запрос (сценарий) и вставляем следующий код, он создает процедуру (я ее назвал SP_ReIndex), которая и будет перестраивать или реорганизовывать индексы в зависимости от степени их фрагментации. Microsoft рекомендует:
- Если степень фрагментации менее 5%, индекс перестраивать или реорганизовывать не нужно;
- Если степень фрагментации от 5 до 30%, лучше выполнять реорганизацию индекса;
- Если степень фрагментации более 30%, лучше выполнять перестроение индекса.
Мы так и поступим.
Код процедуры я прокомментировал, чтобы Вы понимали какой участок кода что делает.
Примечание! В качестве SQL сервера у меня выступает версия Microsoft SQL Server 2016 Express.
Код процедуры для массового перестроения индексов
CREATE PROCEDURE SP_ReIndex (@DbId SMALLINT = NULL) AS BEGIN /* Процедура для массовой переиндексации БД. */ --Запрещаем вывод количества строк --Для того чтобы выводилась только интересующая нас информация SET NOCOUNT ON; --Табличная переменная для хранения названий объектов (индексов, таблиц) для обслуживания DECLARE @IndexTmpTable TABLE (Id INT IDENTITY(1,1) PRIMARY KEY, SchemaName SYSNAME, TableName SYSNAME, IndexName SYSNAME, AvgFrag FLOAT); --Вспомогательные переменные DECLARE @RowNumber INT = 1, @CntRows INT, @CntReorganize INT = 0, @CntRebuild INT = 0; DECLARE @SchemaName SYSNAME, @TableName SYSNAME, @IndexName SYSNAME, @AvgFrag FLOAT; DECLARE @Command VARCHAR(8000); --Идентификатор БД (можно передать во входящем параметре, по умолчанию текущая) SELECT @DbId = COALESCE(@DbId, DB_ID()); --Определяем степень фрагментации индексов на основе системной табличной функции --sys.dm_db_index_physical_stats, а также название индексов и соответствующих таблиц INSERT INTO @IndexTmpTable SELECT Sch.name AS SchemaName, Obj.name AS TableName, Inx.name AS IndexName, AvgFrag.avg_fragmentation_in_percent AS Fragmentation FROM sys.dm_db_index_physical_stats (@DbId, NULL, NULL, NULL, NULL) AS AvgFrag LEFT JOIN sys.indexes AS Inx ON AvgFrag.object_id = Inx.object_id AND AvgFrag.index_id = Inx.index_id LEFT JOIN sys.objects AS Obj ON AvgFrag.object_id = Obj.object_id LEFT JOIN sys.schemas AS Sch ON Obj.schema_id = Sch.schema_id WHERE AvgFrag.index_id > 0 AND AvgFrag.avg_fragmentation_in_percent > 5 --5 - это минимальная степень фрагментации индекса --Количество строк для обработки SELECT @CntRows = COUNT(*) FROM @IndexTmpTable --Цикл обработки каждого индекса WHILE @RowNumber = 30 BEGIN --Формируем строку инструкции и выполняем ее SELECT @Command = 'ALTER INDEX [' + @IndexName + '] ON ' + '[' + @SchemaName + ']' + '.[' + @TableName + '] REBUILD'; EXEC (@Command); SET @CntRebuild = @CntRebuild + 1; --Количество перестроенных индексов END --Выводим служебную информацию о текущей операции PRINT 'Выполнена инструкция ' + @Command; --Переходим к следующему индексу SET @RowNumber = @RowNumber + 1 END --Итог PRINT 'Всего обработано индексов: ' + CAST(@CntRows AS VARCHAR(10)) + ', Реорганизовано: ' + CAST(@CntReorganize AS VARCHAR(10)) + ', Перестроено: ' + CAST(@CntRebuild AS VARCHAR(10)) END
После того как мы выполнили данный сценарий, можем вызвать только что созданную процедуру, например, следующим образом, в данном случае мы не указываем базу данных.
EXEC SP_ReIndex
Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.
На этом у меня все, надеюсь, материал помог начинающим, пока!
DBCC DBREINDEX (Transact-SQL)
Перестраивает один или более индексов для таблицы в указанной базе данных.
В будущей версии Microsoft SQL Server этот компонент будет удален. Избегайте использования этого компонента в новых разработках и запланируйте изменение существующих приложений, в которых он применяется. Используйте вместо нее инструкцию ALTER INDEX.
Применимо к: SQL Server 2008 (10.0.x) и более поздних версий.
Синтаксис
DBCC DBREINDEX ( table_name [ , index_name [ , fillfactor ] ] ) [ WITH NO_INFOMSGS ] Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.
Аргументы
table_name
Имя таблицы, содержащей указанный индекс или индексы для перестроения. Имена таблиц должны соответствовать правилам для идентификаторов.
index_name
Имя перестраиваемого индекса. Имена индексов должны соответствовать правилам для идентификаторов. Если аргумент index_name задан, также должен быть указан аргумент table_name. Если index_name не указан или имеет значение ‘ ‘ , все индексы для таблицы перестраиваются.
fillfactor
Процент пространства на каждой странице индекса для хранения данных при создании или перестроении индекса. Fillfactor заменяет коэффициент заполнения при создании индекса, становясь новым значением по умолчанию для индекса и для всех других некластеризованных индексов, так как кластеризованный индекс перестроен.
Если fillfactor равно 0, использует значение коэффициента заполнения, DBCC DBREINDEX последнее указанное для индекса. Это значение хранится в представлении sys.indexes каталога.
Если аргумент fillfactor задан, также должны быть указаны аргументы table_name и index_name. Если fillfactor не указан, используется коэффициент заполнения по умолчанию 100. Дополнительные сведения см. в статье Указание коэффициента заполнения для индекса.
WITH NO_INFOMSGS
Подавляет все информационные сообщения со степенями серьезности от 0 до 10.
Remarks
DBCC DBREINDEX перестраивает индекс для таблицы или все индексы, определенные для таблицы. При разрешенном динамическом перестроении индекса индексы с ограничениями PRIMARY KEY или UNIQUE можно перестраивать без необходимости удаления и повторного создания этих ограничений. Это значит, что индекс может быть перестроен без необходимости знания структуры таблицы или ее ограничений. Потребность в этом может возникнуть после массового копирования данных в таблицу.
DBCC DBREINDEX может перестроить все индексы для таблицы в одной инструкции. Это проще, чем написание нескольких DROP INDEX операторов и CREATE INDEX . Так как работа выполняется одним оператором, DBCC DBREINDEX автоматически атомарна, в то время как отдельные DROP INDEX операторы и CREATE INDEX должны быть включены в транзакцию, чтобы быть атомарными. Кроме того, предлагает больше оптимизаций, DBCC DBREINDEX чем отдельные DROP INDEX операторы и CREATE INDEX .
В отличие от DBCC INDEXDEFRAG , или ALTER INDEX с параметром REORGANIZE , DBCC DBREINDEX является автономной операцией. Если некластеризованный индекс перестраивается, общая блокировка удерживается в таблице, о которой идет речь, во время операции. Это предотвращает изменения в таблице. Если перестраивается кластеризованный индекс, удерживается монопольная блокировка таблицы. Это предотвращает какой-либо доступ к таблице, делая ее вне сети. Чтобы выполнить перестроение индекса в режиме «в сети» или для управления степенью параллелизма во время операции перестроения индекса, используйте инструкцию ALTER INDEX REBUILD с параметром ONLINE .
Дополнительные сведения о выборе метода для перестроения или реорганизации индекса см. в разделе Реорганизация и перестроение индексов.
Ограничения
DBCC DBREINDEX не поддерживается для использования в следующих объектах:
- Системные таблицы
- Пространственные индексы
- Индексы columnstore, оптимизированные для памяти
Наборы результатов
NO_INFOMSGS Если параметр не указан (необходимо указать имя таблицы), DBCC DBREINDEX всегда возвращает следующее:
DBCC execution completed. If DBCC printed error messages, contact your system administrator. Разрешения
Вызывающий должен быть владельцем таблицы, членом предопределенной роли сервера sysadmin, предопределенной роли базы данных db_owner или предопределенной роли базы данных db_ddladmin.
Примеры
A. Перестроение индекса
В следующем примере перестраивается кластеризованный индекс Employee_EmployeeID с коэффициентом заполнения 80 для таблицы Employee базы данных AdventureWorks2022 .
USE AdventureWorks2022; GO DBCC DBREINDEX ('HumanResources.Employee', PK_Employee_BusinessEntityID, 80); GO Б. Перестроение всех индексов
В следующем примере перестраиваются все индексы для таблицы Employee базы данных AdventureWorks2022 при значении коэффициента заполнения 70 .
USE AdventureWorks2022; GO DBCC DBREINDEX ('HumanResources.Employee', ' ', 70); GO См. также раздел
- ALTER TABLE (Transact-SQL)
- Инструкция CREATE TABLE (Transact-SQL)
- DBCC (Transact-SQL)
- sys.indexes (Transact-SQL)
- sys.dm_db_index_physical_stats (Transact-SQL)
- ALTER INDEX (Transact-SQL)
Обратная связь
Были ли сведения на этой странице полезными?
Как переиндексировать базу sql
Есть много тем, что это может ускорить быстродействие 1с. Кто-нибудь пробовал? Помогает? На сколько процентов?
только на больших базах помогает, до 30 гигов вполне нормально делать раз в неделю или даже месяц
Откройте для себя еще ребилд, апдейт статистики.
(1)60 гигов база. стоит это попробовать?
(3) Процент фрагментации каков?
(4)
я в скл раньше не лазил, как это посмотреть?
Для MS SQL Server рекомендуется выполнять следующие регламентные операции:
Обновление статистик
Очистка процедурного КЭШа
Дефрагментация индексов
Реиндексация таблиц базы данных
(0) что значит переиндексация «может ускорить»? она у вас должна быть выполняться регламентно.. раз в сутки минимум дефрагментация/ребилд.. или я не правильно понял?