Почему этот запрос медленный в первый раз после запуска службы?

Question

Почему этот запрос медленный в первый раз после запуска службы?

Хорошо. Вот что я пытаюсь запустить:

USE tempdb;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3
CROSS JOIN sys.objects s4;

Это один из тех запросов "сделай мне таблицу чисел".

Здесь проблема. Если я запускаю это сразу после (пере) запуска службы SQL Server, это занимает вечность. Не навсегда, как через десять секунд, и я хочу это быстрее. Всегда, как и прежде, я случайно пропустил два часа и все равно должен был убить его. Я думаю, это просто никогда не вернется. И обычно это занимает менее двух секунд на моей машине, чтобы запустить это.

Однако, если я сделаю это вместо этого:

USE tempdb;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3;

DROP TABLE Numbers;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3
CROSS JOIN sys.objects s4;

Тогда это работает, как и следовало ожидать - первый SELECT работает менее чем за две секунды, как и вторая. Почему бы мне просто не использовать версию с тремя столами? Потому что не хватает записей в sys.objects для этого числа в кубе равным миллиону строк результата. Но дело даже не в этом.

Во всяком случае, с этого момента, я могу повторить эту секунду DROP / SELECT…INTO сколько захочу, без проблем. Каким-то образом эта первая версия с тремя столами навсегда исправилась. По крайней мере до следующего перезапуска службы и / или перезагрузки машины. В какой момент, запустив этот последний SELECT снова никогда не вернется. Снова.

Вот где это начинает становиться еще более странным. Если я урежу это первым SELECT вернуться к версии с двумя столами:

USE tempdb;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2;

DROP TABLE Numbers;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO Numbers
FROM sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3
CROSS JOIN sys.objects s4;

Это также делает второй SELECT бежать вечно. Как и версия с одним столом. Каким-то образом эта версия с тремя столами является волшебной!

Что здесь происходит? Почему это медленно?

(И прежде чем кто-либо указывает, что я создаю постоянную таблицу в tempdb, Да, я знаю. Переход на фактические временные таблицы не имеет никакого значения.)

Добавленная информация:

Это SQL Server 2012 Developer Edition
Выход из EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = '[blocked_session_count] DESC' (Сценарий как XML, так что его можно прочитать здесь):



    
        <дд чч: мм: сс.мсс> 00 00:10:45.066 
         52 
         & lt;?query -
ВЫБЕРИТЕ ТОП 1000000 IDENTITY(INT, 1, 1) Number
В номера
ОТ sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3
CROSS JOIN sys.objects s4;

- & GT;? 
         мой собственный логин отредактирован 
         (99 мс)LCK_M_X 
         9 750 
         713 
         702 
         NULL 
        <заблокированный_счет_счет> 0 
         583 273 
         537 
         50 
         3 
        <Статус> приостановлено 
         2 
         NULL 
        <имя_хоста> имя моего собственного компьютера отредактировано 
        <Имя_базы_данных> Tempdb 
        <имя_программы> Microsoft SQL Server Management Studio - запрос 
         2013-11-23 23:48:19.473 
         2013-11-23 23:47:47.060 
         0 
         2013-11-23 23:59:04.560

Дополнительная информация:

Я помещаю это в базу данных tempdb, потому что она является частью скрипта, предназначенного для запуска в целинных установках, и там гарантированно есть база данных tempdb. Как я уже сказал, переход на глобальные временные таблицы ничем не отличается.

18

sql-server tempdb

Источник

user33219 22 ноя '13 в 22:04

2 ответа

Другие вопросы по тегам sql-server tempdb

user73226 24 ноя '13 в 15:37 2013-11-24 15:37 · Answer 1 · 2013-11-24 15:37

Я ~~могу~~ также воспроизвести это 100% времени на моей машине. (см. примечание в конце)

Суть проблемы в том, что вы вынимаете S блокирует строки системной таблицы в tempdb которые могут конфликтовать с замками, необходимыми для внутреннего tempdb очистка транзакций.

Когда эта работа по очистке распределяется на тот же сеанс, которому принадлежит S блокировка может произойти неопределенное зависание.

Чтобы избежать этой проблемы наверняка, вам нужно прекратить ссылаться на system объекты внутри tempdb,

Можно создать таблицу чисел, не ссылаясь ни на какие внешние таблицы. Следующее не требует чтения строк базовой таблицы и, следовательно, также не берет блокировок.

