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 parameter sniffing.

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:

  1. Query Store
  2. 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 sp_executesql.

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.?

Any suggestions/solutions? Plan cache summary

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/scan of a table(with only two columns) is very costly when i check the execution in SentryOne Plan explorer.
  • max memory looks a bit high to me. how many dbs in total per instance ? The server has only 1 instance or multiple ? What is the size of the largest db on the instance ? I am guessing there might be memory pressure - have a look at the ring buffer to see if that is true or not. – Kin Shah Jul 23 '18 at 19:41
  • There is only one instance and there are 5 db's ,but only one db is the main one.All the queries hit that db and it is around 1.5 TB including data and Indexes.Initially i kept 80& as max memory .but as per the application lead suggestion i changed it to 126 GB,as this is a dedicated server. – user9516827 Jul 23 '18 at 19:50
  • 1
    You may want to look at this for details about max server memory sizing. – Max Vernon Jul 23 '18 at 20:59
  • There's also the option of forced parameterization. Test if possible as this isn't necessarily a silver bullet. – John Eisbrener Jul 23 '18 at 21:18
  • 1
    Did you check ring buffer for memory pressure ? I think that is the issue since you have 1.5TB db with 126GB RAM. – Kin Shah Jul 24 '18 at 15:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.