ArcSDE 10.1 Random slow performance

[AntheaTung]

Hi,

I have users that experience very random slow performance.  One day it is great and another day is slow. Different users have different experience in the same room at the time - some fast and some slow.

System info:

sql server 2008 sp3 r2 - Virtual Machine.  issued traceflag 4199 for the sql server spatial index bug and set maxdop to -1 (has 24 cores)

Arcsde 10.1 sp1, Geometry storage type 80% of the data while 20% of the data is in SDEBinary, Electric Geometric network features

Schneider Designer 10.1

Citrix VM XenApp 6.5

Database Size:  30 GB with orthoimagery

Outstanding versions: 778

Versions with conflicts : 123

Every night there is a compress and reconcile process that kicks off. 

There are 44 - 46 users connected to the database at the same time.

We noticed we had to run a rebuild index and statistics with full scan 3 times a day to see improvements but however the random slow days this process does not work.

I have been running a manual compress every 2 -3 hours for the past 3 days.  Today it did not make a difference.

We check Citrix RDP and it makes no difference.  We monitored the database and see no issues.

Any inputs are appreciated !

Thanks in advance.

[SSMIC3038]

Do you have any named versions?

Have you done a full compress (all users off - reconcile named versions then delete them - compress - state ID down to 0)?

[AntheaTung]

I have 788 named versions as of now/. I cannot reconcile and post and delete all versions to state 0.  There are long transaction jobs that spans over a couple of years with outstanding versions.

There is a nightly automated task that reconciles (abort if conflicts) and then compress.

Once a month I kick off all users from the system and do reconcile and compress.

I have been manually doing compress throughout the day - like 3 - 4 times to see if more compress will make any differences and it did not make any difference at all.

Thanks

[SSMIC3038]

Personally I suspect this is the problem.

ESRI promotes those sorts of long transactions but it's at odds with best database practices of regular full compress, so it's a bit of a catch-22.

This is a difficult spot to be in, hundreds of named versions and one has to assume that not all those versions are required or that some were made by users that perhaps didn't have a full understanding of DB administration or even require a named version for their tasks.

If this is indeed the problem than there's not a lot of advice to give, if the version tree needs trimming then it will likely involve data loss.

Personally, I admin hundreds of users and corporate GIS networks for sewer, water distribution and electrical distribution (among other things) and we only keep a few named versions. We tightly regulate named versions. They have a place in a work process but not all users should be making named versions on a whim, the process should be managed/administered.

Just my 2 cents.