sql server replication: I have a web application coded in dotnet and ms sql server. It is a
multiuser system that lets members search for other members via their
profile fields. It gets complicated in that it lets users search based on
distance from a zip code, as well as degree of separation.

However every time I perform a search I see a spike in the cpu to about 50%
(briefly) and there isn't even that many users (still testing) on the
system. The way things are going I doubt it can handle more than 50
simultaneous searches, if that.

The system has 4GB of ram, is running RAID 5, running dual AMD. I suspect
the database design is probably not optimized, but I cannot tell since I
hired programmers to code it up, and I'm not exactly a database expert.

My question is, other than finding some expert to come in and help me
optimize the code, what other steps are there for me to scale out the server
so that searches can be handled when the userbase grows much larger?
Replication and having searches load balanced? What are some of the popular
and/or easier ways to do this?

As always, it depends on a number of factors. Certainly, a 50% spike is
nothing to worry about with the amount of data that you have. I'd try
generating an amount of data similar to that representative of production
and then retry your test with a load expected for production. If you are
CPU bound, then consider going to a 4 or 8 way box.

That said, there is no substitute for expertise. I'd bring in an expert for
a couple of weeks to look at your design and code, and then render an
opinion as to how to proceed.

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
[quoted text, click to view]
I have a web application coded in dotnet and ms sql server. It is a
multiuser system that lets members search for other members via their
profile fields. It gets complicated in that it lets users search based on
distance from a zip code, as well as degree of separation.

However every time I perform a search I see a spike in the cpu to about 50%
(briefly) and there isn't even that many users (still testing) on the
system. The way things are going I doubt it can handle more than 50
simultaneous searches, if that.

The system has 4GB of ram, is running RAID 5, running dual AMD. I suspect
the database design is probably not optimized, but I cannot tell since I
hired programmers to code it up, and I'm not exactly a database expert.

My question is, other than finding some expert to come in and help me
optimize the code, what other steps are there for me to scale out the server
so that searches can be handled when the userbase grows much larger?
Replication and having searches load balanced? What are some of the popular
and/or easier ways to do this?

Shabam,

If I understand the kind of functionality you are providing,
it's not a simple database search, but instead a geographical
or spatial search. SQL Server, like most database products,
is not really designed to make these kinds of searches efficient.

I don't know how you've implemented what you have now,
and it would be easier to help if you could post the query that
is causing the CPU spike, along with the CREATE statements
for the tables, indexes, functions, procedures, etc., that are used.

A few weeks ago, I posted some thoughts in response to a
related question, and maybe you'll find something useful there:

http://groups.google.co.uk/groups?threadm=uPi5fCz9EHA.3260%40TK2MSFTNGP14.phx.gbl

Steve Kass
Drew University


[quoted text, click to view]