Elastic APM show total number of SQL Queries executed on .Net Core API Endpoint

Question

Currently have Elastic Apm setup with: app.UseAllElasticApm(Configuration); which is working correctly. I've just been trying to find a way to record exactly how many SQL Queries are run via Entity Framework for each transaction.

Ideally when viewing the Apm data in Kibana the metadata tab could just include an EntityFramework.ExecutedSqlQueriesCount.

Currently on .Net Core 2.2.3

Answer 1

One thing you can use is the Filter API for this.

With that you have access to all transactions and spans before they are sent to the APM Server.

You can't run through all the spans on a given transaction, so you need some tweaking - for this I use a Dictionary in my sample.

var numberOfSqlQueries = new Dictionary<string, int>();

Elastic.Apm.Agent.AddFilter((ITransaction transaction) =>
{
    if (numberOfSqlQueries.ContainsKey(transaction.Id))
    {
        // We make an assumption here: we assume that all SQL requests on a given transaction end before the transaction ends
        // this in practice means that you don't do any "fire and forget" type of query. If you do, you need to make sure
        // that the numberOfSqlQueries does not leak.
        transaction.Labels["NumberOfSqlQueries"] = numberOfSqlQueries[transaction.Id].ToString();
        numberOfSqlQueries.Remove(transaction.Id);
    }

    return transaction;
});

Elastic.Apm.Agent.AddFilter((ISpan span) =>
{
    // you can't relly filter whether if it's done by EF Core, or another database library
    // but you have all sorts of other info like db instance, also span.subtype and span.action could be helpful to filter properly
    if (span.Context.Db != null && span.Context.Db.Instance == "MyDbInstance")
    {
        if (numberOfSqlQueries.ContainsKey(span.TransactionId))
            numberOfSqlQueries[span.TransactionId]++;
        else
            numberOfSqlQueries[span.TransactionId] = 1;
    }

    return span;
});

Couple of thing here:

• I assume you don't do "fire and forget" type of queries, if you do, you need to handle those extra
• The counting isn't really specific to EF Core queries, but you have info like db name, database type (mssql, etc.) - hopefully based on that you'll be able filter the queries you want.
• With transaction.Labels["NumberOfSqlQueries"] we add a label to the given transction, and you'll be able to see this data on the transaction in Kibana.