Richard

  • Home
All Posts Links About

Richard

  • Home

Most Wanted SQL Server Trace Flags

2017-10-23

SQL Server trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior. Here are the long list of available trace flags you can set. Following are some of the most wanted popular trace flags you might set for your SQL Server. I usually enable them when I setup a new SQL Server. But please test them out in test environment before applying to production servers!!!

1117: When a file in the filegroup meets the autogrow threshold, all files in the filegroup grow. Scope: global only.

Note: Starting with SQL Server 2016 this behavior is controlled by the AUTOGROW_SINGLE_FILE and AUTOGROW_ALL_FILES option of ALTER DATABASE, and trace flag 1117 has no affect. For more information, see ALTER DATABASE File and Filegroup Options (Transact-SQL).

1222: Returns the resources and types of locks that are participating in a deadlock and also the current command affected, in an XML format that does not comply with any XSD schema. Scope: global only. This is very useful for troubleshooting deadlocks.

2371: Changes the fixed auto update statistics threshold to dynamic auto update statistics threshold. For more information, see this Microsoft Support article. Scope: global only.

Note: Starting with SQL Server 2016 this behavior is controlled by the engine and trace flag 2371 has no effect.

3226: By default, every successful backup operation adds an entry in the SQL Server error log and in the system event log. If you create very frequent log backups, these success messages accumulate quickly, resulting in huge error logs in which finding other messages is problematic. With this trace flag, you can suppress these log entries. This is useful if you are running frequent log backups and if none of your scripts depend on those entries. Scope: global only.

4199: Enables query optimizer (QO) changes released in SQL Server Cumulative Updates and Service Packs. QO changes that are made to previous releases of SQL Server are enabled by default under the latest database compatibility level in a given product release, without trace flag 4199 enabled. Scope: global or session or query.

To accomplish this at the database level, see the QUERY_OPTIMIZER_HOTFIXES option in ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).

Starting with SQL Server 2016 SP1, to accomplish this at the query level, add the USE HINT ‘ENABLE_QUERY_OPTIMIZER_HOTFIXES’ query hint instead of using this trace flag.

  • SQL Server
  • Trace Flags

扫一扫,分享到微信

微信分享二维码
Two new features in SQL Server Management Studio 17.3
© 2017 Richard
Hexo Theme Yilia by Litten
  • All Posts
  • Links
  • About

tag:

  • ASPNET
  • DotNetCore
  • VS2015
  • Service Pack 2
  • SQL Server
  • Database Restore
  • SQL Server 2008 R2
  • GitHub
  • Blog
  • Trace Flags
  • SQL Server 2017
  • Linux
  • SQL Server Management Studio
  • SSMS
  • SQL Server 2014

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录(注意不是yilia根目录)执行以下命令:
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置:

      jsonContent:
        meta: false
        pages: false
        posts:
          title: true
          date: true
          path: true
          text: false
          raw: false
          content: false
          slug: false
          updated: false
          comments: false
          link: false
          permalink: false
          excerpt: false
          categories: false
          tags: true
    

  • SQL Server Updates
  • SQLBlog.com
  • SQL Server Central
  • My Old Blog Site 1
  • My Old Blog Site 2
  • Unicode Character Map for Windows
  • Dev Tool List (From Scott Hanselman)
Improve daily life using IT techs.