"Объект не найден" и уровни эскалации блокировок

Пользователи стали жаловаться, что "Отчет отдела" не проводится по причине ошибки со ставкой НДС.

Разбор проблемы в отладчике показывает, что некоторые позиции номенклатуры документа в регистре "ОстаткиНоменклатуры" имеют ссылку партии номенклатуры типа <Объект не найден>

При перепроведении документа - регистратора проблема уходит. Первое время, пока эта проблема возникала не часто, её так и решали перепроведением документов. 

С течением времени, эта проблема стала возникать довольно часто, и назрела необходимость решить её радикально.

Рабочая/тестовая среда:

• Windows Server 2008 R2 Enterprise
• MS SQL Server 2008 R2
• 1С:Предприятие 8.2 (8.2.18.109)
• Конфигурация: Штрих-М: Торговое предприятие, редакция 5.1 (5.1.5.8)

Инструментарий:

• SQL server profiler
• SQL Query
• Консоль запросов 1С
• Обработка "Структура хранения БД"
• SQL сервер 2008: обслуживание, анализ производительности
• SQL Server 2012 Diagnostic Information Queries

Анализ кода

Детальный анализ Общего модуля проведения документов показал, что таблица партий для движений формируется ссылками новых, еще не сохраненных, объектов:

Далее, в отдельной процедуре вновь созданные объекты сохраняются.

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

Настройка тестовой среды

При помощи обработки Структура хранения БД определяем целевую таблицу SQL

Определяем ID объекта:

USE [trade_debug]
Select *
from Sys.objects
WHERE Sys.objects.name = '_Reference82'

Далее настраиваем трассировку SQL server profiler:

Устанавливаем фильтр:

Моделирование ситуации

Не буду подробно останавливаться на всех неудачных попытках смоделировать ситуацию. Расскажу лишь о последней, успешной попытке.

После ряда безуспешных попыток добиться укрупнения блокировок, я вспомнил о доработанной обработке восстановления последовательности документов, которая периодически крутится у нас в фоне.

Итак, запускаем настроенную на нашу таблицу трассировку и выполняем обработку восстановления последовательности... и, вуаля - наши эскалации:

Изучение причин

Посмотрим размеры таблицы:

-- Get Table names, row counts, and compression status for clustered index or heap (Table Sizes)
SELECT OBJECT_NAME(object_id) AS [ObjectName], 
SUM(Rows) AS [RowCount], data_compression_desc AS [CompressionType]
FROM sys.partitions WITH (NOLOCK)
WHERE index_id < 2 --ignore the partitions from the non-clustered index if any
AND OBJECT_NAME(object_id) NOT LIKE N'sys%'
AND OBJECT_NAME(object_id) NOT LIKE N'queue_%' 
AND OBJECT_NAME(object_id) NOT LIKE N'filestream_tombstone%' 
AND OBJECT_NAME(object_id) NOT LIKE N'fulltext%'
AND OBJECT_NAME(object_id) NOT LIKE N'ifts_comp_fragment%'
AND OBJECT_NAME(object_id) NOT LIKE N'filetable_updates%'
AND OBJECT_NAME(object_id) NOT LIKE N'xml_index_nodes%'
GROUP BY object_id, data_compression_desc
ORDER BY SUM(Rows) DESC OPTION (RECOMPILE);

-- Gives you an idea of table sizes, and possible data compression opportunities

Основными причинами эскалации блокировок на уровне SQL server могут быть:

• нехватка памяти
• недостаток индексации таблицы
• нерегулярное обновление статистик
• фрагментация индексов
• процедурный кэш

Большинство причин относится к регламентным процедурам, которые регулярно у нас выполняются. Но не будем слепо их отметать.

Что с памятью:

-- Good basic information about OS memory amounts and state (System Memory)
SELECT total_physical_memory_kb/1024 AS [Physical Memory (MB)], 
       available_physical_memory_kb/1024 AS [Available Memory (MB)], 
       total_page_file_kb/1024 AS [Total Page File (MB)], 
	   available_page_file_kb/1024 AS [Available Page File (MB)], 
	   system_cache_kb/1024 AS [System Cache (MB)],
       system_memory_state_desc AS [System Memory State]
FROM sys.dm_os_sys_memory WITH (NOLOCK) OPTION (RECOMPILE);

-- You want to see "Available physical memory is high"
-- This indicates that you are not under external memory pressure

С памятью все в порядке:

Недостающие индексы

SELECT TOP 10
       [Total Cost] = ROUND(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans),0),
       avg_user_impact,
       TableName = statement,
       [EqualityUsage] = equality_columns,
       [InequalityUsage] = inequality_columns,
       [Include Cloumns] = included_columns
  FROM sys.dm_db_missing_index_groups g 
  INNER JOIN sys.dm_db_missing_index_group_stats s ON s.group_handle = g.index_group_handle 
  INNER JOIN sys.dm_db_missing_index_details d ON d.index_handle = g.index_handle
  WHERE database_id = DB_ID()
  ORDER BY [Total Cost] DESC;

