How to gain Disk related performance with few simple steps

Within your SQL Server environment there are few simple steps you can take up in order to gain performance to a level, for instance when configuring a SQL Server that will only contain a few gigabytes (GB) of data and not sustain heavy read nor write activity, it is not as important to be concerned with the subject of disk I/O and balancing of SQL Server I/O activity across hard drives for maximum performance. Still the system resources such as memory & database optimization plays important role to keepup the performance.

Similary for the larger databases (more than 1TB) will definetly sustain heavy read and/or write activity, it is necessary to drive configuration around maximizing SQL Server disk I/O performance by load-balancing across multiple hard drives. Having said that by taking up relevant RAID levels will give you secret of performance too, AA217083 & MS178048 whitepapers explains more information in this regard.

As a best practice it has been proved that if you combine redundant data management with a Backup and Restore procedure to help make sure that you do not lose data if a hardware failure or other disaster occurs. For such example say RAID 0 uses striping technology for faster access whereas RAID 1 uses mirroring technology for data reliability. A common technique used in relational database management involves using RAID 0 and RAID 1 together. RAID 5 uses a single striped disk array with parity bits written together with the data. When any one disk fails, the parity bits can be used to calculate the missing data until you replace the disk.

By now you will agree that Disk I/O tuning is the important factor in gaining performance and no RDBMS platform is exception in this case, by default I/O performance will be dictated by how fast reads and writes of SQL Server data can be processed by the disk I/O subsystem. Simple as it sounds but to implement you must consider it carefully, also for OLTP based database the transaction log is most import and it is better to use a caching controller for SQL Server log files for the highest performance in writing the transactions to disk. So if the controller guarantees that data entrusted to it will be written to disk eventually, even if the power fails.

Also until you carefully understand the application needs in writing and reading the transactional data well avoid trying to over optimize the IO by selectively placing objects on separate disks.  Most of the hardware vendors provide such support to certify the hardware for better performance, if not you can perform a basic throughput testing of the IO subsystem prior to deploying SQL Server. Here SQLIO is one such tool which can be used for this exercise that gives features of basics of testing an IO subsystem information.

Once you have deployed the SQL Server with the application database then you can take help of DMV sys.dm_os_sys_info to get information such as available resources and the resource consumption. Also this SQLBlog post is very helpful in this case.