What should I do to improve performance of the SQL Server database for use with Notificaton Server or Deployment Server?
These are the performance monitors for tuning SQL that have been used.
SQL server: Buffer Manager—Buffer cache hit ratio
How often can SQL provide data without querying the disk? Microsoft suggests to tune this setting so that the hit ratio is around 90 percent. Allocating more memory to SQL will allow it to buffer more of the commonly requested data. As more memory is allocated to SQL, the likelihood is higher that you could start swapping memory to disk, which is undesirable and defeats the point of increasing memory to SQL.
This counter helps make sure that whatever memory is used is not buffering a lot to keep up the demand. If you increase SQL to use more memory than what the system can handle, then this counter starts to hit above 10 more consistently. This can be resolved either by decreasing how much memory is allowed for SQL (careful not to bring the buffer cache hit ratio to low) or by adding additional memory to the server (up to 4 GB without SQL Enterprise).
PhysicalDisk—Avg. Disk Queue Length
This counter tracks the average number of disk requests during a sample interval. Any time a SQL data request is not found in the buffer (the other 10 percent), it must query for it on the disk. If the queue length remains a fairly high number, it indicates that the disk cannot keep up with all the disk requests. This disk I/O issue gets worse if the system does not have enough memory to function because then the pages/sec starts to climb. More internal SQL delays increase the odds of SQL Dead Lock errors making everything that much worse. Sometimes adding more memory is helpful, but only if there is a high number of Pages/Sec; otherwise faster I/O (more disks in the Array) goes a long way to improve performance.