SQL server 2008 and server spec

wijcc

wijcc

wijcc

Soldato
Joined
10 Aug 2003
Posts
2,693
Location
London
I have a HP proliant ML570 G2 server with dual XEON 3 GHz server and 4 GB of RAM. I have SQL server 2008 running on the machine but when you run certain searches on SQL server the CPU ultilisation maxes out (100% CPU usage). The current server is running win server 2003 32-bit edition.. so I can not increase the RAM, without having to install a 64-bit OS. The ML570 is also used as a DC.

So I am thinking of getting another (cheap-ish) server just to run SQL server. What would be better for running SQL server more RAM or a more powerful CPU?

Thanks in advance guys :)
 
Thanks Stelly..

The DB is one is with GoldMine software, so the DB is maintained and optimised by the tech people from Access/GoldMine, the DB is approx 500 MB in size. So with this size DB what would be a good size amount of RAM?
 
SQL doesn't really use much CPU power as a rule. by far the biggest bottlenecks are RAM and the underlying storage system.

Usually high CPU usage is caused by the front end side of things, usually IIS and alike.

In this case, separating the SQL and front end presentation roles onto separate boxes might be of help.

I'm nto going to bore you with SQL best practice if you don't have a huge amount of control over the DB setup, but if you want to tinker there's a lot of blogs out there from guys deploying million user setups detailing the best way to do things.
Do also bear in mind, just because App vendors "optimised" the system doesn't mean they did anything of use. More often than not i find application install engineers don't know the first thing about optimising databases and generally they blag it by massively over-specing the server requirements at the outset which on smaller scales often hide the config inefficiencies.
 
Limit the amount of memory SQL can use through SSMS, SQL is probably taking all the memory away from AD (bad config mixing SQL + AD).

Also worth monitoring for disk queues. 1GB RAM should be ok if the DB is only 500MB

/edit: Probably worth disabling hyperthreading if it has that enabled.
 
You probably have missing or fragmented indexes and poor execution plans if your CPU usage is that high.

Run a DBCC SHOWCONTIG on your database and look for the "scan density" and "extent scan fragmentation" results for the largest tables. The values should be as close to 100% and 0% as possible, respectively. If they aren't, your need to run a maintenance plan to rebuild the indexes and update the statistics.

I'll provide you with my DBCC SHOWCONTIG script when I'm next in front of my laptop, if you want.
 
Handy tools for SQL Server:

For SQL 2008 R1 you can install the Performance Dashboard (It says 2005 but it works under 2008 as well).

For SQL 2008 R2 you can configure the performance data warehouse.

Both tools can give you a breakdown of which queries are using the most CPU (or indeed I/O :) ). They also both offer suggestions for adding missing indexes.

If you have control over the queries being sent to the SQL server (either you coded the software yourself or can talk to the developers). I've found SQL Sentry to be great. It does a much better breakdown of query plans than sql management studio, and it highlights bottlenecks and costly steps.

That said the CPU problems I've always found the actual problem was with the type of queries being sent to SQL Server, rather than it actually lacking CPU power. Check your software (the application and SQL server) are up to date, and have all the current patches applied.

akakjs
 
You must log in or register to reply here.
Back
Top Bottom