I work on SQL Server 2016 and i had severe performance issues with few queries.
So I posted several posts in here and got solution/suggestion etc and greatly improved the execution time and learned a lot.
Recently I got to know that most of the queries are running fast, but at times some queries are running slow.So i checked for few queries and learned that the query plans are getting stored in the plan cache,but i see lots of plan for a each queries.Some plans are widely used with double digit execution count while a lot are single digit execution count.
I checked the parameters of some plans and I see different parameters.So the general gotcha will be it is due to
Another thing I learned when investigating slow execution of a particular highly used query is that..among good parallel execution plans for that query ,sometimes the query is generating a serial plan and using it even though a parallel plan is available in the plan cache.Next thing i do is delete the serial plan and then the query runs fast.I have been doing this every now and then when i see the query executing slow.
(Application is a Dot Net application and most if the queries are generated through
LINQ-SQL.So i am limited with options and changing query by adding query hints(which needs code change) is not a solution for me.)
After a lot of research and going thorough lots of posts from this forum,i learned that since i am using SQL server 2016 SP2.I have two options:
- Query Store
- Plan Guide.
I thought of using plan guides initially : to force some query hints or forced parameterization.
Since i am using LINQ-SQL ,the queries generating is already parametrized and don't have to force it.So that option is out of scope for me.(Still i created a plan guide with forced parametrization ,but the query still used cached plan and ignored plan guide)
I created plan guides ,to add query hints like
OPTION(RECOMPILE) as mentioned in this post.
Unfortunately i didn't get an answer or some direction to the question.I am not sure why the plan guide didn't work.I couldn't find a proper instruction/information regarding plan guides for
LINQ-SQL generated queries or the query with
So i moved to Query Store, and there everything is via GUI and it is easy.I could find most expensive queries and can see the plans they used previously. I tried what i was not able to do in plan guides,ie forcing a good plan.But still,i found out that the queries are going slow at times and serial plans are being generated in plan cache and query store also failed me.
I learned that my plan cache is being cleared out periodically because i see different counts for plan when i search for a query.Probably that is why serial plans generate ,because good plan gets removed and on subsequent execution serial plan gets into plan cache.
On analyzing the plan cache i learned that most of my queries are Adhoch and is that why plan cache is getting cleared.?
Optmize for adhoc workload is enabled. Total RAM is 128 GB and
max server memory is set to 126 GB
Changing parametrization from simple to forced for the database helps in someway.I think good plans are reused and new serial plans are not getting generated.
Issues that exist:
- Plan cache is getting cleared for some reason.
- Plan guide to add a query hint like OPTION(RECOMPILE) is not working for sp_executesql type queries.
- Some queries which i tuned(removed forced serialization by replacing scalar functions with
TVF) are running slow because
index seeks/scanof a table(with only two columns) is very costly when i check the execution in SentryOne Plan explorer.