Thursday, October 3, 2013

MS SQL Server On IBM Storage & More

MS SQL Server On IBM Storage & More

Physical Storage Design Considerations
From IBM:

(a)    The total number of expected disk I/O operations determines the number of disks. The following general rules apply:
1.    The average server-class hard disk can support 100–150 random transactional IOPS, and 30-50 MB per second sequential - logs I/O.
2.    When planning the number of I/Os consider: random and sequential disk I/O, rate of execution, and number of logs generated.

(b)   
1.    A large stripe unit size benefits a sequential workload, and a small stripe size benefits a random workload. When creating arrays, a large stripe unit size benefits a sequential workload, and a small stripe size benefits a random workload. If you have no other information to base your decisions upon, Microsoft recommends a stripe unit size of 64 KB or 256 KB as a starting point. When the expected workload will include tables larger than 100 MB, the 256KB stripe size gives more efficient read-ahead.

2.    Using an entire array to create a single LUN (logical unit number) is the simplest and recommended design. During LUN creation on the arrays, using an entire array to create a single LUN is the recommended design because multiple LUNs created from a single array can interfere with the performance of the other LUNs (which share spindles). One LUN per array also simplifies performance monitoring.
3.    A large controller cache can greatly improve performance.
4.    When you create partitions in Windows, be sure they are aligned properly.

5.    Use MBR volumes. MBR volumes are recommended over GPT volumes.
6.    Use Basic volumes. Basic volumes are recommended instead of dynamic volumes.
7.    Use the Storport driver. The Storport driver is recommended instead of the older SCSIport driver.
8.    Use multipathing software. Use multipathing software to increase HBA (host bus adapter) bandwidth and reliability.
9.    Use Mount Points for scalability and ease of maintenance. SQL Server 2005 supports mount points, which reduce the number of drive letters required with large multi-node clusters.

From MS:

1.    Always use Page Checksum to audit data integrity.
2.    Consider using compression for read-only file groups for higher storage efficiency.
3.    Use NTFS for security and availability
4.    Use instant file initialization for performance optimization.
5.    Use manual file growth database options.
6.    Use partitioning (available in Enterprise Edition) for better database manageability.
7.    Storage-align indexes with their respective base tables for easier and faster maintenance.
8.    Storage-align commonly joined tables for faster joins and better maintenance.
9.    Choose your RAID level carefully
10.    For optimized I/O parallelism, use 64 KB or 256 KB stripe size.
11.    For future scalability and easy of maintenance, use volume mount points.
12.    To increase bus bandwidth reliability, use multipathing software.
13.    For small servers with less than three disks performing mostly sequential I/O, or servers with approximately eight disks performing random I/O, PCI is sufficient. However, PCI-X is recommended and can service a wider range of servers with varying workload size.
14.    Directly attached I/O is recommended for small- to medium-sized servers. SAN systems are recommended for larger servers.
15.    NAS systems are not recommended. Use iSCSI instead.
16.    For better recoverability, use a SCSI interface instead of SATA and IDE.
17.    For larger server loads, use SCSI or SATA with TCQ support.
18.    Store transaction logs separate from data files. Do not stripe on the same disk as the data files.
19.    For large bandwidth demands on the I/O bus, use a different bus for the transaction log files.
20.    The number of data files within a single file-group should equal to the number of CPU cores.

Tools/ Techniques:
1. Gather IO statistics down to the SQL Server database file level: sys.fn_virtualfilestats (Transact-SQL) - Returns I/O statistics for database files, including log files. In SQL Server, this information is also available from the sys.dm_io_virtual_file_stats dynamic management view.
SQL 2005, 2008, 2008R2, 2012

SELECT * FROM fn_virtualfilestats(NULL,NULL);
--or you can use this DMV
SELECT * FROM sys.dm_io_virtual_file_stats(NULL,NULL);

SQL 2000

SELECT * FROM :: fn_virtualfilestats(-1, -1)


2. Identify SQL Server CPU Bottlenecks: sys.dm_os_performance_counters (Transact-SQL):  SQL Server 2012 : Returns a row per performance counter maintained by the server.
2. Overcoming storage speed limitations with Memory-Optimized Tables: Memory Optimized File Group for SQL Server
3. use the DBCC MEMORYSTATUS command to check for any abnormal memory buffer distribution inside SQL Server.
4. use the built-in windows utility "logman" that comes free. Logman is the command line utility for Perfmon. Thru logman, it is easy to automate perfmon data collection which I will show you below
Annexture:
1. counters for RAID configurations with the following calculations:
Raid 0: I/O per disk = (reads + writes) / number of disks
Raid 1: I/O per disk = [reads + (writes*2)] / 2
Raid 5: I/O per disk = [reads + (writes*4)] / number of disks
Raid 10: I/O per disk = [reads + (writes*2)] / number of disks
Here is an example of your I/O per disk for RAID 1, if we get these values from the counters:
Disk Reads/sec = 90
Disk Writes/sec = 75
The formula for I/O on a RAID-1 array is [reads + (writes*2)] / 2 or [90 + (75*2)] / 2 = 120 I/Os per disk

2.Useful Links:
a)  http://www.mssqltips.com/sql-server-tip-category/9/performance-tuning/
b) http://stackoverflow.com/questions/103423/what-are-the-best-sql-server-performance-optimization-techniques

No comments:

Post a Comment