CSE Performance insufficient

[Legacy User]

We are currently trying to move one of our big models (14 Mio++ Objects) from HE to CSE. Our CSE installation is Windows / SQL Server based and we do have model copy times of 36+ hours. This is not acceptable as we requiere <8 hours.

Unfortunately there seems to be no real tuning guideline available and we get only the hint to engage microsoft concerning SQL server what we will certainly do. Is there anyone out there with CSE experience ?

[DARIUS PANAHY]

It would be helpful to know what tuning steps have been performed so far and what the hardware configuration is, for example, amount of memory, disk configuration, virtual or real hardware, what database features are enabled for  .

 

Some things to check:

 

1) Fast disks - in our experience, a RAID array with 15k rpm disks striped across at least 6 disks with a fast raid controller gives good disk io performance and even better is an enterprise performance SSD drive (we use an OCX zDrive SSD).

 

2) Support client tuning - Use the support client and check the Object Cache setting. We have ours set to 500,000

 

3) Lots of memory for the DBMS

 

4) Install the CSE on the same machine as the DBMS to avoid network latency

 

5) Avoid virtualisation

 

6) Install a good volume of data and then update the database stats (not sure if this is still needed, but it used to be the case that optimised access paths needed updated catalog stats)

 

A customer of ours moved from an HE to a sql server CSE a few years ago and had big performance issues. They managed to get a 5m object model copy down from 10 hours to under 3 hours by tweaking sql server parameters (CPU and IO affinity), and 'balancing' the memory. Further benchmarking indicated that clock speed was a big factor - since the processes only run on one core, having a machine with many cores was not much use for a single model copy, but would allow more parallel activity to enable several model copies to complete in not much more than the time for a single model copy, especially when using our Task Dispatcher to avoid ency contention.

 

They may be able to provide an update since this was 3 years ago.

[Lynn Williams]

I concur with Darius' advice about memory and I/O being the primary factors.

Also I think the example model highlighted by Darius is described in the Feb 2015 post from Aldred Gonzalez in CSE Virtualization

[Aldred Gonzalez]

I concur with the suggestions from Darius and from my earlier post about virtualisation mentioned by Lynn (thanks Lynn!)

 

Here is some more data from our experience in CSE (old CSE was mentioned by Darius above) ...

At current site ... not a component shop so Entities are referenced all the way from the interface to the data layer.

There are also ENTs that are called all over the model ... so have to be careful what happens when one of these change as the downstream impacts cause large rebuilds.

 

Development model is 5.8m objects and the production model is 5.5m.

Old CSE (E7 2850 x 2 - 2.0 GHz and DB on RAIDed SSDs) model copies were 5hrs.

Current CSE (E5 2643 x 2 - 3.5 GHz and DB on RAIDed SSDs) model copies at approx. 2hrs.

If one major ENT changes, current CSE can take 3 hours for the migrate of just the aggregate changes (using IET GuardIEn as the management tool).

 

Our object cache is 10,000,000 ... we have mucked around with this and this seemed there didn't seem much of a difference between 1m and 10m. So we kept the bigger number.

 

I am currently experimenting with memory allocation to SQLServer database ... as our server has 64Gb RAM installed. For the performance tests this was set to 13Gb.

Lately I have set it to 25Gb and smaller migrates seem to work faster as more of the database is withheld in memory.

 

We also installed Ola Hallengrens SQLServer tools (SQL Server Backup, Integrity Check, Index and Statistics Maintenance) and scheduled regular reorgs of the indexes.

 

While we were investigating issues earlier in the conversion across from HE to CSE and tracing the SQL queries, we modified one of the standard Gen indexes ... adding ASSOC_CHG_STATUS to DASCI2 index. This gave a definitely increase in migrate performance.

 

SQLServer doesn't have all the nice CA tools like DB2 has ... but there is a wealth of information on the internet. Especially if you are running the newer versions of SQLServer (we have 2012 now). There are a bunch of SQLs to run to check the performance counters on the database (memory, top SQL queries, explains, unused indexes, etc).

 

Parallelism settings are important on SQLServer. Have been experimenting with this as well. Current Prod CSE is 'max degree of parallelism = 3' and 'Cost Threshold = 10'.

These are based on what CPU you are running ... there is a script on the internet that can help you determine an optimum number - MSDN Blogs

 

Also change the power settings on your server ... we noticed that if set to 'balanced' then the CPU doesn't run at 100% and most of the ticks are taken with checking what the state of the  CPU is at.

Set it to 'high performance' profile ... makes a BIG difference.

 

We had Microsoft in to help us at one stage early in the conversion project and they helped set the original Parallelism settings ... I think they also figured the issue with the power settings as well. I don't think any of the other suggestions they gave at the time made any difference ... we basically traced the migrate/copy processes in SQL Profiler to figure our what queries were taking a long time to process. Now we have been educated about the performance counters available on the database we can use these instead.

 

Lastly ... never hurts to make friends with your SQLServer DBA. Sometimes they can at least give you a breadcrumb to follow ...

 

Hope this helps!!!!