Subscribe to:
Post Comments (Atom)
Popular Posts
Labels
About Me
Best Practices
Career
Data Mining
Documentation
Feature Requests
Humor
MagicPASS
Meme Monday
Mirroring
Parameter Sniffing
PASS
Performance
PowerShell
Presentations
Query Tuning
Recognition
Replication
Scripts
Security
SQL Power Doc
SQL Server 2005
SQL Server 2008
SQLH2
SQLRally
SQLSaturday
SYDI
T-SQL Tuesday
Tips
Troubleshooting
Updates
VirtualBox
Windows
XML
What I'm Saying On Twitter
Copyright © 2015 Kendal Van Dyke. All rights reserved.
Kendal is a database strategist, community advocate, public speaker, and blogger. A practiced IT professional with over 15 years of SQL Server experience, Kendal excels at disaster recovery, high availability planning/implementation, & debugging/troubleshooting mission critical SQL Server environments. Kendal is a Senior Consultant on the Microsoft Premier Developer Support team and President of MagicPASS, the Orlando, FL based chapter of PASS. Before joining Microsoft, Kendal was a SQL Server/Data Platform MVP from 2011-2016.
[About Kendal]
(https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh9MNPJA4l6-fUucihu7dVXmOnHaSrHG-zuAhn6IXsjHqnZ2RJ4xSd3KNLMkYXnKcrDKORYVXxC1WGsrrt8s5U8k1atkLfg0xnLtZA6RbLt6qhppadMtWFJLRpNgg1zLjyG3qFeokfkc0xs/w139-h140-p/IMG_3503.JPG)
13 comments
Thanks for that. I can imagine that a lot of work has gone into getting these results, but it is going to save the likes of me a lot of guesswork.
Good post. I ran similar tests and came up with the same findings e.g. "optimal configuration is a 128 KB RAID stripe, a 64 KB partition offset, and a 64 KB allocation unit size."
BTW, my tests were done on an old compaq RA1000 DAS and a new HP MSA.
Out of curiousity, has anyone tried a 256K stripe and if so, what offsets were tried? MS mentions in their article "Physical Database Storage Design" --
We recommend a 64 KB or 256 KB stripe size for most workloads. When the workload includes table and index range scans on tables that are larger than 100 MB, a stripe size of 256 KB allows for more efficient read-ahead.
Great post, I´m going to try the same with 256 stripe size and different partition offset.
Thanks for your hard work on this! It really clarifies how I plan to setup my array.
When using a 128k stripe size, wouldn't your partition offset need to be 128k? In the last test I believe your performance with a 128k stripe is being negatively impacted by this.
Will you waste disk space if your allocation unit (cluster) size is 64KB and your stripe is 128KB?
Great information. Now if I can just convince my SA to rebuild our SQL disks...
Isn't the idea with the partition offset to align the partition with the stripe size so that your stripe reads are always aligned? Thus, it makes sense that the 128K stripe size is slower because you didn't do a fair comparison. The 128K stripe size should be fastest with a 128K partition offset. All of the stripe reads are unaligned with a 64K partition offset.
Has anyone done the same tests for a 64-bit MySQL database?
Hi Kendal Van Dyke
read part 2 and 3 and am fascinated about the content the articles are providing.
My question is, why does partition offset increasing result in higher performance (I/O etc.)
Isn't so that the only reason of partition offset is to prevent misalignement.
Kind Regards
Is this still relevant? Take a look at this:
http://technet.microsoft.com/en-us/library/dd758814(v=sql.100).aspx
Win2K8 and Win2K8R2 and probably Win2012 already align to 1024KB for maximal compatibilty with stripe sizes up to 1024K. If anything it'd be interesting to see the same tests done with that alignment to see how it affects things.
Doubtful that the same hardware is still around, years later, just waiting for additional tests, though.
Post a Comment