Advice on middleware products for TRUE Scaling out of SQL Server

[IanIpp]

We have a 3 month old quad processor/dual core server running SQL
Server 2005 and already it is getting close to hitting the CPU wall.
An 8 way CPU box is prohibitively expensive and out of the question. I

am looking desperately for a way to TRULY scale out SQL server...in the

same way that IIS can be scaled out via App Center.

The "in the box" solution for SQL Server 2005 scaling out is the DMV.
Unfortunately this solution makes the system less available rather than

more (one server outage takes them all out for that table) and requires

serious rearchitecting of the software to use. Contrast this to IIS
and AppCenter where each added server makes the system more available,
and requires no rearchitecting to work.

Before someone says "what you want can't be done in a
database"...Ora cle has an application server middleware product that
lets you do both of the above. Just plug a new server with Oracle on
it, and you've doubled your capacity. But SQL Server 2005 doesn't yet
have a similar capability.

So I read with great interest the following article that talks about
why this is the case with SQL Server. There are two issues that make
it very difficult to do:
http://www.sql-server-performance.co...bility_availab...
You can create a crude pool using replication, but the performance
times look horrendous.

However, the article also talks about the latest developments in this
field...specifi cally MIDDLEWARE that can create a scale out solution
that is more available and that requires simply adding new servers to
scale up.

I found two companies which seem to offer this new capability:
http://www.metaverse.cc/newsevents.asp?cid=17999
and
http://www.pcticorp.com/product.aspx

Both companies appear to have patents or a patent pending on the
process. I tried to contact metaverse but got no reply, despite their
recent press release. I just emailed Pcticorp today to see if I could
learn more about their product.

My question for this group is:
Does anyone have experience with either of the two products (or any
others that provide this capability)?

Many thanks in advance for your help.

Ian Ippolito
http://www.rentacoder.com

[Greg D. Moore \(Strider\)]

"IanIpp" <ia**********@g mail.com> wrote in message
news:11******** **************@ z34g2000cwc.goo glegroups.com.. .

We have a 3 month old quad processor/dual core server running SQL
Server 2005 and already it is getting close to hitting the CPU wall.
An 8 way CPU box is prohibitively expensive and out of the question. I

am looking desperately for a way to TRULY scale out SQL server...in the

same way that IIS can be scaled out via App Center.

Well the first thing I would say is make damn sure it's not a code issue.

To relate a story, we had two boxes maxing out and were ready to buy a 3rd
in order handle the load.

After reading a white paper on performance tuning, I was able to work with
our developers to rewrite a single stored procedure and get to the point
where ONE box was handling the entire load and still had room to scale.

Ok, that's an extreme case (the boxes were basically doing only the one
thing) but it can show how much a difference simple tuning can make.

Ok, assuming that you've done that, if you can break any of the stuff into
read-only queries, one thing that might work is setup the current server as
a "publishing " server and use replication to push the data to "read-only"
servers.
The "in the box" solution for SQL Server 2005 scaling out is the DMV.
DMV, I'm not familiar with that acronym.
Unfortunately this solution makes the system less available rather than
more (one server outage takes them all out for that table) and requires
serious rearchitecting of the software to use. Contrast this to IIS
and AppCenter where each added server makes the system more available,
and requires no rearchitecting to work.

Before someone says "what you want can't be done in a
database"...Ora cle has an application server middleware product that
lets you do both of the above. Just plug a new server with Oracle on
it, and you've doubled your capacity. But SQL Server 2005 doesn't yet
have a similar capability.

So I read with great interest the following article that talks about
why this is the case with SQL Server. There are two issues that make
it very difficult to do:
http://www.sql-server-performance.co...bility_availab...
You can create a crude pool using replication, but the performance
times look horrendous.
Not necessarily. We do fine with it.

However, the article also talks about the latest developments in this
field...specifi cally MIDDLEWARE that can create a scale out solution
that is more available and that requires simply adding new servers to
scale up.

I found two companies which seem to offer this new capability:
http://www.metaverse.cc/newsevents.asp?cid=17999
and
http://www.pcticorp.com/product.aspx

