c# - postquerynotification - sql server query notification

¿Cómo limpio SqlDependency desde la memoria de SQL Server?

Question

¿Cómo limpio el SQL Server para deshacerme de los objetos SqlDependency vencidos? Después de recibir el evento del objeto SqlDepedency , necesito crear uno nuevo antes de poder obtener un nuevo evento. Sin embargo, el uso de la memoria del proceso de SQL Server sube hasta que se agote la memoria permitida (SQL Server Express). ¿Cómo me deshago de las consultas antiguas?

Código:

// Func: RegisterTableListener
using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.DatabseEventConnectionString))
{
if (cmd == null)
{
    cmd = cn.CreateCommand();

    cmd.CommandType = CommandType.Text;
    cmd.CommandText = "SELECT HostName, LastStatus, LastDetails, xml FROM dbo.[SystemTable]";
}

lock (cmd)
{
    cmd.Connection = cn;
    cn.Open();
    cmd.Notification = null;

    //  creates a new dependency for the SqlCommand
    if (dep == null)
        dep = new SqlDependency(cmd);
    //  creates an event handler for the notification of data
    //      changes in the database.
    dep.OnChange += new OnChangeEventHandler(dependency_OnChange);


    using (SqlDataReader reader = cmd.ExecuteReader())
    {
    // code here to read
    }
}
}

// Func dependency_OnChange
//SqlDependency dep = sender as SqlDependency;
dep.OnChange -= dependency_OnChange;
RegisterTableListener();

Answer 1

Estoy enfrentando exactamente el mismo problema. Estoy creando un componente de acceso a datos que está almacenando en caché algunas consultas de una base de datos de SQL Server 2005. La memoria caché se invalida con este nuevo enfoque brillante, bueno, no tan nuevo, SqlDependency.

Debido a que este componente se usará en ASP.NET, así como en las aplicaciones de formularios y servicios de Windows, estoy buscando una forma común de (internamente) llamar a SqlDependency.Stop ().

Usar un finalizador fue mi primera idea, también, y esto no funcionó. Mi segundo intento fue usar un controlador de eventos para AppDomain.DomainUnload.

Después de todo, esto parece funcionar ... Pero el servidor web incorporado en VS 2005 se bloqueará durante 4-5 minutos con 100% de CPU mientras se ejecuta SqlDependy.Stop (). De hecho, no recuerdo ningún otro proceso que bloquee mi máquina (portátil Pentium M) reproducible tan mal que apenas podría mencionar el Administrador de tareas ... No esperaba que esto fuera posible desde el espacio de usuario e incluso el código administrado (SQL Server se está ejecutando en otro cuadro). Durante este tiempo, incluso el Monitor de rendimiento se niega a registrar nada, por lo que no puedo decir si hay muchos manejadores de Windows o excepciones de .NET involucradas o lo que sea ...

Llamar desde el evento Application_End funciona bien (y toma solo unos pocos milisegundos), sin embargo, esto es específico de ASP.NET.

Algunas ideas

Answer 2

Hay un comportamiento específico de la clase Microsoft SqlDependency. Aunque llame al método SqlDependency.Stop (), libere SqlCommand y SqlConnection, pero mantiene los grupos de conversación (sys.conversation_groups) y los puntos finales de conversación (sys.conversation_endpoints) en la base de datos. Parece que SQL Server carga cada punto final de conversación y usa toda la memoria permitida. Aquí pruebas que lo prueban. Por lo tanto, para limpiar todos los puntos finales de conversación no utilizados y liberar toda la memoria ocupada, debe iniciar este código SQL para su base de datos:

DECLARE @ConvHandle uniqueidentifier
DECLARE Conv CURSOR FOR
SELECT CEP.conversation_handle FROM sys.conversation_endpoints CEP
WHERE CEP.state = ''DI'' or CEP.state = ''CD''
OPEN Conv;
FETCH NEXT FROM Conv INTO @ConvHandle;
WHILE (@@FETCH_STATUS = 0) BEGIN
    END CONVERSATION @ConvHandle WITH CLEANUP;
    FETCH NEXT FROM Conv INTO @ConvHandle;
END
CLOSE Conv;
DEALLOCATE Conv;

Además, SqlDependency no le da la oportunidad de recibir TODOS los cambios de la tabla. Por lo tanto, no recibirá una notificación sobre los cambios durante la resubscripción de SqlDependency.

Para evitar todos estos problemas, utilicé otra realización de código abierto de la clase SqlDependency : SqlDependencyEx . Utiliza la activación de la base de datos y la notificación nativa de Service Broker para recibir eventos sobre los cambios de la tabla. Este es un ejemplo de uso:

int changesReceived = 0;
using (SqlDependencyEx sqlDependency = new SqlDependencyEx(
          TEST_CONNECTION_STRING, TEST_DATABASE_NAME, TEST_TABLE_NAME)) 
{
    sqlDependency.TableChanged += (o, e) => changesReceived++;
    sqlDependency.Start();

    // Make table changes.
    MakeTableInsertDeleteChanges(changesCount);

    // Wait a little bit to receive all changes.
    Thread.Sleep(1000);
}

Assert.AreEqual(changesCount, changesReceived);

Espero que esto ayude.