(616) 371-1037

[email protected]

Photo by sigmund on unsplash

Simple Ways to Inspect the SQL Executed by Entity Framework Core

August 24, 2021 - Rachel Hagerman

No Comments

Slow SQL or incorrect or missing indexes can cause performance issues in an API. And those issues can be difficult to fix or even find. I use a variety of tools when investigating slow APIs, but if the API uses a SQL database I have a few that are invaluable in finding ways to improve performance.

If the API is using a common stack including Entity Framework Core and SQL Server, sometimes the suspected problem is the generated SQL. Maybe the queries themselves are inefficient, or maybe a statement is called multiple times by mistake. These may result in duplicated SQL or unnecessary database requests. If I want to view the SQL being executed by the API, I have several options.

Visual Studio Output

The latest versions of .NET Core make it pretty easy to output the SQL when debugging. You can set the logging level for Microsoft to “Information”. Then you can view the SQL in the output log when running in debug mode from Visual Studio.

"Logging": {
  "LogLevel": {
    "Default": "Debug",
    "System": "Information",
    "Microsoft": "Information"
  }
}

The SQL will then be visible in the Output panel.

output image

MiniProfiler

But my personal favorite way to view the SQL from my API is to use MiniProfiler to set up some basic SQL profiling. Setting up MiniProfiler using memory cache and showing SQL for EntityFrameworkCore API requests is very straightforward. You will need the Miniprofiler.EntityFrameworkCore NuGet package. You will also need to add some simple configuration to the ConfigureServices method.

public void ConfigureServices(IServiceCollection services)
{
    services.AddMiniProfiler(
      options =>
        {
          // (Optional) Path to use for profiler URLs, default is /mini-profiler-resources
          options.RouteBasePath = "/profiler";
          (options.Storage as MemoryCacheStorage).CacheDuration = TimeSpan.FromMinutes(60);

          // (Optional) Control which SQL formatter to use, InlineFormatter is the default
          options.SqlFormatter = new StackExchange.Profiling.SqlFormatters.InlineFormatter();
        }).AddEntityFramework();
}

And adding UseMiniProfiler() to the Configure method.

public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
     if (env.IsDevelopment())
     {
          app.UseMiniProfiler();
      }
…

Now you are ready to debug and view MiniProfiler output of the SQL. To test it, run your API in Debug mode and make a few requests. Then navigate to your API profiler results at [your-api-url]/profiler/results-index.

You will see a list of those requests made, and each row can be clicked to view details on that request.

request list

Clicking one of these rows will show details about the request. If there are SQL statements executed, you will be able to see the SQL exactly as it was executed, along with the timing of the request.

requestdetails

If your request happens to result in identical SQL statements, MiniProfiler will show a warning that these are duplicated statements. This is a good clue if you are looking for reasons why a particular request is slower than expected.

sqlwarning

And from this point, you can also tune performance on the SQL statements or related indexes using Sql Server Management Studio, or one of my favorite tools, SentryOne Plan Explorer. Good luck and happy coding!

Rachel Hagerman

Rachel is a Full-Stack Remote Software Engineer and Architect with over 10 years .NET stack development experience. She started her career in circuitry design and automated test systems before becoming a software consultant. But her true passion has always been for building real, useful, elegant software.

Leave a comment

Your email address will not be published. Required fields are marked *