Can SQL Server be forced to use a cached plan for executing a SP or SQL script?

Can we somehow force SQL Server to use a cached plan for executing a SP or sql query?

We have a parallel plan in prod which estimates data size in GB’s and estimates number of rows to rows equal to table cardinality.

Just before the final output , query plan estimates

• Estimated Number of Rows: 650970000
• Estimated Row Size: 1043 B
• Estimated Data Size: 632 GB

The execution plan does not show “Actual Number of rows read” , so have to go with above parameters.

I am unable to get the same plan while executing the SP or the internal query manually so cannot really do a like for like comparison of any performance improvement that I make to the query / SP.

According to these comments

Can we somehow force SQL Server to use a cached plan for executing a
SP or sql query?

I am unable to get the same plan while executing the SP or the
internal query manually so cannot really do a like for like comparison
of any performance improvement that I make to the query / SP.

It looks like you only want to run the query with the plan in the cache that has the high row estimates for testing purposes.

Altough you should not implement this in production, you could run it like this:

Also note while you can force the estimated execution plan using below hints, values such as the estimated rowcounts, estimated total subtreecost, … will be recalculated. Even when forcing the cached plan. What remains the same is the general shape of the plan, such as operators used and indexes touched.

---

1) Find the estimated plan

You could search for the estimated execution plan in the cache by using the query in this post on Searching the SQL Server Plan Cache

2) Display the plan XML

After getting the plan, display the XML:

3) Replace the apostrophes

Replace all 's with ''s, for example with find and replace:

4) Run the query with the USE PLAN hint

Rerun the query with

OPTION(USE PLAN
N'
Execution plan XML here
');

Make sure that there are no spaces between USE PLAN N' and the execution plan XML.

Correct use:

OPTION(USE PLAN 
N'<ShowPlanXML  Version="1.481" Build="14.0.3223.3">
...

Incorrect use :

OPTION(USE PLAN 
N'
<ShowPlanXML  Version="1.481" Build="14.0.3223.3">
...

You would have to create a temporary stored procedure with the OPTION(USE PLAN N'') or alter the existing stored procedure or use a plan guide to add these hints when using stored procedures.

More information on query hints can be found here.

Looking into why these estimates are so high seems like more of a necessity.
We could help with that, but we would need the (actual) execution plan.

You could upload the plan to PasteThePlan and add it to the question, or ask a new question with the uploaded plan.