3

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 ?

Improve this question

1 Answer 1

Reset to default
2

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Can I set the event would fire only when a some of "SqlNotification" condition will occure ? For example: event will fire up when the Type is 'Subscribe' and Source is 'Statement'
The events fire because you have an incorrect setup. You can't fix your problem by simply declaring "I want to ignore my problem, make it magically work". Fix the problem reported by the event.
What do you mean by saying incorrect setup ? For example: I'm getting SqlNotificationInfo of "Invalid". How can I check what is the problem, or tell SQL not to raise the event when getting it ?
Invalid: A statement was provided that cannot be notified (for example, an UPDATE statement).. You need to troubleshoot and investigate. Use the MSDN to clarify what the event args values mean. Read Creating a Query for Notification to understand what is valid and what not.
OK, I moved to production a version where I write to the event log. I'm getting Info of "Resource", which I've read in the API that: "Resource Fires as a result of server resource pressure. " How can I tell SQL not to throw this kind of event ?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.