-1

I have 200 GB production database. Normally daily prepared query plan count 8K. Most of these plans are non necessary because of the non parametric queries. Software developers are working on this.

When a lot of non parameterized queries are sent, some query's plan will be deleted. New plans are sometimes incorrect and cause the query to timeout. We clear the cache of query plan to resolve problem.

enter image description here

Daily data changes count between 600k-1m (INSERT-UPDATE-DELETE). For this reason, the statistics on some tables are modified. I think it creates incorrect plan when statistics updated.

I changed some settings:

SET AUTO_UPDATE_STATISTICS_ASYNC OFF

SET AUTO_UPDATE_STATISTICS OFF

I update all stats every night with ola.hallengren script and creates good working plans.

But sometimes the problem repeats. All queries need to be parameterized for the solution. This takes a long time. What should I do for a solution during this time? Is it an error to turn off automatic statistics updates?

  • Have you looked at forced parameterization? – Erik Darling Nov 1 '17 at 13:10
  • 3
    Also consider turning on the optimize for ad-hoc workloads to avoid caching many single-use ad-hoc queries. – Dan Guzman Nov 1 '17 at 13:12
  • parameterization status forced and optimize for ad-hoc workloads status true. – Yunus UYANIK Nov 1 '17 at 13:26
0

you can use query hints to enforce the path you would like the SQL program to take, Brent Ozar has some great resources as well as it looks like you are already familiar with ola.hallengren scripts - he might be able to help you out.

I think it all comes down to the amount of time and resources you would like to spend re-engineering the process. There are likely many ways to improve this but no one overarching tactic will suffice.

https://www.brentozar.com/

specifically, the sp_blitz program can allow you to analyze your queries to identify which indexes might be missing or required to give as HINTS.

https://www.brentozar.com/blitz/

I am not affiliated with Brent Ozar or his team but have heard him speak and used his tools to identify and troubleshoot issues with long running queries related to indexing.

  • I use Blitz procedures. I follow the regulations on the Indexes constantly. The queries are being edited by the software developers but will take a long time. I guess there is nothing I can do other than waiting for the correction of the queries. – Yunus UYANIK Nov 2 '17 at 7:34

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.