Здравствуйте, Olaf, Вы писали:

O>Возможно, кто-то решал аналогичные проблемы или поделится своим мыслями относительно прочитанного, буду премного благодарен.

O>Резюмируя, опишу кратко свои вопросы:
O>1. По каким причинам на ваш взгляд возник deadlock? Действительно ли использовался index scan для верификации ссылочной целостности и почему? Или истинная причина в чем-то другом?
O>2. Чем смог помочь кластерный индекс в решении проблемы взаимоблокировки? Время вставки должно было сократиться, но что произойдет, если нагрузка увеличится? Так ли это?

Удалось поймать взаимоблокировку, причем необязательно было давать высокую нагрузку, достаточно ~20 параллельных выполнений запроса на INSERT. Анализ плана запроса показал, что верификация FK выполняется через поиск по индексу, никакого скана в данном случае нет. Решил включить трассировку и посмотреть на одиночном запросе, а что же происходит в БД кроме моей нагрузки, и вот что обнаружил. Между двумя событиями SQL:StmtStarting и SQL:StmtComplleted возникающими при выполнении запроса на insert into dbo.IssueFiles(name, file_stream, path_locator) select @filename, @file, @pl выполняется еще достаточное количество обращений к этой же таблице. Причем реализовано, как я понимаю это на уровне ядра БД, т.к. нигде в планах данная информация не фигурирует.

/*SQL:StmtStarting */ insert into dbo.IssueFiles(name, file_stream, path_locator) select @filename, @file, @pl

set @path_locator = convert(hierarchyid, @path_locator_bin) /* Parent validation is not necessary for top level rows. */     
if (@path_locator.GetLevel() = 1)
set @parent_is_directory = 1     
/*1*/ select @parent_is_directory = is_directory from [dbo].[IssueFiles] where path_locator = @path_locator.GetAncestor(1)
if (@parent_is_directory = 0)
if (@is_directory = 1)        
/*2*/ if exists (select 1 from [dbo].[IssueFiles] where parent_path_locator = @path_locator)
set @return_value = 1
goto complete
set @path_locator = convert(hierarchyid, @path_locator_bin)
/*3*/ select @dummy1 = sum(convert(bigint, is_archive)) from [dbo].[IssueFiles] with (xlock, rowlock, index([PK__IssueFil__5A5B77D5C0E48F84]), forceseek) 
   where path_locator.IsDescendantOf(@path_locator) = 1
set @return_value = 1

/*SQL:StmtComplleted*/  insert into dbo.IssueFiles(name, file_stream, path_locator) select @filename, @file, @pl

В целом видно, что кроме вставки в кучу и верификации FK выполняется еще три чтения FILTABLE. Причем у одного из запросов на выборку стоит подсказка к запросу xlock. C определенной долей уверенности можно сказать, что причина дедлока не в верификации FK как таковой, а в совокупности обращений к таблице, где по всей видимости присутствует сканирование.

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

Если последовательно запустить три запроса на больших объемах данных в таблице без кластерного индекса, то видно, что второй запрос использует сканирование PK индекса, хотя в таблице присутствует подходящий уникальный индекс, по которому можно выполнить поиск. Добавление кластерного индекса приводит к тому, что оптимизатор правильно разбирается в ситуации и использует поиск по соответствующему индексу, именно это и приводит к исчезновению дедлока. Вот собственно и все ответы на вопросы. Открытым остается только один вопрос, а почему Microsoft изначально не предусмотрел наличие кластерного индекса в таблице ?!