WITH Ten(N) AS 
(
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)   
SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO   Numbers
FROM   Ten T10,
       Ten T100,
       Ten T1000,
       Ten T10000,
       Ten T100000,
       Ten T1000000

Действия по воспроизведению

Сначала создайте процедуру

CREATE PROC P
AS
    SET NOCOUNT ON;

    DECLARE @T TABLE (X INT)
GO

Затем перезапустите службу SQL и в одном соединении выполните

WHILE NOT EXISTS(SELECT *
                 FROM   sys.dm_os_waiting_tasks
                 WHERE  session_id = blocking_session_id)
  BEGIN

      /*This will cause the problematic droptemp transactions*/
      EXEC sp_recompile 'P'

      EXEC P
  END;

SELECT *
FROM   sys.dm_os_waiting_tasks
WHERE  session_id = blocking_session_id

Затем в другом соединении запустить

USE tempdb;

SELECT TOP 1000000 IDENTITY(INT, 1, 1) Number
INTO #T
FROM sys.objects s1
CROSS JOIN sys.objects s2
CROSS JOIN sys.objects s3
CROSS JOIN sys.objects s4;

DROP TABLE #T

Похоже, что запрос, заполняющий таблицу Numbers, может попасть в ситуацию оперативной блокировки с внутренними системными транзакциями, которые очищают временные объекты, такие как переменные таблицы.

Мне удалось заблокировать таким образом идентификатор сессии 53. Он заблокирован на неопределенный срок. Выход из sp_WhoIsActive показывает, что этот спид проводит почти все время в подвешенном состоянии. В последовательных прогонах числа в reads столбец увеличивается, но значения в других столбцах остаются в основном такими же.

Длительность ожидания не показывает возрастающую закономерность, хотя указывает на то, что она должна периодически разблокироваться перед повторной блокировкой.

SELECT *
FROM   sys.dm_os_waiting_tasks
WHERE  session_id = blocking_session_id

Возвращает

+----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+
| waiting_task_address | session_id | exec_context_id | wait_duration_ms | wait_type |  resource_address  | blocking_task_address | blocking_session_id | blocking_exec_context_id |                                       resource_description                                       |
+----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+
| 0x00000002F2C170C8   |         53 |               0 |               86 | LCK_M_X   | 0x00000002F9B13040 | 0x00000002F2C170C8    |                  53 | NULL                     | keylock hobtid=281474978938880 dbid=2 id=lock2f9ac8880 mode=U associatedObjectId=281474978938880 |
+----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+

Использование идентификатора в описании ресурса

SELECT o.name
FROM   sys.allocation_units au WITH (NOLOCK)
       INNER JOIN sys.partitions p WITH (NOLOCK)
         ON au.container_id = p.partition_id
       INNER JOIN sys.all_objects o WITH (NOLOCK)
         ON o.object_id = p.object_id
WHERE  allocation_unit_id = 281474978938880

Возвращает

+------------+
|    name    |
+------------+
| sysschobjs |
+------------+

Бег

SELECT resource_description,request_status
FROM   sys.dm_tran_locks 
WHERE request_session_id = 53 AND request_status <> 'GRANT'

Возвращает

+----------------------+----------------+
| resource_description | request_status |
+----------------------+----------------+
| (246708db8c1f)       | CONVERT        |
+----------------------+----------------+

Подключение через ЦАП и запуск

SELECT id,name
FROM   tempdb.sys.sysschobjs WITH (NOLOCK)
WHERE %%LOCKRES%% = '(246708db8c1f)'

Возвращает

+-------------+-----------+
|     id      |   name    |
+-------------+-----------+
| -1578606288 | #A1E86130 |
+-------------+-----------+

Любопытно, что это такое

SELECT name,user_type_id
FROM tempdb.sys.columns
WHERE object_id = -1578606288

Возвращает

+------+--------------+
| name | user_type_id |
+------+--------------+
| X    |           56 |
+------+--------------+

Это имя столбца в табличной переменной, используемой хранимым процессом.

Бег

SELECT request_mode,
       request_status,
       request_session_id,
       request_owner_id,
       lock_owner_address,
       t.transaction_id,
       t.name,
       t.transaction_begin_time
FROM   sys.dm_tran_locks l
       JOIN sys.dm_tran_active_transactions t
         ON l.request_owner_id = t.transaction_id
