When is it not good to use await async?

You typically use async/await when performing I/O bound tasks like reading from a stream, reading from a DB, sending something over the network or waiting for a response.

This makes the thread available to do some other (CPU related work).

Technically, async/await is slower in terms of raw performance, however, it increases the scalability of your application since it allows threads to be available for other work while others are waiting for I/O bound operations.

I have an ASP.NET Core 3.1 based project written using C#.

That means that async/await will allow you to increase your capacity (in requests pers second) a great deal.

at what point using async/await will hurt performance?

it will add a little bit of overhead that is a fixed amount. It will in general be small compared to the costs of the I/O and not really noticable.

When will async/await do more harm than good?

In a web site/service that will never see a load above ~50 requests / second.
And even then the 'harm' will be very small.

Actual numbers depend on the hardware, amount of I/O work etc.

In reality, that code does not need to be called asynchronously since all the code is executed in memory

In that case it will be faster to handle it synchronously.
But I know teams that prefer to have all Actions async, just for uniformity. And since the overhead is so small I consider that a valid approach too.