RE: allocation unit size
- From: stcheng@xxxxxxxxxxxxxxxxxxxx (Steven Cheng[MSFT])
- Date: Tue, 12 Sep 2006 06:41:59 GMT
Hello Jeroen,
As for the disk allocation unit size for the SAN system used as SQL Server
disk, here are some of my understanding and suggestion:
Generally, there is no definite allocation unit size which may fit all
kinds of scenarios. As for the data file of SQL Server database, they
consists of database pages which is an 8KB chunk. Also, such chunk is
aligned on 8KB boundaries within the data files, starting at byte-offset 0
in the file. Therefore, normally any disk allocation unit whch is multiple
of the page chunk(8kb) should be ok.
However, in real world scenario, the size of the allocation unit we should
configured for our disk also depend on how our database
client(applications) will perform query on the certain database(since the
SQL Server's storage engine will perform I/O operation according to the
query). For example, if the application will frequently read large block
of data from that database, then the SQL Server storage engine will
certainly frequently perform large block of I/O read access from physical
disk, and thus make the allocaion unit of the disk to a large value will be
appropriate here(such as 64kb = 8kb * 8). Otherwise, if the database will
always perform read access against small block of data, configure the
allocation unit size to a small value is recommended.
Anyway, if convenient, I would recommend you perform a test on your
environment (use some performance counters or other external peformance
tools) to determine what's the exact value for your condition.
Here are some good articles discussing on the SQL Server data strorage
engine:
#SQL Server Storage Engine
https://blogs.msdn.com/sqlserverstorageengine/archive/category/13831.aspx
#Fragmentation (part 1):
https://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/23/644607.aspx
#Fragmentation (part 2): What are pages
http://blogs.msdn.com/sqlserverstorageengine/archive/2006/06/26/647005.aspx
Hope this helps. If there is any other information you wonder ,please feel
free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
.
- Prev by Date: Re: Installing SQL2000 on Server 2003
- Next by Date: Re: Enable to SA account, in hindsight
- Previous by thread: Re: SQL Server 20005 Licensing
- Next by thread: RE: allocation unit size
- Index(es):
Relevant Pages
|
