SQL Server 2017 – Parameter Sniffing vs Parameterization Forced: Which Takes Precedence?

If you can anticipate typical values, run your stored procedure with such values whenever you (re-)create it. An execution plan will be created based on these values, and stored for future use. Most of my stored procedure .sql files end with an EXEC command with reasonable values, for exactly this purpose (that, and to identify basic errors in the code).

Of course this won't help if your stored procedure is very long running, enough that you can't reasonably execute it during production hours. Note that it's not enough to start the job, it has to complete successfully for a plan to be stored.

1) Is there a recommended way to write these sorts of queries in order to bypass this limitation and take advantage of cached execution plans (other than using forced parameterization at a database level)?

Paul White explained it here with examples.

2) How can I evaluate the impact that these queries are having on the overall performance of the database (e.g., how can I answer quantitatively whether or not it is worth my time to worry about the lack of cached execution plans)?

You need do some collection and aggregation for this one.

• Once you compare the performance of your rewrite as explained in Paul's article you know the difference (in io, cpu) for one call + time for recompilation.
• Multiply that by number of calls (of the changed query) in x period.
• Add those numbers for all the query you rewrite.
• Another approach is to benchmark you overall workload, make change to small number of queries and compare with baseline.
• If moving in the right direction make more changes (provided workload did not change),otherwise go back to starting point. Evaluate one by one.