The best tool to monitor the overall system performance of the SQL Server RDBMS depends on your specific needs and budget. However, some of the most popular and well-regarded tools include:
SolarWinds Database Performance Analyzer (DPA): DPA is a comprehensive database monitoring tool that provides insights into all aspects of SQL Server performance, including CPU, memory, I/O, and query execution. It also includes features for root cause analysis, performance tuning, and capacity planning.
Paessler PRTG Network Monitor: PRTG is a general-purpose network monitoring tool that can also be used to monitor SQL Server performance. It includes a variety of sensors for monitoring SQL Server metrics such as CPU usage, memory usage, and database wait times.
ManageEngine Applications Manager: Applications Manager is a comprehensive application monitoring tool that can also be used to monitor SQL Server performance. It includes features for monitoring database resources, query performance, and deadlock detection.
Datadog SQL Server Monitoring: Datadog is a cloud-based monitoring platform that offers a variety of integrations for monitoring SQL Server, including CPU usage, memory usage, and query performance. It also includes features for root cause analysis and performance tuning.
Other popular SQL Server monitoring tools include:
Site24x7 SQL Server Monitoring
AppDynamics
Redgate SQL Monitor
Dynatrace
When choosing a SQL Server monitoring tool, it is important to consider the following factors:
Features: What features are important to you, such as root cause analysis, performance tuning, and capacity planning?
Ease of use: How easy is the tool to use and configure?
Scalability: Can the tool scale to meet the needs of your growing environment?
Price: How much does the tool cost?
It is also a good idea to try out a few different tools before making a decision. Most SQL Server monitoring tools offer free trials, so you can evaluate them in your own environment before committing to a purchase.
SQL Server monitoring tool Recommendation
If you are looking for a comprehensive and powerful SQL Server monitoring tool, I recommend SolarWinds Database Performance Analyzer (DPA). DPA offers a wide range of features for monitoring SQL Server performance, root cause analysis, performance tuning, and capacity planning. It is also relatively easy to use and configure. However, if you are on a tight budget, there are a number of open source and free SQL Server monitoring tools available, such as Nagios and Zabbix. These tools may not offer as many features as commercial tools, but they can still be effective for monitoring basic SQL Server performance metrics.
SQL Server provides tools with which you can check its performance. By using the sp_monitor stored procedure you can display statistics on CPU, disk I/O, network I/O, and connections. This procedure is used to get a high-level overview of SQL Server behavior. The syntax of sp_monitor is sp_monitor
Overall System Performance
View the code below to see the output of the sp_monitor.
Last_run is the last time that sp_monitor was run.
Current_run is the current date/time.
Cpu_busy is the number of seconds that the cpu was busy processing SQL Server requests.
Io_busy is the number of seconds that SQL Server was doing input or output.
Idle is the number of seconds that SQL Server was not doing anything.
Packets_received is the number of network packets received by SQL Server.
Packets_sent is the number of network packets sent by SQL Server.
Packets_errors are the number of network errors.
Total_read is the total number of disk reads performed by SQL Server.
Total_write is the total number of disk writes performed by SQL Server.
Total_errors is the total number of disk errors that occurred for I/O initiated by SQL Server.
Connections is the total number of connection attempts that were made to SQL Server.
sp_monitor output
SP Monitor
last_run – last_run is the last time that sp_monitor was run.
Current_run – current_run is the current date/time.
Seconds – Seconds is the number of seconds between the last run and the current time.
Cpu_busy – Cpu_busy is the number of seconds that the cpu was busy processing SQL Server requests.
Io_busy – io_busy is the number of seconds that SQL Server was doing input or output.
Idle – idle is the number of seconds that SQL Server was not doing anything.
Packets_received – Packets_received is the number of network packets received by SQL Server.
Packets_sent – Packets_sent is the number of network packets sent by SQL Server.
Packets_errors – Packets_errors are the number of network errors.
Total_read – Total_read is the total number of disk reads performed by SQL Server.
Total_write – Total_write is the total number of disk writes performed by SQL Server.
Total_errors – Total_errors is the total number of disk errors that occurred for I/O initiated by SQL Server.
Connections – Connections is the total number of connection attempts that were made to SQL Server.
What do the numbers mean?
Many of the columns output by sp_monitor have two or three numbers.
The first number is the number of seconds since SQL Server was restarted.
The second number, which is in parenthesis, is the number of seconds since sp_monitor was last run.
The third number is a percentage.
Interpreting sp_monitor
This high-level information can be useful for finding out what is going on with SQL Server. For instance, you will want both error columns to have a value of zero. If the CPU Busy is larger than 90%, you will probably want to have more or faster CPUs. If the I/O Busy column is larger than 90%, you will want to add more disk subsystems to spread I/O out.
After installing SQL Server, you must run sp_monitor once before the information returned is meaningful. The first time it is run, the counters will not be correct.
SQL System - Quiz
Click the Quiz link below to test your knowledge of the concepts presented in this module so far. SQL System - Quiz
The next lesson will cover how to use the database consistency checker to check system performance.
