Can I get SQL performance suggestion like in Azure SQL Database for SQL Server on premises?

So, while yes this should be a comment, its going to take more characters to type than I have space for (i think). With that being said I have afew things worth discussing I think you should check out, if you havent already.

First off, if your SQL Server version allows it TURN ON QUERY STORE. The reason I suggest this is that not only some of the out of the box functionality kind of gives you what youre looking for, but also allows you to dig deeper on your own looking for performance boosting scenarios.

• Out of the box stuff - there are sections in query store dedicated to helping you find regressed queries, resource hog queries and other interesting things youd think youd want to find. This however, assumes you already know what to do with them when you find them.

• Digging deeper - because how query store stores data and the XML you can mine for plans, you have tons of options to find awful things like "table scan" or "keylookup." Again, not as easy to make these queries for yourself, and again assuming you already know how to solve these issues when you find them.

• shit you should already be aware of - all the query stat dmv's and index suggesting dmv's are there for you to review and get an idea of how things are running as well. There other dmv's to assist but I think most of those are in real time tables and would need a custom process set up to monitor (why i am adamant about query sotre because it does this for you). Again, you guessed it, you still need to knwo what to do with this after the fact. index suggestion dmv should be taken with a grain of salt though, because youll want to do an index analysis before going full ham and creating all of the said recommendations.

Now that we've mentioned taking recommendations with a grain of salt - Again the same thought process should be applied to Azure recommendations as I have seen some false positives before (one thing recently was data classification recommendations - for pk_NAMEOFCOLUMNTHATSASURROGATEID, really?), so its not ever going to be spot on I dont think.

Theres also tons of software thats cheap that does the same thing - solarwinds DPA used to be rock solid but after their recent kerfuffle I wouldnt blame you for not even considering that one.