Operating System (OS) performance monitoring

Efficient SQL Server performance monitoring includes monitoring of operating system, SQL Server, and database performance. In How to monitor your SQL Server instances and databases, we presented tools for monitoring the latter two performance metrics, In this article, we will present tools that provide operating system performance monitoring.

Monitoring performance of all three above mentioned groups provides a complete picture of system health and vital information needed for troubleshooting performance problems and bottlenecks. Operating system performance metrics are related to performance of disk, memory, processor, and network. Some of the most important system performance metrics are available memory, average bytes per read/write, average read/write time, disk reads/writes per second, network utilization, pages input per second, pages per second, processor queue length, and processor usage.

The choice of metrics that will be used for monitoring will depend on monitoring goals and performance requirements. It’s important that, a DBA is able to improve performance based on the monitoring results, but also to recognize potential issues and bottlenecks on time, and fix them before they affect the system.

Windows Task Manager

This is a native Windows monitoring tool that is useful for basic monitoring and troubleshooting. It can be helpful for determining where the bottleneck is: on a disk, processor, memory, or network. Other than that, it doesn’t provide enough information for determining the exact problem

  1. To open it, click Ctrl + Alt + Del and select Task Manager. Alternatively, right click on the Taskbar and select the Task Manager from the context menu
  2. Select the Performance tab
  3. In the left pane, select the hardware component you want to monitor: processor, memory, disk, or a network adapter

    Windows Task Manager performance tab

Additional information that can be useful for further analysis are shown in other Windows Task Manager tabs and in Resource Monitor.

Dialog showing the Resource Monitor tab

This information can only indicate the applications and processes that are using the most of computer resources at the moment. There’s no history or statistical data for deeper analysis and troubleshooting of existing or potential performance issues. However, the tool is useful, handy, Windows users are used to it, and is excellent for killing unwanted and non-responding processes.

Windows Performance Monitor

Windows Performance Monitor is a native Windows tool. Besides system performance metrics, it supports a wide range of performance metrics, including SQL Server, SQL Agent, database, and many other. It shows performance metrics in real-time graphs and allows custom selecting of the metrics that will be displayed, defining of metric threshold values, reports generation, and insight in historical data (historical data are available only for the period from the last computer restart)

  1. To start Windows Performance Monitor:
    • Click Start and go to Run. In Windows 8, click Windows + C. Type perfmon and press Enter
    • Open Windows Control Panel, go to System and Security, Administrative Tools, and click Performance Monitor
  2. Select Monitoring tools, Performance Monitor in the left pane
  3. Select and configure the performance metrics you want to monitor. To add a metric:
    • Right-click the graph and select Add Counters in the context menu
    • Press Ctrl + N
    • Click Add (the green plus icon) in the menu
  4. In the Add Counters dialog, select the computer
  5. Expand the metric group and select the metrics you want to monitor

    Windows Performance Monitor - selecting the metrics you want to monitor

Note that some of the metrics can be displayed as an average value. For example, processor metrics can be shown as total values for all processors/cores available on the machine, but also as individual values for each processor/core.

All selected metrics are monitored in a single graph. As this can be difficult to track, you can uncheck the Show box to hide the metric and get a clearer picture. Another trick is to highlight (Ctrl + H) a specific graph.

Showing the counter values on a graph using Windows Performance Monitor

Besides showing the values in the graph, Windows Performance Monitor also provides a text report and a histogram presentation of the data. It provides creating Data Collector Sets that can be used for collecting performance metric values, and show historical data saved in logs.

Windows Performance Monitor is an easy to use tool with low overhead. It provides a wide range of performance counters, but when deeper analysis is required, it cannot provide all the necessarily information. Alerting is limited and doesn’t provide useful details.

ApexSQL Monitor

ApexSQL Monitor is a SQL Server performance monitoring software that monitors system, SQL Server, and database performance. It stores all collated data into own central repository database. It provides alerts when a specific metric value is reached with details of the alert cause, keeps the historical data for unlimited time and displays those data in graphs while adding the minimal overhead.

  1. Start ApexSQL Monitor
  2. To add a SQL Server instance to monitoring, click Add in the menu

    The Add button in the ApexSQL Monitor menu

  3. In the next dialog, enter the SQL Server instance name, authentication type, and credentials if needed

    ApexSQL Performance monitor - entering the SQL Server instance name, authentication type, and credentials

    Local SQL Server instances are automatically detected and listed in the drop-down Server list. To enter an instance that’s not in the list or a remote SQL Server instance, type its name in the Server field.

    By default, the Enable all metrics check box is checked and all system, SQL Server, and database performance metrics available in ApexSQL Monitor are monitored.

  4. To add more SQL Server instances, repeat steps 2 and 3
  5. To configure monitored metrics, click Metrics in the menu

    Configure metrics dialog in ApexSQL Monitor

  6. To add or remove a metric from monitoring, check the check-box at the beginning of the metric row
  7. Use the same form to configure alerting. Check the Alerting check box for the metric and specify threshold values

ApexSQL Monitor provides threshold values for low, medium, and high severity alerts. Where applicable, recommended metric values are provided. For the metrics where values depend on your system and preferences, such as database file size, disk reads/sec, and disk writes/sec, no default threshold values are predefined and baseline values and thresholds can be calculated and used instead. It’s also recommended to check the default values and modify them if they are not applicable to the particular environment and doesn’t meet specific requirements.

ApexSQL Monitor shows the captured metrics in real time graphs. If an alert is raised, it’s clearly indicated at the top of the graph.

Real time performance monitoring in ApexSQL Monitor

In this article we presented 2 Windows native monitoring tools: Windows Task Manager and Windows Performance Monitor. The first one is a tool that provides only basic monitoring information, useful for determining whether there is a problem and where. It doesn’t provide any details required for deeper analysis and troubleshooting, but it’s easy to access and use.

Windows Performance Monitor monitors various performance metrics, presents them in a real-time graph, shows historic data, and be configured for alerting. However, it doesn’t provide enough details for deeper analysis and troubleshooting.

ApexSQL Monitor provides a user-friendly GUI, which enables easy configuration and maintenance of performance metrics and alerts on multiple local and remote SQL Server instances. Besides showing performance metrics in real-time graphs, it keeps historic data in the central repository database and shows it in graphs, which is useful for analysis and determining performance trends.

July 16, 2014