Sometimes your environment is unique enough that the standard recommendations aren’t enough to address your SQL Server performance needs. Your high performance environment might need a tweak to get a little more performance, but the good news is you are not alone. The Microsoft technical team has worked though problems like yours and has some basic recommendations. You can access the specifics from the Microsoft post.
- Table 1 contains the most frequently recommended updates and trace flags for scalability on high-end systems.
- Table 2 contains additional scalability fixes that were included with a cumulative update.
- Table 3 contains recommendations and guidance for additional performance tuning.
|#||Scenario and symptom to consider||Trace flag||Required version/update||Knowledge Base article/Blog link that provides more details|
||T8048||SQL 2014 RTM, SQL 2012 RTM||CSS escalation services blog post
that covers this topic.
||T9024||SQL 2014 RTM, SQL 2012 SP1 CU3||2809338
FIX: High “log write waits” counter value on an instance of SQL Server 2012
||T1236||SQL 2014 RTM CU1||2926217
FIX: Performance problems occur when database lock activity increases in SQL Server
||T1118||SQL 2014 RTM, SQL 2012 RTM||328551
Concurrency enhancements for the tempdb database
Note Enable the trace flag and add multiple data files for the tempdb database.
||T1117||SQL 2014 RTM, SQL 2012 RTM||2154845
Recommendations to reduce allocation contention in the SQL Server tempdb database
||Not applicable||SQL 2014 RTM CU1
Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012 or SQL Server 2014