SQL Server Notification Services Performance Tuning

SQL Server 2000 and 2005 Notification Services can be a heavy burden on a SQL Server, depending on how it is used. For best overall performance, it is recommended to dedicate Notification Services to a dedicated SQL Server.

For example, let's say that you want to use Notification Services to handle e-mail notifications to your website's customers. Your best guess is that your 200,000 customers will want to receive an estimated 4 e-mail notifications a month. As you might well imagine, this could put a large load on the SQL Server, especially if customers all want the notifications at about the same time, which is not unusual.

In this case, for best performance, you would want to dedicate a single SQL Server instance on a dedicated server to run both SQL Server and the Notification Services and its related database(s). And you would not want to combine any other production databases or applications on this same server.

Of course, if your use of Notification Services is light, you can combine it on a non-dedicated SQL Server, assuming it also has a light load. [2000, 2005] Updated 2-25-2005

*****

When you decide to take advantage of SQL Server 2000 or 2005 Notification Services, one or more SQL Server databases are needed. Here are some specific tips on how to performance tune Notification Services:

·         Notification Services makes heavy use of the tempdb database. Because of this, you should consider two things: First, consider allocating a minimum size for the tempdb database, so that when SQL Server is restarted, it will create a new tempdb database of an appropriate size. This prevents SQL Server from having to expand the tempdb database size automatically, which can lead to short bursts of poor I/O performance, as the database is expanded. Second, consider installing the tempdb database on its own dedicated physical disk drive or array in order to reduce I/O contention.
 

·         Notification Services also makes heavy use of the transaction log. To reduce I/O contention, consider locating the transaction log file on its own dedicated physical disk drive. In addition, the Full Backup Recovery model should be used to ensure a minimum of lost data. Also, because the log is heavily used, be sure that it is backed up (log truncated) often so that it does not fill up and stop your server.
 

·         While many indexes are created automatically by Notification Services when the Notification Services database is created, not every potentially useful index is created. Once your system is in production, consider taking a Profiler Trace during a very busy time of the server and use the Index Wizard or the Database Engine Tuning Advisor to identify potential new indexes.
 

·         During peak processing times for Notification Services, consider not running any other queries, or performing any maintenance tasks. These can contribute to poor performance.
 

·         While bottlenecks can occur at any place in the server hardware, two areas especially susceptible to hardware bottlenecks when running Notification Services is the CPU and network bandwidth. You will want to watch these two areas very closely, much more closely that you would with SQL Server not running notification services.

[2000, 2005] Updated 2-25-2005

*****

If you are experiencing performance related issues with Notification Services, Microsoft includes a number of stored procedures that produce a variety of reports you can use to help determine what the problem is. These reports are broken down into these categories:

·         Instance reports

·         Diagnostic reports

·         Detail reports

·         Quantum reports

·         Snapshot reports

Each of these report categories has one or more specific reports you can run. If you suspect a performance problem, one of your first steps should be to run and review one or more of these reports.

Here is one example of how you might approach identifying a performance problem using one or more of these reports:

·         The first step is to identify quantums (a periodic time interval) that is running longer than is should be, that has been skipped, or that has failed. To identify these, you can use the Quantum Performance, Quantum Execution Time, Quantum Failure, and the Quantum Skipped reports.
 

·         Once you have identified "problem" quantums, you next step is to learn what is causing the problem. You can do this by using the Quantum Detail and the Quantum List reports.
 

·         And last, you may need to analyze the data itself to see what is going on, which can provide clues as to why the quantum "problems" exist. You can do this using the Event Batch Details, the Scheduled Subscription Details, the Notification Batch Detail, and any of the relevant Diagnostic or Snapshot reports.

[2000, 2005] Updated 5-16-2005

*****

Just as you can with any SQL Server database, you can use the Performance Monitor and the SQL Server Profiler to help identify Notification Services performance-related problems. Virtually any of the problems you find in user databases can occur in Notification Services databases.

You should also consider performing baselines on your Performance Monitor activity in order to find out what is "normal" for your system, or to set up Performance Monitor alerts in order to be quickly notified if your system gets overburdened. [2000, 2005] Updated 5-16-2005