Knowing the restrictions of disk performance, how do you increase the performance of your database beyond the physics of your hard disk? There are two methods for performance improvement: 1) Caching, and 2) Higher performance Disk Configuraions.
Caching can be done in many different layers. Firstly, SQL Server itself works in a cache. Increased RAM on the host, made available to SQL Server, not needed for the operating system, increases performance dramatically, regardless of your disk.
For smaller systems, a caching disk controller adds performance. Usually these controllers also enable some sort of RAID configuration. However, the cache itself is an added bonus. Make sure, if you use a caching controller with a database, that the controller has a batter backup for the cache. When SQL Server writes to the disk it considers the transaction complete. If that data was still in the cache and power was interrupted, the database could become corrupt. SQL Server would not know that data was not written completely.
Larger systems have large disk controllers, or even SAN storage. Most SAN systems have a cache on board allowing them to serve multiple clients concurrently. I worked with large data warehouses, hosted on a massive, shared, SAN consisting of 150 disk mashed into a single RAID 5 disk. Metrics captured for our utilization showed that 95% of our disk queries were accessed from cache, resulting in optimum performance.
On the extreme end, you can use caching on the applicatnoi or middle tier sides, employing tools such as memcache. Granted, this doesn’t directly address slow disk performance. However, it lets the application be more responsive, allowing the disks to work at a slower speed.
A lot of disk access libraries have forms of caching enabled as well. Techniques such as lazy loading, or predictive loading, attempt to predict what data your application may request next, and be retrieving it before you ask.Most of the current ORMs have this functionality built in.
Monday we’ll look at some ways to improve the performance of the disks themselves.
Cheers,
Ben