Disk IO performance , disk block size tuning and SQL Servers

Disk IO performance , disk block size tuning and SQL Servers  is a critical topic. Disk IO happens at the page level . Aligning the database usage pattern with the IO is key to good throughput.

 A page is 8KB in size . Consider a page as the atomic (or fundamental) unit . Multiply 8K by 128 , this  amounts to 1MB. Therefore 128 pages per MB

 In SQL Server extents are the preferred unit of measurement. An extent is defined as 8 physically contiguous pages . A contiguous page refers to data that is stored in a solid uninterrupted block.This amounts to 16 extents per MB. You could also say an extent is 64 KB broken into 8 x 8KB pages

 There are 2 types of extents : uniform and mixed. The main criteria in deciding which type of extent is dependant on whether the object has sufficient data to make of up 8 pages. i.e When the objects has 8 pages the extents are allocated as uniform extents , but if under 8 extents than they are allocated from mixed extents

 For database servers ,throughput and the capacity to scale are key determinants in optimising Disk IO. Access to memory is far quicker than disk access , therefore minimising the number of trips to disk is an objective  . A large block\buffer size would minimise the number of trips.

 Size decisions need a DBA and hardware \disk administrator. 

1) The DBA must prove there is a disk performance bottleneck. There are many methods , one way is : Disk IO performance and SQLIO

2) Justifying the default size settings is difficult , and can have severe consequences if commited incorrectly.

3)Analyse in conjunction with : memory, server consolidation, RAID level overview of configurations,  placement of random and sequential log files

 4)Analyse SQL Server IO patterns and RAID levels