Speed issues with Large Projects

[MDWigs]

Hello,

I have a number of projects published in project server which contain
10000+ tasks.

These projects take an excessive (15 mins plus) amount of time to
publish after being updated.

Projects with less tasks (around 100) publish in seconds.

I am running Project Server 2003 SP on Windows 2003 SP1 on one machine,
2.4 ghz, 1.5 gig ram. Connecting (via a 200 mb dedicated backbone) to a
database on SQL SP4 on Windows 2003 SP1 on another machine, 2.4 ghz, 2
gig ram.

Does anyone have any ideas regarding this?

I really want to speed up the time it takes for these large projects to
be published.

Cheers,
MDWigs

 

[Gary L. Chefetz [MVP]]

MDWigs:

There are a lot of factors that can affect performance. The number of
assignments is often more impactful than the number of tasks, particularly
if the project is loaded with resources with many assignments in other
projects as well. With that said, your hardware isn't very beefy and 200mb
backbone isn't much to work with either. You're likely to see a performance
gain by installing Project Server and SQL together on a dual processor
machine. Single processor boxes are marginal at best for these applications.

 

[MDWigs]

Thanks for your feedback Gary;

We are using project purely as a scheduling tool at the moment, so
there are no task assignments or resource at all.

Everything I've read says that it is best to put the SQL and Project
Server on two separate machines if possible. You think that it is best
to put then on a single machine?

Also would you suggest a gigbit backbone?

Again the performance issues seem to be directly related to project
size, and updates made to large projects. PWA view generation is fast
with multiple users, it is just publishing the project plans that take
excessive amounts of time.

I am looking to upgrade the hardware and if needed scale out as well
and put different services on different machines if needed.

Your help is greatly apprecaited.
MDWigs

 

[Gary L. Chefetz [MVP]]

MD,

The bottleneck is the views notification service which is a single-threaded
resource hog. Because of this, as long as Project Server is run on a single
processor machine, it's going to be slooooow. On a two processor box, the
service typically grabs the second processor. Yes, gigabit is nice, teamed
dual gigabit nics running full duplex is even nicer. I suspect your problem
is mostly due to a single processor and less so the network connection.

 

[MDWigs]

"The bottleneck is the views notification service"

Are you sure? When these long saves take place I am seeing next to no
load on the Project Server at all, while the SQL server chews loads of
ram. Processor usage is minimal on both machines while this is taking
place.

There is no speed issue when displaying views in PWA, just publishing
projects.

You suggest upgrading to a dual processor machine. On both servers (SQL
and PS) or just the Project Server? Do you still recommend putting both
these services on one machine?

Given that I can't account for a high processor usage while this
procedure is taking place, I want to ensure that upgrading the speed of
my machine will actually resolve this problem.

Thanks for your help,
MDWigs

 

[Gary L. Chefetz [MVP]]

MDW:

You began this discussion by complaining about long publishing cycles. Now
you're complaining about long saves which are generally caused by two
factors: Client machine horsepower and network connectivity between the
Client and SQL server. For large projects, client RAM is a major factor,
followed by connectivity. Take your client to the data center and put it on
the same network segment as the server to determine how much impact the
network is having.

Again, publishing issues are a combination of the machine capacity for
Project Server and SQL server as well as the connectivity between them. By
the way, my laptop has nearly the same specs as your Project Server.

 

[MDWigs]

Gary,

I have project configured to publish to the project server on save, so
I use the terms "publish" and "save" interchangeably.

Also I publish my clients via citrix, so the server they are running
on, a dual proc Xeon 2.4, 2gig of RAM, is connected on the same
backbone as the other servers. I don't think connectivity is the issue.

Nor do I think it is the client machine (citrix server), as I've tried
to publish these plans from it directly with no other load (during a
scheduled downtime) and it still takes 15 mins plus to save/publish.

Running performance monitor doesn't show any particularly large
processor usage on any of the machines.

I'm sorry but I just want to clarify, your initial suggestion was to
install Project Server and SQL together on a single machine. Do you
still recommend this?

I am beginning to think that this is not solely a hardware issue, but a
limitation in Project Server itself. Some inefficiency in the say
complex projects with large amounts of tasks are saved/published.

According to the Microsoft course notes for Course 2732: Planning,
Deploying, and Managing an Enterprise Management Solution, for a
mid-sized deployment it talks about complex projects with an average of
100 tasks.

To me a complex project averages 1000 tasks, not 100 tasks. Perhaps
projects server just isn't set up to handle these project sizes well.

Just a thought. I would be really interested to hear your comments
Gary.

Regards,
MDWigs

 

[Xfactor]

I think I can shed some light on your performance with regards to
project.

When you are SAVING a Project plan from Project Professional, you are
not dealing with the VWNOTIFY (Views Service)process at all. You are
strictly doing an ODBC connection DIRECTLY with the SQL Server. In
fact, only about 5% of the communication over the network goes to the
Application Server (Assuming the Project Server and SQL Server are
split).

So basically, the user's experience is going to be different with how
that user is connected to the network. (Network Card, network
bandwidth, etc).

Now, I have one question for you. Is your SQL Server on a cluster? If
so, and it is using RAID controllers, this is very very bad. WHen
servers are in "cluster mode", the RAID Controllers have to be set to
write-through caching, which does NOT use cache, but slows down any
writes to the database. Caching is what makes serves quicker, but you
can imagine n a cluster, if it acknowledges a write, the other server
wouldn't be in sync in case of a failure. Long story short, clusters
are very bad performers with clustering on SQL. I worked with a large
client that had opened up a Microsoft Premiere ticket, where Microsoft
had reccomended the architecture, and I tested it without the cluster,
and we had a project save go from 85 seconds to 8 seconds.

Long story short, (Impossible now), make the connection between the SQL
Server and Client as quick as possible or use Terminal Services. On
SQL do not use clustering. Now for VIEWS, the publishing aspect, I
have found that moving the Service ONTO the SQL Server reduces cube
build time and project publish tme by 40%. Again, because there is so
many transactions. The Process is started with "BELOW NORMAL"
priority, so even though it's ON your SQL Server, it will not hamper
the performance that much, and if you think about it, everything is
going back to SQL anyway. Use the 2nd box for Sharepoint connections,
and Project Web Acces load. Hope this all helped!

 

[Gary L. Chefetz [MVP]]

I think XFactor restated this nicely.

 

[MDWigs]

XFactor;

Thanks for your help! The SQL Server being clustered was _exactly_ the
problem. We did an all-nighter and shifted the shared storage over to a
new fibre array which allows write-back caching, now everything is
blistering fast.

Cheers,
MDWigs

 

[MDWigs]

Hi Gary,

No offence intended, but XFactor didn't restate anything you said. He
pointed me in a totally new direction that actually fixed the problem.
As much as I appreciate the effort, your advice only caused me more
confusion.

MDWigs