Scheduling Performance Monitoring
SQL Sentry's performance monitoring features clearly illustrate how individual events and combinations of events are impacting server resources. SQL Sentry lets you link any Windows performance counter directly to an individual active event object, or to groups of event objects on different servers that utilize the same network resources with Custom Event views. SQL Sentry manages starting and stopping performance monitoring based on the schedules for its linked objects.
- Performance counters applied to Custom Event views are applied to all objects specified by the instances and individual event objects dialog boxes. Enabled sources and category filters on the Event Sources tab are then applied to the combined objects from instances and event objects to determine which are monitored.
- To use event performance monitoring features in the client (accessing and adding counters), you must be a domain administrator or local administrator on each monitored Windows target.
- The monitoring service needs to have administrative access to all monitored targets to collect performance counter data.
General Performance Monitoring
General performance monitoring is possible within the Event Calendar. This process allows you to schedule performance monitoring throughout the day and receive notifications when defined thresholds are exceeded.
Schedule a single recurring placeholder job on a watched server somewhere in your enterprise by completing the following steps:
- Right-click the Jobs node in the Navigator pane, and then select New > Placeholder Job.
- Set the job to run for less than 10 seconds to prevent it from displaying on the performance graphs by default, and then define a recurring schedule for the job based on how frequently and when you want to sample the counters. For example, every five minutes between 8am and 5pm daily.
- Create a Custom Event view which includes only that job.
- Link counters to the view using the view's Performance Counters node. The performance counters can be on any server in your enterprise.
- Set Performance Counter Minimum and/or Maximum Thresholds and define email alerts or other response actions as needed in the Settings pane.
- Enable monitoring for the view by selecting the Monitor Performance context item. You can disable counter collection at any time by selecting Stop Monitoring Performance.
Monitoring Performance with Custom Views
Performance monitoring can be applied to custom event views similar to individual objects. This allows you to apply the same counter to several related events at once, to determine how their interaction may affect the performance of the same device. When utilizing performance monitoring on custom event views it's important to understand the filtering process and what objects are monitored by Event Manager.
Performance counters applied to custom event views are applied to all objects specified by the instances and individual event objects dialog boxes. Enabled sources and category filters on the Event Sources tab are then applied to the combined objects from instances, and event objects, to determine which are monitored. For a graphical representation, see the filter process flowchart.
Monitoring Event Object Performance
To access this feature, expand the node for any job, task, report or custom event view and then select the Performance Counters node to display a list of available Windows performance counters to link to the event object.
|Server||The default value is the server where the event object resides. Changing this option allows you to monitor performance on any server.|
|Connect||Used to connect to a different server. This allows you to monitor counters on a remote server as well as local counters.|
|Categories||Windows groups performance counters into categories. Select the category from the list in the same way you would if you were using Windows system monitor.|
|Explanation||For convenience, a detailed explanation of the performance counter is displayed here. A complete list and explanation of Windows performance counters can be found with the documentation supplied with Windows system monitor.|
|When the performance counter has been selected, press the Add button to link the counter. Once the performance counter has been added, it displays as a node under Performance Counters; one node for each counter.|
Enabling/Disabling Performance Monitoring
To enable/disable performance monitoring for all counters linked to an object, right-click on the desired object and then select the Monitor Performance or Stop Monitoring Performance.
Note: A job icon, for example, changes from to when performance monitoring is enabled.
Important: For performance data to be collected and displayed on the graphs, performance monitoring must be manually enabled using this process. Adding a counter to an event doesn't automatically enable performance monitoring for an object.
To view collected performance counter data, select the desired counter node:
The graph shows the performance counter data alongside the event instance being monitored, as well as any other instances for watched objects that ran during the same time frame. The top half of the graph represents the performance counter over time. Beneath this, a graphical representation of all watched event objects that ran during the same time is displayed along with a legend to identify each object. This format correlates how an event instance or combination of instances impacts the counter.
The event object being monitored is always shown in green; in this case it's the TEST2MantenancePlan.Subplan_1 job.
Note: To provide a baseline for the counter, data collection begins a short time prior to the event instance's scheduled start time and continues for a short period after it completes.
If the event instance is having a significant impact on the counter resource, you should see the counter jump after the instance starts. If multiple instances are causing contention for the counter resource, the counter jumps further.
Note: If a certain job starts, and the counter values jump significantly, and this increase is sustained until the job completes, this may be an indication that the jobs are fighting for resources. When this happens, both jobs may run for longer than they would otherwise, which can lead to undesired effects such as lengthened maintenance windows, or even worse errors may be encountered in the jobs or other applications using the monitored server.
Customizing the Performance Graph
|Min Y Val: / Max Y Val:||Changing these values resizes the graph Y axis to the selected values.|
|Show Value Labels||Checking this box shows actual counter values above each bar in the performance graph.|
|Auto-scale to Max Value||Checking this box automatically selects the appropriate Max Y Value of the data being shown in the current view. When this option is not enabled, SQL Sentry uses a standard deviation calculation to determine the Max Y Value, causing the tops of some of the bars to be cut off.|
|Jump To Next Instance Of||Checking this box affects how the graph is scrolled when using the Previous/Next buttons on the toolbar. With this box checked, the graph is automatically repositioned to the previous or next event instance, enabling you to conveniently skip over time slots where no performance data is available.|
|Show Live Data||Checking this box shows the performance data graph in real time as the event runs. With this box checked and the event running, the graph automatically scrolls to show new performance data collected.|
|Min Event Duration||Setting this value can eliminate unnecessary noise from short-running recurring events.|
|Previous / Next Arrows||Pressing these buttons moves the currently displayed range forward or backward by one minor increment.|
|Navigation Toolbar||Use the Navigation toolbar at the top of the screen to go to a specific date and time, or change the graph interval.|
Generating Performance Alerts
Performance alerts can be generated based on minimum and maximum thresholds you define for each counter. Once you have added a counter to an object or view, select that counter in the Navigator pane to set the minimum or maximum thresholds in the Settings pane.
Note: There can be only one minimum and one maximum value for a given counter, regardless of how many objects are linked to the counter.
Once you have defined thresholds for a given counter, actions such as Send Email, can be set for the Performance Counter: Threshold Min or Max conditions at any level. This allows you to define a global response whenever any performance threshold is exceeded, while defining a specific response when a threshold is exceeded for a particular job or other monitorable object.
Performance alerts can also be throttled by specifying a length of time any counter must exceed its threshold before an action is taken. You can specify how often the action is taken while the threshold is exceeded. These settings are available from the Settings screen under the SQL Sentry Monitoring Service node in the Navigator pane.