Both companies appear to have patents or a patent pending on the
process. I tried to contact metaverse but got no reply, despite their
recent press release. I just emailed Pcticorp today to see if I could
learn more about their product.

My question for this group is:
Does anyone have experience with either of the two products (or any
others that provide this capability)?

That I can't say much on. Sorry.

Many thanks in advance for your help.

Ian Ippolito
http://www.rentacoder.com

[1492a2001]

IanIpp wrote:

We have a 3 month old quad processor/dual core server running SQL
Server 2005 and already it is getting close to hitting the CPU wall.
An 8 way CPU box is prohibitively expensive and out of the question. I

HP ProLiant DL585-G1 128GB/2.4GHz/DC/4P
Availability Date 11/08/05
TPC-C Throughput 202,551
http://www.tpc.org/tpcc/results/tpcc...p?id=105100101

Are you doing more than 100,000 transactions/minute in an OLTP system
and can't pay an 8 way machine?

Then I guess your problem is other, not with SQL2K5.

[Brad]

Is your problem during ETL or Query processing?

[Erland Sommarskog]

Greg D. Moore (Strider) (mo************ ****@greenms.co m) writes:

Well the first thing I would say is make damn sure it's not a code issue.

To relate a story, we had two boxes maxing out and were ready to buy a 3rd
in order handle the load.

After reading a white paper on performance tuning, I was able to work with
our developers to rewrite a single stored procedure and get to the point
where ONE box was handling the entire load and still had room to scale.

Ok, that's an extreme case (the boxes were basically doing only the one
thing) but it can show how much a difference simple tuning can make.

Ok, assuming that you've done that, if you can break any of the stuff
into read-only queries, one thing that might work is setup the current
server as a "publishing " server and use replication to push the data to
"read-only" servers.

To be blunt, I think Ian has a lot of potential here. Provided of course,
that he has control over the code. If he has a some sleazy third-party
app, tuning may not be that much of an option. Then again, SQL 2005
offers plan guides where you can give hints or complete plans to queries
without direct access to the source code. And he can still add indexes.

The "in the box" solution for SQL Server 2005 scaling out is the DMV.

DMV, I'm not familiar with that acronym.

Dynamic Management Views, the new interface to engine-state information
in SQL 2005.

I guess that Ian was thinking of DPV, Distributed Partioned Views.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Greg D. Moore \(Strider\)]

"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1...

Greg D. Moore (Strider) (mo************ ****@greenms.co m) writes:

Ok, assuming that you've done that, if you can break any of the stuff
into read-only queries, one thing that might work is setup the current
server as a "publishing " server and use replication to push the data to
"read-only" servers.
To be blunt, I think Ian has a lot of potential here. Provided of course,
that he has control over the code. If he has a some sleazy third-party
app, tuning may not be that much of an option. Then again, SQL 2005
offers plan guides where you can give hints or complete plans to queries
without direct access to the source code. And he can still add indexes.

True, I was just giving him the benefit of the doubt. :-)

The "in the box" solution for SQL Server 2005 scaling out is the DMV.
DMV, I'm not familiar with that acronym.

Dynamic Management Views, the new interface to engine-state information
in SQL 2005.

I guess that Ian was thinking of DPV, Distributed Partioned Views.

That makes a LOT more sense. :-)


--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[IanIpp]

All,

Thanks for the replies.

1) Sorry...yes I did mean DPV instead of DMV! The distributed
partioned views is a way to create an updatable copy of data on
multiple databases in SQL 2005 (which MSFT calls a federation). It
sounds good in concept until you realize that the outage of any one
federated database kills access to the table on all of them. So rather
than scaling and becoming MORE available...thi s solution scales at the
expense of less availability. On the other hand, the middleware
products I quoted above promise BOTH scaling and availability.

2) One other thing..."scalin g up" (adding more CPUs) has a less than
linear effect on performance becuase switching and other things hurt
performance. In other words...if you go from a 2 cpu machine to a 4 cpu
machine...you won't get double the performance (some source quote the
last CPU as only increasing 15-17% rather than 50%). But "scaling out"
(adding more servers) with a middleware product (if it truly works)
promises a linear increase in performance. So it would not only be
cheaper but better...and that is always what everyone is looking for.
That is why I'm hoping that someone here has experience with such
products and can comment on them.

