SQL Server 2008 R2 Express Service Broker 正在使用所有可用内存

Question

我有一个在机器上运行的数据库，用于每秒更新信息。我的所有客户提供的数据都返回到一个源，该源又将数据发布到数据库。这一切都很好，我遇到的问题是我的服务代理和监听更改。

我正在使用 SqlDependency 对象来注册特定查询的侦听器。我按预期收到了事件，但是服务器最终变得不可用。我能够确定 Service Broker 使用了超过 900MB 的内存（使我超过了 Express 的 1GB 限制）。我的印象是我的事件一直留在记忆中而不是清除。每次收到事件时，我都会清除该事件侦听器并注册一个新事件侦听器。有没有更合适的方法来从数据库中清除该事件？

另外，我读过所有内容，每次注册新事件时都必须调用 Stop 和 Start。根据我的经验，第二次调用 stop 总是永远挂起。另外，如果 stop 清除所有事件，我有多个侦听器，当我收到一个侦听器时，我不想停止其他侦听器。

这是我用来注册和响应事件的代码：

using (SqlConnection cn = new SqlConnection(Properties.Settings.Default.DatabseEventConnectionString))
{
    using (SqlCommand cmd = cn.CreateCommand())
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SELECT Field1, Field2, Field3, Field4 FROM dbo.Table";
        cmd.Notification = null;

        SqlDependency dep = new SqlDependency(cmd);
        dep.OnChange += new OnChangeEventHandler(dependency_OnChange);

        cn.Open();

        using (SqlDataReader reader = cmd.ExecuteReader())
        {
           // Handle read here;
        }
    }
}

void dependency_OnChange(object sender, SqlNotificationEventArgs e)
{
    // If InvokeRequired returns True, the code
    // is executing on a worker thread.
    if (Dispatcher.CheckAccess())
    {
        SqlDependency dep = sender as SqlDependency;
        dep.OnChange -= new OnChangeEventHandler(dependency_OnChange);
        RegisterTableListener();
    }
    else
    {

        // Create a delegate to perform the thread switch.
        OnChangeEventHandler tempDelegate =
            new OnChangeEventHandler(dependency_OnChange);

        object[] args = { sender, e };

        // Marshal the data from the worker thread
        // to the UI thread.
        Dispatcher.Invoke(tempDelegate, args);
    }
}

知道为什么内存永远在攀升吗？

Answer 1

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

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 重新订阅期间不会收到有关更改的通知。

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

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

希望这会有所帮助。

Answer 2

我不是专家，但是......

你尝试过：

   using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
   {
      // Handle read here;
   }

并可能改变
dep.OnChange -= new OnChangeEventHandler(dependency_OnChange);
到
dep.OnChange -= dependency_OnChange;