Tips 'n tricks related to CAx and PLM software and hardware. Primary posts are about Solid Edge, Insight and Teamcenter data management but there's smatterings of other "stuff" that as a hacker I feel is handy to get your job done. I'm a Siemens PLM Software employee. All opinions posted are my own and do not represent those of my employer. If you are looking for dish commentary, not gonna happen here. This is a technical how-to get stuff done better and faster blog.

January 24, 2012

Tuning Microsoft SQL Server for Teamcenter

GTAC Software Field Bulletin SFB-Teamcenter-6464 has some great tweaks for tuning Microsoft SQL Server 2008 R2 with Teamcenter.
Although the bulletin is intended for deployments of 2,000 concurrent users or more, the bulletin also states that there was no adverse impact setting these tweaks for smaller deployments and that these tweaks could be applied to all deployments of SQL Server with Teamcenter.

You will need a WebKey account to be able to access the GTAC SFB directly:

PROBLEM: Concurrent user counts above a few thousand may result in infrequent transaction failures or unnecessary CPU and I/O utilization.

REMEDY: Scalability benchmarks recently executed at Microsoft with Teamcenter 8.3.2 and SQL Server 2008 R2 revealed a number of indexes, SQL Server configuration parameters, and Windows Server configuration parameters that help to reduce CPU and I/O utilization as well as minimize transient transaction failures.

Deployments of 2,000 concurrent users or more should consider implementing all of the measures below. However no adverse impact was observed at lower numbers of users so these can be implemented on all deployments of SQL Server 2008 R2 or later.

The following should be added to SQL Server 2008 R2 CU7 or later using the SQL Server Management Studio:

NOTE: in the commands that follow, substitute the appropriate database instance name in place of the "@DB_NAME@" string.

Parameter: named-pipe communication Value: Disable (this is the default value and should be left disabled)

Parameter: Trace 1118 Value: -1 (ON) Location/Command/Purpose: To enable, perform the following: Open the SQL Server Configuration Manager. Select SQL Server Services. Select the appropriate instance (if more than one are running on this server) and open the properties. Select the Startup Parameters tab. Add "-T1118" (without the quotes) and apply the changes. You will need to restart SQL Server for the changes to take effect.

The following should be set in the Windows registry on the SQL Server machine and all server pool machines connecting to it:

Parameter: TcpTimedWaitDelay Value: 0x1E (30s) Location/Command/Purpose: HKLM\System\CurrentControlSet\Services\Tcpip\Parameters\(REG_DWORD) (Default value is 0xF0, which sets the wait time to 240 seconds. This will effectively hold the connection hostage for 4 minutes increasing the number of connections in use.)