Monitoring SQL Server vault performance

Because SQL Server is a general purpose DBMS, its performance can vary widely depending on how it is configured. This can have a dramatic effect on the performance of Meridian vaults that use SQL Server. Meridian integrates with SQL Server through its own cache, so monitoring the performance of that cache can provide valuable information to help with tuning SQL Server to work the most effectively with Meridian. Detailed SQL Server monitoring and performance tuning are beyond the scope of this guide. Refer to the SQL Server documentation for more information.

The Meridian cache can be monitored with several performance counters in the AM HT Page cache group in the Windows Performance Monitor. The most important ones are:

• Memory, Kbytes: The current amount of memory claimed by the cache.
• Memory Peak, Kbytes: The peak amount of memory claimed by the cache.

Besides these statistics, also monitor SQL Server memory consumption to detect problems.

When performance problems arise that are not indicated by the counters above, make a Windows Performance Monitor log file with the following counter groups selected and send it to BlueCielo Technical Support for analysis:

• AutoManager EDM Server
• AM HT Page cache
• Memory
• Physical Disk
• Process
• Processor
• SQL Server: Buffer Manager
• SQL Server: SQL Statistics
• System

We recommend that you log data for an entire workday, at an interval of 2–5 minutes. We can then analyze the data for known problems and suggest solutions.

Note Meridian requests large amounts of memory from the operating system. When there is not enough physical memory, Windows will start swapping data to virtual memory. If this happens, performance will drop sharply. You can detect when the system starts swapping by monitoring the Pages/sec counter of the Memory object.