如何从SQL Server内存干净的SqlDependency？ [英] How do I clean SqlDependency from SQL Server memory?

问题描述

我如何收拾SQL Server以摆脱过期的SqlDependency 的对象？我收到来自 SqlDepedency 对象的活动结束后，我需要创建一个新的之前，我可以得到一个新的事件。但是，内存使用SQL Server进程的攀升，直到用完允许的内存（SQL Server的防爆preSS）的。我该如何摆脱旧的查询？

code：

  // FUNC：RegisterTableListener
使用（SqlConnection的CN =新的SqlConnection（Properties.Settings.Default.DatabseEventConnectionString））
{
如果（CMD == NULL）
{
    CMD = cn.CreateCommand（）;    cmd.CommandType = CommandType.Text;
    cmd.CommandText =SELECT主机名，LastStatus，L​​astDetails，XML DBO的[SystemTable。
}锁定（CMD）
{
    cmd.Connection = CN;
    cn.Open（）;
    cmd.Notification = NULL;    //创建了一个的SqlCommand新的依赖
    如果（DEP == NULL）
        DEP =新的SqlDependency（CMD）;
    //创建数据的通知的事件处理程序
    //数据库的变化。
    dep.OnChange + =新OnChangeEventHandler（dependency_OnChange）;
    使用（SqlDataReader的读卡器= cmd.ExecuteReader（））
    {
    // code这里阅读
    }
}
}// Func键dependency_OnChange
//的SqlDependency DEP =发件人为的SqlDependency;
dep.OnChange  -  = dependency_OnChange;
RegisterTableListener（）;
 

解决方案

有是微软的SqlDependency类的特定行为。即使你调用SqlDependency.Stop（）方法，释放的SqlCommand和SqlConnection的 - 它仍然保持通话组（sys.conversation_groups）和会话端点（sys.conversation_endpoints）在数据库中。它看起来像SQL Server的负载每个会话端点，并使用所有允许的内存。 <一href=\"https://github.com/dyatchenko/ServiceBrokerListener/blob/master/ServiceBrokerListener/ServiceBrokerListener.UnitTests/SqlDependencyTest.cs\">Here这证明它的测试。因此，清理所有未使用的交谈端点和释放你要开始这个SQL code为你的数据库的所有占用的内存：

 定义@ConvHandle唯一标识符
DECLARE转化率CURSOR FOR
SELECT CEP.conversation_handle FROM sys.conversation_endpoints CEP
WHERE CEP.state =DI或CEP.state =CD
OPEN转化率;
FETCH NEXT FROM转化率INTO @ConvHandle;
WHILE（@@ FETCH_STATUS = 0）BEGIN
    事后的清理结束对话@ConvHandle;
    FETCH NEXT FROM转化率INTO @ConvHandle;
结束
CLOSE转化率;
DEALLOCATE转化率;
 

此外，的SqlDependency不给你机会获得该表的所有变化。所以，你不要的SqlDependency重新订阅过程中收到有关更改的通知。

要避免我已经使用的SqlDependency类的另一个开源实现所有这些问题 - <一个href=\"https://github.com/dyatchenko/ServiceBrokerListener/blob/master/ServiceBrokerListener/ServiceBrokerListener.Domain/SqlDependencyEx.cs\">SqlDependencyEx.它采用数据库触发器和本地服务代理通知接收有关表的变化的事件。这是一个使用示例：

  INT changesReceived = 0;
使用（SqlDependencyEx的SqlDependency =新SqlDependencyEx（
          TEST_CONNECTION_STRING，TEST_DATABASE_NAME，TEST_TABLE_NAME））
{
    sqlDependency.TableChanged + =（O，E）=＆GT; changesReceived ++;
    sqlDependency.Start（）;    //使表的更改。
    MakeTableInsertDeleteChanges（changesCount）;    //等待一点点地接受所有更改。
    Thread.sleep代码（1000）;
}Assert.AreEqual（changesCount，changesReceived）;
 

希望这有助于。

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?

Code:

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

解决方案

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;

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.

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

Hope this helps.

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