Embedded Files
Moreover, implementing the appropriate RAID level offers data redundancy, fault tolerance, and improved read/write speeds to optimize database storage.
The time it takes for an I/O to complete (How long it takes an operation to finish measured in milliseconds).
Measurement starts when the OS sends a request to the drive (or controller) and ends when the drive finishes processing the request.
Metrics:
< 8ms : excellent
< 12ms : good
< 20ms: fair
> 20ms: poor
Latency is not only measure of performance, SQL Server workload must also be checked as it differs from other server load.
Monitor Performance:
Windows Performance Monitor (PerfMon.exe)
> Physical Disk: Avg. Disk Sec/Transfer
> Physical Disk: Avg. Disk Sec/Read
> Physical Disk: Avg. Disk Sec/Write
Windows Resource Monitor (ResMon.exe)
SQL Server DMV/DMO
Input/Output Operations per Second - is the number of read or write operations per second.
Directly related to latency; constant latency of 1 ms means a drive can process 1,000 I/O per second with queue depth of 1.
IOPS = queue depth / latency
Block size matters - smaller size will allow higher I/Os per second that large block.
Important to OLTP workload where transaction are small and read/write are typically single page and highly random.
Monitor Performance:
Windows Performance Monitor (PerfMon.exe)
> Physical Disk: Disk Transfer/Sec
> Physical Disk: Disk Write/Sec
> Physical Disk: Disk Read/Sec
Windows Resource Monitor (ResMon.exe)
SQL Server DMV/DMO
Measure of data volume over time, usually measured in MB/sec or GB/sec (also known as BANDWIDTH)
Sequential Throughput uses the following:
SQL Server activities.
Full database backups and restore.
Replication synchronization and initialization of HA copies.
Index creation and rebuild.
Data warehouse/reporting large scans
Monitor Performance:
Windows Performance Monitor (PerfMon.exe)
> Physical Disk: Disk Bytes/Sec
> Physical Disk: Disk Write Bytes/Sec
> Physical Disk: Disk Read Bytes/Sec
Windows Resource Monitor (ResMon.exe)
SQL Server DMV/DMO
Redundant Array of Independent/Inexpensive Disk.
This provides a mechanism by which we can bind together as array of disk and expose them as single logical drive known as LUN.
Common RAID levels:
RAID 0 - Stripping
RAID 1 - Mirroring
RAID 5 - Stripping with parity
RAID 6 - Stripping with double-parity
RAID 10 or 1+0 - Stripping over mirrored pairs
This is known as NTFS cluster size.
For SQL Server a 64KB allocation unit size should be used to match the size of a single extent.
This will improve read-ahead performance.
Reduces the number of splits I/Os
Does not allow disk to use NTFS compression
Default size =4KB
useful for file server - optimize for storage of lots of small files
RAID 0 - performance matter more than data loss disk .
Pros:
Fast read & write performance.
Cons:
Total loss of data from single disk failure.
RAID 1 - important data that requires redundancy to protect from disk failures.
Pros:
Mirrored storage protects from single disk loss.
Cons:
Only provides half the storage.
Write performance equals to one disk.
RAID 5- Where reads exceed writes, data files.
Pros:
Maximize available capacity from minimal number of disks with redundancy.
Cons:
Protection from single disk failure only as loss of two disks results in total data loss.
Write penalty for parity calculation.
Significant performance impact occurs when degraded.
RAID 10 or 1+0 - Transaction log files, heavy write data files. And, redundancy is more important than cost.
Pros:
Fast read & write performance.
Support multiple disk features as long as two of the failed disk are not in the same RAID 1 pair.
Cons:
Double the cost of storage.
Other RAID:
RAID 6 - similar to RAID 5 but provides double parity disks to fail and the array remains available.
RAID 50, RAID 100, etc - Specialized RAID configuration that stripe other RAID levels to increase performance.
Is any high performance network whose primary purpose is to enable storage device to communicate with computer systems.
Advantages:
Shared Storage - Increases disk utilization.
> Reduces management by making it easier to create new volume and dynamically allocate storage.
> Create diskless servers that boot from SAN only.
> Mirroring, snapshots, continous data protection, clustering and geo-clustering only offered by SANs
> Improve performance, almost unlimited number of spindles, controllers, and cache can be put together to meet the requirements.
Disadvantages:
Unpredictable performance - when you share disk , controllers, and fiber switches between dozens of servers.
Higher latency - Distance the I/O have to travel (added layer of switches, cabling and ports)
PCI Bus > HBA > FC Switch > FC Ports > Array processor > Disk
Limited bandwidth
> FC now 16Gb/s max
>iSCI 10Gb/s max
> Note: 1 Gb/s = 128MB/s
Expensive Cost
Sources and good reads:
IEPTO2 - SQLSkills immersion event docs (Recommended Training "https://www.sqlskills.com/sql-server-training/ ")
https://www.brentozar.com/sql/sql-server-san-best-practices/
Report abuse