Application performance monitoring (APM) is one of the main monitoring techniques used in tech organizations. Performance is an essential attribute of applications and shouldn’t be overlooked. Our topic for today’s post can be considered both a subset and a complement of APM: SQL Server performance monitoring.
We’ll start by covering some basics. We’ll define APM and why it’s so important. Then we’ll explain why database performance monitoring is crucial for the success of any APM strategy. Finally, we’ll cover some essential metrics you should be aware of when monitoring the performance of your SQL Server instance.
APM Fundamentals
As we’ve mentioned, SQL Server performance monitoring—and database performance monitoring in general—can be considered a subset of application performance monitoring.
So, before we delve deeper into the nuts and bolts of SQL Server performance monitoring, it’s important for us to be on the same page when it comes to the overall concept. That’s why we’re starting with some basics. We’ll define APM, and then we’ll talk about the importance of APM tools and the benefits they can provide you.
After that, we’ll finally get to databases, explaining how monitoring them can help you get full visibility into your IT infrastructure.
What Is Application Performance Monitoring and Why Should You Care?
Let’s start with some basics. What’s APM? Why should you care about it?
APM, as mentioned previously, stands for application performance monitoring. It can also mean application performance management, though that’s not what we’ll cover in this post.
Application performance monitoring refers to a set of practices and tools you can use to monitor the overall performance of an application. Doing so allows you to detect and fix performance problems early in the process. It can also bring more benefits we’ll talk about soon.
But what does APM consist of? What is involved in APM?
First, despite the “Application” in its name, APM can go beyond the source code of an application. For instance, it’s common for APM tools to track metrics across a complete informational infrastructure. This includes not only the application itself, but also servers—such as web servers and application servers.
And of course, database metrics should also be monitored.
However, APM would probably have a different name if it didn’t care about application code. But it does care about application code, which is evidenced by an important component of many APM tools—source code performance profiling.
Another essential component of APM is transaction traces. Being able to trace individual web requests, for instance, is invaluable when trying to optimize an application. It tightens the feedback loop for developers, allowing them to clearly see how a single request “travels” through the layers of an application, interacting with the many dependencies a typical application usually has.
Why Is APM Important?
Simply put, performance is essential. Even though some call performance a nonfunctional requirement, we beg to differ. We prefer to side with those who, like Jeff Attwood—co-founder of Stack Overflow—affirm performance is a feature.
Poor performance causes fewer users to find your site or application. That’s right: Google punishes poor performance.
If users find your app but it performs poorly, they’re less likely to stay long and buy your offering. Yes, poor performance hurts conversion rates. Slow is the new down!
To make things worse, users are less likely to go back to poorly performant web applications.
These are just three of the ways in which poor performance makes your application less profitable: your site won’t rank, so users won’t find your site. If they find it, they won’t stay long, nor will they buy anything. And then they’re less likely to come back.
To avoid the problems above—which are far from being an exhaustive list of the downsides of poor performance—you need to give performance the care it deserves. That’s why improving performance is so important.
Add Databases to the Mix and Get Full-Stack Monitoring
When talking about APM and APM tools, many people get the impression that it only refers to source code performance monitoring.
That’s certainly not the case. A tool or approach that only monitors application code would have a very limited usefulness. Bear in mind the concept of a bottleneck. Think about it: you can spend an awful lot of time and effort on optimizing your application code. But if most of your performance issues come from a different source, it won’t give you the expected results.
It’s crucial to acknowledge code isn’t everything when it comes to performance monitoring. The application code interacts with different elements of your IT infrastructure and factor those in as well.
Among those other components, the most prominent of all is certainly the databases. Databases are ubiquitous and contribute immensely to the overall performance—or lack thereof—in your application.
SQL Server Monitoring With AppOptics SQL Server Plugin
SolarWinds® AppOptics™ APM is a comprehensive APM solution. It supports many different programming languages and frameworks. When it comes to database monitoring, AppOptics has got your back too: by using its SQL Server plugin, you can start monitoring your SQL Server instances in an easy and fast way.
We’ll now cover some of the SQL Server metrics AppOptics can help you track. You’ll understand what the metric is about and what you can gain by tracking it.
Database IO
This metric refers to SQL Server IO metrics from sys.dm_io_virtual_file_stats.
sys.dm_io_virtual_file_stats is a table_valued function object offered by SQL Server you can use to retrieve useful IO data. Since it’s a table_valued function, you select from it while at the same time supplying a database ID and a database file ID as parameters. To learn more about the parameters, consult SQL Server documentation.
Here’s an example of how to query the function:
select * from sys.dm_io_virtual_file_stats(5, 1)
Why should you care about this metric?
Simply put, SQL Server is an intensive IO process and, as such, it’s constantly reading data from disk to satisfy the queries it receives.
Since an essential part of performance optimization relies on correctly identifying the bottleneck, it’s important to check whether the IO activities of your SQL Server aren’t the culprit of your performance issues.
Database Properties
The second metric on our list is database properties. It refers to properties of the current database from the sys.databases system view. More specifically, the values tracked by this metric are the state and recovery_model columns retrieved by the view.
According to the SQL Server documentation, a recovery model is defined in the following way:
“A recovery model is a database property that controls how transactions are logged, whether the transaction log requires (and allows) backing up, and what kinds of restore operations are available. Three recovery models exist: simple, full, and bulk-logged.
“Typically, a database uses the full recovery model or simple recovery model. A database can be switched to another recovery model at any time.”
The state column indicates the current state of a database. A database is always in a given state, such as ONLINE or OFFLINE. To see the full list of states, refer to SQL Server’s documentation.
Why should you care about this metric?
Simply put, being able to correctly determine the current SQL Server database state is an essential part of SQL Server administration.
Understanding not only what the current state is but also how a database moves between those states can help when diagnosing and solving SQL Server problems.
Memory Clerks
Memory clerks are one of the most important components of the memory architecture in SQL Server. Whenever a memory consumer needs to allocate memory, they use a memory clerk. Memory clerks are used to track and manage the memory consumption of each component.
SQL Server offers a system view we can use to quickly query and visualize data about all the memory clerks. It’s called sys.dm_os_memory_clerks. The memory clerks metric in AppOptics offers the data from the sys.dm_os_memory_clerks in a more digestible way.
For instance, in AppOptics, the clerks have more friendly names than the original names found via the system view.
Monitoring the SQL Server memory clerks boils down to the importance of identifying the bottleneck, as we’ve already mentioned when talking about the IO metrics.
Along with IO, memory is another essential component of SQL Server. Ensuring everything is all right with SQL Server’s memory management is an essential administrative task and will help you when troubleshooting issues in your SQL Server instance.
Through the SQL Server Operating System (SQLOS), SQL Server manages its memory allocations, instead of relying on the underlying operating system. Closely watching how your SQL Server instance is using its memory can only benefit your APM approach, and AppOptics offers you a friendlier way to monitor those metrics.
Server Properties
The server properties metric has a self-explanatory name. It returns useful properties of the server itself. These include the following:
- The total numbers of databases in all possible database states
- Physical memory and total numbers of CPUs on the server
- The version of the current SQL Server install
- The total uptime of the SQL Server version
As we’ve mentioned when defining APM, the concept applies not only to application code, but to virtually all parts of the informational infrastructure. This includes the servers themselves. Since the server is the foundation of the IT infrastructure, it makes sense to carefully monitor—and improve, if necessary—its performance.
SQL Requests
The sql_requests metric in AppOptics returns a snapshot of two system views: sys.dm_exec_requests and sys.dm_exec_sessions.
The sys.dm_exec_requests view returns information about requests being executed by SQL Server. The sys.dm_exec_sessions view returns information about all active user connections.
The types of info you get with this metric are running requests, wait types, and blocking sessions.
Tracking these metrics will allow you to troubleshoot problems related to requests. You can, for instance, identify which sessions are experiencing waits. You can go one step further and determine which session causes the other one to be blocked, which will allow you to close the offending session.
Wait Stats
The wait_stats metric gives you information about the tasks currently waiting in SQL Server. It’s further broken down into several submetrics:
- Wait time in milliseconds
- Number of waiting tasks
- Resource wait time
- Signal wait time
- Maximum wait time in milliseconds
- Wait type
- Wait category
In the same way tracking sessions and requests is useful to troubleshoot requests related problems—e.g., a session being blocked—understanding wait statistics from SQL Server can help you diagnose and fix problems.
What are wait statistics? SQL Server keeps recording information related to the reasons why its threads have to wait. This information can then be used to help you find the cause of your performance woes.
Azure Managed Instances
The sys.server_resource_stats view can be used to see CPU usage, IO, and storage data for an Azure SQL Managed Instance.
The azure_managed_instances metric in AppOptics retrieves data from this specific system view.
Monitoring this metric is important for the same reason you should care about monitoring metrics from your physical server—the foundation of your infrastructure can make or break the performance of the overall system.
So, in the same way measuring those metrics is important when working with your on-prem server, they also matter a great deal when it comes to the cloud.
Schedulers
The sys.dm_os_schedulers view is a system view that returns information about schedulers.
According to MSSQLTips, “A scheduler can be described as a piece of software that coordinates the execution of different processes and administers its available resources. SQL Server has its own scheduling mechanism that is implemented in what we know as SQLOS.”
The schedulers metric in AppOptics returns statistics about schedulers from the sys.dm_os_schedulers view.
Why is it important to keep track of this metric?
Think of the scheduler as a sort of processor in SQL Server. It’s the responsibility of the scheduler to manage the execution and resources to ensure a given task is executed to completion. Each query in SQL Server is broken down into tasks. Each task is then assigned to one or more works, which finally run them on schedulers.
Visualizing and understanding the process of task execution in SQL Server will help you when you need to perform an analysis to find the root cause of an issue.
Database Monitoring: The Secret Sauce for Your APM Strategy Recipe
Many people think that application performance monitoring is restricted to analyzing source code. While getting down to code level is indeed a powerful way of troubleshooting, it’s far from being the only way. An application is supported by an infrastructure made up of many parts, and they all contribute to the overall success—or lack thereof—of the application.
The database is a central piece of this puzzle. That’s why database monitoring is so important. In this post, you’ve learned a little bit more about APM, why it’s important, and what the main metrics you should track are when it comes to your SQL Server performance.
This post was written by Carlos Schults. Carlos is a .NET software developer with experience in both desktop and web development, and he’s now trying his hand at mobile. He has a passion for writing clean and concise code, and he’s interested in practices that help you improve app health, such as code review, automated testing, and continuous build.
