A forum question came up a few days ago regarding an abnormally large procedural cache. In an attempt to help out, I threw together a few ideas (some wrong, some right) and started looking at the types of query that were in the cache.
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
With parameterization set to simple, the query optimiser "occasionally" parameterized a plan. This resulted in the following plans:
For each of the datetimes used in the query, we got a different plan. I'm going to confess that I had to force this scenario just to demonstrate the effect. Now imagine the impact with 500 users putting different dates in 20 times per day. Our plan cache would grow like crazy.
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
I created a plan guide to help with a query (again from MSAccess). I've never done this before, but it wasn't too hard
Here's the code
/****** 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?
This code will catch anything passing a string into my query and convert it into a parameterized query. Again, I got huge performance gains. My CPU is down to less than 30% all day and most importantly I'm getting fewer and fewer compiles.
I'm no expert in this area, but now that I know it's another tool in my arsenal I will start researching more. Hopefully, somebody better than myself in this area can put me onto "stage 2". I'm not going to take any credit for the information in the 2 links, I'm just pleased that the information is out there.
