Hi,

Referencing the Dell SQL Server Reference Architecture whitepaper above.
I just installed a SQL Server box (Dell 2950 with 8 internal 2.5 inch 146k 10k SAS drives) with the following Raid Partitions:

Disk 0 (Drives 1&2) - OS and tempdb - Raid 0
Disk 1(Drives 3&4) - Logs - Raid 1
Disk 2(Drives 5 to 8) - Database - Raid 10

Do I need to use Diskpart on any of the drives and if so which ones.

The command for diskpart is:

Create partition primary align=1024

What number should I use for align?

I want to know where to get the info for the following formula:

((Partition offset) * (Disk sector size)) / (Stripe unit size)

What Partition offset should I use. Also when formatting the drives with NTFS what Allocation unit size should I use.

Thanks

Bunti

Hey guys...Bunti is a customer of mine. Ping me if you need specifics :-)

Good evening,

first fo all, I`m not very experienced with administering SQL databases, but I think I can give more hints to solve your query when you take a look on the hardware options of a DELL PE 2950 with a PERC 6 ( I assume a PERC was offered with the server) :
the diving of OS , Temp Db, Logs and DB is a very good startup, always a good idea to seperate operting system from database applications, the chosen raidlevels are excellent choice, good I/O for reading and writing, the usage of write cache was nearly useless, we performed several I/O tests with raid 1, 10 and 5 with and without activated write cache, on raid 1 or 10 the activated write cache did not significantly raised the I/O, but the read cache might helped to optimize, the READ AHEAD feature gives the system the option to read and hold all blocks on the logical drive, without the used operating system will read the requested blocks, read ahead will buffer the follwing blocks and cache them if needed, one word to the write cache; if using write cache the requests of write or reading commands will be cached on the write cache store and , if server looses power, will be battery backuped on the dimm, the battery holds the contnet of cache for 72 hours.. sounds great huh ? but in seldom cases it might happen, if write cache is activated , the write and read requests are cached on dimm, the server starts up first access the cache then the logical drive, so especially on databases it might happen that the database requests the transaction log but will be feeded with the content of cached commands, this can cause inconsistency, I often advise on that issue and recommend, if possible to write and read directly on logical drives, so my recomend is to activate the read ahead , but disable the write cache on databases, use raid 1 or 10, write cache can be activated and deactivated in perc bios or via open manage software, but this needs fast harddrives like 10 or 15 k SAS harddrives.

..the riadlevel does not need to resetup. I`ve checked several white papers and sql advisors, all of them recommend 64 K blocksize for sql, but I also found some interesting postings where people used 128k or 256k, I assume it is really dependign on DB usage, type of DB and recommendations of vendors like SAP or Oracle. to take the safe way, use 64K blocksize, the blocksize you can setup in the perc raid controller bios when setting up the logical drive and / or adjust or setup in windows, but be advised, changing blocksizes (also known as disk clusters) will result in a reformat, so remind it before changing blocksize. Be also warned that some application cannot read or write on logical drives with smaller blocks like 64 K. a good explanation can be found on http://sqlserver-qa.net/blogs/perftune/archive/2007/10/16/2488.aspx , to find out if you are using a correct offset is to use the tool DISKPAR , this tool comes with windows 2003 and is not compatible on win2k, with the command DISKPAR - i {number of logical drive} you will get a summary , at starting offset you see a value , take this vale and divide it the used blocksize , for example : the value is 32256 , you used a blocksize 0f 4k = 4096 bytes, divide the the 32256 / 4096 = 7,875 , the offset is not optimized, to get an optimized offset the result must be a full value like 8 or 7.
how to optimize the offset ? befor I`ll explain that PLEASE BE aware if doin the Partition will be deleted and recreated !!!!!
with the command DISKPAR -s followed by the numer oflogical disk the partion will be deleted and recreated then the offset witll be optimized. Also refer http://technet.microsoft.com/de-de/library/bb643096(EXCHG.80).aspx first
A correct offset will result in optimized drive access, slow I/O`s can be caused by incorrect settings, the drive needs too long to access the requested blocks and the drive spins , the read/write head needs to be repostioned.

Sry for the 3 postings , I `m definately typing too much , sry for that

I hope these infos help you to optimize your database solution , make also sure to use latest firmware and driver for perc raid controller, please post your result

Best regards from Germany

Marcus

Marcus,
Glad to have you in the techcenter community. Thanks for the info!

--Brian