|
|
Posted by hiddenhippo on 08/07/07 10:31
On Aug 7, 7:11 am, "Jack Vamvas" <DEL_TO_RE...@del.com> 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" <fox...@gmail.com> 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.
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.
Navigation:
[Reply to this message]
|