WHERE  resource_description = '(246708db8c1f)'

Возвращает

+--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+
| request_mode | request_status | request_session_id | request_owner_id | lock_owner_address | transaction_id |    name     | transaction_begin_time  |
+--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+
| U            | GRANT          |                 53 |           227647 | 0x00000002F1EF6800 |         227647 | droptemp    | 2013-11-24 18:36:28.267 |
| S            | GRANT          |                 53 |           191790 | 0x00000002F9B16380 |         191790 | SELECT INTO | 2013-11-24 18:21:30.083 |
| X            | CONVERT        |                 53 |           227647 | 0x00000002F9B12FC0 |         227647 | droptemp    | 2013-11-24 18:36:28.267 |
+--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+

Итак SELECT INTO транзакция держит S заблокировать ряд tempdb.sys.sysschobjs относящиеся к табличной переменной #A1E86130, droptemp транзакция не может получить X заблокировать этот ряд из-за этого противоречивого S замок.

Повторный запуск этого запроса показывает, что transaction_id для droptemp транзакция неоднократно меняется.

Я предполагаю, что SQL Server должен распределить эти внутренние транзакции по пользовательским спидам и расставить приоритеты перед выполнением пользовательской работы. Таким образом, идентификатор сеанса 53 застрял в постоянном цикле, где он запускает droptemp транзакция, заблокирована пользовательской транзакцией, запущенной на том же spid. Откатывает внутреннюю транзакцию, а затем повторяет процесс бесконечно.

Это подтверждается отслеживанием различных событий блокировки и транзакций в SQL Server Profiler после зависания spid.

Profiler

Я также проследил события блокировки до этого.

Блокировка событий Блокировка

LockAquisitionPatternBlockingTransaction

Большинство блокировок общих ключей вынуты SELECT INTO транзакция по ключам в sysschobjs быть освобожденным немедленно. Исключением является первая блокировка на (246708db8c1f),

Это имеет некоторый смысл, так как план показывает сканы вложенных циклов [sys].[sysschobjs].[clst] [o] и поскольку временные объекты получают отрицательные объекты, они будут первыми строками, встреченными в порядке сканирования.

Я также столкнулся с ситуацией, описанной в OP, когда запуск трехстороннего перекрестного соединения, кажется, позволяет четырехстороннему успешному завершению.

Первые несколько событий в след для SELECT INTO Транзакции существуют совершенно по другому шаблону.

LockAquisitionPatternNonBlockingTransaction

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

Вместо того чтобы сохранять блокировку на первом ключе, а затем шаблон получения и отпускания последующих ключей, кажется, что они получают гораздо больше блокировок, не освобождая их изначально.

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

Обновить

Элемент подключения, о котором я говорил, не был помечен как исправленный, но теперь я использую SQL Server 2012 с пакетом обновления 2 (SP2) и теперь могу воспроизводить только временную самоблокировку, а не постоянную. Я все еще получаю самоблокировку, но после некоторого количества неудачных попыток выполнить droptemp Транзакция успешно завершена, похоже, возвращается к обработке пользовательской транзакции. После этого фиксирует системную транзакцию, затем выполняется успешно. Все еще на том же спиде. (в одном примере выполняется восемь попыток. Я не уверен, что это будет повторяться последовательно)

user61305 22 ноя '13 в 22:19 2013-11-22 22:19 · Answer 2 · 2013-11-22 22:19

Вместо того, чтобы преследовать эту проблему, почему бы вам просто не создать таблицу один раз в model базы данных, тогда она будет создана для вас в tempdb автоматически?

Для актуальной проблемы мы не знаем. Мое первое предположение было бы, что ваш начальный размер для вашего файла (ов) tempdb очень маленький (например, 1 МБ). Поэтому, когда вы создаете таблицу, она должна расширять файл (ы), чтобы вместить ее. Это может быть довольно дорого, особенно если у вас не включена мгновенная инициализация файла, а также может быть очень дорого расширять журнал, чтобы приспособиться к требуемой там деятельности.

Помимо этого, мы могли бы продолжать догадываться, но вы будете лучше подходить для расследования того, что на самом деле происходит. Вопросы, которые вы хотите задать:

Для спида, который пытается создать таблицу, что делает sys.dm_exec_requests скажем для wait_type?
Есть ли у него blocking_session_id?
Если так, что делает эта сессия?