Problem with performance

Jan 26, 2016 at 7:01 AM
I noticed a small problem
If we have a large number of DML operations each operation is carried out in their transactions we see small problem with to get data from table.
I suggest that the problem is related to the lock a table if on this table we have a trigger for "Table Dependency" component.
Jan 29, 2016 at 11:08 AM
Hello tolik51

Transaction are needed because if a message is extracted from QUEUE and something goes wrong, being in a transaction, the message remains in QUEUE.
Jan 29, 2016 at 11:14 AM
Edited Jan 29, 2016 at 11:14 AM
Also....keep in consideration:

TAKEN from:
Pro SQL Server 2008 Service Broker
by Klaus Aschenbrenner

When to Use Query Notifications

Query notifications are designed for data that doesn’t change frequently. They are also best
used in server-side applications rather than client-side applications. Remember that each
request for a notification is registered in SQL Server. If thousands of client applications each
request a notification, this can create a scalability problem on your database server.
Microsoft’s recommendation is that for client-side applications, you should limit the use of
query notifications to no more than ten concurrent users.

For large-scale applications, query notifications can be a powerful addition to meet high
load demands. Imagine a large-scale website that provides online information to thousands or
even millions of users. Rather than having each user’s update trigger yet another query on the
server to see which information is available, the query can be cached, and matching queries
can be served directly from the cache.

On a smaller scale, drop-down lists are another typical set of data that is requested frequently
but not updated often. Product lists, state lists, country lists, vendors, salespeople,
and other information that changes at a much lower frequency than it is requested are great
candidates for notifications.