Although storage management can be an afterthought for the DBA, it really shouldn’t be. Storage issues are vitally important and unless managed appropriately, it can be very costly. 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% between 1996 and 2007. Even so, it is common for storage-related issues to be relegated to the backburner by DBAs, but every database professional should understand modern storage basics.
A DBMS must store data persistently in files or datasets 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-related objects you will encounter with database systems, including storage groups for interoperating with the storage subsystem, system-levels files, and datasets (logs, system parameters, etc.), image copy backups (stored on disk or tape), and others such as library datasets and temporary datasets.
Storage-related information is available to DBAs from multiple sources including the System Catalog, statistics, and operating system utilities, but the details are scattered all over the place and it can be difficult to gain a complete, accurate, and up-to-date picture. Furthermore, any historical view into storage usage by the DBMS typically must be managed manually.
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 such as “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 dataset 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-to-date version of the DBMS. For example, consider mainframe DB2. As of z/OS 1.7 system-managed datasets can have up to 123 extents on each of 59 volumes for a total of 7,257 extents. Otherwise, the limit remains 255. Also, extent consolidation, introduced in z/OS 1.5, requires SMS-managed STOGROUPs. When a new extent is adjacent to old, they will be merged together automatically. This can result in some extents being larger than the PRIQTY or SECQTY specification.
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 your table spaces and indexes; 2) monitoring the Storage Groups and the associated volumes of a database system or instance; 3) monitoring all underlying datasets 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 datasets; 5) tracking of image copy backup datasets, including HSM migration; and 6) a way to delete Image copy backup datasets that are no longer needed because 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; and 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 the reaction to issues such as shortages, potential errors, and superfluous datasets.
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?