• Article

Monitoring maintenance plans for Project Server 2010

 

Applies to: Project Server 2010

Topic Last Modified: 2011-06-20

A database maintenance plan is only as good as its integrity is preserved, which requires monitoring. Operationally, maintenance tasks must be monitored for success and duration, and system performance must be monitored to find system degradation before users report it. We recommend that you use solution monitoring software, such as System Center Operations Manager. Microsoft has released management packs for it that provide monitoring according to best practices for specific products involved in the Microsoft Project Server 2010 architecture, such as Microsoft SQL Server, Microsoft SharePoint Server 2010, and Project Server 2010.

Some maintenance tasks, such as nightly backups or hourly transactional log backups, may have a significant effect on system performance or data integrity if they do not operate. Therefore, it is important to monitor their execution. Most operational environments have an extant alarming system —Project Server database maintenance plan execution should be integrated with these if it is possible. If you cannot do this, basic alarming can be quickly implemented through SQL Server Agent's Operator mechanism combined with SQLMail via SMTP. The SQL Server Agent jobs created when a maintenance plan is scheduled can be edited to add operator notification upon maintenance task completion, success, or failure. Depending on the solution scale and operational maturity of the environment, a mixture of positive ("the task ran successfully") and negative ("an error occurred") notifications are suggested. This mixture may change over time — during initial implementation and test periods, it is reassuring to receive regular notifications, which may not be necessary as the system matures.

For more information, see Using SQL Server Profiler (https://go.microsoft.com/fwlink/p/?LinkId=221734)

Monitoring SQL Server Counters

As part of your monitoring plan, we recommend that you monitor the following SQL Server counters.

Objects and Counters Description

General Statistics

This object provides counters to monitor general server-wide activity, such as the number of current connections and the number of users connecting and disconnecting per second from computers that are running an instance of SQL Server.

User Connections

This counter shows the number of user connections on your instance of SQL Server. If you see this number increase by 500 percent from your baseline, you may see a performance reduction.

Databases

This object provides counters to monitor bulk copy operations, backup and restore throughput, and transaction log activities. Monitor transactions and the transaction log to determine how much user activity is occurring in the database and how full the transaction log is becoming. The amount of user activity can determine the performance of the database and affect log size, locking, and replication. Monitoring low-level log activity to gauge user activity and resource usage can help you determine performance bottlenecks.

Transactions/sec

This counter shows the number of transactions on a given database or on the whole SQL Server instance per second. This number is to help you create a baseline and to help you troubleshoot issues.

Locks

This object provides information about SQL Server locks on individual resource types.

Number of Deadlocks/sec

This counter shows the number of deadlocks on the SQL Server per second. This should typically be 0.

Average Wait Time (ms)

This counter shows the average amount of wait time for each lock request that resulted in a wait.

Lock Wait Time (ms)

This counter shows the total wait time for locks in the last second.

Lock Waits/sec

This counter shows the number of locks per second that could not be satisfied immediately and had to wait for resources.

Latches

This object provides counters to monitor internal SQL Server resource locks called latches. Monitoring the latches to determine user activity and resource usage can help you determine performance bottlenecks.

Average Latch Wait Time (ms)

This counter shows the average latch wait time for latch requests that had to wait.

Latch Waits/sec

This counter shows the number of latch requests per second that could not be granted immediately.

SQL Statistics

This object provides counters to monitor compilation and the kind of requests sent to an instance of SQL Server. Monitoring the number of query compilations and recompilations and the number of batches received by an instance of SQL Server gives you an indication of how quickly SQL Server is processing user queries and how effectively the query optimizer is processing the queries.

SQL Compilations/sec

This counter indicates the number of times the compile code path is entered per second.

SQL Re-Compilations/sec

This counter indicates the number of times statement recompiles are triggered per second.

Plan Cache

This object provides counters to monitor how SQL Server uses memory to store objects such as stored procedures, ad hoc and prepared Transact-SQL statements, and triggers.

Cache Hit Ratio

This counter indicates the ratio between cache hits and lookups for plans.

Buffer Cache

This object provides counters to monitor how SQL Server uses memory to store data pages, internal data structures, and the procedure cache, and it also provides counters to monitor the physical I/O as SQL Server reads and writes database pages.

Buffer Cache Hit Ratio

This counter shows the percentage of pages found in the buffer cache without having to read from disk. The ratio is the total number of cache hits divided by the total number of cache lookups since an instance of SQL Server was started.