We have a extreme unusual performance issue in our TC. The TC speed is very fluctuating, for example checkout needs sometimes 10 seconds and the a few seconds later the same procedure needs about 2 minutes. The structure expand needs the double time than on a reference system. And we've got the feeling that the system extrimly slow down when 2 or 3 people loads big structures to NX. We see in our server logs that normaly all TC Servers and the Database Server have a maximum CPU usage of 20%, but somtimes the DB Server had a peak up to 50%. In the TCLogs we see that especialy when we open structures there are a lot of sql queries. The reason for that seems to be an occurrence "In Context" overwriting thats automaticly is in our DB because we activates the NX arrangememt sync. GTAC believes that this is no system failure but they are creating a performance patch for TC11.5 to fasten this queries up. I did not believe that this is our only problem here, but there is nothing concrete I can say. I thing these fluctuating is some DB Server Issue or something in the 4tier communication.
Maybe someone else had the same problems and found a solution or can gave me a hint what we can check.
Here are oure system data:
2 Enterprise Server, each for 64 User
MSSQL 2014 based Database
1 Webtier Server
1 Dispatcher Server
1 T4EA Server
I know this issue is not very concrete, but sometimes it helps to talk about to find solutions 😉
Let's start at the database instance...
MS SQL 2014 > Apply CU1 from Microsoft. https://support.microsoft.com/en-us/help/2887899/
Also, see SFB-Teamcenter-6464
Run this SQL to check indexes (fragmentation >50%, page_count >15)
SELECT SI.NAME AS INDEX_NAME, OBJECT_NAME(DMV.object_id) AS TABLE_NAME , avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats( DB_ID(), NULL, NULL, NULL, 'Detailed') as DMV LEFT OUTER JOIN sys.indexes AS SI ON DMV.OBJECT_ID = SI.OBJECT_ID AND DMV.INDEX_ID = SI.INDEX_ID WHERE avg_fragmentation_in_percent > 50.0 AND page_count > 15 AND SI.NAME IS NOT NULL AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX') ORDER BY avg_fragmentation_in_percent DESC;
Run in a TC command window
index_verifier.exe -u=infodba -p=infodba -g=dba -o=DO_IT
Rerun the SQL and compare stats. Same as first run.
Collect all the SQL statements that start with 'ALTER' from the text file (DO_IT).
Execute the 'ALTER' commands in SQL server manager.
Rerun the SQL and compare stats. No rows returned. Indexes are successfully defragmented.