A lot of the queries were in the format
select * from stock where stockid=55
Investigation time
I looked at our own systems and checked the performance dashboard, query store, and a group of other reports. Through these, I found I had issues with a Microsoft Access client linking to our database. Every time the user scrolled to a new record, a sub-form was performing an un-parameterised query against one of our key tables.
What I really wanted was for Access to be a bit smarter and send a query that was more like:
select * from stock where stockid=@0
I finally managed to find an article by Brady Upton, which lead me down a rabbit hole of things that can protect my SQL Server from Access and Excel. The article describes a database setting, Parameterization. This is, by default, set to "simple" but can be set to "forced". I'll let Brady take the credit for describing the difference. Please read the article, but here's what happened for me.
Before the change
After the change
I tried setting the system to forced parameterization, and I got this result:
Both queries are now using the same plan, giving a 50% saving in the plan cache!!
But the article led me to another source of information in this article.
Next steps
/****** Object: PlanGuide TemplateGuide1 Script Date: 29/10/2019 15:23:21 ******/EXEC sp_create_plan_guide @name = N'[TemplateGuide1]' , @stmt = N'select Count ( xxx_user_name ) as C1 from "v_user_role" where ( xxx_user_name = @0 and xxx_role_name = @1 )' , @type = N'TEMPLATE', @params = N'@0 varchar(8000),@1 varchar(8000)', @hints = N'OPTION(PARAMETERIZATION FORCED)' GO?
