SQL Server Setup and Optimization

[Schlep]

I am a hardware guy setting up a new server for our applications folks and have some questions...anyone?

Server Specs:
Windows Server 2003 Enterprise
SQL Server 2000 Enterprise
Quad 3.0 GHz Xeon Processors
12 GB RAM
Two - Internal RAID 1 Arrays - 68 GB & 136 GB
One - External RAID 5 Array - 1.1 TB (5 x 300 GB)
10 GB - C: partition on first RAID 1 Array (OS)
58 GB - D: partition on first RAID 1 Array (Apps)
136 GB - E: partition on second RAID 1 Array (SQL Logs)
1.1 TB - F: partition on external RAID 5 Array (Data)

Questions:
Pagefile -
-which partition should it go on?
-what size should it be (custom, system managed or none?)
/3GB switch:
-should it be used in this case?

 

[JayKusch]

Are you also the one setting up accounts on this server?


Thanks

J. Kusch

 

[mrdenny]

Page file should go on C or D. Keep is off of E and F. Putting it on E or F will slow down the database.

You should be able to keep the page file fairly small. SQL won't use the page file, and with 12 GB of RAM, you should be able to allocate a max of 10 GB of RAM to the SQL Server, leaving 2 GB for the OS. I'd start the page file at a gig or two, and see how the system responds. If more is needed increasing the page file isn't a big deal.

I would use the /3GB switch for this.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[JayKusch]

First off I would probably set your pagefile on the E: partition since 136Gbs for TLogs is quite large. If you are looking for another boost ... may I suggest you create the SQL Server TempDB on a partition by itself too. Maybe pinch a few GBs from the E: or F: drive.

As for the switch for memory, you are going to have to use the /3GB /PAE in the boot.ini to realize the additional memory SQL Server can use.


Thanks

J. Kusch

 

[Schlep]

Jay and mrdenny,

Thanks for your advice. PAE is already enabled, as is the /3GB switch.

My first instinct was to go with the D: drive (applications), but thought the E: drive (sql logs) might be an option as well since it is a separate RAID 1 array from the OS and Application partitions which share an array.

Since I have differing opinions on this, anyone else want to cast their vote?

 

[JayKusch]

I am not opposing MrDennys' suggestion at all! In fact he correct. I was working w/ what was given. I was not going to suggest using C: in that I feel you should keep only the most necessary OS and apps on that partition so that you do not fill it up for some reason and thus crash the server due to a lack of space.

I also do not know what "apps" you are planning on running on the D: drive though 58GB is quite a bit of space. So if either C: or D: we to be options ... I would choose D:

BUT ... if possible ... create another partition for the swap itself. Yes there will be a number of partitions but if they are being leveraged properly, this should not be a concern.

Thanks

J. Kusch

 

[mrdenny]

So you have my logic for placing the swap file where I did, I'll include it here.

When I look at swap file placement, drive space is obvsoully important. However something that I feel is just as important is physical drive config. The reason that I said either c or d is that they are on the same spindles. Putting the swap file on either c or d will have the same underlying effect on the system, both the c and d drives will slow down, as they are both on the same drives.

With the amount of space you have on d, assuming that you are just putting the binarys for SQL, anti-virus, etc on the D drive, that would be a good place for it. It's on physically seperate spindles from the database files (I'm assuming this will be a high load system, based on the 12 GB of RAM), and there is enough space on D that you can grow it pretty much as large as needed without issue.

As usual, sorry for my crappy spelling.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)