|
|
|
SSChampion
Group: Administrators
Last Login: 2 days ago @ 8:26 PM
Points: 23,166,
Visits: 6,925
|
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Thursday, February 11, 2010 1:43 AM
Points: 25,
Visits: 39
|
|
I've read the articles on partition alignment and seeing the potential gains of 20-30% was quite keen to try them out.
We use a SQL2005 environment over WS2003, however, the original filesystems were installed over W2K and SQL2K so it looked a fair bet our partitions would be out of alignment.
My first step was to test each drive array to see which ones were in and which ones were out of alignment, this was pretty straight forward and as expected most were in need of attention 
Before doing anything we wanted to get a base line so we could measure the improvement and to be honest wave a little flag to the business ... after reading the articles several times along with several blogs it seemed that using a batch file to run various SQLIO tests was a decent way to go.
The information was collated, our test server was then aligned and the tests were run again - the difference was not showing as 20-30% gain  I did the same type of test on other arrays - RAID1 pairs, RAID1+0 sets and the results were inconsistent.
Is there a better way to get reliable performance results? e.g. results that relate to real world gains or losses as our tests don't back up the official Microsoft data so we're left in a quandary over whether it's worth implementing the changes to production systems.
Thoughts?
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, March 18, 2010 7:19 AM
Points: 180,
Visits: 343
|
|
| I have heard mixed results on this myself in the past and the bottom line for 24/7 shops out there is we just can't afford thet kind of down time for production boxes. So, it is not a very realistic solution for most of us...
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 2,395,
Visits: 5,216
|
|
Rob L-566658 (2/1/2010)
The information was collated, our test server was then aligned and the tests were run again - the difference was not showing as 20-30% gain I did the same type of test on other arrays - RAID1 pairs, RAID1+0 sets and the results were inconsistent. Is there a better way to get reliable performance results? e.g. results that relate to real world gains or losses as our tests don't back up the official Microsoft data so we're left in a quandary over whether it's worth implementing the changes to production systems. Thoughts?
Rob, what sort of results did you get? Was it better at all, worse?
I would say using SQLio and SQLioSim is a good way to go, plus just comparing perfmon results before and after. Perfmon would be showing throughput with your actual day to day workload.
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Thursday, February 11, 2010 1:43 AM
Points: 25,
Visits: 39
|
|
That's the problem we had - some results were better some were worse but from server to server the better or worse ones were not the same.
At this stage I tested only on non production systems so that I could test the results outside of working time to ensure my load testing was the only thing running on both the before and the after tests so that the comparison was valid.
For instance this test;
Test 5 - Sequential writing for 360 seconds using 8k blocksize and 8 outstanding I/O requests per thread
This one varied from +10% to -84% in the 4 separate arrays we tried.
We ran 16 separate runs - Random Writing, Sequential Writing, Random Reading & Sequential Reading all using each of 8k, 64k, 128k & 256k blocksizes.
I'm no expert on SQLIO but I do believe the tests were done in a representative manner so was a bit disappointed when the magic wand didn't work 
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 9:53 AM
Points: 2,395,
Visits: 5,216
|
|
If you ever find a magic wand let me know
Main gotcha with SQLio is making sure the file sizes are large enough so it is not all done in cache.
|
|
|
|
|
SSC-Addicted
Group: General Forum Members
Last Login: Thursday, March 11, 2010 8:08 AM
Points: 408,
Visits: 532
|
|
Let me start by saying I cannot claim responsibility for authoring either of these scripts... nor can I tell you who did as I didn't note where I found them initially and now when i search for them again i get so many results of identical entries with no reference to their source...
The document referenced in the editorial has reference to dmdiag -v but in the event you don't have the support tools installed...
**Powershell-found at http://sqlblog.com/blogs/linchi_shea/archive/2008/11/24/finding-disk-partition-offsets-made-easy.aspx**
$a = Get-WmiObject -class Win32_DiskPartition
foreach ($obj in $a)
{if (($obj.StartingOffste / 1024) -ne 64)
{Write-Host $obj.DiskIndex
Write-Host $obj.Index
Write-Host ($obj.StartingOffset / 1024)}}
**vbscript-found at http://technet.microsoft.com/en-us/library/cc966412.aspx**
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\CIMV2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_DiskPartition",,48)
For Each objItem in colItems
Wscript.Echo "Disk: " & objItem.DiskIndex & " Partition: " & objItem.Index & " StartingOffset: " & objItem.StartingOffset/1024 & "KB"
Next
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Thursday, February 11, 2010 1:43 AM
Points: 25,
Visits: 39
|
|
 yeah we tried to recruit Harry Potter as a DBA but we couldn't afford him
Agree about the file size - from one of the blogs I read that, so we used files between 50gb and 70gb to make sure we were truly using io on disk rather than memory cache.
Good point to make though.
Interesting to hear from anybody else that has tested the alignment pre and post figures and how their implementations fared 
|
|
|
|
|
Valued Member
Group: General Forum Members
Last Login: Wednesday, March 17, 2010 11:08 AM
Points: 72,
Visits: 339
|
|
I'm also very interested in seeing results like this.
Was there other activity happening when the SQLIO tests were run? Multiple LUNs per RAIDGROUP, or any other spindle-sharing, can hide this.
Were the SQLIO data files contiguous on disk (external [filesystem level] fragmentation)?
Were the SQLIO data files in the same relative position on the disk (i.e. if one test had the files on the inner tracks [probably near the end of the partition], and another had the files on the outer tracks, the tests were not comparable)?
Benchmarking at this level requires a lot of careful setup; I'd recommend using the same spindles (which you did), short-stroking each configuration as identically as possible, and sized to the size of the test files (i.e. if you have a 146GB spindle, and you're going to test 70GB files, then create a 70GB partition and leave the other 70GB unallocated).
|
|
|
|
|
SSC Rookie
Group: General Forum Members
Last Login: Thursday, February 11, 2010 1:43 AM
Points: 25,
Visits: 39
|
|
Yes, all good advice.
Our testing was the only activity on the server. As the process of changing alignment is destructive the only files left on the disk were the test files we created in SQLIO and each time we ran tests the same size file was used so the comparison should be representative.
From my testing so far, I don't see it is worth the effort in making the same changes on the production systems, however, I am open to retesting using other quantifiable metrics that may prove more consistently that it is worth it.
|
|
|
|
