October 12, 2011 at 2:56 pm
Hi Guys
Got a strange one for you.
I have been looking into getting the optimal performance out of disk drives on some of our server. The server has three logical drives Data1, Data2 and Logs. Data1 and Data2 are both in a four disk RAID 1+0 array while the Logs drive is a two disk RAID 1 array. All the arrays have a 512k stripe size.
I have been reading about disk partition alignment and file cluster sizes. Since we are using Windows 2008, the partition alignment is taken care of. From what I read changing the cluster size from the the default which is 4k to 64k.
Before i made the change, i ran some intensive queries to get some benchmarks. These queries basically moved tables from the Primary file group on Data1 to the secondary file group on Data2. The results we as follows:
Query 1 = 2.12mins
Query 2 = 56.34mins
Query 3 = 36.41mins
Query 4 = 4.13mins
These were done on a fresh install and there was no other activity on the server at the time.
I then formatted all the disks with a 64k cluster size, restored the database and then ran the queries again with these results:
Query 1 = 3.44mins
Query 2 = 58.32mins
Query 3 = 36.58mins
Query 4 = 4.38mins
I was expecting to see a noticeable decrease in the time in takes to run the above queries. Since i did not see this i reformatted the drives back to the default cluster size. Restored the databases and ran the queries again to make sure the execution results were the same as the first round of testing. Results:
Query 1 = 6.23mins
Query 2 = 60.11mins
Query 3 = 36.38mins
Query 4 = 4.28mins
Confused by the random results, i restored the database and ran again. Results:
Query 1 = 6.03mins
Query 2 = 58.23mins
Query 3 = 36.38mins
Query 4 = 4.26mins
So now I am pretty confused, first of all as to why i did not see a performance increase and secondly as to why the queries started off being much slower than the benchmark but got closer to the values as i progressed.
Any thoughts/ideas?
Thanks
October 12, 2011 at 6:15 pm
You should have 64K stripe size for SQL Server. You may also test with the stripe size of 256K because it can help increase the performance of the read-aheads.
October 13, 2011 at 1:52 am
From what I read as long as cluster size / stripe size = an integer then all was good.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply