Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Блокировки с waitresource METADATA: SECURITY_CACHE  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
Вот blocked-process-report из профайлера:

<blocked-process-report monitorLoop="435990">
  <blocked-process>
    <process id="processf7f075498" taskpriority="0" logused="0" waitresource="METADATA: database_id = 6 SECURITY_CACHE($hash = 0x6:0x0)" waittime="1188" ownerId="5965340749" transactionname="SEC Cache Coherency" lasttranstarted="2015-02-19T19:38:11.280" XDES="0x6b2280180" lockMode="Sch-M" schedulerid="13" kpid="3384" status="suspended" spid="119" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2015-02-19T19:38:11.280" lastbatchcompleted="2015-02-19T19:37:42.613" lastattention="1900-01-01T00:00:00.613" hostpid="9200" loginname="etsecadmin" isolationlevel="read committed (2)" xactid="0" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame line="1" stmtstart="-1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000" />
      </executionStack>
      <inputbuf>
Proc [Database Id = 6 Object Id = 1410156119]   </inputbuf>
    </process>
  </blocked-process>
  <blocking-process>
    <process status="suspended" waitresource="METADATA: database_id = 6 SECURITY_CACHE($hash = 0x6:0x0)" waittime="6394" spid="61" sbid="2" ecid="0" priority="0" trancount="0" lastbatchstarted="2015-02-19T19:38:06.057" lastbatchcompleted="2015-02-19T19:38:04.043" lastattention="1900-01-01T00:00:00.043" clientapp="EFTR2.Console" hostname="EFTR-WEB" hostpid="29860" loginname="etsecadmin" isolationlevel="snapshot (5)" xactid="0" currentdb="6" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
      <executionStack>
        <frame line="1" stmtstart="-1" sqlhandle="0x02000000c422ab110e2dec3085cb9d78c44ee22b53cf206b0000000000000000000000000000000000000000" />
      </executionStack>
      <inputbuf>
SELECT MAX(_ts) FROM v_Owners (nolock)   </inputbuf>
    </process>
  </blocking-process>
</blocked-process-report>


В blocked-process Object Id = 1410156119 - это процедура, которая получает параметры и через merge when matched - when not matched изменят-вставляет эти параметры в одну строку таблицы, которой нет в v_Owners.
Постоянно возникают блокировки с lockMode="Sch-M" и waitresource="METADATA: database_id = 6 SECURITY_CACHE($hash = 0x6:0x0)".

Что это за ресурс? С чего возникает Sch-M? Никаких create, alter внутри процедуры нет. Только один merge.
Как бороться с такими блокировками?

Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
20 фев 15, 18:44    [17295015]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки с waitresource METADATA: SECURITY_CACHE  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Поиск в гугле находит несколько довольно занятных документов. Вы уже с ними ознакомидись?
20 фев 15, 18:54    [17295064]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки с waitresource METADATA: SECURITY_CACHE  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
Да, смотрел. Но ничего конкретного. Кто-то связывает с параллелизмом, кто-то с рекомпиляцией. У Микрософта есть "Указано только в ознакомительных целях. Не поддерживается. Совместимость с будущими версиями не гарантируется".
20 фев 15, 19:21    [17295165]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки с waitresource METADATA: SECURITY_CACHE  [new]
Crimean
Member

Откуда:
Сообщений: 13147
а это не связано с:
- рекомпиляциями
- разными коннект сетами для конкурирующих процессов?
21 фев 15, 00:38    [17296223]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки с waitresource METADATA: SECURITY_CACHE  [new]
Crimean
Member

Откуда:
Сообщений: 13147
второй вопрос снимается. сеты одинаковы, увидел. первый - актуален.
21 фев 15, 01:42    [17296362]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки с waitresource METADATA: SECURITY_CACHE  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 8805
Truncate, удаление столбцов, добавление?
21 фев 15, 17:05    [17297248]     Ответить | Цитировать Сообщить модератору
 Re: Блокировки с waitresource METADATA: SECURITY_CACHE  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
Владислав Колосов
Truncate, удаление столбцов, добавление?
Это всё проверили и перепроверили. Ничего этого нет. Хотя, да, меня несколько смущает Sch-M, потому что моё понимание проблемы несколько другое. Я это себе представляю так.

При вызове хранимой процедуры, или простого запроса, или выполнении динамического sql, сервер проверяет, а есть ли у пользователя разрешения на это. Начиная с SQL 2005, результат проверки стал кэшироваться в области памяти, называемой SECURITY_CACHE, чтобы при повторном выполнении той же операции не проверять заново, а взять результат из кэша. Соответственно чем больше пользователей и больше всяких разных запросов, тем больше размер этого кэша. Сначала его размер никак не ограничивался, поэтому он рос пока не занимал всю имеющуюся память, после чего производительность сервера резко падала. После выполнения DBCC FREESYSTEMCACHE('TokenAndPermUserStore') кэш очищался, память освобождалась, производительность восстанавливалась.

В SQL2005 SP2 появились два флага трассировки 4618 и 4610, с помощью которых размер кэша можно было ограничить 1024 и 8124, скажем так, условными записями соответственно. Таким образом кэш перестал расти неограниченно. Но для кого-то и 8124 было мало, поэтому в SP3 появился новый флаг - 4621. Можно было прописать в системном реестре для соответствующего экземпляра SQL-сервера в двоичном исчислении предельный размер кэша. И по взводу флага 4621 SQL сервер начинал использовать это размер.

Начиная с SQL 2008 размер этого кэша можно регулировать опциями access check cache bucket count и access check cache quota процедуры sp_configure. Для значения опций равным 0 сервер устанавливает:

- access check cache bucket count - 256 на ч86 и 2048 на x64;
- access check cache quota - 1024 на x86 и 8192 на x64.

Также в описании этих опций есть фраза: "Майкрософт рекомендует изменять эти параметры только под руководством службы поддержки пользователей Майкрософт".

Мне представляется, что проблема в том, что размер SECURITY_CACHE у нас недостаточный, поэтому и возникают блокировки из-за ожидания (waitresource="METADATA: database_id = 6 SECURITY_CACHE($hash = 0x6:0x0)") реорганизации этого кэша. Правда, тип блокировки (Sch-M) немного смущает. Таких блокировок очень много. Есть события blocked-process-report, где только одна сторона blocked-process или blocking-process и нет второй.

Но, очень даже может быть, что мои выводы неправильны, и причина совсем в другом. Может быть, кто-нибудь ещё что подскажет.
И второе, как правильно настроить значения для access check cache bucket count и access check cache quota? Никто этим не занимался?
22 фев 15, 09:00    [17298424]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить