Parameter sniffing means that one set of parameters produces a dramatically different execution plan than the other, and that if the wrong plan is cached, you get adverse performance effects.
This answer is based on your simplified query - to get accurate advice for your query, you'll need to post the query and the two different plans that resulted from parameter sniffing. (I'd always rather get to the accurate root cause rather than troubleshooting a simplified example, but I've gotta work with the code you posted, so here it goes.)
Your query only has one table in it (assuming that Widgets isn't a view):
SELECT * FROM Widgets WHERE CustomerId=@0
That means if you have a nonclustered index on CustomerID, some CustomerID values might produce a plan with a nonclustered index seek followed by a key lookup, whereas other parameters will do a clustered index scan across the Widgets table.
There are a few ways you could fix this scenario, and I'm going to list them in a generally safest-to-most-risky way:
Use OPTION (RECOMPILE) on the query. This does require a code change to add the line to the query, but then every execution of this query should get the most appropriate plan. The risk is higher CPU use for plan execution (although that generally won't matter in a single-table, single-predicate query like this where the plan will be ultra simple to generate).
Cache every variety of the plan. You noted passing the query in as a string will get each parameter to cache its own individual plan. While that will work today, it does bloat the plan cache (taking up more of SQL Server's memory). The risk here is that someone will turn on Forced Parameterization, a database-level option that will parameterize all of your queries whether they're sent in as strings or not, and suddenly you're back to troubleshooting this issue again.
The rest of these are valid solutions, but not for your single-table, single-predicate query. I'm only listing them here for posterity and clarity:
Use the OPTIMIZE FOR UNKNOWN query hint, or as we like to call it, optimize for mediocre. Requires a query change, and gives you a generally-good-enough plan. This will avoid random changes of the query plan due to parameter sniffing, but the risk is that it still won't be the most performant plan.
Use the OPTIMIZE FOR query hint with a specific CustomerID. This also requires a code change, and you would optimize the query for one of your larger customers. This will get a query plan that's great for big customers, and not-so-good for small customers. Small customer performance will go down, but the big customers won't cripple the system. The risk is that your customer distribution will change, and this will no longer be the right plan for the app as a whole.
Use a query plan guide. You can get exactly the query plan you want, and then pin the plan guide to memory. Here's the Books Online section on plan guides. I'm not usually a big fan of this because if your indexes change, the query plan won't take advantage of the new indexes. If your query changes, the plan guide will no longer be in effect. Suddenly the system might perform terribly, and people will have forgotten that a plan guide was helping before.
Use a stored procedure with manual logic. Have branches that call different stored procedures, one for large customers and one for small customers. This is only used for much larger, more complex queries that can have variations between minutes and hours (or not completing at all).
Best Answer
I've done this on specific indexes before now, to aid oft-run heavy queries. Effectively what they have done is create multiple clustered indexes: when any of those indexes is used to find rows no extra work is needed looking up the rest of the data in the real clustered index (or the heap if there is no real clustered index).
For some indexes where needed to support certain query patterns, certainly yes.
But to do this with all indexes, I would just as certainly say no.
It is going to be wasteful of space to do where not actually needed, and will slow inserts/updates significantly. It may slow down as many read queries as it helps too, because each index page holds less records so any query needing to reference a chunk of the index for filtering but not using all other columns will have to access more pages. This will make your database more memory-hungry: those pages will need to be loaded into the buffer pool, potentially ejecting other useful pages if memory is low. If compression is used on those indexes to try mitigate the effect on storage and memory requirements, then it will be pushing extra load to the CPUs instead.
This is a common pattern with poorly optimised use of an ORM (or just naive ORMs) and in these cases I've seen SQL Server's index adviser (and similar 3rd party tools) suggest indexes with many
INCLUDEd columns, so I would agree with your suggestion that this is why the indexes have been created this way.But while it may make all such queries slightly faster and some of them significantly faster, I suspect that in many cases any benefit is so small as to not be worth the extra memory footprint required by your common working set, the space on disk, and the IO between disk and memory.
Also remember that the ORM might not be selecting out all columns of all tables tha a query touches, so that benefit might only hold for the main target of the current request, and the larger indexes may penalise the query when other objects are used for filtering but not returning data (
SELECT * FROM table1 WHERE id IN (SELECT someID FROM table2 WHERE someColumn='DesiredValue')perhaps).Another consideration for the excess space used, particularly if the data is large, is that it will have an impact on your backup strategy: storage and transfer costs for those backups, potential restore times, and so forth.
Generally I think the considerations here are going to be the same in each case, though any excess memory/IO cost imposed by the large indexes may be more directly visible in Azure where you can tweak the service tier and therefore infrastructure cost more easily rather than having a relatively fixed set of hardware resource. If using standard/premium tiers instead of vcore based pricing then you'll be affected more by the IO cost in standard as premium includes significantly more IO per DTU. If you are using multi-region backups or redundancy or other non-local features in Azure than there might be a bandwidth cost associated with the extra space taken by uneccesarily wide indexes.