When configuring storage for SQL Server, we need to optimize for three key I/O patterns:

-- Typical SQL Server file types and their characteristics
-- DATA FILES (.mdf/.ndf): Random reads/writes (70% read/30% write)
-- LOG FILES (.ldf): Sequential writes (90% write)
-- TEMPDB: Highly volatile random I/O

For production environments, these RAID configurations have proven effective:

• Data files: RAID 10 (4+ disks) for optimal performance and redundancy
• Transaction logs: RAID 1 (2 disks) for write performance
• TempDB: RAID 0 or SSD (4+ disks) when rebuilds are acceptable

Example PowerShell script to verify disk alignment:

Get-Disk | Where-Object {$_.PartitionStyle -eq "GPT"} | 
Select-Object Number, PartitionStyle, 
@{Name="Alignment";Expression={($_.AllocatedSize % 65536) -eq 0}}

Always separate these components physically:

1. OS (C:\) - System databases (master, msdb)
2. D:\ - Data files (user databases)
3. E:\ - Log files
4. F:\ - TempDB

Sample T-SQL to move system databases:

ALTER DATABASE master MODIFY FILE 
(NAME = 'master', FILENAME = 'D:\SQLSystem\master.mdf');

ALTER DATABASE msdb MODIFY FILE  
(NAME = 'MSDBData', FILENAME = 'D:\SQLSystem\msdbdata.mdf');

-- Restart required after master database move

Use this DMV query to monitor file latency:

SELECT 
    DB_NAME(vfs.database_id) AS database_name,
    mf.physical_name,
    io_stall_read_ms / NULLIF(num_of_reads, 0) AS avg_read_latency,
    io_stall_write_ms / NULLIF(num_of_writes, 0) AS avg_write_latency
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
ORDER BY (io_stall_read_ms + io_stall_write_ms) DESC;

Before designing storage layouts, it's crucial to understand SQL Server's I/O characteristics. Data files (.mdf/.ndf) experience random reads/writes, while log files (.ldf) have sequential writes. TempDB has unique usage patterns - heavy random I/O for temporary objects and version stores.

Always separate these critical components:

-- Example filegroup configuration
ALTER DATABASE YourDB 
ADD FILEGROUP PRIMARY_FG;
GO

ALTER DATABASE YourDB 
ADD FILE (
    NAME = 'YourDB_Data',
    FILENAME = 'E:\SQLData\YourDB_Data.mdf',
    SIZE = 100GB,
    FILEGROWTH = 10GB
) TO FILEGROUP PRIMARY_FG;
GO

ALTER DATABASE YourDB 
ADD LOG FILE (
    NAME = 'YourDB_Log',
    FILENAME = 'F:\SQLLogs\YourDB_Log.ldf',
    SIZE = 50GB,
    FILEGROWTH = 5GB
);
GO

For enterprise environments, consider these RAID configurations:

• Data files: RAID 10 (4+ disks) for optimal performance and redundancy
• Transaction logs: RAID 1 (2 disks) for sequential write performance
• TempDB: RAID 0 or RAID 10 on fastest available storage

Two predominant approaches:

1. Consolidated Storage Pool:

Single large RAID 10 array (8+ disks)
├── Partition 1: Data (E:\)
├── Partition 2: Logs (F:\)
└── Partition 3: TempDB (G:\)

2. Dedicated RAID Groups:

RAID 10 (4 disks) → Data (E:\)
RAID 1 (2 disks) → Logs (F:\)
RAID 0 (2 disks) → TempDB (G:\)

For production systems, move system databases off C: drive:

-- Moving master database example
USE master;
GO
ALTER DATABASE master 
MODIFY FILE (NAME = 'master', 
    FILENAME = 'E:\SystemDBs\master.mdf');
GO

ALTER DATABASE master 
MODIFY FILE (NAME = 'mastlog', 
    FILENAME = 'F:\SystemLogs\mastlog.ldf');
GO

For high-performance systems:

• Consider flash storage for TempDB
• Use multiple TempDB data files (1 per logical processor, up to 8)
• Implement storage tiering for archive data

Key DMVs to monitor I/O performance:

SELECT DB_NAME(vfs.database_id) AS database_name,
    mf.physical_name,
    vfs.*
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN sys.master_files mf ON vfs.database_id = mf.database_id
    AND vfs.file_id = mf.file_id
ORDER BY io_stall DESC;