una tipo temporales tablas tabla stored partir las eliminar ejemplo donde dinamica crear consultar consulta almacenan sql-server stored-procedures temp-tables sqlperformance

sql-server - tipo - tabla temporal sql server stored procedure



SQL Server SELECT INTO y bloqueo con tablas temporales (8)

Entonces, recientemente, un DBA está tratando de decirnos que no podemos usar la sintaxis de

SELECT X, Y, Z INTO #MyTable FROM YourTable

Para crear tablas temporales en nuestro entorno, porque esa sintaxis causa un bloqueo en TempDB durante la ejecución del procedimiento almacenado. Ahora, he encontrado una serie de cosas que detallan cómo funcionan las tablas temporales, el alcance de la ejecución, la limpieza y similares. Pero por mi vida, no veo nada sobre el bloqueo debido a su uso.

Estamos tratando de encontrar pruebas de que no deberíamos tener que pasar y crear CREATE TABLE #MyTable ... para todas nuestras tablas temporales, pero ninguna de las partes puede encontrar pruebas. Estoy buscando alguna información que la gente tenga.

Información Adicional

Actualmente trabaja con SQL Server 2005, y pronto será SQL Server 2008 (ediciones Enterprise)

¿Por qué no hacer lo siguiente?

SELECT X, Y, Z INTO #MyTable FROM YourTable WHERE 1 = 2

La declaración se ejecutará instantáneamente, creando su tabla temporal y evitando cualquier posible bloqueo. Entonces podrías insertarlo como de costumbre:

INSERT #MyTable SELECT X, Y, Z FROM YourTable

Bueno, si eso fuera cierto, mssql tendría problemas, ya que cualquier consulta grande puede usar tempdb para contener una copia de las filas. Esto a menudo se puede ver en los planes de consulta como un spool de tabla o puede ser utilizado por el operador HASH JOIN si se queda sin memoria para sus depósitos.

Puede ver el uso de variables de tabla, que mssql intentará almacenar en la memoria y mover a tempdb si crecen hasta ser grandes.

DECLARE @foo TABLE (x int, y int, z int) INSERT INTO @foo(x, y, z) SELECT x, y, z FROM YourTable

Por supuesto, debe evaluar si primero se requiere la tabla y la copia temporal. Aunque si la consulta es lo suficientemente compleja como para usar una tabla temporal es mucho más legible, también podría ser lo suficientemente compleja como para que una tabla temporal valga la pena.

Ese consejo ha estado flotando por mucho tiempo :

Cuellos de botella en SQL Server 6.5

Muchas personas usan una consulta SELECT ... INTO para crear una tabla temporal, algo como esto:

SELECT * INTO #TempTable FROM SourceTable

Mientras esto funciona, crea bloqueos contra la base de datos tempdb durante la instrucción SELECT (bastante tiempo si está rastreando muchos datos en la tabla de origen, y aún más si SELECT ... INTO está en el inicio de una transacción explícita de mayor duración) Mientras el bloqueo está en su lugar, ningún otro usuario puede crear tablas temporales. La ubicación real del cuello de botella es un bloqueo en las tablas del sistema tempdb. En versiones posteriores de SQL Server, el modelo de bloqueo ha cambiado y se evita el problema.

Afortunadamente, solo fue un problema para SQL 6.5. Fue arreglado en 7.0 y más tarde.

Esto probablemente flotará durante mucho tiempo, alimentando los bolsillos de varios "consultores". Como todos los mitos, tiene un núcleo de verdad y una gran cantidad de BS.

La verdad: SQL 2000 y las versiones anteriores tenían problemas de contención conocidos en torno a la asignación de extensiones en tempdb. De hecho, la afirmación era cierta en todas las bases de datos, pero más visible en tempdb debido a un uso intensivo de tempdb. Está documentado en KB328551 :

Cuando se utiliza mucho la base de datos tempdb, SQL Server puede experimentar contención cuando intenta asignar páginas.

Desde la salida de la tabla del sistema sysprocesses, el origen de espera puede aparecer como "2: 1: 1" (página PFS) o "2: 1: 3" (página SGAM). Dependiendo del grado de conflicto, esto también puede hacer que SQL Server parezca no responder por períodos cortos.

Estas operaciones usan fuertemente tempdb:
Repite crear y soltar tablas temporales (locales o globales).
Variables de tabla que usan tempdb para fines de almacenamiento.
Mesas de trabajo asociadas con CURSORES.
Tablas de trabajo asociadas con una cláusula ORDER BY.
Tablas de trabajo asociadas con una cláusula GROUP BY.
Archivos de trabajo asociados con HASH PLANS.

El uso intenso y significativo de estas actividades puede conducir a problemas de contención.

Se agregó una -T1118 traza -T1118 en SQL Server 2000 SP3 que forzaba a SQL a usar un algoritmo round-robin para asignaciones de páginas mixtas. Este nuevo algoritmo, cuando se correlaciona con la práctica de implementar tempdb encima de un conjunto de archivos de igual tamaño, uno para cada CPU, aliviaría la disputa. El indicador de seguimiento aún está presente en SQL 2005/2008, aunque es mucho menos probable que sea necesario.

Todo lo demás acerca de este mito es más o menos BS.

  • ¿el uso de tablas #temp causa bloqueo? No. En el peor, aumenta la contención bajo carga en SQL 2000 y versiones anteriores, pero eso está muy lejos de decir que bloquea cualquier cosa. Tendría que medir primero y ver que este es el caso, y si es así implementar las medidas correctivas (asignar un archivo tempdb por CPU, hacerlos de igual tamaño, activar -T1118).
  • Selecciona ... en #temp bloquear algo durante la selección? Realmente no.
  • Selecciona ... en #temp bloquear algo durante la duración del procedimiento almacenado que contiene la selección? Diablos no Solo leí esa afirmación y estallé en carcajadas.

Para más detalles, hay este artículo: Conceptos erróneos alrededor de TF1118 .

Puede obtener el bloqueo si crea tablas #temp dentro de una transacción. Si bien esto generalmente no se recomienda, he visto esto mucho.

Sin embargo, el bloqueo que esto causa está en algunas tablas del sistema en tempdb que no afectan a otras conexiones desde la creación de tablas temporales (excepto tal vez para las versiones SQL anteriores a 2000). Significa que ejecutar sp_spacesused en tempdb se bloqueará a menos que establezca el nivel de aislamiento de transacción para que no se lea. También las propiedades de visualización en tempdb de SSMS fallarán y el tiempo de espera sin duda se debe a que está utilizando el nivel de aislamiento de transacción comprometido de lectura.

Yo diría que la falta de una prueba de bloqueo significa que no hay bloqueo, que es su prueba. ¿Por qué el método en el que se crea la tabla temporal (CREAR o SELECCIONAR ... EN) marca la diferencia al bloquear TempDB?

SELECT INTO #temp_table contiene un bloqueo de SELECT INTO #temp_table en tempdb durante la vigencia de la instrucción porque parte del trabajo que está logrando es crear la tabla. Esto es fundamentalmente diferente que crear primero la tabla usando CREATE TABLE #.... y luego ejecutar un conjunto basado en INSERT. SELECT INTO tiene ventajas sobre INSERT , en particular, la operación se registra mínimamente si el modelo de recuperación de la base de datos es un registro simple o agrupado.