I wish upon a new server ....

Howdy guys.

I have the pleasure of assisting a friend with some changes to his
system. One thing he needs is a new SQL setup that will scale.

I was thinking of a quad exon box, 32 bit win2003 enterprise, 12 gig
ram attached to a SAN via fiber with 15,000 rpm drives.

If I need better perfromance, I will add servers on top of the san ...

One reason for the horsepower is we are dealing with a bad old app that
issues dynamic sql for 75% of its processing.

Thoughts ?

And ahead of time, I know sql server is choking itself at some point in
this as well . . . .

Thanks !!

> One reason for the horsepower is we are dealing with a bad old app that
> issues dynamic sql for 75% of its processing.

Buying hardware to solve a software issue is probably throwing your money
away, especially if you need to continue to scale in future. How about
investing some of that cash to fix the code first, then see what hardware
you need.

--
David Portas
SQL Server MVP
--

<csomberg@dwr.com> wrote in message
news:1129765159.626612.115640@z14g2000cwz.googlegroups.com...
> Howdy guys.
>
> I have the pleasure of assisting a friend with some changes to his
> system. One thing he needs is a new SQL setup that will scale.
>
> I was thinking of a quad exon box, 32 bit win2003 enterprise, 12 gig
> ram attached to a SAN via fiber with 15,000 rpm drives.
>
> If I need better perfromance, I will add servers on top of the san ...
>
> One reason for the horsepower is we are dealing with a bad old app that
> issues dynamic sql for 75% of its processing.
>
> Thoughts ?
>
> And ahead of time, I know sql server is choking itself at some point in
> this as well . . . .
>
> Thanks !!
>

(csomberg@dwr.com)  writes:
> I have the pleasure of assisting a friend with some changes to his
> system. One thing he needs is a new SQL setup that will scale.
>
> I was thinking of a quad exon box, 32 bit win2003 enterprise, 12 gig
> ram attached to a SAN via fiber with 15,000 rpm drives.
>
> If I need better perfromance, I will add servers on top of the san ...
>
> One reason for the horsepower is we are dealing with a bad old app that
> issues dynamic sql for 75% of its processing.

Beware that this app may not work well with that much memory. If the app
generates lots of dynamic SQL, SQL Server saves the plans for all these
queries in cash. If the queries are similar, they end up in the same
hash bucket, which the net result that looking up if there is an already
cached plan for a query, may take a very long time.

If the app is that civiilzed that it uses parameterised queries that uses
sp_executesql, then this is less likely to be an issue.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

We are re-writing the app but that will take 6 months. The additional
reason for the box is new development and replication as well.

So, thoughts on a new box ?

Me.

(csomberg@dwr.com)  writes:
> We are re-writing the app but that will take 6 months. The additional
> reason for the box is new development and replication as well.
>
> So, thoughts on a new box ?

Hardware recommendations in general is not my area. However, I am aware of
the problem of dynamic SQL + lots of memory, which is why I wanted to
alert you.

If you find that the new box behaves unexpectedly poorly, you can keep this
in mind, and rip out some GB of memory until you have fixed the app.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Hardware requirements will vary, but a bad application on very good
hardware will just have more room to underperform.  It's hard giving
advice without knowing more about your scenario:

1.  Are you running an OLAP or OLTP application?
2.  You're planning on developing on the same box?  Bad idea, but
perhaps I'm misunderstanding what you're saying.
3.  How many other databases are you using?  What's the overall amount
of data you're looking at?   How many users?
4.  What's your anticipated growth rate?  How critical is this
database, and how will you manage redundancy?
5.  What's your current bottleneck?  CPU, memory, disk space, network
traffic?

You may want to see if you can pick up an article from SQL Server
magazine by Kevin Kline called Bare Metal Tuning; pretty informative
when it comes to these sorts of questions.

If you want to skip ahead, buy the most expensive box with the biggest
set of hard drives and the most memory you can afford at this time and
the fastest CPU, and then kick yourself hard when the prices drop next
year. :)