Recommended Configuration Options for SQL Server

sqlserver2014

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.

The recommendations are grouped into three tables as follows:
  • 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
1
  • You encounter high CMEMTHREAD waits.
  • SQL Server is installed on systems with 8 or more cores per socket
T8048 SQL 2014 RTM, SQL 2012 RTM CSS escalation services blog post

that covers this topic.

2
  • You are using features that rely on log pool cache (for example, Always On)
  • SQL Server is installed on systems with multiple sockets.
T9024 SQL 2014 RTM, SQL 2012 SP1 CU3

2809338

FIX: High “log write waits” counter value on an instance of SQL Server 2012

3
  • Your instance of SQL Server is handling thousands of connection resets because of connection pooling.
T1236 SQL 2014 RTM CU1

, SQL 2012 SP1 CU9

2926217

FIX: Performance problems occur when database lock activity increases in SQL Server

4
  • Your application workload involves frequent tempdb usage (creation and drop of temp tables or table variables).
  • You notice user requests waiting for tempdb page resources because of allocation contention.
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.

5
  • You have multiple tempdb data files.
  • The data files at first are set to the same size.
  • Because of heavy activity, tempdb files encounter growth and not all files grow at the same time and cause allocation contention.
T1117 SQL 2014 RTM, SQL 2012 RTM 2154845

Recommendations to reduce allocation contention in the SQL Server tempdb database

6
  • Performance issues because of SOS_PHYS_PAGE_CACHE and CMEMTHREAD waits during memory allocation on large-memory computers.
Not applicable SQL 2014 RTM CU1

,

SQL 2012 SP1 CU9

2926223

Performance problems occur in NUMA environments during foreign page processing in SQL Server 2012 or SQL Server 2014

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s