El mito de que la tabla DROP y TRUNCATE no están registradas

existe un mito persistente en el mundo de SQL Server de que los comandos DROP TABLE y TRUNCATE TABLE no están registrados.

no lo son. Ambos están completamente registrados, pero eficientemente registrados.

Puedes demostrártelo fácilmente., Ejecute el siguiente código para configurar una base de datos de prueba y la tabla, y tenemos 10.000 filas de nuestra tabla:

Resultados:

Y, a continuación, el código siguiente, que trunca la tabla en una transacción y comprueba el número de fila:

BEGIN TRAN;GOTRUNCATE TABLE ;GO SELECT COUNT (*) AS N'RowCount'FROM ;GO

Resultados:

Ahora, la tabla está vacía., Pero puedo revertir la transacción y poner todos los datos de nuevo:

ROLLBACK TRAN;GO SELECT COUNT (*) AS N'RowCount'FROM ;GO

Resultados:

Claramente el TRUNCATE operación debe ser iniciado lo contrario, el deshacer la operación no iba a funcionar.

entonces, ¿de dónde viene el concepto erróneo?

viene del comportamiento de las operaciones DROP y TRUNCATE en tablas grandes., Se completarán casi instantáneamente, y si mira en el registro de transacciones usando fn_dblog justo después, solo verá un pequeño número de registros generados de la operación. Ese pequeño número no se correlaciona con el tamaño de la tabla que se trunca o se cae, por lo que parece que las operaciones DROP y TRUNCATE no están registradas.

pero están completamente registrados, como demostré anteriormente. ¿Dónde están los registros de las operaciones?,

la respuesta es que los registros de registro se crearán, simplemente no inmediatamente, por un mecanismo llamado ‘deferred drop’, que se agregó en SQL Server 2000 SP3.

cuando se elimina o trunca una tabla, todas las páginas de archivos de datos asignadas a la tabla deben desasignarse.,Uire un bloqueo de asignación exclusivo en la extensión
sondee el bloqueo de página para cada página en la extensión (adquiera el bloqueo en modo exclusivo e inmediatamente suéltelo, asegurándose de que nadie más tenga la página bloqueada)
No libere el bloqueo de extensión, garantizando que nadie más pueda usar esa extensión
mover a la siguiente extensión
End

Como todos los bloqueos de extensión se mantuvieron hasta el final de la operación, y cada bloqueo toma una pequeña cantidad de memoria, era posible que el administrador de bloqueos se quedara sin memoria cuando div id=»c46cdb1324″> oTRUNCATE de una tabla muy grande ocurrió., Algunos clientes de SQL Server comenzaron a encontrar que se encontraban con condiciones de falta de memoria en SQL Server 2000, ya que las tablas crecieron muy grandes y superaron ampliamente el crecimiento de la memoria del sistema.

el mecanismo de caída diferida simula la operación DROP o TRUNCATE completándose inmediatamente, desenganchando las asignaciones para la tabla y poniéndolas en la ‘cola de caída diferida’, para su posterior procesamiento por una tarea en segundo plano. Esta operación de desenganche y transferencia solo genera un puñado de registros de registro., Esta es la operación que está siendo realizada y revertida en mi ejemplo de código anterior.
la ‘tarea de fondo de drop diferido’ gira cada pocos segundos y des-asigna todas las páginas y extensiones en la cola de drop diferido en pequeños lotes, garantizando que la operación no se quede sin memoria. Todas estas desasignaciones están completamente registradas, pero recuerde que la desasignación de una página llena de datos o registros de índice no registra las eliminaciones individuales de esos registros; en su lugar, toda la página solo se marca como desasignada en el mapa de bytes de asignación PFS (espacio libre de página) relevante.,

a partir de SQL Server 2000 SP3, cuando realice un DROP o TRUNCATE de una tabla, solo verá unos pocos registros generados. Si espera un minuto más o menos, y luego mira en el registro de transacciones de nuevo, verá que se han generado miles de registros de registro mediante la operación de abandono diferido, cada uno de ellos desasignando una página o extensión. La operación se registra completa y eficientemente.,

he Aquí un ejemplo, con el escenario que hemos creado anteriormente:

CHECKPOINT;GOTRUNCATE TABLE ;GOSELECT COUNT (*) AS N'LogRecCount'FROM fn_dblog (NULL, NULL);GO

Resultados:

Como puede ver, hay claramente no son registros de registro de la cancelación de la asignación de los 10.000 páginas, más de 1.250 extensiones en la tabla Tablaprueba.

si espero unos segundos, y luego corro el código fn_dblog nuevamente, obtengo:

Puede que te preguntes por qué no hay al menos 10,000 registros de Registro, uno por cada página que se desasigne., Esto se debe a que las desasignaciones de páginas incluso se registran de manera eficiente, con un registro de registro que refleja los cambios de asignación de páginas PFS para 8 páginas de archivos de datos consecutivas, en lugar de un registro de registro para cada página de archivo de datos que refleja su cambio de estado de asignación en la página PFS.

SQL Server siempre intenta producir el menor registro de transacciones posible, sin dejar de cumplir las reglas sobre el registro completo o mínimo basado en el modelo de recuperación actual., Si desea ver los registros reales generados por los mecanismos de desenganche y Transferencia y deferred drop, simplemente sustituya * por COUNT ( * ) en el código fn_dblog anterior y busque una transacción con el nombre de transacción establecido en DeferredAllocUnitDrop::Process.

en futuras publicaciones analizaré los aspectos internos que sustentan otros mitos persistentes sobre los aspectos de rendimiento del motor de almacenamiento de SQL Server.