Install SQL Server for best performance

Last Post 19 Mar 2007 11:51 PM by SQLUSA. 10 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
davidlima
New Member
New Member

--
14 Mar 2007 09:31 PM
Install SQL Server for best performance

I currently have a .net application that uses SQL server 2000 svcpk3a as the back end to a custom .net application.

My question is where should I install the SQL Server, SQL Data, and the .net web app to get the best performance.

Meanwhile server 2003 Enterprise, iis, and the .net frame work is installed on the crive.

My server hardware is configured as follows:

HP Proliant 360

(2) Dual Core Xeon processors
8Gb of memory
(6) Hard drives total-
(2) setup as mirrored drives on separate controller that makeup the crive
(4) setup as RAID drives on separate controller that makeup the drive


So with this configuration should I install my sql server application on the crive and place the sql data on the drive.

Also where should I place the actuall web app. On the crive with the ISS and .net installation or on the drive

***Install option 1***
Crive (2) mirrored drives
iis and .net frame work
.net application

Drive (4) RAID drives
SQL Server application
SQL Data

***Install option 2***
Crive (2) mirrored drives
iis and .net frame work
SQL Server application

Drive (4) RAID drives
.net application
SQL Data

Respectfully,

David


davidlima
New Member
New Member

--
16 Mar 2007 09:22 PM
So I understand to achieve best performance when using SQL in a .net frame work the following approach is recommended:

1)Install SQL and IIS on two different servers.
2)Put the log and the data files on two separate drives.
3)Put the tempDB on a third drive.

My question is how do I move them over to different locations?

a- Is it done during the installation?
b- Do I copy and past them to different locations?
c- Is it done by exporting and importing?
d- None of the above. Some other method that I am missing

SQLUSA
New Member
New Member

--
19 Mar 2007 11:51 PM
Typically, you may need one or more webservers as "app servers" connecting to SQL Server on a dedicated server for high performance.


Kalman Toth, DB, DW & BI Architect
SQLUSA: http://www.sqlusa.com/order2005grandprix/
The Best SQL Server 2005 Training in the World


AussiePete
New Member
New Member

--
17 Apr 2007 04:39 PM
I am guessing that your D drive is using RAID 5 (eg 4 x 74GB -> about 207GB formatted space) and not RAID 10 (4 x 74GB -> about 138GB formatted space).

If the D drive is in RAID 5 then you should move the transaction log file (.ldf) for your databas over to the C drive to improve performance (space permitting) as the write performance on 4 disk RAID 5 is poor compared to a mirrored pair.

Write your backups to the D drive.

Typically your .net app and IIS dont hit the disks that much compared to a busy database so where you put them isnt as important. D drive sounds good as you have more space there.
SQLUSA
New Member
New Member

--
17 Apr 2007 05:20 PM
To add to Aussie

tempdb should be on RAID 1 drive E

Kalman Toth, Business Intelligence Architect
SQL Server 2005 Training - http://www.sqlusa.com
akempfDOL
New Member
New Member

--
17 Apr 2007 06:40 PM
or TempDB can also be Raid0

it is a performance vs stability trade-off

if you're running a high-volume database and you know that your disk is never going to fail? then use Raid0 for temptb

but warning-- losing a drive in tempdb would effectively bring your db server down



akempfDOL
New Member
New Member

--
18 Apr 2007 03:44 PM
what I reccomend is this:

(6) Hard drives total-

1 hard drive for logfiles and system (Windows/SQL Server executables)
1 hard drive for tempDb
4 hard drives for a RAID1+0 set... for the _DATA_.

you'll get better performance not using Raid for tempDb and log files.

sorry-- but those are the FACTS

Performance trumps everything else, including availability

CompiledMonkey
New Member
New Member

--
22 May 2007 05:53 PM
I'm in a similar situation right now. We have a RAID 5 setup with 5 73gb drives that came in today. We have a spare drive that I could put in the system to set it up differently if that makes sense. Here's what I'm thinking:

(1 and 2): C Drive for Windows, SQL Server, System Data/Logs, User Logs, Backups
(3 thru 6): D Drive for User Data

Does it make sense to do this instead of running the 5 current drives in RAID 5 and using it for everything?
SQLUSA
New Member
New Member

--
23 May 2007 01:27 AM
A RAID1 performs 2-3 times better in batch writes than RAID5 (same manufactured drives).

That is why preferred in dedicated setup for tran log and tempdb

Kalman Toth, Database Architect
SQL Server 2005 Training - http://www.sqlusa.com
CompiledMonkey
New Member
New Member

--
23 May 2007 05:28 AM
quote:

Originally posted by: rm
CompiledMonkey has a spare disk. If it's same type as others, I prefer to build 2 arrays (raid1 and raid5).


Correct, that was my question.
SQLUSA
New Member
New Member

--
24 May 2007 03:29 AM
So what goes on RAID1?

Tran log? Or tempdb?

Is your app trasaction intensive? Or processing intensive with explicit use of temp tables?

Kalman Toth, DBA
SQL Server Training - http://www.sqlusa.com/bestpractices
You are not authorized to post a reply.

Acceptable Use Policy