una - Disparadores asincrónicos en SQL Server 2005/2008
sql server que es (9)
Crear tabla (s) de historial. Al actualizar (/ eliminar / insertar) la tabla principal, inserte valores antiguos de registro (pseudo-tabla eliminada en el desencadenante) en la tabla de historial; también se necesita información adicional (marca de tiempo, tipo de operación, tal vez contexto del usuario). Los nuevos valores se guardan en la tabla en vivo de todos modos.
De esta forma, los desencadenantes se ejecutan rápidamente (er) y puede cambiar las operaciones lentas al visor de registro (procedimiento).
Tengo desencadenantes que manipulan e insertan una gran cantidad de datos en una tabla de seguimiento de cambios para fines de auditoría en cada inserción, actualización y eliminación.
Este desencadenador hace su trabajo muy bien, al usarlo podemos registrar los valores antiguos / nuevos valores deseados según los requisitos del negocio para cada transacción.
Sin embargo, en algunos casos en que la tabla fuente tiene muchas columnas, la transacción puede demorar hasta 30 segundos, lo cual es inaceptable.
¿Hay alguna manera de hacer que el desencadenador se ejecute de forma asíncrona? Cualquier ejemplo.
Hay un conflicto básico entre "hace su trabajo muy bien" e "inaceptable", obviamente.
Me parece que estás tratando de usar disparadores de la misma manera que usarías los eventos en una aplicación de procedimientos OO, que en mi humilde opinión no se correlaciona.
Llamaría a cualquier lógica de activación que demora 30 segundos, no más de 0.1 segundos, como disfuncional. Creo que realmente necesita rediseñar su funcionalidad y hacerlo de otra manera. Diría "si quieres que sea asíncrono", pero no creo que este diseño tenga sentido en ninguna forma.
En cuanto a los "desencadenantes asincrónicos", el conflicto fundamental básico es que nunca se podría incluir tal cosa entre las instrucciones BEGIN TRAN y COMMIT TRAN porque se ha perdido la pista de si tuvo éxito o no.
Me pregunto si podría etiquetar un registro para el seguimiento del cambio insertando en una tabla de "demasiado proceso", incluyendo quién hizo el cambio, etc.
Entonces podría aparecer otro proceso y copiar el resto de los datos de forma regular.
No es que yo sepa, ¿pero está insertando valores en la tabla de auditoría que también existen en la tabla base? De ser así, podría considerar rastrear solo los cambios. Por lo tanto, una inserción podría rastrear el tiempo de cambio, usuario, extra y un montón de NULL (en efecto, el valor anterior). Una actualización tendría el tiempo de cambio, el usuario, etc. y el valor anterior de la columna modificada solamente. Una eliminación tiene el cambio en, etc. y todos los valores.
Además, ¿tiene una tabla de auditoría por tabla base o una tabla de auditoría para la base de datos? Por supuesto, el último puede resultar más fácil en esperas ya que cada transacción intenta escribir en una tabla.
No puede hacer que el desencadenador se ejecute de forma asíncrona, pero puede hacer que el desencadenador envíe de forma síncrona un mensaje a la cola de SQL Service Broker . La cola puede ser procesada asincrónicamente por un procedimiento almacenado.
Para realizar un procesamiento asincrónico, puede usar Service Broker, pero no es la única opción, también puede usar objetos CLR.
El siguiente es un ejemplo de un procedimiento almacenado (AsyncProcedure) que asincrónico llama a otro procedimiento (SyncProcedure):
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Runtime.Remoting.Messaging;
using System.Diagnostics;
public delegate void AsyncMethodCaller(string data, string server, string dbName);
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void AsyncProcedure(SqlXml data)
{
AsyncMethodCaller methodCaller = new AsyncMethodCaller(ExecuteAsync);
string server = null;
string dbName = null;
using (SqlConnection cn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("SELECT @@SERVERNAME AS [Server], DB_NAME() AS DbName", cn))
{
cn.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
reader.Read();
server = reader.GetString(0);
dbName = reader.GetString(1);
}
}
methodCaller.BeginInvoke(data.Value, server, dbName, new AsyncCallback(Callback), null);
//methodCaller.BeginInvoke(data.Value, server, dbName, null, null);
}
private static void ExecuteAsync(string data, string server, string dbName)
{
string connectionString = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI", server, dbName);
using (SqlConnection cn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("SyncProcedure", cn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@data", SqlDbType.Xml).Value = data;
cn.Open();
cmd.ExecuteNonQuery();
}
}
private static void Callback(IAsyncResult ar)
{
AsyncResult result = (AsyncResult)ar;
AsyncMethodCaller caller = (AsyncMethodCaller)result.AsyncDelegate;
try
{
caller.EndInvoke(ar);
}
catch (Exception ex)
{
// handle the exception
//Debug.WriteLine(ex.ToString());
}
}
}
Utiliza delegados asíncronos para llamar a SyncProcedure:
CREATE PROCEDURE SyncProcedure(@data xml)
AS
INSERT INTO T(Data) VALUES (@data)
Ejemplo de llamada AsyncProcedure:
EXEC dbo.AsyncProcedure N''<doc><id>1</id></doc>''
Desafortunadamente, la asamblea requiere un permiso INCORRECTO.
SQL Server 2014 introdujo una característica muy interesante llamada Durabilidad retrasada . Si puede tolerar perder algunas filas en caso de un evento catastrófico, como un bloqueo del servidor, realmente podría aumentar su rendimiento en schenarios como el suyo.
La durabilidad de la transacción retrasada se logra utilizando las grabaciones de registro asíncronas en el disco. Los registros del registro de transacciones se guardan en un búfer y se escriben en el disco cuando se llena el búfer o cuando se produce un evento de descarga del búfer. La durabilidad de la transacción demorada reduce la latencia y la contención dentro del sistema
La base de datos que contiene la tabla primero debe ser alterada para permitir una mayor durabilidad.
ALTER DATABASE dbname SET DELAYED_DURABILITY = ALLOWED
Entonces podría controlar la durabilidad por transacción.
begin tran
insert into ChangeTrackingTable select * from inserted
commit with(DELAYED_DURABILITY=ON)
La transacción se comprometerá como duradera si la transacción es de base de datos cruzada, por lo que esto solo funcionará si su tabla de auditoría está ubicada en la misma base de datos que el activador.
También existe la posibilidad de modificar la base de datos como forzada en lugar de permitida. Esto hace que todas las transacciones en la base de datos se retrasen con el tiempo.
ALTER DATABASE dbname SET DELAYED_DURABILITY = FORCED
Para la durabilidad retrasada, no hay diferencia entre un apagado inesperado y un cierre / reinicio esperado de SQL Server. Al igual que los eventos catastróficos, debe planificar la pérdida de datos. En un apagado / reinicio planificado, algunas transacciones que no se han escrito en el disco se pueden guardar primero en el disco, pero no debe planificarlo. Planee como si un apagado / reinicio, ya sea planificado o no, pierde los datos de la misma manera que un evento catastrófico.
Se espera que este extraño defecto se aborde en una versión futura, pero hasta entonces puede ser conveniente asegurarse de ejecutar automáticamente el procedimiento ''sp_flush_log'' cuando el servidor SQL se reinicia o se apaga.
Sospecho que su desencadenante es uno de estos desencadenantes genéricos de generación de cs / texto diseñados para registrar todos los cambios para toda la tabla en un solo lugar. Bueno en teoría (quizás ...), pero difícil de mantener y usar en la práctica.
Si pudieras ejecutar de forma asíncrona (lo que aún requeriría almacenar datos en algún lugar para volver a iniciar sesión más adelante), entonces no estás auditando ni tienes historial para usar.
¿Quizás podría ver el plan de ejecución del disparador y ver qué bit está tardando más?
¿Puedes cambiar la forma en que auditas, digamos, por mesa? Puede dividir los datos de registro actuales en las tablas relevantes.
estos artículos muestran cómo usar Service Broker para la auditoría asíncrona y deberían ser útiles: