sql server - invalid - ¿Por qué esta consulta es lenta la primera vez que inicio el servicio?

invalid column name sql server

Question

De acuerdo. Esto es lo que trato de ejecutar:

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;

Esta es una de esas consultas de "hazme una tabla de números".

Aquí está el problema. Si ejecuto esto inmediatamente después de que se reinicie el servicio de SQL Server, se demorará una eternidad. No para siempre como en diez segundos y lo quiero más rápido. Por siempre como dentro, lo dejé pasar más de dos horas por accidente y todavía tenía que matarlo. Estoy pensando que nunca volverá nunca. Y normalmente toma menos de dos segundos en mi máquina para ejecutar esto.

Sin embargo , si hago esto en su lugar:

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;

Entonces funciona como es de esperar: el primer SELECT ejecuta en menos de dos segundos, al igual que el segundo. ¿Por qué no uso la versión de tres mesas? Porque no hay suficientes entradas en sys.objects para ese número en cubos para igualar un millón de filas de resultados. Pero ese ya no es el punto.

De todos modos, de aquí en adelante, puedo repetir ese segundo DROP / SELECT…INTO tanto como quiera, no hay problema. De alguna manera, esa primera versión de tres mesas lo hizo bien para siempre. Al menos, hasta la próxima vez que se reinicie el servicio y / o se reinicie la máquina. En ese punto, volver a ejecutar ese último SELECT nunca vuelve. Otra vez.

Aquí es donde empieza a ponerse más raro. Si comparto ese primer SELECT nuevo a una versión de dos tablas:

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;

Esto también hace que el segundo SELECT ejecute para siempre. Al igual que una versión de una mesa. De alguna manera, esa versión de tres mesas es mágica!

¿Que esta pasando aqui? ¿Por qué esto es lento?

(Y antes de que alguien señale que estoy creando una tabla permanente en tempdb , sí, lo sé. Cambiar a las tablas temporales reales no hace ninguna diferencia).

Información añadida:

• Esto es SQL Server 2012 Developer Edition
• La salida de EXEC sp_WhoIsActive @find_block_leaders = 1, @sort_order = ''[blocked_session_count] DESC'' (programada como XML para que se pueda leer aquí) es:

<?xml version="1.0" ?> <RESULTS1> <RECORD> <dd hh:mm:ss.mss>00 00:10:45.066</dd hh:mm:ss.mss> <session_id>52</session_id> <sql_text>&lt;?query -- 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; --?&gt;</sql_text> <login_name>my own login name redacted</login_name> <wait_info>(99ms)LCK_M_X</wait_info> <CPU> 9,750</CPU> <tempdb_allocations> 713</tempdb_allocations> <tempdb_current> 702</tempdb_current> <blocking_session_id>NULL</blocking_session_id> <blocked_session_count> 0</blocked_session_count> <reads> 583,273</reads> <writes> 537</writes> <physical_reads> 50</physical_reads> <used_memory> 3</used_memory> <status>suspended</status> <open_tran_count> 2</open_tran_count> <percent_complete>NULL</percent_complete> <host_name>my own machine name redacted</host_name> <database_name>tempdb</database_name> <program_name>Microsoft SQL Server Management Studio - Query</program_name> <start_time>2013-11-23 23:48:19.473</start_time> <login_time>2013-11-23 23:47:47.060</login_time> <request_id>0</request_id> <collection_time>2013-11-23 23:59:04.560</collection_time> </RECORD> </RESULTS1>

Más información añadida:

La razón por la que estoy poniendo esto en tempdb es que es parte de un script destinado a ser ejecutado en instalaciones vírgenes, y se garantiza que tempdb estará allí. Como dije, cambiar a las tablas temporales globales no es diferente.

Answer 1

En lugar de perseguir este problema, ¿por qué no creas la tabla una vez en la base de datos model , luego se creará automáticamente en tempdb ?

Para el problema real, no lo sabemos. Mi primera suposición sería que su tamaño inicial para sus archivos tempdb es muy pequeño (como, 1 MB). Entonces, cuando creas la tabla, tiene que expandir los archivos para acomodarlos. Esto puede ser bastante costoso, especialmente si no tiene habilitada la inicialización instantánea de archivos , y también puede ser muy costoso aumentar el registro para acomodar la actividad requerida allí también.

Aparte de eso, podríamos seguir adivinando, pero usted estará mejor preparado para investigar lo que realmente está sucediendo. Preguntas que querrás hacer:

1. Para el spid que intenta crear la tabla, ¿qué dice sys.dm_exec_requests para wait_type ?
2. ¿Tiene un blocking_session_id ?
3. Si es así, ¿qué está haciendo esa sesión?

Answer 2

También puedo reproducir este 100% del tiempo en mi máquina. (ver nota al final)

El problema es que está eliminando los bloqueos S en las filas de la tabla del sistema en tempdb que pueden entrar en conflicto con los bloqueos necesarios para las transacciones internas de limpieza de tempdb .

