6 мар. 2017 г.

Проблемы производительности. Фрагментация индексов.

Недавно на практике получилось увидеть влияние чрезмерной фрагментации индексов на производительность базы, конфигурация 1С: ERP. Ранее я читал, что излишняя фрагментация влияет на скорость выполнения операций INSERT, UPDATE, DELETE, в чем и удалось убедиться. Как обычно бывает, проблему обрисовали в общих чертах - у клиента тормозит база. В таких случаях, когда не известно, с чего начать - начинаем с настройки сбора данных по счетчикам производительности и технологического журнала. В моем случае анализ данных по счетчикам производительности проблем загруженности оборудования не выявил. Так же в существующие настройки техжурнала была добавлена секция для сбора событий с длительностью, превышающей три секунды.

 <log location="e:\tech_log\long_events" history="4">
  <event>
   <ne property="name" value=""/>
   <ge property="Durationus" value="3000000"/>
  </event>
  <property name="all"/>
 </log>

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

--use name_of_base -- указываем имя базы для анализа
       
SELECT
TableName = OBJECT_NAME(s.[object_id]),
IndexName = i.name,
i.type_desc,
[Fragmentation %] = ROUND(avg_fragmentation_in_percent,2),
page_count,
partition_number,
'alter index [' + i.name + '] on [' + sh.name + '].['+ OBJECT_NAME(s.[object_id]) + '] REBUILD' + 
 case
 when p.data_space_id is not null then ' PARTITION = '+convert(varchar(100),partition_number)
 else ''
 end + ' with(maxdop = 4,  SORT_IN_TEMPDB = on)' [sql]

FROM sys.dm_db_index_physical_stats(db_id(),null, null, null, null) s
INNER JOIN sys.indexes as i ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
left join sys.partition_schemes as p on i.data_space_id = p.data_space_id
left join sys.objects o on  s.[object_id] = o.[object_id]
left join sys.schemas as sh on sh.[schema_id] = o.[schema_id]
WHERE s.database_id = DB_ID() AND
    i.name IS NOT NULL AND
    OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 and
    page_count > 100 and
    avg_fragmentation_in_percent > 10
ORDER BY 4 desc

Результат скрипта впечатлил - фрагментация по таблицам, так или иначе участвующих в проблемных операциях, достигала 90%. После устранения данной проблемы длительные события DBMSSQL ушли из лога техжурнала, пользователи подтвердили восстановление приемлемой скорости работы.

Начали разбираться с настройкой планов обслуживания сервера SQL. Операции обслуживания индексов были настроены по схеме в воскресенье - перестроение индекса (ALTER INDEX REBUILD), в остальные дни - реорганизация (ALTER INDEX REORGANIZE). Вроде бы нормально, но в настройках перестроения был указан флаг "Сохранять индексы в сети", что работает только для MS SQL Enterprise Edition. В следствие чего перестроение не происходило вообще.


Так же был произведен анализ роста фрагментации в течении дня. Оказалось, что 1С: ERP может за несколько часов увеличить этот показатель до 90%. Поэтому в ежедневное обслуживание продуктивной базы был добавлен следующий скрипт, с запуском за час до начала рабочего дня и в обед:

USE current_work_base -- устанавливаем текущую базу
SET NOCOUNT ON; -- отключаем вывод количества возвращаемых строк, это несколько ускорит обработку
DECLARE @objectid int; -- ID объекта
DECLARE @indexid int; -- ID индекса
DECLARE @partitioncount bigint; -- количество секций если индекс секционирован
DECLARE @schemaname nvarchar(130); -- имя схемы в которой находится таблица
DECLARE @objectname nvarchar(130); -- имя таблицы 
DECLARE @indexname nvarchar(130); -- имя индекса
DECLARE @partitionnum bigint; -- номер секции
DECLARE @frag float; -- процент фрагментации индекса
DECLARE @command nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации

-- Отбор таблиц и индексов с помощью системного представления sys.dm_db_index_physical_stats
-- Отбор только тех объектов которые являются индексами (index_id > 0), 
-- фрагментация которых более 10% и количество страниц в индексе более 128
SELECT
    object_id AS objectid,
    index_id AS indexid,
    partition_number AS partitionnum,
    avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0 AND page_count > 128;

-- Объявление курсора для чтения секций
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;

-- Открытие курсора
OPEN partitions;

-- Цикл по секциям
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions
           INTO @objectid, @indexid, @partitionnum, @frag;
        IF @@FETCH_STATUS < 0 BREAK;
  
-- Собираем имена объектов по ID  
        SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
        FROM sys.objects AS o
        JOIN sys.schemas as s ON s.schema_id = o.schema_id
        WHERE o.object_id = @objectid;
        SELECT @indexname = QUOTENAME(name)
        FROM sys.indexes
        WHERE  object_id = @objectid AND index_id = @indexid;
        SELECT @partitioncount = count (*)
        FROM sys.partitions
        WHERE object_id = @objectid AND index_id = @indexid;

-- Если фрагментация менее или равна 30% тогда дефрагментация, иначе реиндексация
        IF @frag <= 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
        IF @frag > 30.0
            SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
        IF @partitioncount > 1
            SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
   
