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)
 DBCC fileheader & sector size?

Author  Topic 

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2007-02-05 : 18:18:48
DBCC fileheader ('master')

This shows the sector size on 2005 to be 4096. On 2000 it's 512. I'm a bit confused about all this sector stuff. If I create a new DB it is set to 512, no matter what the allocation unit size for the physical disk is set to. Is there a way to change th sector size of a database? Are there any pros/cons?

I glanced at the 2005 I/O basics article from MS and it was confusing. I'm not sure I follow the sector size between SQL and NT. Is there an article any of you guys know about for this topic that has pictures? lol

tkizer
Almighty SQL Goddess

38200 Posts
Posted - 2007-02-05 : 18:32:36
Why would you want to change the sector size? Why is this even a consideration?

Tara Kizer
Go to Top of Page

MohammedU
Posting Yak Master

145 Posts
Posted - 2007-02-06 : 01:56:53
Sector size returned by DBCC fileheader is NTFS formatted sector size...
Secotr size change what you are looking is not sql version related...

I believe you can change the sector size while formatting the drive...

http://support.microsoft.com/kb/926930


MohammedU
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2007-02-06 : 10:57:28
quote:
Originally posted by tkizer

Why would you want to change the sector size? Why is this even a consideration?

Tara Kizer


I have been messing with changing the allocation unit size on the array and other SAN settings for a server that is not in production yet. The SAN engineer (third party) had suggested that an allocation unit size of 64k would be best for SQL Server. In testing I didnt notice any difference between 4, 8, or 64k. The only change that made a difference was aligning the sectors with diskpart.

One of the other DBAs saw the article for 2005 I/O basics and it mentions that the sector size is a max of 4k and can go down to 521 bytes. There are several things I don't understand.


- What is the difference between the sector size and allocation unit? I had assumed they were the same.

- Why does master and model have a 4k sector size and any new db I create have 512 bytes? They are on the same array; shouldn't they be the same if they are on the same partition and NTFS controls that?



So I wanted to change the value because I wanted to see the impact of the change because I don't know any better.



Thanks.
Go to Top of Page

SQLServerDBA_Dan
Aged Yak Warrior

752 Posts
Posted - 2007-02-06 : 14:08:17
Taken from the format help:

  /A:size         Overrides the default allocation unit size. Default settings
                  are strongly recommended for general use.
                  NTFS supports 512, 1024, 2048, 4096, 8192, 16K, 32K, 64K.
                  FAT supports 512, 1024, 2048, 4096, 8192, 16K, 32K, 64K,
                  (128K, 256K for sector size > 512 bytes).
                  FAT32 supports 512, 1024, 2048, 4096, 8192, 16K, 32K, 64K,
                  (128K, 256K for sector size > 512 bytes).


Sounds to me like NTFS only supports sector sizes of 512; but, it doesn't answer why my master and model are 4096 on our 2005 server.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site