1 / 16

Resource Advisor for SQL Server: Automating DBMS Performance Prediction and Analysis

The Resource Advisor for SQL Server presents an innovative approach to automating database management system (DBMS) performance prediction. It features live system monitoring, lightweight end-to-end tracing, and automated analysis to assist database administrators in making informed resource upgrade decisions. This tool addresses performance bottlenecks by identifying limiting resources such as memory, disk, and CPU. Adaptive query optimization enhances resource allocation. Initial results indicate significant improvements in throughput and latency, offering a robust platform for future performance optimization strategies.

jamuna
Télécharger la présentation

Resource Advisor for SQL Server: Automating DBMS Performance Prediction and Analysis

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Resource Advisor for SQL ServerAutomating DBMS performance prediction Dushyanth Narayanan, Paul Barham, Eno Thereska, Anastassia Ailamaki

  2. What and why • Live system monitoring • Lightweight, end-to-end tracing • Workload agnostic • Automated analysis • Answering “what-if” questions • Visualization • To aid DB admins • Resource upgrade decisions • Identify limiting resource • Memory, disk, CPU, locks, …

  3. Outline • Instrumentation • Where, how, and how much • Initial Results • “What if” I bought more memory? • Current status • Papers, patents, etc. • Future work • Storage, CPU, locking, … • Adaptive query optimizer

  4. Instrumentation • Resource usage / multiplex points • E.g. buffer touch, transaction start, … • Source-level • Private copy from SQL Server tree • Function call interface • Automatically generated stubs • Minimally invasive • Lightweight, non-blocking ETW events • 189 lines modified in 6 files

  5. Resource models • Buffer manager • page reference trace, allocations • cache simulator • Disk • analytic model: single spindle, random access • disk params, Q length  service time • queue length from throughput, #users • CPU scaling • by clock speed, SPECint, …

  6. Accuracy of “what-if”: throughput

  7. Accuracy of “what-if”: mean latency

  8. Outline • Instrumentation • Where, how, and how much • Initial Results • “What if” I bought more memory? • Current status • Papers, patents, etc. • Future work • Storage, CPU, locking, … • Adaptive query optimizer

  9. Status • Submitted to MASCOTS • Patent filed • “Predicting database system performance” • White paper for SQL Server • Tracing recommendations • Potential tech transfer to Indy • Collaboration with CMU (ongoing)

  10. Future Work • Simulation of transaction control flow • avoid limitations of analytic approach • Storage model [with Thereska, Ganger @ CMU] • random + sequential mix, RAID, … • Locking • what happens as #users increases? • Making commit order deterministic • simulate the performance impact • Resource feedback for query optimizer • Feedback-driven cohort scheduling

  11. Resource Advisor architecture

  12. End-to-end visualization • Detailed, per-request information

  13. Buffer cache model accuracy

  14. Disk model accuracy

  15. Changing the transaction rate

  16. Latency has high variance

More Related