Skip to content

Instantly share code, notes, and snippets.

@ronascentes

ronascentes/enable_trace_flags.sql

Last active October 4, 2016 17:42
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ronascentes/866a198a4069f684f780fd842e3ffccb to your computer and use it in GitHub Desktop.
Save ronascentes/866a198a4069f684f780fd842e3ffccb to your computer and use it in GitHub Desktop.
Download ZIP
Useful trace flags for SQL Server performance improvement
Raw
enable_trace_flags.sql
-- Trace flag 1118 turns off mixed page allocations. Preventing mixed page allocations reduces the risk of page latch contention
-- on the SGAM allocation bitmatps that track mixed extents; which Paul says is one of the leading causes for contention in tempdb.
-- When doing allocations for user tables always allocate full extents. Reducing contention of mixed extent allocations
DBCC TRACEON (1118,-1)
-- simply stops SQL Server from writing backup successful messages to the error log.
DBCC TRACEON (3226,-1)
-- Changes to automatic update statistics
DBCC TRACEON (2371,-1)
-- When growing a data file grow all files at the same time so they remain the same size, reducing allocation contention points.
-- applies to the entire SQL Server instance, not just to one DB and it affects all files in the same filegroup in a database
DBCC TRACEON (1117,-1)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment