SQL 2000 performance issues

hi there,

Just wondering if anyone can offer any advice on an a SQL performance
issue i have repeatedly come up against in work. I am a sys admin for
an internet company and we are having problems with SQL eating up too
much of the CPU on a brand new server. I am new to SQL so apologies
is my questions sound a bit simple. I have recently migrated one of
our databases to a new server (2 x CPU, 4gb RAM) and things were
running sweet. Recently we have launched a few new websites running
off the back of the database. The average CPU utilisation is now
hovering at about 50%. The problem is that we have a number of other
sites ready to launch off the database. We are running SQL 2000 on
windows 2003 standard x64 R2.

Obviously the first port of call would be to look at the database/
websites and try and optimise the code in order to improve the
performance but it is proving really difficult to squeeze any
resources out of the company. The directors of the company think that
because they have spent =A322k on a new SQL server they do not have to
put any more effort in. Whilst things have been pretty easy for a few
months as usual everyone has taken their eye off the ball. I can
quite easily see a situation arising in a couple of months were we
have a database that requires more power that the server it is running
on.

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.

I am looking at a number of different options:
short term: what could be done to improve the situation
long term: what type of solution could i put in place? 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.

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

Any tips/ideas/pointers gratefully received.

Re: SQL 2000 performance issues

Another thing you could try , which is inexpensive, is to take a Trace of
the various dbs at their busy periods.
See if you can idebtify the very slow sql statements , e.g over 5000 , and
work to enhance these statements.


--

Jack Vamvas
___________________________________
Need an IT job? http://www.ITjobfeed.com




"Fox1977" wrote in message
news:1186438670.488303.93160@d55g2000hsg.googlegroups.com...
hi there,

Just wondering if anyone can offer any advice on an a SQL performance
issue i have repeatedly come up against in work. I am a sys admin for
an internet company and we are having problems with SQL eating up too
much of the CPU on a brand new server. I am new to SQL so apologies
is my questions sound a bit simple. I have recently migrated one of
our databases to a new server (2 x CPU, 4gb RAM) and things were
running sweet. Recently we have launched a few new websites running
off the back of the database. The average CPU utilisation is now
hovering at about 50%. The problem is that we have a number of other
sites ready to launch off the database. We are running SQL 2000 on
windows 2003 standard x64 R2.

Obviously the first port of call would be to look at the database/
websites and try and optimise the code in order to improve the
performance but it is proving really difficult to squeeze any
resources out of the company. The directors of the company think that
because they have spent £22k on a new SQL server they do not have to
put any more effort in. Whilst things have been pretty easy for a few
months as usual everyone has taken their eye off the ball. I can
quite easily see a situation arising in a couple of months were we
have a database that requires more power that the server it is running
on.

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.

I am looking at a number of different options:
short term: what could be done to improve the situation
long term: what type of solution could i put in place? 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.

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

Any tips/ideas/pointers gratefully received.

Re: SQL 2000 performance issues

On Aug 7, 7:11 am, "Jack Vamvas" wrote:
> Another thing you could try , which is inexpensive, is to take a Trace of
> the various dbs at their busy periods.
> See if you can idebtify the very slow sql statements , e.g over 5000 , and
> work to enhance these statements.
>
> --
>
> Jack Vamvas
> ___________________________________
> Need an IT job? http://www.ITjobfeed.com
>
> "Fox1977" wrote in message
>
> news:1186438670.488303.93160@d55g2000hsg.googlegroups.com...
> hi there,
>
> Just wondering if anyone can offer any advice on an a SQL performance
> issue i have repeatedly come up against in work. I am a sys admin for
> an internet company and we are having problems with SQL eating up too
> much of the CPU on a brand new server. I am new to SQL so apologies
> is my questions sound a bit simple. I have recently migrated one of
> our databases to a new server (2 x CPU, 4gb RAM) and things were
> running sweet. Recently we have launched a few new websites running
> off the back of the database. The average CPU utilisation is now
> hovering at about 50%. The problem is that we have a number of other
> sites ready to launch off the database. We are running SQL 2000 on
> windows 2003 standard x64 R2.
>
> Obviously the first port of call would be to look at the database/
> websites and try and optimise the code in order to improve the
> performance but it is proving really difficult to squeeze any
> resources out of the company. The directors of the company think that
> because they have spent =A322k on a new SQL server they do not have to
> put any more effort in. Whilst things have been pretty easy for a few
> months as usual everyone has taken their eye off the ball. I can
> quite easily see a situation arising in a couple of months were we
> have a database that requires more power that the server it is running
> on.
>
> 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.
>
> I am looking at a number of different options:
> short term: what could be done to improve the situation
> long term: what type of solution could i put in place? 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.
>
> Is there any performance gains to be made by upgrading to SQL 2005?
>
> Any tips/ideas/pointers gratefully received.

I would agree. Your best bet is to the responsibility back to the
programmers. It's so easy to create sloppy SQL statements that
trundle along sequentially scanning database tables; and when you've a
few thousand rows this is going to hit big time. If you then start
joining tables with sequential scans then the CPU is going to be hit
big time. As Jack suggests start tracing the SQL statements/stored
procedures. Take them (sql, stored procs) out of the code and query
analyse them if you want. But personally if I was in your position
then I'd be trying to rule out;

a) that the server hasn't got scheduled tasks such as virus scanning
b) that the disks are all scanned to ensure that they're not corrupt
etc (bad sectors)

and trying to *prove* that the problem is else where, be it with the
programmers or something/one else.

Re: SQL 2000 performance issues

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