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.
- First, SQL standard will max out at 2 GB of memory. With Windows 2000 and 2003* 32-bit operating systems, there is only 4 GB of memory addressing available that can be used by the system regardless of the physical memory on the system. Normally only 2 GB of memory addressing is available for the USER space (where applications like SQL and Altiris run), while the other 2 GB is reserved for the kernel of the operating system. Enabling the /3GB switch in the boot.ini gives the USER space 3 GB of memory addressing, which leaves 1 GB for the operating system. The /3GB switch can only be used on Windows 2003 (any version) and Windows 2000 Adv or Data Center and should not be used in systems that have over 16 GB of physical memory.
Note: Prior to implementing the /3GB switch, review the guidelines provided in article 25079: "Understanding the /3GB, /USERVA, and Free Page Table Entries."
- If SQL and the Notification Server are on the same server, start out by setting SQL to a fixed 1 GB or if you can use /3GB switch then set SQL to a fixed 1.5 GB. Keep monitoring the three counters and adjust SQL memory accordingly.
- With SQL Enterprise version, you can enable the AWE option, which allows SQL to store its buffered data outside the 4 GB of memory addressing space allowing SQL to use more than 2 GB of memory and frees the USER space for other applications. AWE is generally useful when the system has 4 GB or more of physical memory. AWE does require the /PAE switch in the boot.ini. As with SQL standard, you still need to monitor the three counters and adjust accordingly; however, with SQL Enterprise version you can now increase the SQL memory setting without causing unwanted paging by adding more physical memory.
- Because tuning a larger SQL server can be different than tuning a ASP.NET application server, you should consider putting SQL on its own server when either of the following are found:
- You have SQL Standard and have to set SQL memory higher to keep the hit ratio from going below 90 percent, but at the cost of having a consistently higher Memory Page\Sec counter (Avg. 30+).
- You have SQL Enterprise with /AWE enabled and have to allocate more than 6 GB of memory to SQL in order to keep the buffer hit ratios from going below 90 percent. (Generally, this means that the system already has 8 GB or more of physical memory.)