SQL 2000 performance issue

[Fox1977]

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.

 

[mrdenny]

Yes SQL 2005 does come with performance gains.

SQL Clustering does not add additional CPU power. It adds redundency only.

Using a load ballencer in front of a couple of database won't work either. These aren't web servers where the data is static. They are SQL Servers were you have constently changing data. Setting up SQL Servers to run behind a load ballancer is very difficult to setup and more so to maintain.

You should bring in a database person and have them check out the database, and get it running up to snuff.

Have the indexes been rebuild recently? Have the statistics been updated recently? What does your disk layout look like? If memory serves correctly SQL 2000 Standard doesn't come in a 64bit edition, so the 64bit hardware is only going to do so much for you. thread183-1170932 will give you some info about 32bit vs 64bit SQL.

Unless the SQL Code is very bad, and there is a ton of load on the web sites (like thousands of page views per minute) there's no way that a couple of web sites would take out a decent SQL Server.

Short Term: Find a DBA to take a look at the machine and correct your indexes, and stats. Additional indexes may need to be created. Some DBAs aren't very expensive, some are. Typically you get what you pay for.

Long Term: Have a DBA do a health check on the system every once and a while (monthly or so, plus when ever there is a problem).

Clustering is expensive, as it does require Enterprise Edition as well as shared storage such as a SAN. One Enterprise Edition license covers you for one CPU in one machine. If you have 2 CPUs then you need 2 CPU licenses.

If you are running a web site off of a CAL license (which you probably are based on your cost of £22k, SQL Server 2000 Standard Edition CPU licenses MSRP for ~$5k per CPU) you are not running your SQL Server with the correct license. This FAQ faq962-5153 covers SQL Server licensing in detail.

When licensing for a cluster you have to license any nodes which are active. So if you have an active/active cluster with two CPUs per node then you need four CPU licenses. SQL Server 2000 Enterprise Edition licenses MSRP for ~$20k and SQL Server 2005 Enterprise Edition licenses MSRP for ~$25k.

How many page views are the web sites getting?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Fox1977]

Thanks for the advice,

Much appreciated. I've just done a bit of work this morning adding up the page views per month. The websites running off the back of the databases had 4 million page views in total for July 2007.

 

[mrdenny]

That's and average of 89 per minute. Assuming that the site is only in use 8 hours per day that's still only 267 per minute. Unless your queries are VERY poorly written the SQL Server should never even feel that.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Fox1977]

thanks for the tips. I'm busy working on this one.

Not sure how the sites are setup but I know from speaking to the developers the SQL queries are quite complex and there are a few per page. The sites are tourism sites for a number cities across the UK and involve some pretty detailed accommodation searches.

The first few steps i am going to take is to follow the performance tips over at SQL performance.com. I am updating the server to the latest service pack, increasing the page file to double the ram in the system (8gb - 2gb on C drive and 4 gb on Transaction logs disk - data files are on a seperate disk). I am also in the middle of defragging the drives.

Appreciate the tips. I know the database is pretty big but it isnt too much to ask to expect it to run on a pretty powerfull box. What type of database size/tranactions would you expect to be able to run on a box of that power.

Think the plan is to get some kind of DBA but in the meantime im going to try and score some brownie points and spee things up a bit.

Cheers

 

[mrdenny]

You never actually said how powerful the machine is. But any newer machine should be able to handle hundreds of transactions per second up to thousands of transactions per second without a problem.

Granted that all depends on how complex the query is that you are running.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[gmmastros]

There are lots of performance gains to be had in many queries. While you may not want to re-write/modify the queries, that may be the only way to solve this problem.

Often times, people like to 'throw money' at performance problems. Sometimes it's the right thing to do, but in most cases, optimizing the queries will give you more benefit than hardware.

My suggestions for the queries are...

1. Make sure you have the proper indexes. You don't want to have too many indexes because they can slow down your insert, update, and delete statements. But you need to have your selects be fast. Properly indexing a database is a balancing act.

2. After you have indexed the database, make sure you have sargable where clauses. Hint: google search on 'Sargable Where Clause'.

-George

"the screen with the little boxes in the window." - Moron

 

[Fox1977]

yep george we have certainly had the throw money at it solution! As per normal we have had 6 months of things running fine and now things are creeping back up.

The server is a hp dl 380 with 2 dual core amd opterons, 2.6ghz, 4gb ram, three sep disks for systen, transaction logs and datafiles.

 

[mrdenny]

This machine should be running fine. The database needs to be optomized so that you are getting the most out of your money.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com