Investigating the effects of SQL hints on diverse storage devices through empirical analysis

Abstract

3-1) With the increasing demand for large-scale data management and analytics, applications have diverse I/O needs, and various storage devices are being adapted for database systems to meet these specific I/O requirements. For example, traditional Hard Disk Drives (HDDs), known for their cost-efficiency and high capacity, are used to store cold data and handle large-scale analytics. In addition, emerging Serial Advanced Technology Attachment (SATA) and Non-Volatile Memory Express (NVMe) Solid-State Drives (SSDs), which offer high performance, are utilized for storing hot data and executing real-time analytical tasks. However, due to the distinct characteristics of these devices, careful consideration of queries, databases, I/O operations, and storage devices is essential to effectively utilize underlying storage devices in database systems. In this paper, we analyze SQL hints, which are directives or suggestions provided to the database optimizer, and examine their implications on I/O performance using two popular databases with various storage devices, including HDD, SATA SSD, and NVMe SSD. Our analysis shows that when SQL hints are not used, databases can fully leverage HDD performance but cannot exploit the full potential of SATA and NVMe SSDs. Notably, the performance with NVMe SSDs can even be worse than that of HDDs in certain situations. By adopting SQL hints, while performance with HDD decreases by up to 77.14% due to the characteristics of the device, the performance of SATA and NVMe SSDs can be improved by up to 83.74% and 92.07%, respectively. These results emphasize the importance of carefully adapting SQL hints by considering the unique characteristics of devices, and with correct adoption, SQL hints can improve the performance of databases.