SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Get In a Line


Get In a Line

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: Administrators
Points: 62062 Visits: 19101
Comments posted to this topic are about the item Get In a Line

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Rob L-566658
Rob L-566658
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 116
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 Ermm 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?
TravisDBA
TravisDBA
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1960 Visits: 3069
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...

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10280 Visits: 13687
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 Ermm 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.

---------------------------------------------------------------------
Rob L-566658
Rob L-566658
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 116
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 ;-)
george sibbald
george sibbald
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10280 Visits: 13687
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.

---------------------------------------------------------------------
BJ Hermsen
BJ Hermsen
SSChasing Mays
SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)SSChasing Mays (603 reputation)

Group: General Forum Members
Points: 603 Visits: 880
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
Rob L-566658
Rob L-566658
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 116
:-D yeah we tried to recruit Harry Potter as a DBA but we couldn't afford him:-P

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 Unsure
Nadrek
Nadrek
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1870 Visits: 2726
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).
Rob L-566658
Rob L-566658
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 116
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search