April 2, 2007 at 7:19 am
I am about to build a machine hosting the MS SQL 2005. It will run on a Windows 2003 64bit machine, with RAID5 (15k drives), 4GB RAM, and 1 Quad-core CPU. It's to be a stand-alone SQL server, and it will host multiple databases. One of it will be for Solomon Accounting software for a 70-person company (I believe this may be the biggest one of all).
Any thoughts on how to configure it to get the best performance? Any comments and suggestions will be appreciated.
April 3, 2007 at 8:42 am
1) I strongly recommend hiring a local sql server expert for a few hours to evaluate your server, data, access patterns, etc and help out with the initial configuration. It should be money well spent.
2) Without knowing any more than the VERY little information at hand, I would say try to avoid having your OS, application installs, OS page file, tempdb and database log files on raid5. It offers poor write performance. Use mirrored drives instead for those. Set sql server's max memory to between 3 and 3.5 gb, assuming NO other stuff runs on the box (which you should not do if at all possible). Consider setting maxdop to 1, or cost for parallelism to 50 or so to minimize parallel queries. With one CPU sharing L2/3 cache among 4 cores, parallel stuff could reduce performance instead of improve it. Set up good routine index maintenance. Set up AND TEST your backups.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 9, 2007 at 2:17 pm
i would at the minimum have a RAID1 volume for the OS, another one for the mdf/ndf files and another for the ldf files.
we have a server where we dumped a bunch of databases and logs on one big RAID5 volume and it works pretty good, but it will depend on your configuration and the amount of work done.
for a 70 person company and accounting software i think it's OK. the above is around 500GB worth of databases and replication pushing around 1000000 commands a day
April 10, 2007 at 6:54 am
There isn't a single answer to a question like this. I'd start by looking over the best practices as listed by Microsoft here: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/default.mspx
Specifically some information on storage: http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
You can't go too wrong accepting the defaults from the install, at least initially. Set up some kind of performance monitoring, index maintenance, dbcc & backups.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply