Managing Query Compilation Memory Consumption to Improve DBMS Throughput

TL;DR

This paper investigates how query compilation memory impacts overall DBMS throughput and proposes a solution in SQL Server 2005 to stabilize performance under high memory demand.

Contribution

It introduces a new aspect of query optimization focusing on compilation memory and presents a practical solution to improve throughput stability.

Findings

Enhanced throughput stability under high memory contention

Effective management of query compilation memory in SQL Server 2005

Demonstrated improvements in diverse workload scenarios

Abstract

While there are known performance trade-offs between database page buffer pool and query execution memory allocation policies, little has been written on the impact of query compilation memory use on overall throughput of the database management system (DBMS). We present a new aspect of the query optimization problem and offer a solution implemented in Microsoft SQL Server 2005. The solution provides stable throughput for a range of workloads even when memory requests outstrip the ability of the hardware to service those requests.