Profiling SQL Server 2005 query notifications for basic articles

In this series of articles, we'll delve into how to combine the. NET 2.0 and SQL Server 2005 query notification features to inform the application when critical data changes in order to eliminate the recurring query database.

First, the introduction

One of the typical problems with database applications is updating stale data.

Imagine a typical E-commerce site that displays products and their classifications. A vendor's product list is likely to change infrequently, and its categorized list will not even change frequently. However, each time a user browses to the site, the lists must be repeatedly queried from the database. This is clearly a typical inefficient resource utilization, and developers and architects are racking their brains to try to reduce this waste.

Buffering technology is one of the techniques of "minimizing" the repeated querying of this almost "stagnant" data. This data can be queried once and stored in a buffer, and the application can repeatedly access data from the cache. Occasionally, the cache is updated to get new data. However, there are several problems with time scheduling for updating the cache. How long does it have to operate? For example, how often do you want your product classification to change once in a while? Once every few months? How about refreshing the buffer every two months? Do you know what's going to happen? After you flush the cache, the category is updated, and it will remain stale for two months before the next refresh.

Query notification is a new outcome of Microsoft's Ado.net and SQL Server team collaboration. In short, a query notification allows you to buffer the data and issue notifications only when data in SQL Server has changed. Once notified, you can refresh your buffer or take any action you need.

A new feature "Service Broker" introduced in SQL Server 2005 makes it possible to query notifications. Service Broker introduces the queue mechanism into database management, which uses a set of queues to communicate with the service, which in turn knows how to communicate back to call the corresponding entity. In fact, these queues and services are some of the same class objects as tables, views, and stored procedures. Although Service Broker can be used entirely within SQL Server, Ado.net knows how to communicate with Service Broker to trigger this mechanism and retrieve notifications from service Broker.

Note When data in SQL Server changes, the query notification allows you to buffer the data and notify you.

At the. NET end, there are a number of ways to "hook in" this functionality. Ado.net 2.0 provides the System.Data.SqlClient.SqlDependency and System.Data.Sql.SqlNotificationRequest classes. SqlDependency is a high-level implementation of sqlnotificationrequest and is the most likely class to use when you use Ado.net 2.0. ASP.net 2.0 also communicates with Service Broker through the System.web.caching.sqlcache-dependency class, which provides a wrapper for the SqlDependency. And this is done directly by using the <%OutputCache> directive in a ASP.net page to provide functionality declaratively. This allows asp.net developers to easily implement caching that is not valid in data that is dependent on SQL Server.

Two. NET communication with Service Broker

How did these technologies come together to solve the "buffer puzzle"? Although you can take a number of steps to allow SQL Server to provide services to. NET, the key is that queries sent to SQL Server have a flag attached to them to tell SQL Server, in addition to returning the result set, the SQL The server should also register the query (and its requester) with service Broker. To do this, you create a queue that perceives the query and a service attached to the queue, and knows how to return to the client. If any row in the result set is updated in the database, the item in the related queue is triggered and, conversely, a message is sent to its service, and a notification is sent back to the application that initialized the request.

Figure 1 is a snapshot of SQL Server Management Studio that shows the queues (queues) and services (service) in the Service Broker section of the database.

Figure 1. The figure shows the default queues and services in the pubs database used by. NET query notifications.

Here are some important things to understand about this process:

· There are rules to indicate which types of queries SQL Server receives.

· Once SQL Server sends back notifications, queues and services are deleted. This means that you can only get a notification on each request. A typical application will requery the database and, at the same time, request the creation of a new dependency in Service Broker.

· The information returned to the application is only "something changed". The application is not notified of what has changed (refer to the Sqlnotificationeventargs in this article)
section for more information).

· Although the dependency is bound to the row returned from the query, it is not filtered by a single column in the query. If you have a query-it returns the basic member name of your organization and the address of one of those individual changes (however, its name does not change), this triggers a change notification. Hopefully, this particular behavior will change in future versions.