Cuando este trabajo de limpieza se asigna a la misma sesión que posee el bloqueo S puede producir un bloqueo indefinido.

Para evitar este problema, debe dejar de hacer referencia a los objetos del system dentro de tempdb .

Es posible crear una tabla de números sin hacer referencia a ninguna tabla externa. Lo siguiente no necesita leer filas de la tabla base y, por lo tanto, tampoco tiene bloqueos.

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 

Pasos para reproducir

Primero crea un procedimiento

CREATE PROC P
AS
    SET NOCOUNT ON;

    DECLARE @T TABLE (X INT)
GO

Luego reinicie el servicio SQL y en una conexión ejecute

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 

Luego en otra conexión ejecuta

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

La consulta que rellena la tabla de Números parece que se las arregla para entrar en una situación de bloqueo en vivo con las transacciones internas del sistema que limpian objetos temporales como las variables de la tabla.

Me las arreglé para obtener la sesión id 53 bloqueado de esta manera. Está bloqueado indefinidamente. La salida de sp_WhoIsActive muestra que este spid pasa casi todo el tiempo suspendido. En ejecuciones consecutivas, los números en la columna de reads aumentan, pero los valores en las otras columnas permanecen prácticamente iguales.

La duración de la espera no muestra un patrón creciente, aunque indica que debe desbloquearse periódicamente antes de bloquearse nuevamente.

SELECT *
FROM   sys.dm_os_waiting_tasks
WHERE  session_id = blocking_session_id

Devoluciones

+----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+
| 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 |
+----------------------+------------+-----------------+------------------+-----------+--------------------+-----------------------+---------------------+--------------------------+--------------------------------------------------------------------------------------------------+

Usando el id en la descripción del recurso

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 

Devoluciones

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

Corriendo

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

Devoluciones

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

Conectando a través del DAC y ejecutando

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

Devoluciones

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

Curioso por lo que es eso.

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

Devoluciones

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

Este es el nombre de la columna en la variable de tabla utilizada por el proceso almacenado.

Corriendo

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)'' 

Devoluciones

+--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+
| 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 |
+--------------+----------------+--------------------+------------------+--------------------+----------------+-------------+-------------------------+

Por lo tanto, la transacción SELECT INTO mantiene un bloqueo S en la fila en tempdb.sys.sysschobjs perteneciente a la variable de tabla #A1E86130 . La transacción droptemp no puede obtener un bloqueo X en esta fila debido a este bloqueo S conflictivo.

La ejecución de esta consulta revela repetidamente que el transaction_id para la transacción droptemp cambia repetidamente.

Especulo que SQL Server debe asignar estas transacciones internas en spids de usuario y priorizarlas antes de hacer el trabajo de usuario. Por lo tanto, el Id. De sesión 53 se bloquea en un ciclo constante en el que inicia una transacción droptemp , se bloquea cuando la transacción del usuario se ejecuta en el mismo spid. Deshace la transacción interna y luego repite el proceso de forma indefinida.

Esto se confirma al rastrear los diversos eventos de bloqueo y transacción en el Analizador de SQL Server una vez que el spid se cuelga.

También rastreé los eventos de bloqueo antes de eso.

Bloquear eventos de bloqueo

La mayoría de los bloqueos de claves compartidos que se extraen mediante la transacción SELECT INTO en las claves de sysschobjs se liberan de inmediato. La excepción es el primer bloqueo en (246708db8c1f) .

Esto tiene algún sentido, ya que el plan muestra escaneos de bucles anidados de [sys].[sysschobjs].[clst] [o] y dado que a los objetos temporales se les dan un objeto negativo, serán las primeras filas encontradas en el orden de escaneo.

También me encontré con la situación descrita en el OP, donde ejecutar una unión cruzada de tres vías primero parece permitir que la de cuatro vías tenga éxito.

Los primeros eventos en la traza para la transacción SELECT INTO son un patrón completamente diferente.

Esto fue después de un reinicio del servicio, por lo que los valores de los recursos de bloqueo en la columna de datos de texto no son directamente comparables.

En lugar de retener el bloqueo en la primera clave y luego un patrón de adquisición y liberación de claves posteriores, parece adquirir mucho más bloqueos sin liberarlos inicialmente.

Supongo que debe haber alguna variación en la estrategia de ejecución que evite el problema.

Actualizar

El elemento de conexión que mencioné acerca de esto no se ha marcado como fijo, pero ahora estoy en SQL Server 2012 SP2 y ahora solo puedo reproducir el bloqueo automático en lugar del permanente. Sigo teniendo el bloqueo automático, pero después de algunos intentos fallidos de ejecutar la transacción de droptemp correctamente, parece que volvemos a procesar la transacción del usuario. Después de eso, la transacción del sistema se ejecuta con éxito. Todavía en el mismo spid. (Se ejecutan ocho intentos en un ejemplo. No estoy seguro de si esto se repetirá constantemente)