3) Regarding tuning queries,etc.

Yes, we have control over the code but we already run
extensive/constant query tuning and add/adjust indexes and regularly
use the Database Tuning Advisor (see my post here for some of the
existing bugs I've found in SQL 2005's DTA:
http://rentacoder.com/CS/blogs/real_...03/17/447.aspx
). We also update statistics and defrag the indices (and rebuild the
ones that can't be defragged). There are 2 bugs I have open tickets on
with indices not being defragged even after rebuilding...an d not on
small tables, but large ones with thousands of pages of data. I'll
update my blog once MSFT gives more information on what is going on.

But if you are growing, tuning, defragging indices, etc. can only get
you so far. Eventually you WILL run into the limitations of your
hardware. Guaranteed. So it's not a true solution...it just delays
the inevitable.

Regarding:

HP ProLiant DL585-G1 128GB/2.4GHz/DC/4P

Availability Date 11/08/05
TPC-C Throughput 202,551
http://www.tpc.org/tpcc/results/tpcc...p?id=105100101
Are you doing more than 100,000 transactions/minute in an OLTP system
and can't pay an 8 way machine? Then I guess your problem is other, not
with SQL2K5.

I looked at this result and was encouraged for a minute that perhaps we
might be able to make better use of the hardware "somehow". But then I
looked deeper. I have to wonder how applicable these #s are to "real
life". Maybe I'm offbase, but our machine is the top of the line Dell
quad processor/dual core model..and comes in at about $35k (just the
machine...no software licenses). The machine in this contest was
priced at half a million dollars ($500,000)! What are they running
this thing on? My understanding is that Microsoft devotes an entire
team to doing exotic things to the hardware that companies without PHDs
in computer engineering and system design cannot do. I have also heard
(but don't know if it's true) that they add features to SQL Server
after receiving their workload to make it perform well...and if so then
this is something else that no one else can do. If I'm wrong about
any of this, someone please correct me. If I'm essentially right, then
it's not reasoanble to expect these rates.

So in that vein...I'd be interested to hear about anyone with "real
life" implementations and the TPS they are achieving? First, what is
the best way to measure TPS? I found the perf mon counter
"batches/sec"...is that what others use? If so, then we are at about
6,000-8,000/minute on a 64 bit quad processor/dual core machine and
currently at 70-80% CPU capacity. This is far below the 202,000/minute
of the TPC benchmark. What do other people get on this stat?

4) Does anyone have experience with a read-only database in a real life
situation? In reading some papers it seems that using replication to
do this will severely slow down your inserts and updates (one quoted
50-80%). That isn't a realistic solution. Another possible solution
is mirroring and using a snapshot, but if you do this, Microsoft won't
support your database anymore (also not realistic). Maybe log shipping
is the best way...what is your real life experience?

5) Does anyone have experience using these middleware products which
promise better performance and price than traditional scaling up and
more availability than traditional "scaling out" via DPVs or a read
only database?

Ian Ippolito
www.RentACoder.com

[Stu]

Hey Ian,

Not sure if this will help you or not, but I'm interested in
"real-world" statistics as well. Using your batch requests/sec metric,
we're getting anywhere from 21,000 -30,000 batches per minute on a
32-bit dual XEON machine, with 8 gig of RAM. We run a mixture of both
OLTP and OLAP databases on that box.

HTH,
Stu

[Brad]

Lan,
Just curious, is this a transactional system or data warehouse or both?
We have extensive experience with scaling out SQL Server, but only
from a data warehousing (ETL and Reporting) perspective and not a
transactional perspective.
Thanks,
Brad

[IanIpp]

Thanks Stu...that is interesting. We're getting less per minute than
you are on twice the # of processors. What percentage of CPU usage
does your box average?

Brad...this is a transactional system (OLTP) database only, with no
data warehousing running on it.

Does anyone else want to comment on their configuration, batches/minute
(or other metric) and CPU usage?

Ian