Using The System Monitor Tool For Measuring SQL Server Performance
System Monitor, a program to measure the performance of SQL Server allows you to view the objects of SQL Server and performance counters in addition to the behavior of other objects, including threads, processors, memory, and cache. These objects individually associate set of computers for measuring delays, device usage, and other counters indicating overall performance status. You might have heard about the program before as a monitoring tool for Windows OS; however, it has the ability to monitor SQL Server and Windows OS at the same time to find out the relation between the both. This is why the tool is also well known as SQL Server Performance Monitor. In order to determine the behavior of the system, the program will monitor the SQL Server Buffer Manager counters and disk I/O counters. Moreover, you can obtain SQL Server performance and activity statistics. The System Monitor is elaborated in the following sections:
How System Monitor proves to be Beneficial
The System Monitor tool benefits you in the following ways:
- You can view the data from multiple computers at the same time.
- You can view the charts that show current activity and change them. These charts also display the updated counter values at user-defined frequency.
- You can add system alerts that provide the details of event in alert log. The added alerts issue a network alert to notify you.
- It allows you to export data from alert logs, charts and reports to a database so that it can be re-manipulated.
- The tool lets you create log files that comprise objects’ data from different systems.
- You can view activity reports or create new reports with the help of current log files.
- You are allowed to run a predefined application when counter value has a mismatch with user-defined value.
· You can save the chart, log, alert, report settings, etc.
· You can select sections from existing files and append them to a single file to create archive.
Areas to Assess
Generally, when you attempt to monitor MS Windows OS or SQL Server for performance, the following three areas are the primary focus: processor utilization, disk activity, and memory usage.
If you monitor a computer where System Monitor is in process, you may experience some performance decline. Therefore, it is recommended to either log the data of System Monitor to some other disk or run the program from a computer placed at a remote location. Moreover, it is better to monitor some specific counters that you want because monitoring a large number of counters leads to excessive resource usage that results in sluggish performance of the system you are monitoring.
Tasks of System Monitor
Running System Monitor
Now is the time to know how the SQL Server Performance Monitor program manages to monitor SQL Server. It is all done with the participation of RPCs (remote procedure calls) used by System Monitor for collecting information from SQL Server. If you have Windows rights to use the program, you are also permitted to use it for monitoring MS SQL Server. When you monitor SQL Server using System Monitor, a little performance overhead is sure that depends on number of counters, hardware platform, and the update interval that has been selected. It is equally important to know that the System Monitor tool is integrated with SQL Server to handle performance if it degrades.
Monitoring Disk Usage
SQL Server performs read/write operations on the disk with the help of Windows input/output calls and keeps track of the way I/O is performed. The input/output subsystem includes various components, including disks, system bus, CD-ROM drive, controller cards, and other I/O devices. The System Monitor tool mainly targets the following areas while it monitors disk activity:
· It monitors disk I/O and detects excess paging.
· It isolates the disk activity created by SQL Server.
Monitoring CPU Usage
System Monitor is also responsible for assessing CPU if usage rates fall in the normal range. If it is found that CPU usage rate is higher than the prescribed range, the application design is poor and the situation is also an indication that CPU needs to be upgraded or multiple processors are required. Poor application design problem can be resolved by optimizing it, which results in relatively lower CPU usage. On an average, when CPU usage reaches 80-90%, the need to upgrade the CPU or to add multiple processors falls.
How CPU Utilization Can be Determined?
A simple way for measuring the CPU Utilization is using the counter ‘Processor: % Processor Time’ in System Monitor. With the counter, it is easy to monitor the time taken by CPU to execute an active thread. A system that contains multiple processors, each of which has to be monitored; the separate instance of the counter for every single processor should be monitored. On the other hand, the counter ‘System: %Total Processor Time’ is used if average for all the system processors is to be determined instead of one.
In addition, the following counters also contribute to monitoring CPU usage:
Processor: % Privileged Time: The counter monitors the time taken by the processor for executing SQL Server I/O requests and other Windows kernel commands.
Processor: %User Time: It monitors the time consumed by the processor for executing SQL Server and other user processes.
System: Processor Queue Length: This counter corresponds to the count of threads that wait for the processor time. When threads within a process need more than available processor cycles, processor bottleneck situation occurs. Furthermore, if a good number of processes try to use processor’s time, installation of a faster processor may be needed. In case of a system having multiple processors, a processor can be added.
In order to determine the processor usage, you need to consider the type of work performed by an instance of SQL Server.
Monitoring Memory Usage
Memory usage is a major concern that is monitored by System Monitor. The tool keeps a check that memory usage is limited to its range by monitoring the instance of SQL Server at certain intervals. The below given object counters monitor low-memory condition:
Available Bytes: As the name indicates, the counter provides the number of available bytes that can be used by processes. If Available Bytes displays low values, it is a clear indication of memory shortage on the computer.
Pages/sec: The counter is responsible to indicate the following page count:
- Pages extracted from disk because of hard page faults
- Pages written to the free space on the disk because of page faults
The high rating of Pages/sec counter is an indication of excessive paging. Monitoring the ‘Memory: Page Faults/sec’ counter helps to determine that paging is not the cause of disk activity. Moreover, if ‘Process: Page Faults/sec‘ counter is monitored for an instance of SQL Server process, it is helpful to determine whether the SQL Server or any other process causes the excessive paging.
An important note includes that SQL Server has the ability and flexibility to change memory requirements according to the available memory resources. In case of requirement of additional memory, it tells the Windows OS to figure out the availability of physical memory. If the memory is available, SQL Server uses it. On the other side, if SQL Server has additional memory that does not have to be used by it, this memory is simply released to the OS. If required, the server configuration options ‘max server memory’ and ‘min server memory’ can be used to override the described option.
You need to check the below given performance counters for monitoring the amount of memory used by the SQL Server:
· SQL Server: Memory Manager: Total Server Memory (KB)
· SQL Server: Buffer Manager: Buffer Cache Hit Ratio
· Process: Working Set
· SQL Server: Buffer Manager: Total Pages
Creating a SQL Server Database Alert
System Monitor has the ability to allow the creation of an alert that becomes active when System Monitor counter value is reached. System Monitor responds to the alert by launching an application. You can define an alert using SQL Server Agent and SQL Server Management Studio.
Following are the requirements for defining an alert:
- Name of the alert
- An event or performance condition to trigger the alert
- An action taken by SQL Server Agent to respond to the specified event or performance condition
Creating Alerts, Reports, Charts and Logs
System Monitor also allows to create Alerts, Reports, Charts and Logs for monitoring an instance of SQL Server. Charts, reports and alerts for an activity provide feedback instantly and log files allow you to keep track of counters for a long time period. Below section provides a descriptive look at the tasks handled by each of them:
Alerts: As explained above, alerts are raised to notify you when some specific events occur. A log for an alert is able to monitor performance of object instances and counters that you have selected. A notification is followed by recording of event date and time by the alert log when the given value is exceeded by the counter. Moreover, you can also create a network alert generated by an event.
Reports: Reports are used to show the values that change continuously. These values are for selected objects and are displayed in columns.
Charts: You can create charts for monitoring the performance of desired objects and counters. You can add the following to a chart:
· Combinations of objects and counters of System Monitor
· Objects and counters of MS Windows
Creating charts is advantageous in the following ways:
· To determine the cause of low performance of a computer or application
· To identify the reason for the need of capacity increment
· To display a comparison in the form of histogram chart
· To monitor the system regularly to find performance problems
· To display a trend as line chart
· To monitor a local or remote computer on a real-time basis for short term
Each of the charts you have created displays a part of information to be monitored.
Logs: You can have a record of current activity of the objects and computers that you have selected by creating logs that are used later to view them and do further analysis. A single log file is capable to save data from multiple computers. For instance, to collect performance information about the selected objects on different computers, different logs can be created. You are also enabled to save the selections and use them later. The entire log information is sufficient to troubleshoot the issues or do further planning.
Using SQL Server Objects
System Monitor uses objects and counters for monitoring activity on the computer systems that run an instance of SQL Server; these objects and counters are provided by SQL Server as an object is a resource of SQL Server. Each object houses multiple counters that are responsible to determine the aspects of objects that have to be monitored. For example, the resource SQL Server Locks consists of two counters:
· Number of Deadlocks/sec
· Lock Timeouts/sec
Note that some objects may have multiple instances. It generally happens when multiple resources of the specified type lie on a single computer, such as the object ‘Processor’ contains multiple instances in case of multiple processors on one computer. On the other hand, some objects have a single instance, for example, the object ‘Database’ has a single instance for every single database on MS SQL Server. In case of multiple instances of an object, counters can be added for tracking statistics of each instance. Moreover, you can display SQL Server counter statistics by configuring the System Monitor tool. You are also enabled to set a value for a counter of SQL Server and create an alert when the counter reaches or exceeds this value.
However, it is important for you to note that for displaying the SQL Server statistics, installation of an instance is mandatory. Additionally, if a SQL Server instance is stopped or even restarted, the statistics display process will be interrupted in between. A great thing is you are able to view the SQL Server counters in snap-in of System Monitor even when SQL Server is not in running state.