I've known about Auto-Parameterization in SQL Server for a long time, but just recently got into a situation when it magically worked or did not work for me.
I've done a research and got some very interesting facts on how SQL Server determines to auto-parameterize a query or not.
I've used following script to create and populate a test table:
I used following list of cases to come up with different indexing scenarios:
Then I used following script to generate and capture generated plans:
(DO NOT TRY THIS ON YOUR PRODUCTION SERVER)
And here is the result of these test cases:
Based on the results I could make following assumptions for a single table query with single equality column in a WHERE clause:
SQL Server will Auto-Parameterize a query in following cases:
1. If Clustered Index Seek is performed.
2. If Clustered Index Scan is performed.
3. If Index Seek is performed for covering index (No RID or Key Lookup).
4. If Index Seek is performed for Unique Index or UniqueConstraint
5. If Table Scan is performed for a column, which included in any other index.
6. If Table Scan is performed with no indexes in a table at all.
SQL Server will NOT Auto-Parameterize a query in following cases:
1. If Index Seek is performed for not covering and not unique index resulting RID or Key Lookup.
2. If Table Scan is performed for a column, which not included in any index and other indexes exist.
As you can see, Auto-Parameterization in SQL Server, does not depend on query cost at all and it is very hard to tell if there are any benefits for it in some instances. So, I'd strongly recommend to explicitly parameterize/prepare all your production queries.
If you are really sure you want ALL your queries must be parameterized and you can't change the application code you can use "FORCE" parametrization setting for your database like this:
ALTER DATABASE <YourDatabase>
SET PARAMETERIZATION FORCED;