如何从 SQL Server 内存中清除 SqlDependency? [英] How do I clean SqlDependency from SQL Server memory?

问题描述

如何清理 SQL Server 以清除过期的 SqlDependency 对象?从 SqlDepedency 对象接收到事件后，我需要先创建一个新事件，然后才能获得新事件.但是，SQL Server 进程的内存使用量会增加，直到用完允许的内存 (SQL Server Express).如何摆脱旧查询?

How do I clean up the SQL Server to get rid of expired SqlDependency objects? After I receive the event from the SqlDepedency object, I need to create a new one before I can get a new event. However, the memory use of the SQL Server process climbs until it runs out of the allowed memory (SQL Server Express). How do I get rid of old queries?

代码:

// 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();

推荐答案

Microsoft SqlDependency 类有一个特定的行为.即使您调用 SqlDependency.Stop() 方法，释放 SqlCommand 和 SqlConnection - 它仍将会话组 (sys.conversation_groups) 和会话端点 (sys.conversation_endpoints) 保留在数据库中.看起来 SQL Server 加载了每个会话端点并使用了所有允许的内存.这里测试证明了这一点.因此，要清理所有未使用的会话端点并释放所有占用的内存，您必须为您的数据库启动此 SQL 代码:

There is a specific behavior of Microsoft SqlDependency class. Even though you call SqlDependency.Stop() method, release SqlCommand and SqlConnection - it still keeps conversation groups (sys.conversation_groups) and conversation endpoints (sys.conversation_endpoints) in the database. It looks like SQL Server loads every conversation endpoint and uses all allowed memory. Here tests that prove it. So, to clean all unused conversation endpoints and release all occupied memory you have to start this SQL code for your database:

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;

此外，SqlDependency 不会让您有机会接收表的所有更改.因此，您不会在 SqlDependency 重新订阅期间收到有关更改的通知.

Also, SqlDependency doesn't give you an opportunity to receive ALL changes of the table. So, you don't receive notification about changes during SqlDependency resubscription.

为了避免所有这些问题，我使用了 SqlDependency 类的另一个开源实现 - SqlDependencyEx.它使用数据库触发器和本机 Service Broker 通知来接收有关表更改的事件.这是一个用法示例:

To avoid all these problems I'd used another open source realization of SqlDependency class - SqlDependencyEx. It uses database trigger and native Service Broker notification to receive events about changes of the table. This is an usage example:

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

希望这会有所帮助.

这篇关于如何从 SQL Server 内存中清除 SqlDependency?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！