May 23, 2008 at 12:36 pm
I have a Dell R900 with 2 Quad core CPU's, 32GB of RAM, and 8 x 73GB 15k rpm HDDs.
The 8 HDDs are in a single RAID10 array.
The OS of the box is Windows 2003 x64 Enterprise.
I have SQL 2005 x64 Enterprise installed.
There are 4 integrated Broadcom gigabit NICs.
I have 2 NICs teamed on my client network.
I have 2 NICs teamed on my iSCSI storage network. I am using Microsoft iSCSI Initiator 2.07.
I have full gigabit switching everywhere.
My SAN consists of 2 Lefthand NSM2120 modules. Each module has 12 SAS 15k rpm spindles in RAID10. The two modules are in the same Lefthand cluster, address by a single virtual IP.
I have SQL installed on the server's local disk.
I have the MDF and LDF files on different volumes of the SAN.
I have SQL running its services with a new domain account.
I have enabled Lock Pages in Memory for that account.
Performance is good, but I want to make sure I have all of the elementary bases covered.
Do I need to check the box "User AWE to allocate memory"?
Do I need to change the "Minimum server memory" and "Maximum server memory" values?
I do not have jumbo frames or flow control enabled on any network segment. After doing some research, it did not seem that these would be a benefit to enable.
Any other SQL or Windows settings that are considered best practice?
May 23, 2008 at 12:48 pm
On x64 you do not need AWE enabled.
I believe you do want to set the Max Memory. Here is a post from this thread that mentions this:
EdVassie
Posted 3/5/2008 9:21 AM
Hopefully you will haveseen this already, but in 64-bit you MUST set the upper memory limit for SQL Server. Otherwise it treats the pagefile space as part of the server memory and you end up with some of SQL memory permanently on disk.
Also, if you are using DTS, any custom components you put in the windows \System32 folder in 32-bit need to go in the \sysWOW64 folder in 64-bit.
Author: SQL Server FineBuild 1-click install and configuration of SQL Server 2005. All information provided is a personal opinion that may not match reality.
Fun: All adults are no more than overgrown kids, but some are too childish to admit it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 23, 2008 at 12:52 pm
Do I set the max memory to 32GB or something less than that? Seeing as how SQL2005 is the only thing this server does, 2GB of RAM is plenty for the OS ( I guess). So 30GB max then?
May 23, 2008 at 1:10 pm
Here are some links from the thread I mentioned earlier:
[url]http://support.microsoft.com/kb/918483">
http://support.microsoft.com/kb/918483
I saw another one on another thread on SSC, but I can't remember where.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 26, 2008 at 12:06 pm
You may want to make multiple data files and spread them accross different luns. Best practice suggest .25 files to every processor.
Also, I would put the tempdb on another lun. SQL 2005 does a lot more with tempdb than any previous version. This will give you better performance for aggregations, groupings, temp tables etc..
Note: you should put the tempdb on a fault tolerant drive. If the drive with tempdb goes down, SQL goes down. For tempdb it is also best practice to create a datafile per each processor.
This link should help:
http://www.microsoft.com/technet/prodtechnol/sql/bestpractice/storage-top-10.mspx
May 27, 2008 at 12:36 am
Turn off Hyper Threading on SQL 2005 host computers as the default.
HT-enabled motherboards show markedly degraded performance under heavy load. Disabling HT restores expected levels, according to reports from within the IT industry.
In some cases when high load is applied SQL Server CPU usage increases significantly but SQL Server performance degrades. Occasionally they would also see message in error log indicating one of the threads can't acquire a spinlock. Customers also noticed that when HT is disabled under the same circumstances CPU usage increases slightly and performance is at appropriate level.
May 27, 2008 at 5:04 am
I'd split up that 8 disk internal RAID 10 into a RAID 1 for the OS and a six disk RAID 10. The six disk RAID 10 I'd dedicate to tempdb. Tempdb is recreated each time the service is restarted so there's no benefit to having it on the external storage. This has the side-benefit of also giving you a dedicated IO channel to tempdb...
If you're OLTP, I'd follow Adam Haines' recommendation for the single tempdb file per processor. If you're running a warehouse I wouldn't. I've never seen the latching problems on tempdb in a warehouse that multiple tempdbs are trying to solve, and managing the tempdb file sizes proactively is really hard - you need to keep them the same but you need autogrow on also. Not worth the headache until you have the problem that multiple files is trying to address.
I would put logs on their own LUN.
May 27, 2008 at 11:30 am
To add to what others have said, even though you set the permission to lock pages, that doesn't automatically lock the memory pages for you. You still have to set it via sp_configure for the 'set working set size' parameter. You're running Enterprise so it's a nonissue for you here, but note this setting has no effect on SQL Standard edition. Std ed. will ignore this setting entirely and let Windows page with SQL.
Here's the script I use to configure memory across my platform. I also leave Windows at least 4GB of RAM. From empirical evidence, the x64 versions of Windows seem to eat up more RAM than the 32-bit flavors. Hope this helps.
-- AWE switch for added performance/stability
sp_configure 'awe enabled', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
-- Max Memory (cap memory so that the OS has 4GB)
sp_configure 'max server memory', '28672'
GO
RECONFIGURE WITH OVERRIDE
GO
-- Min Memory (set this to same value as max memory)
sp_configure 'min server memory', '28672'
GO
RECONFIGURE WITH OVERRIDE
GO
--*****locks memory pages for sql, run this only for Enterprise edition (don't have to do this for SQL Std.)
sp_configure 'set working set size', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply