Use datadog to gain insight in performance and server load

[Joost]

Various software exists to gain insight in server load, database load and application performance. As Novulo’s applications are generated to regular C# code and run with MSSQL Server databases, analytics software that supports this Microsoft stack can be used for Novulo, including Datadog.

This post shares best practices for using Datadog for Novulo applications.

Datadog products and apps

This post describes using the following Datadog products and apps for Novulo applications:

1. Product: Infrastructure Pro or Infrastructure Enterprise with apps
   1. System
   2. IIS
   3. NTP
   4. When on Azure: Azure
2. Database monitoring
   1. SQL Server

Infrastructure list

For the infrastructure in your account, go to Infrastructure > Hosts

Here you will see the hosts, including the apps that are installed.

In this screenshot, you see the host ws101 to be a host serving both IIS and SQLServer.

The other two hosts are a typical example for a split host. One is hosting IIS, the other is hosting SQL-server.

Analyse end point performance

Datadog IIS log visualization makes it easy to analyze the performance of REST endpoints that are configured in Novulo, as endpoints all have their specific recognizable URL-structure. (Novulo actions in the back-end are all handled through default.aspx; for these requests, Datadog IIS doesn’t offer much specific insights).

To do so, go to Logs:

Here, you’ll see all IIS logs. To gain insight in the performance, we typically add the @duration column here:

By searching a specific path, you can see all requests for that specific path-structure. The * works as a wild-card. For example, the search path belows search for all requests to the cmsurl endpoint, irrespective of the path.

service:iis @http.url:/*/rest/hcms/cmsurl*

This will give a list-based overview of all requests to that specific endpoint and the duration. Clicking on the logs will provide your more insight.

Now, we typically want to show the performance on a dashboard. To do so, we typically first change the visualization to Time series. This will show the number of requests per time frame:

Click on save to dashboard:

Which will then show this chart on the dashboard:

Now, if you want to change visualization, you can edit your chart. If you want to visualize duration (Average, Max, p75, p95), select Duration here:

Then, give it a nice name and you have your dashboard:

Using Database monitoring

Using the database app gives you insight in individual queries and their load. To go here, navigate to APM > Database monitoring:

Here, you see the databases that are being monitored. ln this example, the first one is SQL server installed on Windows, the second is AzureSQL:

Clicking on the host will show you the consolidated queries that are being executed and their load on the server. This should act as a starting point for analysis and optimization.

Database query patterns to watch out for

There are several patterns to watch out for:

Queries for records in a lock

When you see this pattern, it means there are queries executed during record locks, which can’t be optimized. See Novulo Record Locks, the Lock Warning Triangle and the impact on database queries for more information and solutions.

CASE q0t1.id
WHEN ?
	THEN ?
ELSE q0t1.field_name
END =