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
''s, for example with find and replace:
4) Run the query with the USE PLAN hint
Rerun the query with
Execution plan XML here
Make sure that there are no spaces between
USE PLAN N' and the execution plan XML.
N'<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3223.3">
Incorrect use :
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" 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.