SqlDependency leads to strange queryplans

Question

Hi All,

we have encountered strange issues in our database systems. In a client the message TimeOutException was found and at first we could not pinpoint the problem. After a week we found out that 3 of our 90 tables where using op to 2 minutes for doing a simple update. Selects no problem, insert no problem but updates...... It was taking ages. In the query plan of updates on each of these three tables we saw strange things happen. The query plan stated that it needed parallelism over tables that where not involved in the UPDATE table SET field=value WHERE id=id_value. The query plan showed a locking index that was not part of the system It was hanging in sys.[query_notification_objectid]. We could not even delete. After a loing investigation we could see that a query setup in the SqlDependency object was JOINing over 6 tables. We also found out that a windows service had no try catch block in its code and that that thread was not working anymore due to some error (of course not logged). The only way we could the database get working again was to restart the database, and restart that service again. It was a question of time before the database hung itself up again. We have changed the windows service. One change was the try catch, the other change was not using a JOIN over 6 tables anymore but only notify if a change happens in 1 table. We updated the service on production and after 3 days we see normal query plans again, no parallelism. The index created by probably the service broker was gone.
What we conclude now is that a query for SqlDependency over more than 1 table the SQL Server creates an index. That a SqlDependency object is gone leads to a database not giving an answer anymore but not cleaning up the created index until after timeout.
Does anyone have encountered the same issues when using Service Broker in SQL Server and using SqlDependency for receiving notifcations ?

Answer 1

When you set up a query notification with SqlDependency, SQL Server uses the same framework as for indexed views. I don't know if it exactly creates an index, but you certainly gets ab overhead comparable to if you had created an indexed view. When you perform an update on one of the tables in the query, SQL Server needs to compute if the result actually changes.

Answer 2

Hi @Herman Talstra ，

Please refer to this similar thread: https://stackoverflow.com/questions/13033362/sqldependency-service-broker
Hope this could give you some ideas.

Best regards,
Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.