I’m going to make this quick and dirty. I’ve set up my ASP.NET web applications to leverage SQLCacheDependency with SQL Server 2005 Query Notifications. The setup process takes a some time and debugging can be tricky. But the payoff can be enormous. The bottom line is that the performance increase on page load times is well worth the effort, especially for data that doesn’t change all that often. I found it really useful for caching product data on my eCommerce sites, especially as the number of products in the system grew to over 5,000. However, I always seem to miss a step when when configuring my SQL Server 2005 databases; so this post is for my own reference, but if it helps someone else out there, even better.

The original article I used as a basis for configuring my applications is at the url below:

http://www.simple-talk.com/sql/t-sql-programming/using-and-monitoring-sql-2005-query-notification/

Follow the steps there and you’re good to go. Especially use the SQL Profiler debug steps at the bottom of the article if you get tripped up. One thing that I always had to do with my databases to get everything to work properly was execute the following query:

use <dbname>
EXEC dbo.sp_changedbowner @loginame = N'<dbuser>', @map = false

Make sure you use this caching technique responsible though. The Query Notifications can use up processing power so only cache data that you know will give your application a performance bump. Also beware of memory usage as you cache more and more data. You could end up caching so much data your application needs to restart often and that could cause slow page load times.