Help spec a database server (RAID)

[Sin_Chase]

Once you have decided on your raid research disk alignment and block sizes. I would say these 2 factors alone would be just as, if not more important than the Raid config you use, especially on the smaller scale.

Last SQL server I deployed the Live DBs on a 4 disk Raid-10 and Logs on a single Mirror. Theory being that Database and Logs are active simultaneously and I wanted them on separate spindles and volumes.

Spec was a DL380G5 with 8GB of memory, 2 processors, 4 DISKS in Raid10, and 4 disks in 2 separate Raid-1 configuration for Temp DBs and Logs. But I would say that this would be overkill for your application. I would even go as far as saying dumping your DBs on a Mirror and the Logs on another. Disks aligned with correct block sizes for 25-30 users in a half decently specced machine? Plentifull.

Also, what controller are you planning to run those SAS drives off?

 

[ThomP]

He already suggested 8 disks in his original spec... So going for a decent amount of RAM and a second CPU makes the system futureproof without adding that that much to the final price (you're looking at £200 for the CPU and probably the same again for the RAM).

Alternatively, you can do it a lot lot cheaper if you wanted. We picked up some T105 quad core Opteron tower machines for £150. Stick in some RAM and 4 SATA disks in RAID10 and it would be a very nice little system for <£500.

Hi Daz,

I've just looked at Dells website and for the Dell T105 they only list it having 2x non swapable 3.5" bays? Are you putting two HDs here and then two HDs where DVD/CD-ROM drives go?

I saw the T300 comes with 4x 3.5" bays is this the one you mayb talking about?

 

[kevkbuk]

I take it your using mirroring then. A quick win might be to reindex the whole DB, if this doesnt help then just start a sql profile trace and leave it to catch most of the usage, save it to a table.

If you post a few of the worst offenders I am happy to tell you what indexes you need. Are you currently on SQL server 2005?

It's currently on a software raid mirror running SQL 2000. Thanks for the advice on the profiler. I had already started to use it and have made some progress and identified problem areas. The current plan is to continue with this and see how far we get before buying a new server.

Our problems seem to be some poor query design and lots of temporary tables and not indexing, but thanks for the offer of help, much appreciated.

 

[iaind]

If thats the case I'd concentrate on either getting the software vendor to sort it or sorting it yourself. Reindexing could make the world of difference although the existing server may benefit from some faster HDs and a RAID card (and an OS/SQL upgrade if you feel compelled)

Up until recently, my core DB server (12gb data, Progress) was on a Dual 2.4Ghz single core Xeon, 2gb RAM and 6 x 36gb 10k disks. The quantity of disks was down to Progress effectively using 2 types of transaction logging so it was specced with 3 mirrors. That was still performing absolutely fine and is now whirring away quite happily on VMWare. That server literally caught fire FWIW, possibly one reason not to buy a Dell

However, in my last job (same type of organisation of the same size, so the db system was performing identical functions), the DB was on a Dual dual core 3.0Ghz Xeon with 4Gb RAM and 15k disks. It was incredibly slow, but the server was never stretched for resources; CPU would tick along at minimal usage and memory would be less than 50%.

It was all down to bad DB design (that was Ingres, if anyone cares) - but just goes to show, you might spend all that money on new kit to find it doesnt run any quicker because your limitation is the design of the DB

 

[daz]

I've just looked at Dells website and for the Dell T105 they only list it having 2x non swapable 3.5" bays? Are you putting two HDs here and then two HDs where DVD/CD-ROM drives go?

I'm not using it with 4 disks, i was just suggesting that the OP might be able to. I'm sure he could figure out a way to squeeze an extra couple of disks in there.

 

[oneilldo]

It's currently on a software raid mirror running SQL 2000. Thanks for the advice on the profiler. I had already started to use it and have made some progress and identified problem areas. The current plan is to continue with this and see how far we get before buying a new server.

Our problems seem to be some poor query design and lots of temporary tables and not indexing, but thanks for the offer of help, much appreciated.

You'll not need a new server if you can change the queries and underlying db design. A quick win would be to reindex the entire DB. If its not accessed overnight then you could just start it going (before doing this it might be best to check fill factor - see bottom point). Unfortunately it will lock the table in 2000, but if its not used overnight then go for it. I can't imagine it taking more than 30mins for the entire DB. Alternatively you could just do the worst offenders (as shown in profiler or the largest). If your db is in full mode ensure you backup trans logs regually as a reindex of a xgb table results in xgb of transaction log usage.

Few more hints for you. Make sure you keep the tables as narrow as poss i.e. if the field can only contain a 1 or a 0 then use a bit, if its 0-255 then use tinyint. if its character field that is always 10 chars in length then use char(10) instead of varchar etc etc. Make sure the memory settings are ok and the server isnt paging. if you have 4GB of ram then you can use the /3gb switch which will allow sql server to use 3gb and the kernel 1gb. Check to see what degree of parrelism is set (not sure if your current server is multi proc or not). Make sure that the temp tables you create only have the neccesary columns in and make sure the DB you create these in (assuming it isnt the tempdb) doesnt have loads of dormant temp tables hanging around.
Finally auto grow is painful so make sure all your DB's have sufficient free space (including transacton logs). If the DB has grown repeatedly (sometimes its set to grow by 1% a time) over time then the data files themselves could be fairly fragmented.

Only retrieve the fields you need (can then more easily utilise covering indexes) and only update those fields that have changed (avoids updating indexes when not needed). Finally check your fill factor for your indexes. If you can reindex frequently then a fill factor of >=90% will be best for a DB that is mainly reads. In simple terms if you have two identical tables, one has 40% fill factor and the other 80%. The 80% fill factor table will be twice as fast to read from.