Overview
This research investigates the practical effectiveness of large language model (LLM)-driven index tuning within the context of Microsoft SQL Server. The study compares LLM performance against the industry-standard Database Tuning Advisor (DTA) across both industrial benchmarks and real-world enterprise customer workloads.
Research Context
Database indexes are essential for optimizing database performance. The process of index tuning involves selecting appropriate indexes for a specific database workload. Existing state-of-the-art index tuning tools, such as Microsoft SQL Server's DTA, operate by utilizing a "what-if" API. This API estimates the cost of a query for a given index configuration. These tools receive a SQL workload and constraints, such as a storage bound, and subsequently search a large configuration space to identify an index configuration that results in a low optimizer-estimated cost for the given workload.
LLMs present an alternative approach to index tuning, leveraging knowledge acquired from publicly available training data. However, the comparative effectiveness of LLM-driven index tuning relative to established index advisors—particularly in enterprise workload scenarios—has previously remained unclear.
Approach
The study focused on assessing the practical effectiveness of LLM-driven index tuning. This assessment was conducted on Microsoft SQL Server. The evaluation involved both industrial benchmarks and real-world enterprise customer workloads. The performance and recommendations of the LLM-driven approach were directly compared with those of DTA.
Findings
- LLMs, in several instances, identified index configurations that significantly outperformed those found by DTA in terms of execution time.
- Despite these instances of superior execution time performance, LLMs demonstrated high variance in the quality of their index recommendations.
- Index recommendations generated by LLMs were frequently substantially worse than DTA's recommendations when evaluated based on optimizer-estimated cost.
Why This Matters
The discrepancy between LLM performance in execution time versus optimizer-estimated cost presents a challenge for integrating LLMs into cost-based index advisors like DTA. This challenge is rooted in the fact that these traditional advisors rely on cost estimation for their optimization processes. The high variance in LLM recommendation quality also indicates a need for further development to achieve reliable, consistent performance.
Key Limitations Mentioned by Researchers
The research points to areas of future work that may be important for robustly leveraging LLMs for index tuning.