Исходя из текущих данных, не могу сказать, что необходимо бросаться создавать эти индексы (Total cost невелик)

Скорее всего эти данные за небольшой период, можно продолжить наблюдения.

Проверим эффективность текущих индексов таблицы:

-- Possible Bad NC Indexes (writes > reads)(Bad NC Indexes)
SELECT OBJECT_NAME(s.[object_id]) AS [Table Name], i.name AS [Index Name], i.index_id, 
i.is_disabled, i.is_hypothetical, i.has_filter, i.fill_factor,
user_updates AS [Total Writes], user_seeks + user_scans + user_lookups AS [Total Reads],
user_updates - (user_seeks + user_scans + user_lookups) AS [Difference]
FROM sys.dm_db_index_usage_stats AS s WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON s.[object_id] = i.[object_id]
AND i.index_id = s.index_id
WHERE OBJECTPROPERTY(s.[object_id],'IsUserTable') = 1
AND s.database_id = DB_ID()
AND user_updates > (user_seeks + user_scans + user_lookups)
AND i.index_id > 1

and OBJECT_NAME(s.[object_id]) like '%ence82%'
ORDER BY [Difference] DESC, [Total Writes] DESC, [Total Reads] ASC OPTION (RECOMPILE);

-- Look for indexes with high numbers of writes and zero or very low numbers of reads
-- Consider your complete workload, and how long your instance has been running
-- Investigate further before dropping an index!

Исходя из этой таблицы, могу сказать, что индекс, созданный по рекомендации SQL (ADD_BY_JAN) для этой таблицы значительно эффективнее нативных 1С-ных. 

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

SELECT TOP 100
       DatbaseName = DB_NAME(),
       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 and
         OBJECT_NAME(s.[object_id]) like '%ence82%'
  ORDER BY 4, page_count

Фрагментация незначительная

Обновление статистик проводится регулярно:

-- When were Statistics last updated on all indexes? (Statistics Update)
SELECT o.name, i.name AS [Index Name],  
      STATS_DATE(i.[object_id], i.index_id) AS [Statistics Date], 
      s.auto_created, s.no_recompute, s.user_created, st.row_count,
	  st.used_page_count
FROM sys.objects AS o WITH (NOLOCK)
INNER JOIN sys.indexes AS i WITH (NOLOCK)
ON o.[object_id] = i.[object_id]
INNER JOIN sys.stats AS s WITH (NOLOCK)
ON i.[object_id] = s.[object_id] 
AND i.index_id = s.stats_id
INNER JOIN sys.dm_db_partition_stats AS st WITH (NOLOCK)
ON o.[object_id] = st.[object_id]
AND i.[index_id] = st.[index_id]
WHERE o.[type] = 'U'
and o.name like '%ence82%'
ORDER BY STATS_DATE(i.[object_id], i.index_id) ASC OPTION (RECOMPILE);  

-- Helps discover possible problems with out-of-date statistics
-- Also gives you an idea which indexes are the most active

Процедурный кэш также очищается регулярно.

Таким образом, в данном случае, описанные причины не влияют на эскалацию блокировок.

LOCK_ESCALATION

В SQL server 2008 существует возможность управлять эскалацией блокировок. По умолчанию для таблиц установлено значение TABLE. Мы можем отключить эскалацию. Отключение эскалации чревато дополнительной нагрузкой на память, но с памятью у нас все в порядке, поэтому отключаем её:

USE [trade_debug]
ALTER TABLE _Reference82 SET (LOCK_ESCALATION = DISABLE)

Проверить режим эскалации блокировок можно с помощью скрипта:

USE [trade_debug]
SELECT lock_escalation, lock_escalation_desc, name 
FROM sys.tables
WHERE lock_escalation_desc='DISABLE'

После отключения эскалации на тестовой базе запускаем ранее настроенную трассировку в профайлере и обработку восстановления последовательности. Трассировка не показала эскалаций для данной таблицы.

В последнее время (до изменений на рабочей базе) жалобы на проблемы с проведением поступали 2-3 раза в неделю. На рабочей после отключения эскалации две недели - полет нормальный. Периодически провожу мониторинг на наличие Партии Номенклатуры <Объект не найден>

ВЫБРАТЬ
	ОстаткиНоменклатуры.Регистратор,
	ОстаткиНоменклатуры.Номенклатура,
	ОстаткиНоменклатуры.Номенклатура.Код,
	ОстаткиНоменклатуры.Партия
ИЗ
	РегистрНакопления.ОстаткиНоменклатуры КАК ОстаткиНоменклатуры
ГДЕ
	ОстаткиНоменклатуры.Партия.Код ЕСТЬ NULL