Cluster size on SAN.

Question

Cluster size on SAN.

Crispin Proctor

SSCoach

Points: 18161
More actions
December 30, 2003 at 7:12 am

#76104

Addition to my other topic.
The SAN has 4k clusters.
I read in BOL that an extent (Which contains pages) is 64k.
Now, if I am right (Might not be ) then for each extent, the disk will have to do 4 reads. Would this be a problem? Would 64k clusters not be better. Disk would read 64k into memory which would be 8 pages. Would this make more demands on the memory (Chache?)
Would it be worth while to format the drives to suite? Speed increase be worth it?
quote:
Extents are the basic unit in which space is allocated to tables and indexes. An extent is 8 contiguous pages, or 64 KB. This means SQL Server 2000 databases have 16 extents per megabyte.
Cheers,
Crispin
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 1

Crispin -

I don't have numbers to back this up. But I found better performance when I formatted my SAN with 64k clusters. We made this the standard on any system we build.

At another office they experienced outages due to fragmentation when they had the SAN at 4k clusters. Once they reformatted to 64k they quit having the fragmentation problem.

Michelle

Crispin Proctor SSCoach Points: 18161 More actions · Answer 2

quote:
Crispin -
I don't have numbers to back this up. But I found better performance when I formatted my SAN with 64k clusters. We made this the standard on any system we build.
At another office they experienced outages due to fragmentation when they had the SAN at 4k clusters. Once they reformatted to 64k they quit having the fragmentation problem.
Michelle

My maths is almost as bad as writing without spell check

It would have to make 16 reads befor getting all 64k.

It makes sense to me that 64k clusters better. One thing I cannot find is Does SQL load an entire extent into memory to work with a row/page? If so, 64k is definitely the answer. I can see i will make friends when I ask the guys to reformat. 🙂

I just don't want to sit with egg on my face.

You say yours improved. Maybe this is what I need....

Cheers,

Crispin

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

mbaker SSC Eights! Points: 901 More actions · Answer 3

Extents are used to allocate space. Pages are used to do I/O.

Depending upon your application and SAN hardware, you may end up with significantly worse performance using 64K clusters.

I would suggest 8K clusters and reading the SAN hardware documentation to determine what type of caching and read-ahead logic is implemented in the SAN.

Happy New Year!

Mike

Crispin Proctor SSCoach Points: 18161 More actions · Answer 4

Thanks Mike.

Why do you say I might end up with worse performance?

If IO is only done for pages then a 8k cluster would be best(?)

Although, having a 64k cluster would force SQL to keep alot more data in cache,

Which due to my limited memory on the box, is not great.

What would I be looking for when reading up on the hardware?

I also saw in BOL is recommends 64k clusters.

I wish I had a test box to play with all this. Yes, any changes I am making are on production.

Cheers,

Crispin

HAPPY 2K4

Edited by - crappy on 01/01/2004 11:10:51 PM

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!