A tool that is often used to diagnose performance problems, particularly when the problem is reproducible is the Windchill profiler. The data in the output file will rule in or rule out a server side performance problem quickly, unfortunately though the output is cryptic and difficult to make sense of.
I've just posted here(authenticated) a knowledge base article on how to understand the output.
The presentation contains lots of details about understanding the output file, it may seem overwhelming. A couple of things to keep in mind:
- The most common Windchill performance problem is one in which there are multiple round trips (hundreds or more) being made to the database.
- To see if the profile contains any of these operations select #SQL #Count, and then upside down arrow to inverse the tree
- Scan the SQL statements looking for high 'count' values, but only those without 'DB FETCH' immediately after them (this will be clearer when you read the presentation)
- 'DB FETCH' results represents the rows returned from an operation
- often it is normal activity but once the counts starting getting in the tens of thousands or hundreds of thousands often represent performance and scalability problems
- The second thing to do is to look for long running SQL statements
- #SQL #COST and use the upside down arrow to invert if necessary so there is a list of SQL statements all starting with 'cost'
- 'cost' is a time in ms i.e. cost=11568 is 11.568 seconds
- 'cost' times under a second are not particularly reliable and shouldn't be depended on for accuracy
- Look for SQL statements near the top like:
- 'cost=11568:SELECT ....'
- These are SQL statements which could be in need of tuning
- Statements like 'cost=568:DB FETCH:SELECT ...' are the time it takes to return rows from the database to method server and unless the time is very high likely represent normal Windchill operations
The Windchill profiler is a powerful diagnostic tool and with practice it's often possible to tell quickly if there is a performance problem with a particular operation.
