Performance Tips for SQL Server

*For SQL Server 2012 or above

  • Make sure that advanced configurations (i.e. max memory settings) are correct and follow the standards of your organization. Use check_advanced_settings.sql;
  • Make sure that all standards trace flags are in place, you may try the finer stats trace flag 2371 (KB2754171), especially if you have large tables;
  • Also you may use the trace flag 1118 (KB2154845) to help contention in tempdb database. For others useful trace flags, see here;
  • Keep up to 8 data files for tempdb database (KB2154845);
  • Based in my own experience, enable SET READ_COMMITTED_SNAPSHOT ON isolation level. I had seen approximately 50% TPS increases after enabling it on my systems;
  • You may try to tweak your MAXDOP settings if there are any misbehaved queries and depending on the number of CPUs on your system. Please refer to KB2806535;
  • Make sure that database compatibility level is set correctly;
  • Set cost threshold for parallelism = 50. Please refer to this nice article https://www.brentozar.com/archive/2013/09/five-sql-server-settings-to-change;
  • Run index maintenance and update statistics regularly;
  • Purge old data regularly.