Как переорганизовать и восстановить индексы в MS SQL?

Индексы являются объектами файлов базы данных MS SQL. Эти объекты схемы действуют как оглавление базы данных. Они используются для улучшения операций извлечения данных из таблицы базы данных MS SQL.

Однако со временем таблица может стать фрагментированной из-за непрерывных операций INSERT и UPDATE. Также, как и другие объекты, индексы также подвержены повреждениям. Если индексы повреждены, вы можете получить ошибки, например:

Table error: table 'Items' (ID 1954106002). Data row does not have a matching index row in the index 'IX_Advertise_BrandCopy_Price_Stock' (ID 69). Possible missing or invalid keys for the index row matching:

​

Msg 8955, Level 16, State 1, Line 1

​

Data row (1:11226494:9) identified by (Id = 11078215) with index values 'AdvertiseFlag = 1 and BrandCopyParentId = 0 and NSFPPrice = 137.50 and NSFPQtyInStock = 0 and Id = 11078215'.

​

Msg 8951, Level 16, State 1, Line 1

Для устранения ошибок, связанных с повреждением в индексах или когда уровень фрагментации индексов увеличивается, можно перестроить или переорганизовать индексы. В этой статье мы рассмотрим, как переорганизовать и перестроить индексы в MS SQL Server. Мы также упомянем продвинутый инструмент восстановления базы данных MS SQL, который может быстро и с полным уровнем точности восстановить индексы и другие объекты из поврежденных файлов SQL базы данных.

Проверьте процент фрагментации индексов

Прежде чем переорганизовать индексы, необходимо знать процент фрагментации. Вы можете использовать следующую команду, чтобы проверить процент фрагментации индекса:

SELECT 

    OBJECT_NAME(object_id) tableName12,

    index_id,

    index_type_desc,

    avg_fragmentation_in_percent,

    page_count

FROM 

    sys.dm_db_index_physical_stats (

        DB_ID('Adventureworks2019'), 

        OBJECT_ID('[Person].[Person]'), 

        NULL, NULL, 'DETAILED'

    );

​

Переорганизация индексов в SQL Server

Если уровень фрагментации индекса находится между 10 и 30, рекомендуется переорганизовать индекс. Вы можете использовать следующий код на T-SQL для переорганизации индекса:

ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person]

REORGANIZE;

​

Как альтернативу, вы можете использовать SQL Server Management Studio (SSMS) для реорганизации индексов. Следуйте нижеприведенным шагам:

• В SSMS подключитесь к вашему экземпляру SQL Server.
• В Обозревателе объектов разверните базу данных.
• Разверните папку Таблицы, а затем папку Индексы.
• Щелкните правой кнопкой мыши на индексе, который необходимо реорганизовать, затем выберите Переорганизовать.

Перестройка индексов в MS SQL

Если фрагментация высокая, то можно перестроить индексы. Можно использовать команду DBCC DBREINDEX для перестройки индекса. Вот как выполнить эту команду:

DBCC DBREINDEX

(

    table_name

    [ , index_name [ , fillfactor ] ]

)

    [ WITH NO_INFOMSGS ]

​

Эта команда не поддерживает пространственные индексы и индексы столбцов памяти, оптимизированные для хранения. 

Как альтернативу, можно использовать команду ALTER INDEX:

ALTER INDEX [PK_Person_BusinessEntityID] ON [Person].[Person]

REBUILD;

​

Также можно перестроить индексы, используя графический интерфейс пользователя в SSMS. Вот шаги:

• В SSMS, в Обозревателе объектов разверните базу данных, содержащую таблицу, для которой необходимо перестроить индекс.
• Разверните папку Таблицы, а затем таблицу, для которой необходимо перестроить индекс.
• Разверните папку Индексы, щелкните правой кнопкой мыши на индексе, который необходимо перестроить, и выберите Перестроить.

Восстановление базы данных SQL Server

Если переорганизация или восстановление индексов не помогает, то вы можете попробовать использовать команду DBCC CHECKDB с опцией REPAIR_ALLOW_DATA_LOSS для восстановления базы данных. Вот как использовать эту команду:

Сначала измените режим базы данных на SINGLE_USER, используя следующую команду:

ALTER DATABASE BusinessEntity SET SINGLE_USER

Затем выполните команду DBCC CHECKDB вот так, чтобы восстановить базу данных:

DBCC CHECKDB (N ’BusinessEntity’, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS; 

GO

​

После восстановления установите режим базы данных на MULTI_USER, выполнив следующую команду:

ALTER DATABASE BusinessEntity SET MULTI_USER

Указанная команда DBCC CHECKDB может помочь вам устранить все ошибки, связанные с повреждением индексов. Она может восстановить как кластеризованные, так и не кластеризованные индексы в базах данных SQL. Однако она не гарантирует полного восстановления данных и может привести к потере данных. Чтобы предотвратить потерю данных, вы можете использовать любые профессиональные инструменты восстановления MS SQL. Они разработаны с использованием передовых алгоритмов для восстановления всех объектов, включая кластеризованные и не кластеризованные индексы, хранимые процедуры, триггеры и т. д., из поврежденной базы данных.

Вывод

Вы можете снизить фрагментацию индексов путем их переорганизации или восстановления. В этой статье мы объяснили, как проверить процент фрагментации в индексах и как восстановить индексы.