Essential Trace Flags to enable at SQL Server Services startup

 List of trace flags to enable on SQL server service startup:


Server NameTrace FlagStatusGlobalSession
PRD-MSSQL01C1222110
PRD-MSSQL01C1224110
PRD-MSSQL01C4136110

Reason: 

  1. 4136 will help in preventing parameter sniffing on sql server and in turn will result in query optimizer to create optimized plans for same queries having different parameters which will result in better performance. Note: Please ensure that you thoroughly test this option, before rolling it into a production environment.
    Scope: Global or session
  2. 1224 disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation. This helps avoid "out-of-locks" errors when many locks are being used.
    Scope: Global or session
  3. 1222 helps in detecting deadlocks 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.
    Note: Avoid using trace flag 1222 on workload-intensive systems causing deadlocks.
    Scope: Global only


Comments

Popular posts from this blog

Enabling fixed Sidebar for blogger Contempo template for screen sizes smaller than 1440px

Adding copy to clipboard button to every google code prettify pre blocks in blogger

Solution: More Posts & Posts Links not working with Adsense Anchor ads on Touch screen mobiles in blogger