I'm not going to say there's a right or wrong way here, but I'll share my personal experience.
I've tried both ways, and ended up finding that it's best to start with transient and then expand the scope as necessary.
Think in terms of the Single Responsibility Principle and Reasons for Change. The reason that your service's lifetime scope may need to change will likely be linked to a change you make to the service implementation itself. When that Reason happens, you want to only need to change a single class.
If you have a service that needs to be longer-lived than most of your other services, all you have to do is make it longer-lived and inject factories for any shorter-lived dependencies into it.
On the other hand, if you have a class that needs to be shorter-lived than most of your other services, you'll end up having to inject it as a factory into all the longer-lived services. So one Reason for Change impacts code all over your code base.
Of course, this is assuming you're doing things correctly: avoiding captive dependencies and remembering to make non-thread-safe services transient. I find it's far easier to remember which services need to be long-lived and override the defaults in that direction, than it is to remember which services shouldn't be long-lived and override the defaults that way. And I've found that failure to do the latter leads to much more insidious bugs that are harder to notice and result in greater damage.
With regards to performance: I haven't done performance testing with the ASP.NET Core built-in DI framework, but I've found that SimpleInjector is fast enough that the time and memory overhead of creating new instances of services is trivial in comparison with the other work my app does (like querying databases).
With regards to preventing opening multiple connections: SQL Server connections are pooled automatically so the cost to creating a new SqlConnection()
and disposing it several times in a request is generally pretty trivial.