· The notice is returned, through a SqlConnection that is established specifically for this purpose. This connection is not included in the connection pool.

Iii. when to use query notifications

Query notifications are designed for data that is not constantly changing. It is best to apply it to server-side applications (such as asp.net or remoting) rather than to client applications, such as Windows Form applications. Remember that each notification request is registered in SQL Server. If you have a large number of client applications that have notification requests, this may cause your server to generate resource problems. Microsoft recommends that for client applications, you should limit the use of query notifications to no more than 10 concurrent users.

For large-scale applications, query notifications can be a powerful help rather than simply adding more and more servers to meet the requirements. Imagine a large software company that delivers online software updates to millions of users. Instead of causing each user's update to trigger another query on the server to determine which components are needed, it is possible to buffer the query results and service matching queries directly from the cache.

Note: For client applications, you should limit your query notification usage-no more than 10 concurrent users.

For a smaller case, a drop-down list box is another typical dataset, and the dataset is not updated as many times as requested. Product lists, state lists, country lists, vendors, salespeople, and even more infrequently changing information are good candidates for using alerts.

Iv. preparing for the use of enquiry notices

Because SQL Server 2005 is in a highly secure state by default, you need to "turn on" some features to use query notifications. First, you need to start the Service Broker function for each database you want to use. To do this, you can use the following command in T-sql:

Use MyDatabase

ALTER DATABASE mydb SET enable_broker

In addition, you need to grant some SQL Server permissions to allow Non-administrator accounts to participate in the use of query notifications.

V. Sqldependency.start and stop

Both SqlDependency and SqlCacheDependency require that the static method Sqldependency.start () be called before any notification request. This method is responsible for creating a SqlConnection to receive notifications when data changes. Note that you only need to build these content at the beginning of the lifecycle of an application. For example, in a asp.net application, the Application_Start event handler for a Global.asax file is a good place to implement this functionality.

Note that the Start method should be called for each connection included in the notification. Therefore, if you are accessing multiple databases in your application, you need to call start for each database. In the following example, there is a connection string pubsconn for the pubs database, which is defined in the Web.config file for this application.

To cut off this connection, you can use Sqldependency.stop (), which is also a static method.

The following are the referenced contents: Sub Application_Start (ByVal sender as Object, _ ByVal e as EventArgs) System.Data.SqlClient.SqlDependency.Start _ (System.Configuration.ConfigurationManager. _ connectionstrings ("PubsConn"). ConnectionString) End Sub Sub Application_End (ByVal sender as Object, ByVal e as EventArgs) System.Data.SqlClient.SqlDependency.Stop _ (System.Configuration.ConfigurationManager. _ connectionstrings ("PubsConn"). ConnectionString) End Sub

If you look at SQL Server Profiler while calling start and stop, you'll see a lot of interesting information. When Start is invoked, the application runs a query to ensure that Service Broker is supported, and then creates a stored procedure that is used to purge sqldependency queues and services in the Service Broker infrastructure. Finally, it runs a SQL Server waitfor command, which is responsible for querying the entrance to the Notification Service section. This is what you need to do explicitly if you use Ado.net's low-level sqlnotificationrequest objects.

Throughout the design process of. NET 2.0, the SqlDependency underlying architecture was changed from a push mode (from SQL Server) to a pull mode (from. net). The reason for this is to solve some of the security problems caused by the first design. Microsoft's Sushil Chordia published an article on MSDN about this improvement, which describes in detail the underlying mechanism of this improvement.

Six, your first notice

Next, let's start by using SqlDependency to analyze how all of these work together.
First, we create a class notificationtest to access your data. In this class, you also create a typical function to query some data from the authors table in the pubs database and return a SqlDataReader.

The following are the referenced contents: Imports System.Data.SqlClient Public Class Notificationtest Public Function deptest () as SqlDataReader Dim Conn as New SqlConnection (connstring) Conn. Open () Dim cmd as New SqlCommand ( "SELECT * from authors (", conn) ") Dim RDR as SqlDataReader RDR = cmd. ExecuteReader () return RDR End Function End Class

