Using the SQL Server Cache Dependency :
Enabling SQLDependency has two apporoaches.
· Polling Based
· Notification Based
Polling Approach:
With polling, the database server maintains information about when particular tables have last been updated in a table named AspNet_ SqlCacheTablesForChangeNotification.
This is the table that ASP.NET uses to learn which tables are being monitored for change notification and also to make note of any changes to the tables being monitored. The table has three columns
Ø tableName : Contains names of the tables enabled for SQL cache invalidation.
Ø notificationCreated : shows the date and time when the table was enabled for SQL cache invalidation.
Ø changeId : Used to communicate to ASP.NET any changes to the included tables. ASP.NET monitors this column for changes and, depending on the value, either uses what is stored in memory or makes a new database query.
The ASP.NET RUNTIME PERIODICALLY POLLS THE DATABASE to check what tables have changed since they were entered into the cache. Those tables whose data has been modified have their associated cache items evicted.The process is as below.
ASP.NET makes a separate SQL Server request on a completely different thread to the AspNet_ SqlCacheTablesForChangeNotification table to see if the changeId number has been incremented. If the number is changed, ASP.NET knows that an underlying change has been made to the SQL Server table and that a new result set should be retrieved. When it checks to see if it should make a SQL Server call, the request to the small AspNet_SqlCacheTablesForChangeNotification table has a single result. With SQL Server cache invalidation enabled, this is done so quickly that you
really notice the difference.
The Stored Proceduire used is : AspNet_SqlCachePollingStoredProcedure
This approach requires below steps when using the new SQL Server Cache Dependency features:
1. Enable your database for SQL Cache Dependency support.
2. Enable a table or tables for SQL Cache Dependency support.
3. Include SQL connection string details in the ASP.NET application’s web.config.
4. Utilize the SQL Cache Dependency features in one of the following ways:
Ø Programmatically create a SqlCacheDependency object in code.
Ø Add a SqlDependency attribute to an OutputCache directive.
Ø Add a SqlCacheDependency instance to the Response object via Response.AddCacheDependency.
Notification/Push Approach:
With notification, the database automatically alerts the ASP.NET runtime when the results of a particular query have been changed since the query was last executed, at which point the cached items associated with the query are evicted.
The notification option requires less setup than polling and is more granular since it tracks changes at the query level rather than at the table level.
Unfortunately, notifications are only available in the full editions of Microsoft SQL Server 2005 (i.e., the non-Express editions). However, the polling option can be used for all versions of Microsoft SQL Server from 7.0 to 2005.
You must perform two configuration steps to enable Push SQL cache dependencies:
· You must Configure Your Database by enabling the SQL Server 2005 Service Broker.
· You must Configure Your Application by starting the notification listener.
However there are significant limitations on the types of queries that you can use with Push dependencies. Here are some of the more significant limitations:
· The query must use two-part table names (for example, dbo.Movies instead of Movies) to refer to tables.
· The query must contain an explicit list of column names (you cannot use *).
· The query cannot reference a view, derived table, temporary table, or table variable.
· The query cannot reference large object types such as Text, NText, and Image columns.
· The query cannot contain a subquery, outer join, or self join.
· The query cannot use the DISTINCT, COMPUTE, COMPUTE BY, or INSERT keywords.
· The query cannot use many aggregate functions including AVG, COUNT(*), MAX, and MIN.
This is not a complete list of query limitations. For the complete list, refer to the Creating a Query for Notification topic in the SQL Server 2005 Books Online or the MSDN website (msdn.Microsoft.com).
References: Link1, Link2, Link3
Hope this helps.
Regards,
Arun
Disclaimer: The information contained in this message may be privileged, confidential, and protected from disclosure. If you are not the intended recipient, or an employee, or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer.
No comments:
Post a Comment