Posted by Hugo Kornelis on 08/07/07 22:52

On Mon, 06 Aug 2007 22:17:50 -0000, Fox1977 wrote:

(snip)
>Can anyone offer any advice on the way forward with this one? I know
>I need to looking into putting in a solution that will scale but it is
>going to go down like a lead ballon after spending 22k 8 months ago.

Hi Fox1977,

How much work has already been done WRT query and index tuning?

If the answer to that question is "very little", then that's where you
need to start. Throwing lots of money at the hardware can yield
performance benefits, but (unless your current hardware is crappy and
your pockets are deep) you should not expect to see improvements beyond,
well, tenfold at most (and two- to threefold is probably a lot more
realistic).

OTOH, with rewriting queries and changing indexing, you can sometimes
see hundred- or thousand-fold improvement in performance (though ten- to
twenty-fold is more common here).

Of course, if the code and indexing have already been optimized, your
return of another optimizing pass will be a lot less. Since you've given
us all the details of your hardware but none of the typical workload,
the development history of your DB, or anything else, you'll have to
make these assessments.

>I am looking at a number of different options:
>short term: what could be done to improve the situation

In a poorly optimized system, there's often a lot of "low hanging
fruit": relatively easy changes to get the first performance gains. If
you have a reasonable understanding of SQL Server performance, you
should be able to identify them fairly quickly. Problem is, if you don't
have adequate understanding of SQL Server performance, you'll have to
hire a consultant for this - but you'll probably have a hard time
identifying the good consultants from the terrible ones.

There are also some quick wins on the hardware side, such as making sure
that data and log files are on seperate hard disks, preferably with
their own controllers; that no other activities take place on the disk
with the log files; that (especially for SQL Server 2005) tempdb has
it's own hard disk; etc. Some googling should help you find these and
similar tips.

>long term: what type of solution could i put in place?

Depends on a lot of things. What works wonders for some would do nothing
(or even hurt performance) for others. How much data is in your
databases? How often does it change? How often is it queries?

> I am looking
>at SQL clustering but it looks very pricey. Do i need the enterprise
>version to do active/active clustering and how many servers does an
>enterprise licence cover me for. I have also though about having some
>kind of virtual cluster of machine with the virtual machine running
>acorss several servers.

My knowledge of clustering is limited. AFAIK, clustering is mainly a
high availability feature, not a high performance feature. I have no
idea if and how an active/active cluster would affect your performance.

I checked some documentation; it appears as if clustering is supported
on standard edition as well as enterprise - but do doublecheck this
before spending any cash on it!

License costs for SQL Server are per processor, not per server. So for a
server with 1 CPU, you need 1 license; for a four-way server, you need
to have four licenses. What counts is the actual number of sockets, not
the number of logical processors or cores - so a server with a dual-core
or quad-code processor can still run on a single-processor license.

I believe that there are some special licensing rules for clusters, but
maybe those apply only to active/passive clusters? I really suggest to
have a chat with an MS sales rep about this.

>Is there any performance gains to be made by upgrading to SQL 2005?

Overall, SQL Server 2005 performs better than SQL Server 2000. But there
are no individual guarantees. Some people have reported an overall lower
performance after upgrading to SQL Server 2005.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Back to original message]