Now let's modify the code to add this dependency. First, declare an object named SqlDependency. To make it available to other functions in the class, I define it as a class variable.

Then, you need to change this query. Query notifications require you to explicitly enumerate the columns in your query and always use a "two-part" table name. Notice the new query text in the revised code example.

Then, instantiate the new SqlDependency and attach it to the command.

That's all. When the command is executed, the dependency is with it until the database. While it processes the query, SQL Server can see this dependency and send it to Service Broker to register it.

The following are the referenced contents: Imports System.Data.SqlClient Public Class Notificationtest Dim DEP as SqlDependency Public Function deptest () as SqlDataReader Dim Conn as New SqlConnection (connstring) Conn. Open () Dim cmd as New SqlCommand (_ "SELECT au_id, Au_lname,au_fname" & _ "From Dbo.authors", conn) DEP = New SqlDependency (cmd) Dim RDR as SqlDataReader RDR = cmd. ExecuteReader () return RDR End Function End Class

Now that you have registered dependencies, you are not capturing it when the notification returns to the application. However, the SqlDependency class provides two ways to understand a notification. One way is through the onchange event, you can capture it by creating an agent, another way is through the property haschanges, you can test it in your application logic. In the following code, I added code in the Ondepchange event to test the notification at some later time.

The following are the referenced contents: Imports System.Data.SqlClient Public Class Notificationtest Dim DEP as SqlDependency Public Function deptest () as SqlDataReader Dim Conn as New SqlConnection (connstring) Conn. Open () Dim cmd as New SqlCommand (_ "Select Au_id,au_lname,au_fname from" + _ "Dbo.authors", conn) DEP = New SqlDependency (cmd) AddHandler Dep. OnChange, AddressOf Ondepchange Dim RDR as SqlDataReader RDR = cmd. ExecuteReader () return RDR End Function ' Processor method Public Sub Ondepchange (ByVal sender as Object, _ ByVal e as Sqlnotificationeventargs) Dim Depinfo as String = e.info.tostring ' Do something to respond to the notice End Sub Public ReadOnly Property HasChanges () as Boolean Get return DEP. HasChanges End Get End Property End Class

Now let's look at how it works. First, place a breakpoint on the End Sub line of the Ondepchange event. Then, you can test it by calling the Deptest function from your favorite Web page, form program, or console program. After returning to SqlDataReader, open the Authors table in Visual Studio 2005 's Server Explorer or in SQL Server Management Studio and edit a field's contents. For example, once the change is locked, the breakpoint should be activated when you move the cursor to a new row in the table.

Seven, Sqlnotificationeventargs

When you see that the notification is actually coming from the database, you can analyze the value of the corresponding variable, which is a Sqlnotificationeventargs object. SqlDependency always returns this object with the OnChange event, and it is useful. Where Sqlnotificationinfo is a type of enumeration with 18 possible values. Some of the values correspond to normal conditions, while others show problems. These enumerations have Update,insert and delete-telling you what type of change has occurred in the data. There are other values that will not be sent even when the event occurs. For example, restarting the server fires all notifications, and the enumeration value drop or truncate tells you that an action has been implemented on the dependent table.

In addition, there are situations where dependencies are not even registered, such as if you attempt to set a dependency on an update query to return to invalid. The return value query shows that your query syntax does not conform to the strict rules of the notification. The last two enumerated values in the table are enumerated above, and several other enumerated values associated with the failed to register the query are returned immediately when the command is executed.

You can get a complete list of these enumerations by looking for the Sqlnotificationinfo enumeration documentation in the MSDN Library.

When I talk about query notifications on some occasions, people always ask me, "Does the notice tell you what happened?" ”。 The answer is "no".

In short, Sqlnotificationeventargs is able to give you the most detailed information in a notification, which is useful when debugging a row.