9

In SQL server there are so many trace flags. Why some of them need to be turned off. Some where i saw that trace flag 8017 need to be turned off. So i want to know which trace flag need to be tuned off and Why?

Improve this question

3 Answers 3

Reset to default
10

All should be turned off until you know why they should be implemented. Now if you inherit a server with a couple of trace flags on, do not just turn them off if you do not why they are there.

On a new server, knowledge first, then trace flag on. On a old server, leave as is, knowledge, make decision.

Improve this answer
11

The Database Upgrade Advisor can report that trace flag 8017 needs to be turned off during the upgrade process, and re-enabled afterward.

Trace flag 8017 controls whether SQL Server creates schedulers for all logical processors, including those that are not available for SQL Server to use (according to the affinity mask).

Enabling the flag does not create these 'offline' schedulers, saving a few resources. It also means you cannot dynamically add processors for SQL Server by changing the affinity mask; you would need to change the affinity mask and then restart the instance.

If you do not understand why the trace flag is present, do not remove it. There should be some documentation somewhere in your organization to say when and why it was added (and by whom). If not, well there's a process you should fix :)

Improve this answer
2
  • 2
    On my 2008 Express test database it was enabled after installation by default.
    – eckes
    Jun 9, 2017 at 17:29
  • Yeah, mine is just there by default (AFAICT) too. Where are the docs explaining why it might exist when I never added it explicitly?
    – binki
    Sep 2, 2019 at 22:01
3

To add more to what Raadee and Paul White (also confirm what eckes's comment already stated), TF 8017 is enabled by default in all SQL Server Express Edition versions since 2005. It's probably a way of throttling number of CPUs (sockets and/or cores) unsupported by SQL Server edition.

Tested on:

  • SQL Server 2014 Express Edition, with 1 and 2 sockets, trace flag is on
  • SQL Server 2016 Express Edition, with 1 socket, trace flag is on
  • SQL Server 2016 Standard Edition, trace flag is off

CPU limits are described in various Microsoft documents, for example in Compute capacity limits by edition of SQL Server.

On 3rd party resources, TF 8017 is documented on Steinar Anderson's Trace Flag list which, by the way, links to this question.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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