none
SQLDependency blocking deletions from the table it is monitoring RRS feed

  • Question

  • I am using SQLDependency to notify an application when changes are made to table in a SQL Server 2008 R2 database.

     

    Deleting a small number of rows from a table that SQLDependency is monitoring succeeds.

    However, any attempt to delete a large number of rows from this table in a single statement are blocked by the process monitoring the table.

     

    Batching up a large delete into smaller deletes also results in the process doing the deletes being blocked.

     

    Why is the SQLDependency blocking  the delete statement and how can I delete large numbers of rows from a table that is being monitored by SQLDependency?

    Wednesday, October 5, 2011 10:12 AM

Answers

All replies

  • Hi Andy,

    Welcome!

    According to your description, it's hard for me to give you right directions. Would you please send your code and T-sql script to let me reproduce your problem: v-alache@microsoft.com

    BTW, Here are some related links:

    http://msdn.microsoft.com/en-us/library/a52dhwx7(v=vs.80).aspx

    http://www.dreamincode.net/forums/topic/156991-using-sqldependency-to-monitor-sql-database-changes/

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, October 6, 2011 8:16 AM
    Moderator
  • Hi Andy,

    How large is the table and how many rows (approximately) are you deleting?

    Each row deletion is triggering an event and this many be difficult to scale to a large number of rows.

    SqlDependency is designed for monitoring more static tables where there is not a huge amount of insert/update/delete activity.


    Matt
    Sunday, October 9, 2011 6:44 PM
    Moderator
  • Hi Matt,

      Thanks for your reply.

     

      During our testing we hit blocking problems when we attempted to delete 500 rows in a single transaction from a table containing 5000 rows.

     

      Most of the time the tables we are monitoring are fairly static, however a few of our tables need to be cleared down and repopulated occasionally, these are the tables we are having issues with.

     

       I can’t find  anywhere where the limits of SQLDependency are documented, can someone point me in the right direction?

     

       I know very little about the internals of query notification: Are the blocking issues we are experiencing due to the rate of change or the volume of change?

     

      We are basically using SQLDependency to invalidate a cache when the contents a table are modified: If we create a table that contains a single row (notification table) and write a trigger that updates this notification table whenever an insert update or delete is applied to the main table, would monitoring the notification table using SQLDependency avoid any blocking issues?

     

           Thanks in advance

                   

                  Andrew

    Monday, October 10, 2011 9:18 AM
  • We don't have any published performance #s for SqlDependency. From my testing, if you delete 500 rows, this sends 500 notifications to the client, so I agree for performance in you scenario it would be better to have a dummy table that you listen to for changes versus listening to the actual table. This would avoid all locks on the primary table.
    Matt
    Monday, October 10, 2011 4:42 PM
    Moderator
  • Hi Andy,

    Any updates for your question?

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, October 14, 2011 1:32 AM
    Moderator