Примечание.
Для доступа к этой странице требуется авторизация. Вы можете попробовать войти или изменить каталоги.
Для доступа к этой странице требуется авторизация. Вы можете попробовать изменить каталоги.
В этой статье описывается, как устранять и устранять проблемы блокировки, вызванные блокировками компиляции.
Исходная версия продукта: SQL Server
Исходный номер базы знаний: 263889
Итоги
В Microsoft SQL Server только одна копия плана хранимой процедуры обычно находится в кэше одновременно. Для применения этого требуется сериализация некоторых частей процесса компиляции, и эта синхронизация выполняется частично с помощью блокировок компиляции. Если одновременно выполняется одна и та же хранимая процедура, и для этой хранимой процедуры необходимо получить блокировку компиляции при каждом запуске, идентификаторы сеансов (SPID) могут начать блокировать друг друга, так как каждая из них пытается получить монопольную блокировку компиляции для объекта.
Ниже приведены некоторые типичные характеристики блокировки компиляции, которые можно наблюдать в выходных данных блокировки:
waittypeдля заблокированных и (обычно) блокирующих сеансов SPID являетсяLCK_M_X(эксклюзивным) иwaitresourceимеет формуOBJECT: dbid: object_id [[COMPILE]], гдеobject_idявляется идентификатор объекта хранимой процедуры.Блокировщики имеют
waittypeзначение NULL, состояние выполняется. Заблокированные сеансы имеютwaittypeLCK_M_X(монопольную блокировку), состояние спящего состояния.Хотя общая продолжительность инцидента блокировки может быть длительной, один сеанс (SPID) не блокирует другие SPID в течение длительного времени. Существует скользящей блокировки; как только одна компиляция завершена, другой SPID берет на себя роль блокировщика головы в течение нескольких секунд или меньше, и т. д.
Следующие сведения приведены из моментального снимка sys.dm_exec_requests во время блокировки такого типа:
session_id blocking_session_id wait_type wait_time waitresource
---------- ------------------- --------- --------- ----------------------------
221 29 LCK_M_X 2141 OBJECT: 6:834102 [[COMPILE]]
228 29 LCK_M_X 2235 OBJECT: 6:834102 [[COMPILE]]
29 214 LCK_M_X 3937 OBJECT: 6:834102 [[COMPILE]]
13 214 LCK_M_X 1094 OBJECT: 6:834102 [[COMPILE]]
68 214 LCK_M_X 1968 OBJECT: 6:834102 [[COMPILE]]
214 0 LCK_M_X 0 OBJECT: 6:834102 [[COMPILE]]
В столбце waitresource (6:834102) 6 — это идентификатор базы данных, а 834102 — идентификатор объекта. Этот идентификатор объекта принадлежит хранимой процедуре, а не таблице.
Сценарии, которые приводят к компиляции блокировок
В следующих сценариях описываются причины монопольной компиляции блокировок хранимых процедур или триггеров.
Хранимая процедура выполняется без полного имени
- Пользователь, который запускает хранимую процедуру, не является владельцем процедуры.
- Имя хранимой процедуры не полностью соответствует имени владельца объекта.
Например, если пользователь dbo владеет объектом dbo.mystoredproc и другим пользователем, Harryвыполняет эту хранимую процедуру с помощью команды exec mystoredproc, начальная подстановка кэша по имени объекта завершается ошибкой, так как объект не является владельцем. (Пока неизвестно, существует ли другая хранимая Harry.mystoredproc процедура. Поэтому SQL Server не может быть уверен, что кэшированный план dbo.mystoredproc является правильным для выполнения.) Затем SQL Server получает монопольную блокировку компиляции процедуры и делает подготовку к компиляции процедуры. Это включает разрешение имени объекта на идентификатор объекта. Прежде чем SQL Server компилирует план, SQL Server использует этот идентификатор объекта для более точного поиска кэша процедур и может находить ранее скомпилированный план даже без квалификации владельца.
Если существующий план найден, SQL Server повторно использует кэшированный план и не компилирует хранимую процедуру. Однако отсутствие квалификации владельца заставляет SQL Server выполнять второй поиск кэша и получать монопольную блокировку компиляции, прежде чем программа определяет, что существующий кэшированный план выполнения можно повторно использовать. Получение блокировки и выполнение подстановок и других работ, необходимых для достижения этой точки, может привести к задержке блокировки компиляции, которая приводит к блокировке. Это особенно верно, если многие пользователи, не являющиеся владельцем хранимой процедуры, одновременно запускают процедуру без указания имени владельца. Даже если вы не видите SPID,ожидающие блокировки компиляции, отсутствие квалификации владельца может привести к задержкам в выполнении хранимой процедуры и вызвать высокую загрузку ЦП.
Следующая последовательность событий записывается в сеанс расширенного события SQL Server при возникновении этой проблемы.
| Имя события | Текст |
|---|---|
| rpc_starting | mystoredproc |
| sp_cache_miss | mystoredproc |
| sql_batch_starting | mystoredproc |
| sp_cache_hit | mystoredproc |
| ... | ... |
sp_cache_miss происходит при сбое поиска кэша по имени, но затем соответствующий кэшированный план был в конечном счете найден в кэше после разрешения неоднозначного имени объекта на идентификатор объекта и есть sp_cache_hit событие.
Решение этой проблемы блокировки компиляции заключается в том, чтобы убедиться, что ссылки на хранимые процедуры квалифицированы владельцем. (Вместо exec используйте exec mystoredprocdbo.mystoredproc.) Хотя квалификация владельца важна по соображениям производительности, вам не нужно квалифицировать хранимый proc с именем базы данных, чтобы предотвратить поиск дополнительного кэша.
Блокировка, вызванная блокировками компиляции, может быть обнаружена с помощью стандартных методов устранения неполадок блокировки.
Хранимая процедура часто компилируется
Перекомпиляция — это одно из объяснений для блокировки компиляции хранимой процедуры или триггера. Способы повторной компиляции хранимой процедуры включают EXECUTE... WITH RECOMPILECREATE PROCEDURE ...WITH RECOMPILEили используют .sp_recompile Дополнительные сведения см. в разделе Перекомпиляция хранимой процедуры. Решение в этом случае заключается в сокращении или устранении перекомпиляции.
Хранимая процедура префиксируется sp_**
Если имя хранимой процедуры начинается с sp_ префикса, и он не находится в базе данных master, вы увидите sp_cache_miss до попадания кэша для каждого выполнения, даже если владелец квалифицирует хранимую процедуру. Это связано с тем sp_ , что префикс сообщает SQL Server, что хранимая процедура является системной хранимой процедурой, а системные хранимые процедуры имеют разные правила разрешения имен. (Предпочтительное расположение находится в базе данных master.) Имена созданных пользователем хранимых процедур не должны начинаться.sp_
Хранимая процедура вызывается с помощью другого регистра (верхний /нижний)
Если для создания процедуры используется другой буквенный регистр (верхний или нижний) из случая, который использовался для его создания, процедура может активировать событие CacheMiss или запросить блокировку КОМПИЛЯЦИи. Чтобы проиллюстрировать, обратите внимание на разные буквы, используемые в CREATE PROCEDURE dbo.SalesData ... сравнении EXEC dbo.salesdataс . В конечном итоге процедура использует кэшированный план и не перекомпилируется. Но запрос блокировки КОМПИЛЯЦИи иногда может вызвать ситуацию блокировки блокировки, описанную ранее. Цепочка блокировок может возникать, если существует множество сеансов (SPID), которые пытаются выполнить одну и ту же процедуру, используя другой случай, отличный от случая, который использовался для его создания. Это верно независимо от порядка сортировки или сортировки, используемой на сервере или в базе данных. Причина этого поведения заключается в том, что алгоритм, используемый для поиска процедуры в кэше, основан на хэш-значениях (для производительности), а хэш-значения могут измениться, если дело отличается.
Решение состоит в том, чтобы удалить и создать процедуру с помощью того же регистра буквы, что и тот, который используется при выполнении процедуры приложением. Кроме того, можно убедиться, что процедура выполняется из всех приложений, используя правильный регистр (верхний или нижний).
Хранимая процедура вызывается как событие языка
Если вы пытаетесь выполнить хранимую процедуру как языковое событие вместо RPC, SQL Server должен проанализировать и скомпилировать языковой запрос событий, определить, что запрос пытается выполнить определенную процедуру, а затем попытаться найти план в кэше этой процедуры. Чтобы избежать этой ситуации, в которой SQL Server должен анализировать и компилировать языковое событие, убедитесь, что запрос отправляется в SQL Server в качестве RPC. Например, в коде .NET можно использовать SqlCommand.CommandType.StoredProcedure для обеспечения события RPC.
Хранимая процедура или sp_executesql использует строковый параметр больше 8 КБ
При вызове хранимой процедуры или sp_executesql и передаче строкового параметра размером более 8 КБ SQL Server использует тип данных двоичного большого объекта (BLOB) для хранения параметра. В результате план запроса для этого выполнения не сохраняется в кэше планов. Таким образом, каждое выполнение хранимой процедуры или sp_executesql получение блокировки компиляции для компиляции нового плана. Этот план удаляется при завершении выполнения. Дополнительные сведения см. в заметке в кэшировании плана выполнения и повторном использовании строковых литералов размером более 8 КБ. Чтобы избежать блокировки компиляции в этом сценарии, уменьшите размер параметра до менее 8 КБ.
Ссылки
Команда OPEN SYMMETRIC KEY предотвращает кэширование плана запроса