SS2k5 EE: SAN, Allocation Units, and all that jazz

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SS2k5 EE: SAN, Allocation Units, and all that jazz

Author

Topic

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2007-02-02 : 16:10:56

I've been doing some testing on the SAN/Disk subsystem over the past few days. I've run into something that is bugging me though. Hopefully someone here has run into the same thing or can answer.

*All LUNs have been created with default selects.

Test 1:
LUN1 (H:\) - Created partition with diskmanager in win2003.
LUN2 (I:\) - Created partition with diskpart and aligned the sectors at 64k.
Formatted both disks to have 4k in the allocation units.
Created a new blank DB on each disk.
With Profiler running, I ran a bulk insert for both disks and also ran a "Select count(*), Sum(total) from tableA" for both databases and cleared the proc cache inbetween.
Profiler showed the reads and writes io for the import and selects as a the same number (468721 reads and 312182 writes).

Test 2:
Deleted partitions
LUN1 (H:\) - Created partition with diskmanager in win2003.
LUN2 (I:\) - Created partition with diskpart and aligned the sectors at 64k.
Formatted both disks to have 8k in the allocation units.
Created a new blank DB on each disk.
With Profiler running, I ran a bulk insert for both disks and also ran a "Select count(*), Sum(total) from tableA" for both databases and cleared the proc cache inbetween.
Profiler showed the reads and writes io for the import and selects as a the same number (468721 reads and 312182 writes).

Test 3:
Same as test 2 but formatted with 64k allocation units.

So here is my question: Shouldn't the I/O go down or at least change if you change the allocation unit size for the disk?

Thanks,
Daniel

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-02 : 20:10:49

SQL Server transfers data to/from disk in units of extents (8 database pages), so the IO count you are seeing reflects that, not the underlying OS transfers of control intervals.

You should look a the Perfmon Physical Disk IO statistics.

CODO ERGO SUM

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2007-02-05 : 11:28:10

quote:
Originally posted by Michael Valentine Jones

SQL Server transfers data to/from disk in units of extents (8 database pages), so the IO count you are seeing reflects that, not the underlying OS transfers of control intervals.

You should look a the Perfmon Physical Disk IO statistics.

CODO ERGO SUM

/sigh Do you know of a tool that will give the exact number of i/o (reads and writes) for a given time frame? Perfmon is less than ideal cause it only shows i/o per sec.

Thanks.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-05 : 11:48:08

quote:
Originally posted by SQLServerDBA_Dan

quote:
Originally posted by Michael Valentine Jones

SQL Server transfers data to/from disk in units of extents (8 database pages), so the IO count you are seeing reflects that, not the underlying OS transfers of control intervals.

You should look a the Perfmon Physical Disk IO statistics.

CODO ERGO SUM

/sigh Do you know of a tool that will give the exact number of i/o (reads and writes) for a given time frame? Perfmon is less than ideal cause it only shows i/o per sec.

Thanks.

Couldn't you just multiply the IOs per second times the number of seconds in the given time frame?

CODO ERGO SUM

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts

Posted - 2007-02-05 : 11:57:45

quote:
Originally posted by Michael Valentine Jones

quote:
Originally posted by SQLServerDBA_Dan

quote:
Originally posted by Michael Valentine Jones

SQL Server transfers data to/from disk in units of extents (8 database pages), so the IO count you are seeing reflects that, not the underlying OS transfers of control intervals.

You should look a the Perfmon Physical Disk IO statistics.

CODO ERGO SUM

/sigh Do you know of a tool that will give the exact number of i/o (reads and writes) for a given time frame? Perfmon is less than ideal cause it only shows i/o per sec.

Thanks.

Couldn't you just multiply the IOs per second times the number of seconds in the given time frame?

CODO ERGO SUM

Don't think so. I'm not really looking for that anyway.

I just want to know the amount of physical writes/reads it takes to perform a query or bulk insert. It was my understanding that 4k allocation units are a poor choice for SQL Server because it writes/reads in 8k or higher. Knowing that it will write in an 8k unit you would assume that it would take 2 physical writes to write a single page, because the page is split across 2 allocation units. Am I totally wrong with how that works? I'll admit this is new to me and that's why I'm trying to do some testing to see the changes.

Thanks.

*EDIT:
In my most recent tests, I've aligned the sectors on all 3 arrays and formatted array 1 to 4k allocation units and array 2 to 64k. I cannot tell any difference in Perfmon, Profiler, or the physical run time of SQL Server tasks between the two settings.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources