sp_QueryReproBuilder

sp_QueryReproBuilder – Query Store Reproduction Script Generator

Welcome to the home of sp_QueryReproBuilder, a stored procedure that extracts queries and their parameters from SQL Server Query Store and generates executable reproduction scripts you can run in a new query window.

If you’ve ever needed to reproduce a query performance issue, you know the pain of tracking down the right parameter values, SET options, and execution context. This procedure does all of that for you automatically.

View the full README on GitHub

Jump to a Section:

Overview

sp_QueryReproBuilder digs into Query Store data for a specific database and generates ready-to-run reproduction scripts for any query it finds.

It’s designed to make reproducing query performance issues as easy as clicking on a cell in SSMS. The executable_query column in the output is clickable XML that opens a formatted, executable script.

You can filter queries by plan_id, query_id, procedure name, query text, or a date range to find exactly the query you need to reproduce.

What You Get

For each query, sp_QueryReproBuilder captures and generates:

  • Query text with parameter declarations removed and replaced with actual values
  • Parameter values extracted from the compiled query plan
  • Context settings decoded from binary: ANSI options, language, date format, etc.
  • Embedded constants extracted from plans using parameter embedding optimization (OPTION(RECOMPILE))
  • Warnings about potential reproduction obstacles (temp tables, table variables, encrypted modules, etc.)

For queries with parameters, the script generates EXECUTE sys.sp_executesql statements with proper parameter declarations and values. For queries without parameters, you get the query text with context settings applied.

Parameters

sp_QueryReproBuilder offers the following parameters:

  • @database_name – The database to look at Query Store in (default: current database)
  • @start_date – Begin date of your search, converted to UTC internally (default: last seven days)
  • @end_date – End date of your search, converted to UTC internally (default: current date/time)
  • @include_plan_ids – Comma-separated list of plan ids to search for
  • @include_query_ids – Comma-separated list of query ids to search for
  • @ignore_plan_ids – Comma-separated list of plan ids to ignore
  • @ignore_query_ids – Comma-separated list of query ids to ignore
  • @procedure_schema – Schema of the procedure you’re searching for
  • @procedure_name – Name of the programmable object you’re searching for
  • @query_text_search – Query text to search for; leading and trailing wildcards added automatically
  • @query_text_search_not – Query text to exclude; leading and trailing wildcards added automatically
  • @help – Displays help information (default: 0)
  • @debug – Prints dynamic SQL, statement length, parameter and variable values (default: 0)
  • @version – OUTPUT; for support
  • @version_date – OUTPUT; for support

Usage Examples

Here are some common usage examples for sp_QueryReproBuilder:

-- Basic execution: generate repro scripts for all queries in the last 7 days
EXECUTE dbo.sp_QueryReproBuilder;

-- Generate repro scripts for specific plan IDs
EXECUTE dbo.sp_QueryReproBuilder
    @include_plan_ids = '12345,67890';

-- Generate repro scripts for specific query IDs
EXECUTE dbo.sp_QueryReproBuilder
    @include_query_ids = '100,200,300';

-- Filter by date range
EXECUTE dbo.sp_QueryReproBuilder
    @start_date = '2026-01-01',
    @end_date = '2026-01-31';

-- Search for queries from a specific procedure
EXECUTE dbo.sp_QueryReproBuilder
    @procedure_schema = 'dbo',
    @procedure_name = 'usp_GetCustomerOrders';

-- Search for queries containing specific text
EXECUTE dbo.sp_QueryReproBuilder
    @query_text_search = 'SELECT * FROM Orders';

-- Exclude queries containing specific text
EXECUTE dbo.sp_QueryReproBuilder
    @query_text_search_not = 'temp table';

-- Combine multiple filters
EXECUTE dbo.sp_QueryReproBuilder
    @include_query_ids = '500',
    @start_date = '2026-01-15',
    @end_date = '2026-01-20',
    @query_text_search = 'Products';

Output Details

The procedure returns multiple result sets with everything you need:

  • executable_query – Clickable XML containing the ready-to-run query script. Click on it in SSMS to view the formatted, executable script.
  • embedded_constants – For queries with OPTION(RECOMPILE), shows literal values embedded in the plan
  • parameter_values – The compiled parameter values from the plan
  • query_id / plan_id – Identifiers to correlate back to Query Store
  • query_sql_text – The original query text from Query Store
  • query_plan – The query execution plan (clickable XML)
  • Runtime statistics – Duration, CPU, reads, writes, memory, etc.
  • Wait statistics – Broken down by category

Warnings

Warnings are displayed in the executable_query script header and indicate potential issues with reproduction:

  • temp table – Query uses temporary tables that may need to be created
  • table variable – Query uses table variables that may need to be created
  • parameter embedding optimization – Query uses OPTION(RECOMPILE) with embedded constants
  • parameter count mismatch – Parameter declarations exist but no values found in plan (likely local variables)
  • plan too large for XML parsing – Query plan couldn’t be cast to XML
  • encrypted module – Query is from an encrypted module
  • restricted text – Query text is restricted (contains passwords or sensitive information)

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.