Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Get In a Line Expand / Collapse
Author
Message
Posted Monday, February 1, 2010 1:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:57 AM
Points: 33,268, Visits: 15,438
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
Post #856976
Posted Monday, February 1, 2010 2:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:54 AM
Points: 97, Visits: 96
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?
Post #857000
Posted Monday, February 1, 2010 4:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:03 PM
Points: 1,335, Visits: 3,069
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. ..."
Post #857058
Posted Monday, February 1, 2010 5:33 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:35 AM
Points: 5,992, Visits: 12,940
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.


---------------------------------------------------------------------

Post #857079
Posted Monday, February 1, 2010 5:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:54 AM
Points: 97, Visits: 96
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
Post #857085
Posted Monday, February 1, 2010 6:05 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:35 AM
Points: 5,992, Visits: 12,940
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.


---------------------------------------------------------------------

Post #857096
Posted Monday, February 1, 2010 7:23 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 1:19 PM
Points: 471, Visits: 841
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
Post #857168
Posted Monday, February 1, 2010 7:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:54 AM
Points: 97, Visits: 96
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
Post #857171
Posted Monday, February 1, 2010 10:26 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 15, 2014 11:09 AM
Points: 869, Visits: 2,399
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).

Post #857383
Posted Monday, February 1, 2010 10:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:54 AM
Points: 97, Visits: 96
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.
Post #857409
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse