Hi Folks,

Nope this is not a job offer for 64K 🙂

Today I was having a chat with our SQL developer, as we are looking for a good DBA, once of the topics that came up was the B-Tree (Balanced Tree) structure for the SQL Storage engine. I was mentioning that the ideal DBA should at least understand how 64K extents , each containing 8 x 8k pages can cause some issues if your disk partitions are not aligned.

Anyway, we got into a heated debate of a DBA should know some of these principles. I thought to myself if I was going to win this discussion, I would need to pull something that everyone has heard of and apply my defence, and so I did!

If we use a DBCC ShowContig for a table, we will find the first two fields are the number of pages scanned and then the number of extents.

Now theoretically, for a system (Reporting system at that), the fill factor will be high, due to index references being appended to the B-Tree structure, so lets assume that we have such a reporting system that never gets a page split.

Then for a 100% fill factor index system, we should find that the (number of pages / 8 )  = number of extents.

This is of course the starting point to how SQL server detects logical fragmentation, whilst taking the fill factor into account.

Of course all DBA’s should know this, and so I applied it to disk partition alignment, and finally convinced my team mate 🙂 Since a good DBA will alter index fill factors for a reporting, OLAP, Transaction system respectively.

So, what about disk cluster alignments. Well it is important to optimise the disk for the way SQL stores day in 8K pages and 64k extents.

Microsoft discusses this in allot of detail on MSDN and any DBA wanting to optimise the Disk Subsystem, especially if you forked out allot of money for the disks such as a RAID 10 configuration.

So, what is the cluster size of a SQL optimised disk? it should of course be 64K!

Here is an example in which the F: drive is created on disk 3, aligned with an offset of 1,024 KB, and formatted with a file allocation unit (cluster) size of 64 KB.

However, aligning the partition will also be a crucial factor to disk usage. We must start the alignment and a place where we can ensure 64k extent FITS SNUGLY into a 64k cluster and not Overflow into another one.

Command Line Syntax

C:\>diskpart
Microsoft DiskPart version 6.0.6001
Copyright (C) 1999-2007 Microsoft Corporation.
On computer: DevMachine

DISKPART> select disk 3
Disk 3 is now the selected disk.
DISKPART> create partition primary align=1024
DiskPart succeeded in creating the specified partition.
DISKPART> assign letter=F
DiskPart successfully assigned the drive letter or mount point.
DISKPART> format fs=ntfs unit=64K label=”MyFastDisk” nowait

Want to know more?

The article can be read here.

http://msdn.microsoft.com/en-us/library/dd758814.aspx

I hope this will help any DBA’s out there when planning a SQL dedicated disk subsystem and put the companies money in well spent hardware into optimal use.

• Uncategorized