Analyse workload-level index usage with PowerShell and Query Store

Last year I spoke at sqlbits about PlanInspector, a tool I developed to analyse SQL Server execution plans. This session builds on that work by introducing WorkloadInspector, another PowerShell tool that surfaces detailed index usage information across an entire workload. WorkloadInspector will be released as an open-source project.
DBAs are expected to make confident index decisions across many databases, often with limited time and incomplete application context. Traditional index analysis relies on individual plans or DMVs that reset on server restarts or cache eviction.
WorkloadInspector outputs a database-specific, reviewable index change script. This is done by analysing all execution plans stored in Query Store. As a result, it becomes possible to base index analysis on persisted workload data. WorkloadInspector parses and analyses index usage at both the index and column level. This shows in detail how individual index columns participate in read and write operations, including filtering and search conditions. These insights make it possible to distinguish between indexes and index columns that contribute to query performance and those that add overhead.

Attendees will learn to: • understand index-related operations in XML execution plans • analyse workload-level index and column usage using Query Store data • distinguish index structures that contribute to performance from those that add overhead • generate and review database-specific index change scripts