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:
For those unable to access the GTAC field bulletins I have copied and pasted the bulletin below:
Solution Center
________________________________________
________________________________________
[ Siemens PLM Software ]
S O F T W A R E SFB-Teamcenter-6464
F I E L D Page 1 of 3
B U L L E T I N Issue Date: January 12, 2012
Revision Date: January 12, 2012
Supersedes: N/A
Submitted By: William Halpin
Responsible Group: Teamcenter
GTAC 800 # Options(s): 2,3,1
PRODUCT: Teamcenter
SUBJECT: Tuning SQL Server for large deployments
----------------------------------------------------------------------------
Affected Software: Teamcenter
Associated PR Number(s): N/A
Scope: Teamcenter on SQL Server
Revision: -
----------------------------------------------------------------------------
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.
For SQL Server 2008 R2, upgrade to Cumulative Update 7 (CU7) or later.
SQL SERVER INITIALIZATION PARAMETERS
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.
Parameter: Max Degree of Parallelism
Value: 1
Location/Command/Purpose:
sp_configure 'max degree of parallelism', 1;
reconfigure;
Parameter: AUTO_UPDATE_STATISTICS
Value: ON
Location/Command/Purpose:
alter database @DB_NAME@ set AUTO_UPDATE_STATISTICS_ON
Parameter: AUTO_UPDATE_STATISTICS_ASYNC
Value: ON
Location/Command/Purpose:
alter database @DB_NAME@ set AUTO_UPDATE_STATISTICS_ASYNC_ON
Parameter: READ_COMMITTED_SNAPSHOT
Value: ON
Location/Command/Purpose:
alter database @DB_NAME@ set READ_COMMITTED_ON
{see also SFB-Teamcenter-6327}
WINDOWS SERVER 2008 R2 PARAMETERS
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.)
Parameter: maxuserport
Value: 0x0000ffff (65535)
Location/Command/Purpose:
HKLM\System\CurrentControlSet\Services\Tcpip\Parameters\(REG_DWORD)
(This is especially important on the web tier as each
user requires a separate ephemeral IP port.)
The following should be set in a Windows command shell with administrative
priviledges:
Parameter: Receive-Side Scaling State
Value: disabled
Location/Command/Purpose:
netsh int tcp set global rss=disabled
Parameter: Chimney Offload State
Value: disabled
Location/Command/Purpose:
netsh int tcp set global chimney=disabled
Parameter: NetDMA State
Value: disabled
Location/Command/Purpose:
netsh int tcp set global netdma=disabled
Parameter: Direct Cache Acess (DCA)
Value: disabled
Location/Command/Purpose:
netsh int tcp set global dca=disabled
Parameter: Receive Window Auto-Tuning Level
Value: normal
Location/Command/Purpose:
netsh int tcp set global autotuninglevel=normal
Parameter: Add-On Congestion Control Provider
Value: ctpc
Location/Command/Purpose:
netsh int tcp set global congestionprovider=ctcp
Parameter: ECN Capability
Value: disabled
Location/Command/Purpose:
netsh int tcp set global ecncapability=disabled
Parameter: RFC 1323 Timestamps
Value: disabled
Location/Command/Purpose:
netsh int tcp set global timestamps=disabled
TEAMCENTER INDEXES
The following indexes should be added for Teamcenter releases prior to Tc
9.1 for SQL Server 2008 R2 CU7 or later.
The following should be added with the Teamcenter install utility:
install -add_index infodba infodba dba APA_TYPE_class 0 imantype type_class
install -add_index infodba infodba dba APA_PROPINFO_bobject 0 propertyinfo
business_ object
install -add_index infodba infodba dbaAPA_USER_status_license 0 POM_user
status license_level
The following should be added using the SQL Server Management Studio:
CREATE INDEX pipformtypedef_0 on PFORMTYPEDEF(rstorage_classu);
CREATE INDEX pipform_1 on PFORM(rdata_filec, puid);
CREATE CLUSTERED INDEX [PIPOM_F_LOCK_VUID] ON [dbo].[POM_F_LOCK]([vuid] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =
ON) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [PIPOM_M_LOCK_0] ON [DBO].[POM_M_LOCK ([PROCESS]
ASC) WHERE LOCK_MODE IS NULL
CREATE INDEX [new_index_53_52_PPOM_MEMBER] ON [M08TCwr_001].[dbo].
[PPOM_MEMBER] ([rgroupu]) INCLUDE ([puid])
CREATE INDEX [new_index_51_50_PGROUPMEMBER] ON [M08TCwr_001].[dbo].
[PGROUPMEMBER] ([rroleu]) INCLUDE ([puid])
CREATE INDEX [new_index_4_3_PTASKINBOX] ON [M08TCwr_001].[dbo].[PTASKINBOX]
([rowneru])
CREATE INDEX [new_index_6_5_PIMANTYPE] ON [M08TCwr_001].[dbo].[PIMANTYPE]
([pisAbstract]) INCLUDE ([puid])
CREATE INDEX [new_index_2_1_PPOM_ATTRIBUTE] ON [M08TCwr_001].[dbo].
[PPOM_ATTRIBUTE] ([plength])
----------------------------------------------------------------------------
If any questions arise regarding this field bulletin, the
Global Technical Access Center may be contacted at:
All USA & Canada (800) 955-0000
If you are outside the United States and Canada, any questions
regarding this or any other SFB should be directed to your local
support office, quoting the SFB number.
No comments:
Post a Comment