tempdb consumption issue?

Hi Dave,

As for tempdb, the cause is known. I found out the SPID error back when I was migrating Oracle to SQL Server back in 2011. I created this call C10646599 and found out that Windchill PDMLink 10 would only work with SQL Server with 1 method server. Yes, it will run, but it will be really bad with tempdb. I finally got PTC R&D to admit to what I found out at Comdev when I migrated ORACLE to SQL SERVER. PTC R&D are now in contact with Microsoft SQL Server to find a way to have more than 1 method server session connecting to the same database table. It will happen especially when you have multiple foreground and background methodservers. You’ll get transaction errors. I found out that after completing the migration, I could not start Windchiil with multiple method servers. I was getting transaction issues. I could only successfully start Windchill with 1 single method server. After so many successful migrations and only having issues starting Windchill with multiple background and foreground method servers, I just gavethe single method servera try and walla no issues of transaction errors.

In my current company, we are having the same issue as you do.

There was alwaysa bug in SQL Sever handling multiple process pointing to the same record. As a result, SQL Server locks the record which causes a SPID or transaction locks. You don't see the error in Windchill much until you try to restart the server which then produces a transaction lock error and you have to restart all method servers again.

The feedback from the customers I know and in PTCUSER.org site is that there is at least a 25% to 30% performance loss when moving to SQL Server from Oracle.

This is the statement I got from PTC Technical Support with the SPR2179344 which is in regards to my current environment.

http://technet.microsoft.com/en-us/sqlserver/ff803383.aspx

And of course, it is not released yet. The funny thing about that statement is that there is no guarantees that it will fix the issue. Neither PTC or Microsoft will test this issue until it's release.

The behaviour is like when I was at Reebok. Though we get the java heaps now in our Windows VM environment with 4 foreground and 3 background, there is a performance issue with processing and memory at the same time. That slowness, lag, locking will cause the database to be backlogged in queries and transactions which results in a larger growing tempdb. I do know and experienced for many years testing and helping other customers with Windows environments. We had major issues with Windows VMs especially on large scale Windchill implementations beyond a little laptop install. I’ve actually brought down some VM ESX host servers before when Windchill took over the entire server. Here is a technical explanation of java and java garbage collection. The behavior of Windchill with Java and OS is the issue:

• 
  
• http://docs.oracle.com/cd/E18930_01/html/821-2431/abeic.html
• 
  
• NUMA
  on Windows and Java always had issues. 64BIT Windows OS is still maturing.
• 
  
• http://developer.amd.com/community/blog/huge-pages-and-numa-on-windows-operating-systems/
• 
  

I have to constantly shutdown my Windchill system and run these sqls to clean up the database then reboot the SQL Server database machine to reclaim the diskspace almost every day. The performance of Windchill immediately degrades after 1 day to a point where simple transactions are held for so long in tempdb and users cannot check-in.

What will happen is that your tempdb in SQL Server will keep on growing and transactions will slowly backup due to being locked internally. With Windchill PDMLink, it is customary to have more than 1 foreground and backgrounds to balance the load of users and other queued items.

Depending on the usage/# transactions and the number of users, the tempdb will grow because inherently the tables are locked and not released for the next transaction. I can tell from the PTC TCs that our current IT system and software architecture with OS and database is not the configuration for high performance enterprise system especially for our Mold Master rates of transactions. Those job workspaces of drawings and assemblies have to be multiplied by 6 due to design automation and WVS publishing on release states (under review, preliminary release, released, obsolete). Don’t forget workspaces for each publish of drawings and assemblies. Just the designers custom jobs alone is 30, 000 workspaces a year. Then you should have a minimum of 180, 000 workspaces for publishing and automation. Then you multiply that number by the number of custom components and drawings per job, this can range from 20 to 300. Thus, the publishing alone is 3,600,000 to 54,000,000 workspaces a year just for publishing. It’s simple math.

It’s hard to change a ton of code behavior in a couple of years between 2 large companies especially Microsoft and PTC.

Buyer beware and not always go with software or consulting just due to price pointsor whatsome sales marketing documents. You must always test, load test andperform UATsbefore you release or approve even the IT infrastructure. Sometimes you pay much much more in the long run purchasing cheaper infrstructure, software and consulting.

Say hi to all the great Bose guys for me. Really nice people out there in Framingham, MA. Is Dr. Bose retired yet?

Take care,

Patrick

In Reply to David Santos:

Hi, we went live with Windchill 10.1 on Feb 3 2014. Since go live, we have had an issue where our tempdb directory is consuming an enormous amount of space. We have had to shut the application down twice already to prevent running out of space completely. Below are some additional details. We are in the process of replicating migrated data to 11 sites (3 sites at a time) and we are running publishing on a backlog of over 800k CAD objects. Has anyone experienced a similar issue with tempdb?

• 
  
• Windchill 10.1 M30
• 
  
• SQL Server 2008 R2
• 
  
• tempdb filling up consuming on average 1GB/hr
• 
  
• tempdb is currently 120GB for a 60GB database
  and growing
• 
  
• Cause unknown.
• 
  

Thanks,

Dave