Resource Advisor: Database performance prediction and visualization

The aim of this project is to automate capacity planning for SQL Server 2005 installations, by combining lightweight, low-overhead instrumentation of a live DBMS with analytic and simulation models of its resource usage: CPU, disk, and buffer cache. By changing the resource parameters in the models, we can answer "what-if" questions about changing resources. For example, "What would be the change in throughput and latency if I double the amount of memory.

Research Areas

Database performance prediction and visualization

The aim of this project is to automate capacity planning for SQL Server 2005 installations, by combining lightweight, low-overhead instrumentation of a live DBMS with analytic and simulation models of its resource usage: CPU, disk, and buffer cache. By changing the resource parameters in the models, we can answer "what-if" questions about changing resources. For example, "What would be the change in throughput and latency if I double the amount of memory".

Current Status

We have added instrumentation to a private copy of the SQL Server 2005 source to generate ETW events indicating resource usage: for example buffer page accesses, user-level scheduler context switches, I/O requests and completions. These events are consumed by our prototype Resource Advisor, which can predict the effect on throughput and mean latency by transaction type, of changes either in available buffer memory or transaction rate. Here is a recent research paper about the Resource Advisor, written in collaboration with researchers from Carnegie Mellon University.

Future Work

We are extending the Resource Advisor's capabilities to deal with changes in disk and processor configuration. This will require better models of the storage system and processor performance, and also tracing and modelling of the locking/synchronization behaviour as the number of processors increases. Our trace-based approach gives us a detailed picture of the resource usage of each transaction in the live system. In addition to capacity planning, this could be used for other purposes, for example as feedback to the cost estimates generated by the query optimizer.

Project Members