March 16, 2005 at 9:54 am
Hi All,
I got a dev server with 2* 140GB SCSI RAID 0.
Now from what I read here and a couple other places, the best stripe size to make for SQL is 64k.
Here's where I get confused.
Assuming SQL creates a file at 0 bytes and increases it by 64k each time:
If your strip size is 64k and your cluster size is 64k (As per BOL) is an extent not going to only live on one drive?
Surely if you make your stripe size 32k and your clusters 64K, each extent will be split in two. One per drive.
How does having the stripe size matching the cluster size benifit (or negitive) you?
Am I missing something?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
March 17, 2005 at 12:00 pm
All I can say is: "SQL Reads in 'extents' (64kb) and writes in 'pages' (8kb)". Al the rest is hardware tuning. You might want to perform some I/O benchmarking.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 18, 2005 at 1:39 am
Yes, it reads an extent at a time but how does it write a changed row? Does it only write back that page with the changed data or does it write back the extent.
Busy reading through Inside SQL 2000. Sure I saw it there somewhere.
Will be doing some test with differant stripe sizes and see.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
March 18, 2005 at 3:35 pm
Here's how it works:
1) The before and after image of the changed row are written to the transaction log.
2) The page in memory that contains the row is updated.
3) The database page is updated when either a checkpoint command is issued for the database or the 'lazy writer' process pick up the page out of memory.
It still writes the entire page. What I have omitted for simplicity's is any affects on indexes and triggers.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 23, 2005 at 6:27 am
The theory is that your disk controller attempts to arrange random i/o into sequential i/o for writes thus making the process better, it's all explained in Kalen delany's book inside sql server 2k and in the m/s admin guide ( to pick two books as examples ) Both these would make good reading.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
March 25, 2005 at 9:49 am
64K is how SQL write information onto the disk. With the disks set to 32 K this will cause additional I/O, 2 instead of 1 for reads and writes. Yes SQL used 8 K pages but store everything in 64 K blocks. So to get the best performance set the disk stripe size to 64 K or multiple of 64 k to reduce I/O.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply