RAID Level Consideration for SQL Server Data Bases

Two factors you should consider before going to configure RAID level for SQL Server Data Bases are,

• Frequency of data write operation compared to read operation 
• Nature of I/O (Sequential/Random)

RAID 5 /RAID 6 can be chosen for read only Data Bases. Also these RAID configurations are the right choice for data warehouses, or for storing data where write latency doesn't impact overall system performance.

If frequency of write operation is more than read operation, RAID 1+0 (RAID 10) provides the best performance since it doesn't perform parity calculations during write operations. Of course, RAID 1+0 arrays bring a much higher implementation cost, since the available storage is exactly half of the configured disks in the array. So, this RAID configuration is the right choice for OLTP implementations of heavy-write databases.

RAID 1 can be used for the transaction log since it is written to disk sequentially. RAID 1 or RAID 1+0 can be used for the “tempdb” database files since it is a write heavy database.

In addition, the following points need to be considered,

• NTFS format allocation unit sizes should be 64 K (it is 4 K by default) for database data files which perform better than 4K size.
• Place data and log files on separate, dedicated disks, and making special consideration for the “tempdb” database.
• For highly transactional databases, the transaction log for each database should be located on dedicated physical disks to avoid write I/O bottlenecks.
• Since “tempdb” is used for temporary storage only, Solid State Disks can be used to significantly improve the I/O characteristics.

The SQL Server memory leak myth

If you glance at the Processes tab in Windows Task Manager on your DB Server, you can see sqlservr.exe process uses huge amount of memory.  Is that SQL Server has a memory leak? No.

SQL Server is designed to use memory in large quantities, in order to cache information, such as recently used data pages. This improves performance by reducing the need to consistently read data from disk (a much slower and more expensive operation than accessing it from memory).

SQL Server will use as much memory as you can give it, and it will not release the memory that it has allocated under normal operations, unless the operating system has set the memory low resource notification flag, which triggers SQL Server to reduce its memory allocations.

However, SQL Server offers two instance-level memory settings that can be used to control how memory is allocated to, and removed from, the buffer pool. They are,

·     min server memory - specifies the minimum size to which SQL Server can shrink the buffer pool when under memory pressure; it does not specify the minimum amount of memory that SQL Server will initially allocate.

·     max server memory – specifies the maximum amount of memory that SQL Server can use for the buffer pool, which is primarily used for caching data pages in memory.

It is recommended that the max server memory is set appropriately for a SQL instance, based on workload, in order to ensure that the Windows OS still has sufficient available memory, to prevent the OS low memory notifications.

To Change max server memory

Right Click on Instance name in Object Explorer -> Select Properties -> Select memory page in server properties -> Change server memory options.