-2

I watched a lesson on Youtube about SQL Server performance tuning. They demonstrated on a query "Wait Time per Core Per Sec". They use an update script as below:

DBCC TRACEON(4199,-1);

DBCC FREEPROCCACHE;

Go

Can anybody explain this script? I used SQL Server 2017.

My question: Is it a benefit when I apply this script to improve the performance?

closed as off-topic by hot2use, Erik Darling, George.Palacios, kevinsky, mustaccio Jun 13 at 12:49

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – George.Palacios, kevinsky, mustaccio
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    No you don't benefit from this. Trace flag 4199 in on by default from SQL Server 2016 onwards. FREEPROCCACHE clears the plan cache, this is useful when you want to ensure you generate a new plan, it's not a performance switch. Check out the MS Docs. docs.microsoft.com/en-us/sql/t-sql/database-console-commands/… – gareth Jun 13 at 10:51
  • Do you know the way to improve query performance ? pls – Rada Tann Jun 13 at 10:54
  • generally, or you have 1 specific query causing you pain? – gareth Jun 13 at 10:56
  • 2
    The script does not tune the query; its intent is to measure performance. Generally one improves performance by examining the execution plan and performing query and index tuning. – Dan Guzman Jun 13 at 11:06
2

Before SQL Server 2016 you needed the trace flag to enable query optimizer fixes from previous SQL Server versions. Since SQL Server 2016 these fixes from previous versions are applied by default and TF 4199 will enable query optimizer fixes that are released in CUs and SPs (but since you are using SQL Server 2017, SPs aren't applicable to you).

Enabling the TF might enhance performance, but it might do the exact opposite for certain queries as well. Different behavior from the QO can result in different execution plans and thus different performance.

Since SQL Server 2016 you can enable the optimizer fixes on a database level by setting the QUERY_OPTIMIZER_HOTFIXES database option.

For a specific query you can test the performance by using the query hint ENABLE_QUERY_OPTIMIZER_HOTFIXES or by using QUERYTRACEON.

In general, don't expect a major difference by setting the TF on or off. Instead you should start by tuning your queries and indexes.

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