MSSQL Server Configuration

[crmayer]

I am looking for some input on setting up MS SQL Server to try and achieve the best performance possible. This seems to be a common question with a lot of different answers or recommendations. So far we are torn on exactly which way to go.

After hours of searching, I found an article that made this statement:
"Further, read performance is critical to SQL Server performance, write performance is secondary. When a query is executed, the user waits on reads to complete before the system responds with the data that is requested. When modifications are done to the database, the lazy writer will write that data out at a later time, so, although write performance is important, no users ever wait on writes to occur (except to the transaction log)."

I can see there being logic behind this, but is this a true statment?

This is the configuration we are looking at.
DB Server
3 36GB drives RAID 1 for os and mssql
4 73GB drives RAID 5 for transaction log file
6 73GB drives RAID 10 (MD1000 External storage) for data files.

We are seeing that a RAID 10 writes slower then a RAID 5, which raised some concern to us, but if that above statment it true, this might be the best setup?

Then comes the question, when setting up the RAID, what is the best stripe size? Dell documentation tells us 8k, our software vendore tells us 16k, but the default is 64K????

Are the other settings that are might not be looking at that would make a lot of difference to us? Something that might help us?

Any help would be of great help here....

Thanks in advance.

 

[mrdenny]

No it's not a true statement. When updating data you have to wait while the SQL Server finds the records and modifies them.

Here is an FAQ that I wrote on setting up drive configs faq962-5747.

I would stick with the 64k stripe size as the default Windows block size is 64k blocks.

I recommend RAID 1 or RAID 0+1 for the transaction logs and RAID 5 for the data files. Typically data files are 90+% reads so read performance is more important that worrying about the small amount of time taking to calculate parity.

After you check out my FAQ let me know if you have any other questions.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com

 

[crmayer]

Good point, I can see that.

I will check out your post, but I can not believe all the results you get when searching on this saying that RAID 10 is the best for both read and write. We find that the write speed on RAID 10 is about 3 to 4 times slower then RAID 5.

You would stick with 64K even on the database RAID configuration?

Thanks, I will let you know if anything else comes up.

 

[mrdenny]

I've never needed to change the block size before so yeah, I'd leave it at 64k.

RAID 10 can be faster then RAID 5 because it doesn't need to calculate parity. However you are spending a lot of money for that speed. When taking cost into account RAID 5 is the best bang for the buck for a high read system.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com

 

[crmayer]

We already have the equipment for RAID 10, so cost is not an issue.
Dell is coming out, we think there is a hardware problem due to our slow write times to our external storage.

Hopefully we will know more after they are done.

 

[mrdenny]

Have you enabled the write cache on the RAID controllers?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

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

http://www.mrdenny.com