Monitoring database changes without using 'SqlDependency' class

Question

I'm currently using Microsoft SQL Server 2008.

My production server has a very heavy load.

My C# application uses 'SqlDependency' class in order to monitor some table changes.

On development environment - the application works fine, and the change events from the SQL server gets fired correctly.

On production environment - the application gets change events even when there wasn't any change. I have read that the 'SqlDependency' doesn't perform well on heavy load.

I'm looking for other solution to this scenario, any suggestions ?

Answer 1

If you get notified when there is no change it means something else triggered the notification. You must always inspect the SqlNotificationEventArgs argument received on your OnChange handler and react appropriately based on the Info, Source and Type. In production you very likely get a notification like Info.Options signifying incorrect SET options (ie. an environment specific problem).

As for the comment about SqlDependency under load, you will have to link the source. But query notification overall is designed for cache invalidation of data that changes seldom.