Сообщение Re: [SQL Server] FILETABLE, deadlock и кластерный индекс от 17.03.2015 12:02
Изменено 18.03.2015 6:15 Olaf
Здравствуйте, 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 выполняется еще достаточное количество обращений к этой же таблице. Причем реализовано, как я понимаю это на уровне ядра БД, т.к. нигде в планах данная информация не фигурирует.
В целом видно, что кроме вставки в кучу и верификации FK выполняется еще три чтения FILTABLE. Причем у одного из запросов на выборку стоит подсказка к запросу xlock. C определенной долей уверенности можно сказать, что причина дедлока не в верификации FK как таковой, а в совокупности обращений к таблице, где по всей видимости присутствует сканирование.
Более детально наверно сложно проанализировать эту ситуацию, т.к. внутренние запросы, которые выполнятся дополнительно, остаются за бортом и я не вижу пока возможности проанализировать и сравнить планы выполнения в каждом из вариантов. Очевидно, что кластерный индекс кардинально меняет картину ...
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 как таковой, а в совокупности обращений к таблице, где по всей видимости присутствует сканирование.
Более детально наверно сложно проанализировать эту ситуацию, т.к. внутренние запросы, которые выполнятся дополнительно, остаются за бортом и я не вижу пока возможности проанализировать и сравнить планы выполнения в каждом из вариантов. Очевидно, что кластерный индекс кардинально меняет картину ...
Re: [SQL Server] FILETABLE, deadlock и кластерный индекс
Здравствуйте, 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 выполняется еще достаточное количество обращений к этой же таблице. Причем реализовано, как я понимаю это на уровне ядра БД, т.к. нигде в планах данная информация не фигурирует.
В целом видно, что кроме вставки в кучу и верификации FK выполняется еще три чтения FILTABLE. Причем у одного из запросов на выборку стоит подсказка к запросу xlock. C определенной долей уверенности можно сказать, что причина дедлока не в верификации FK как таковой, а в совокупности обращений к таблице, где по всей видимости присутствует сканирование.
Более детально наверно сложно проанализировать эту ситуацию, т.к. внутренние запросы, которые выполнятся дополнительно, остаются за бортом и я не вижу пока возможности проанализировать и сравнить планы выполнения в каждом из вариантов. Очевидно, что кластерный индекс кардинально меняет картину ...
Если последовательно запустить три запроса на больших объемах данных в таблице без кластерного индекса, то видно, что второй запрос использует сканирование PK индекса, хотя в таблице присутствует подходящий уникальный индекс, по которому можно выполнить поиск. Добавление кластерного индекса приводит к тому, что оптимизатор правильно разбирается в ситуации и использует поиск по соответствующему индексу, именно это и приводит к исчезновению дедлока. Вот собственно и все ответы на вопросы. Открытым остается только один вопрос, а почему Microsoft изначально не предусмотрел наличие кластерного индекса в таблице ?!
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 изначально не предусмотрел наличие кластерного индекса в таблице ?!