How can I view the Actual Query Plan from Redgate

Nicholus

Is it possible to view the actual query plan for a query that ran earlier

Jon_Kirkwood

Hi @Nicholus

 

Thank you for reaching out on the Redgate forums regarding your SQL Monitor inquiry around query plans.

By default, estimated query plans are monitored and viewable in SQL Monitor. Setting up actual query plans may require some extra configuration in your estate. 

 

This page here may provide the requested information about setting up SQL Server & PostgreSQL instances to monitor query plans.

https://documentation.red-gate.com/sm13/preparing-to-add-monitored-servers-199100079.html#Preparingtoaddmonitoredservers-Monitoractualplans

 

I've taken an extract from this page on how to set up monitoring for actual query plans. This is available for SQL Server 2019 and onwards

 

Preparing to monitor actual query plans (SQL Server 2019 and above)

Actual query plans are query execution plans that include runtime information.

 

Actual query plan collection can be enabled at the instance level, except for Azure SQL Server, using the trace flag 2451. If set through a T-SQL query this will reset on server restart.

 

For SQL Server on Windows this can be set permanently by adding to the startup parameters using Sql Server Configuration Manager:

 

 

Or at the database level using the LAST_QUERY_PLAN_STATS database configuration option:

 

Database Configuration OptionToggle source code

 

For cloud instances, we recommend using the database configuration option, as there is no way to add the flag to the startup parameters. For Azure SQL Managed Instances, however, there is an option to set up a job that starts automatically with SQL Server Agent and runs the T-SQL query to set on the trace flag. More details about job automation are on the Microsoft documentation.

Adding PostgreSQL instances

SQL Monitor uses standard PostgreSQL extensions to provide visibility of PostgreSQL's state. These include pg_stat_statements to track query performance, auto_explain to present query plans, and a foreign data wrapper to allow access to the PostgreSQL error log. You will also need to configure a user that SQL Monitor will use to log in as, and grant the user permission to see the data it needs. See Preparing PostgreSQL for monitoring. 

 

Query plan data is stored within SQL Monitor for a defined timeframe. This is configured through Options > Preferences > Data Retention Settings

 

Setting a timeframe for Estimated query plans data & Actual query plans data will allow you to define how long this data is retained within SQL Monitor 

 

Once configured, this information should be viewable in SQL Monitor when selecting any query to load up more data about it.

Hope this helps with your inquiry