We always turn on "Automatically apply recommendations" including both Create and Delete.
There are lots of ways to use Sitecore and structure your data. Different setups will have subtly different influences on SQL Server.
You should enable the SQL Query Store for all your databases:
ALTER DATABASE AdventureWorks2012 SET QUERY_STORE (OPERATION_MODE = READ_WRITE);
This will allow SQL to monitor performance and give you suggestions if you have queries that are executing a lot, or are performing badly. In my experience there are a few indexes it always adds. Sometimes it takes them away again a bit later.
If you see a lot of executions of a particular query you should think about adjusting your caching. Database requests, fast as they are, are 1000x slower than memory requests.
Also, a bit off topic, but don't forget to rebuild your indexes and heap tables periodically as they can get very fragmented, thanks to the use of
UNIQUEIDENTIFIER columns as clustered index fields.