-- Если реиндексация, то для ускорения добавляем параметры использования TEMPDB(имеет смысл только если TempDB на отдельном физ. диске) и многопроцессорной обработки 
   IF @frag > 30.0
   SET @command = @command + N' WITH (SORT_IN_TEMPDB = ON, MAXDOP = 0)'; 
        EXEC (@command);
        PRINT N'Executed: ' + @command;
    END;

-- Закрытие курсора
CLOSE partitions;
DEALLOCATE partitions;

-- Удаление временной таблицы
DROP TABLE #work_to_do;
GO

Рекомендуется к прочтению:
План обслуживания «на каждый день» – Часть 1: Автоматическая дефрагментация индексов
Зачем разработчику 1С «индексировать» измерения регистров и реквизиты?
Технологический журнал от «А» до «Я»

Следите за вашими индексами, коллеги :)

8 комментариев:

  1. Большое спасибо за статью.

    Но возник вопрос, можно ли применить данные рекомендации к базе 1С 7.7 работающей на MSSQL 2000?
    Возможно ли как-то собрать информацию о проблемах базы на указанной версии MSSQL? К примеру, не смог найти информацию о мониторинге запросов к базе.

    ОтветитьУдалить
    Ответы
    1. Посмотрите http://www.softpoint.ru/archive/article_id34.php
      Под рукой данной версии нет, чтобы попробовать.

      Удалить
    2. Документация на sql 2000
      microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

      Удалить
    3. из документации
      This examplereturns a full tableresult set for every index on every tablein the pubs database.

      USE pubs
      DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES

      Удалить
    4. E. Use DBCC SHOWCONTIG and DBCC INDEXDEFRAG to defragment the indexes in a database
      This exampleshows a simple way to defragmentall indexes in a databasethat is fragmented abovea declared threshold.
      /*Perform a 'USE ' to select the database in which to run the script.*/

      -- Declare variables
      SET NOCOUNT ON
      DECLARE @tablename VARCHAR (128)
      DECLARE @execstr VARCHAR (255)
      DECLARE @objectid INT
      DECLARE @indexid INT
      DECLARE @frag DECIMAL
      DECLARE @maxfrag DECIMAL
      -- Decide on the maximum fragmentation to allow
      SELECT @maxfrag = 30.0
      -- Declare cursor
      DECLARE tables CURSOR FOR
      SELECT TABLE_NAME
      FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_TYPE = 'BASE TABLE'
      -- Create the table
      CREATE TABLE #fraglist (
      ObjectName CHAR (255),
      ObjectId INT,
      IndexName CHAR (255),
      IndexId INT,
      Lvl INT,
      CountPages INT,
      CountRows INT,
      MinRecSize INT,
      MaxRecSize INT,
      AvgRecSize INT,
      ForRecCount INT,
      Extents INT,
      ExtentSwitches INT,
      AvgFreeBytes INT,
      AvgPageDensity INT,
      ScanDensity DECIMAL,
      BestCount INT,
      ActualCount INT,
      LogicalFrag DECIMAL,
      ExtentFrag DECIMAL)
      -- Open the cursor
      OPEN tables
      -- Loop through all the tables in the database
      FETCH NEXT
      FROM tables
      INTO @tablename
      WHILE @@FETCH_STATUS = 0
      BEGIN
      -- Do the showcontig of all indexes of the table
      INSERT INTO #fraglist
      EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
      WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
      FETCH NEXT
      FROM tables
      INTO @tablename
      END
      -- Close and deallocate the cursor
      CLOSE tables
      DEALLOCATE tables
      -- Declare cursor for list of indexes to be defragged
      DECLARE indexes CURSOR FOR
      SELECT ObjectName, ObjectId, IndexId, LogicalFrag
      FROM #fraglist
      WHERE LogicalFrag >= @maxfrag
      AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
      -- Open the cursor
      OPEN indexes
      -- loop through the indexes
      FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
      WHILE @@FETCH_STATUS = 0
      BEGIN
      PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
      ' + RTRIM(@indexid) + ') - fragmentation currently '
      + RTRIM(CONVERT(varchar(15),@frag)) + '%'
      SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
      ' + RTRIM(@indexid) + ')'
      EXEC (@execstr)
      FETCH NEXT
      FROM indexes
      INTO @tablename, @objectid, @indexid, @frag
      END
      -- Close and deallocate the cursor
      CLOSE indexes
      DEALLOCATE indexes
      -- Delete the temporary table
      DROP TABLE #fraglist
      GO

      Удалить
  2. Пробежался по документации, на отдельную статью тянет :)
    Общий мониторинг запросов можно осуществлять через System (Performance) Monitor по счетчикам SQL Server: SQL Statistics.
    Счетчики группы показывают сведения о запросах Transact-SQL, включая число компиляций, повторных компиляций и число полученных пакетов. Анализ позволяет оценить скорость компиляции запросов и общую эффективность работы оптимизатора запросов.

    ОтветитьУдалить
  3. Спасибо большое. Навели на верный путь

    ОтветитьУдалить