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:


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