Effective monitoring of SQL database and its ecosystem

Learn what aspects need to be monitored for powerful troubleshooting and preventing the performance degradation of the environment your SQL database runs on.

SQL database monitoring is not just query and response time check. When monitoring database performance, you need to take into account all the underlying hardware and software that support it. Here is a short overview of areas that need to be tracked as part of data collection for performance analysis and troubleshooting.

Uptime/connectivity monitoring

Availability/uptime is the most essential metric of server monitoring. It is the first step in any troubleshooting process. Reasons for server or service down may vary, but it can be physical disconnection of a cable or a server switched down for maintenance. It may also be the result of larger scale failure when a switch/router between the SQL and your monitoring system is down.

The basic check includes pinging the node or nodes where your database is running (whether physical or virtual). It also includes checking if the relevant network service is running. Ping is a default network service monitored for all nodes in NetCrunch that is enabled by default as a global monitoring pack. Additionally, MS SQL Server Tabular Data Stream Protocol are detected and monitored out of the box. MS SQL Browser Service is predefined and available to monitor, you can add it to monitoring in one click.

Host resources monitoring

A server isn’t just a single piece of hardware. It is an aggregation of various physical components. Overall server performance depends on the performance of its individual parts. Host health directly influences how quickly an application launches and how fast it responds to commands from the users - your goal is to measure how well its available capacity matches database requirements and whether its resources are not overstretched.

The most important components of a server as far as monitoring is concerned are the hard disk, memory, and processor. You can also include here disk health monitoring using SMART technology, or IPMI monitoring of the power supply. If your server is on a virtual machine, agentless monitoring of VMware ESXi and vCenter is part of NetCrunch already.

NetCrunch automatically applies default monitoring packs for memory, CPU, and disk to each device that is recognized as a Windows system, so you are all set out of the box here.

Host performance monitoring

Connectivity and access are a bare minimum. You need to drill further to verify the health of the operating system that supports the database. Host performance is about how well your host is equipped and working to ensure the smooth performance of the database on top of it. Host performance monitoring helps identify the health status of all critical components of the host where your database is running, including metrics related to the system services, paging file, network interface, and established connections.

NetCrunch comes with 25 predefined monitoring packs for various aspects of Windows system monitoring. Some of them are automatically enabled and monitored, tracking over a hundred metrics as recommended by the system vendor. Other monitoring packs are predefined and easy to apply to multiple devices with a single click.

Windows predefined monitoring packs in NetCrunch are listed below.

Database performance monitoring

Effective performance monitoring should involve checking the service status of the database services, such as SQL Server, SQL Server Agent, or SQL Server Browser. It is recommended to track several other metrics related to the amount of login and logouts, user connections, pages, deadlocks, etc. These metrics will give you an advance warning if the database system is in danger of failure.

NetCrunch includes a predefined monitoring pack for MS SQL Server 2012-2019 that allows tracking key performance metrics of the application, including Windows and network services, MS SQL event log warnings, and errors. It also contains several ready to use reports such as: Processor Bottleneck Analysis, Disk Usage and Performance, Memory Usage Analysis, MS SQL Server 2012-2019 CPU Performance, MS SQL Server 2012-2019 Memory, and MS SQL Server 2012-2019 I/O Report.

All metrics and statuses related to SQL server monitoring described above are by default tracked by NetCrunch. It collects over 40 various SQL server parameters out of the box.

Error Monitoring

Database system errors are hard to avoid. You can easily set up tracking SQL errors and assign them selected event severity. Please follow instructions and examples for monitoring Windows event logs for warnings and errors in NetCrunch.

It is also important to track the sheer volume of errors - you can set an alert to be notified if the frequency or number of errors increases over the norm in a specific time. Tracking errors are part of the Windows monitoring pack in NetCrunch.

Log Monitoring

Both servers and applications generate a log of system events. Log files have always been an invaluable tool in system troubleshooting so it shouldn’t come as a surprise that log monitoring is an important part of server monitoring. Collecting all log events may overload your system, so it is recommended to monitor selected types of event log entries.

For best results, all operating system and application log files should be stored on a log server. That speeds up the monitoring process since the software doesn’t have to remotely read the logs.

Database Monitoring

NetCrunch comes with two SQL sensors that allow reading data from the SQL table to treat them as metrics. It can be both metrics related to the SQL own performance but can be also metrics saved to SQL database by an external software or data source. In the second situation, the database is treated as a data source for monitoring something that is not possible to be monitored in another way.

Read more about advanced SQL query monitoring.

Security Monitoring

The types of monitoring described so far are related to issues that arise unintentionally. However, certain system problems are the result of deliberate, unauthorized, or malicious actions of users, malware, or third parties. Such action pose danger to your data, systems, and business. The recommended way of security monitoring is tracking specific security events by event id and setting up a specific alerting action in response to them. For example, failed SQL server login event id in Windows Event Log is 18456. You can easily define alerts for specific security events that you want to track, for example if you want to monitor multiple failed logins to SQL server events.

Final thoughts

It is important to note that with NetCrunch, you do not need to install any agents to monitor all metrics, services, and events described above. It tracks and stores a large number of performance metrics and performance indicators for a long period of time, helping you identify trends and make informed planning for future capacity requirements. NetCrunch also helps you easily isolate and drill down to the problematic elements of your network that negatively affect overall performance, thus reducing the time you would have spent on troubleshooting if you had to switch between several narrow monitoring tools.

There are a few highly specialized but very expensive SQL database tuning products on the market. But if your goal and responsibility are ensuring sufficient resources and performance levels for your network environment and all systems and applications that run on it based on a broad collection of metrics and performance indicators, NetCrunch is the easiest, most obvious choice.

serversqlwindows