June 7, 2012 at 12:27 pm
Does anyone have a gut feel for the overhead of using Service Broker vs. Change Data Capture?
We need to push data to a client app when there are changes to a specific set of tables and I think we can implement it with either SB or CDC.
SB would involve putting a trigger on the table that throws the inserted/deleted info into a queue, then have a SP to process the queue and send the data up to the client. The trigger is fast (just needs to dump into a queue) and the upload is asynchronous (and will not block the modification, which is good), but would still be near real-time.
CDC would handle gathering up the data and putting it in the CDC table. The process would again be asynchronous and impact the DML change even less, but we would need a trigger on the CDC table to process the changes and send the data up.
Both will work (I did a proof-of-concept), but I don't have a good feel for which will be lighter on resources (memory/disk/cpu). I plan to put them both under load to see how they performed, but thought I'd ask here if anyone has had performance experience with one or the other.
Thanks,
Chad
[PS] - SQLDependency won't work for us because it only indicates that something changed and doesn't indicate the row itself that caused the change. Would have to scan the whole table, figure out what it was, then push the update. Replication won't work because we are pushing the changes up to a client app, not to another database. We could use update timestamps and poll for changes, but it seems like frequent polling would be more resource intensive than these other two options and we would have to do something to catch deletes too.
June 7, 2012 at 12:41 pm
When you say push to a client application, can you be a bit more specific?
June 7, 2012 at 12:52 pm
We have an app our clients (remote/off-site) use and developed technology to refresh their view in real-time when data changes on our servers. What they see is the most up-to-date info regardless of who or what caused it to change (them, us or someone somewhere else). There are several tiers we have to go through to get the data to the correct set of end users (those whose view would be impacted by the change) and we have it all working, we just need to figure out the best way to identify what changed and trigger the process to begin.
That may not have answered your question... if not, let me know.
June 7, 2012 at 12:56 pm
So, we are talking about cached data in the application. Have you looked at Query Notification?
June 7, 2012 at 1:31 pm
Yes, it is in a sense a cache. I thought of it more of a view than a cache, but the idea still holds. We're not refreshing the whole cache, just the portion that changed and each cache has the potential to be different from the rest (i.e. we need to target the clients that are updating, not just update all of them).
We did look at Query Notification. Well, we looked at SQLDependency which uses Query Notification if I understand it correctly. The reason we turned away from SQLDependency was that it only indicates that there was a change, not what the change was. We would need to add modification timestamps and some kind of delete log in order to capture only the subset of data that we need to be concerned about. So we would need a trigger to log the deletes to another table and to ensure that the timestamp wasn't coerced to some other value as part of the insert/update. At that point (if we have to have a trigger - OH THE PAIN!), I figured we might as well use the trigger to push the data out rather than just logging and waiting for some other process to poll. I mean, the trigger is already required and has the exact data we need, why not skip a step? SB made the process asynchronous since I didn't want the trigger to take any more time than absolutely necessary. Alternatively, CDC reads the t-log so we wouldn't need a trigger on the live table, but we would need one on the CDC table to let us know that something had happened.
June 7, 2012 at 1:41 pm
Well, I don't know anything about CDC and only a little about SB. I would think SB might be a bit easier to implement having used it to handle tasks during a mirrored failover.
June 7, 2012 at 1:56 pm
Thanks Lynn. I have more experience with Service Broker too, but I'm willing to learn and tackle something new if there is another option that is better and I'm more concerned with performance this time.
June 7, 2012 at 2:01 pm
As quickly as a trigger can send a message to a queue for SB, if you are more familiar with SB then that is the direction I would go.
June 15, 2012 at 3:55 am
With Service Broker you can have multi-threading so your queue can initiate multiple copies of the activation procedure, each picking up a single message from the queue. That could speed up the overall process by getting multiple CPUs to do the work simultaneously.
With CDC you do not have this advantage.
Here is an example of a queue configured to work on 4 threads simultaneously:
ALTER QUEUE queueName1
WITH ACTIVATION
( STATUS = ON,
PROCEDURE_NAME = actProc1,
MAX_QUEUE_READERS = 4,
EXECUTE AS SELF
);
GO
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply