by Craig S. Mullins
Although storage management can be an afterthought for the DBA it really shouldn’t be. The cost of managing storage can be as much as 10 times higher than the initial cost of acquiring the storage. And the growth rate for disk storage was 37 percent between 1996 and 2007. So storage issues are vitally important and unless managed appropriately it can be very costly. Even so, it is common for storage-
A DBMS must be store data persistently in files or data sets of some sort. Depending on the DBMS, table spaces and index spaces each may require one, or possibly more, files to store the actual data. Some DBMS products combine multiple table spaces into a single O/S file. But there are many other storage-
A responsible DBA with a comprehensive storage strategy will be able to ensure that all databases have sufficient allocation to satisfy business requirements. They will be able to answer questions like “Why is database storage growing when our business is not?” Wasted storage will be minimized and a proactive approach to adding more storage when required will be adopted.
Today’s modern storage architecture uses disk arrays, or RAID (Redundant Array of Independent Disk). An array is the combination of two or more physical disk devices in a single logical device or multiple logical devices. The array is perceived by the system to be a single disk device.
And what about extents? When a file or data set reaches its allocated size the operating system can acquire an additional extent thereby increasing the size of the file. Many believe that modern storage devices render extent management obsolete, but that is not exactly true. For one thing, many of the latest extent management features work only with modern system managed storage and the most up-
Even if all database storage is system managed, extents can impact performance. Elapsed time can increase with multiple extents if there is heavy insert activity. For reads and updates the number of extents should not impact performance. Regardless, you no longer need to continuously monitor extents and clean them up immediately by reorganizing. It is still a good practice to periodically clean up extents, but there are other methods of reducing extents that are quicker and easier than REORG.
In terms of storage best practices, it is a good idea to perform regular and proactive monitoring. Examples of things you should be tracking include: 1) space used by your entire DBMS, individual databases, and of your table spaces and indexes; 2) monitoring the Storage Groups and the associated volumes of a database system or instance; 3) monitoring all underlying data sets and files for all table spaces and indexes including Used, Allocated, Primary and Secondary Quantity, Extents and the Volumes they are on; 4) alerts for Page Sets of table spaces and indexes that reach their maximum size and maximum number of data sets; 5) tracking of image copy backup data sets, including HSM migration; and 6) a way to delete Image copy backup datasets that are no longer needed because of they have been removed from the system catalog or backup management software.
Whenever possible, create alerts to automatically inform you of problems, shortages, and potential errors. Automate remediation tactics so that the alert tells you what happened, as well as what was done to correct the issue. Tools may be able to assist in automating reaction to shortages, potential errors, superfluous data sets, etc.
The better the database system works with the storage systems, the better your database applications will perform. And that is what it is all about, right?
From Database Trends and Applications, June 2013.
© 2013 Craig S. Mullins,