0

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.

  • 1
    questions I have are, what is the execution time, and what are you trying to get it down to? Are you able to provide a SQL Fiddle albeit with limited and ficticious data to help us understand more about what you are trying to do. Also, the exectuion plan can change on a number of factors at all times so the cached plan might not actually be the right plan. Lastly with such a large dataset, why are you trying to pull back so many at once? have you throught about staggering the calls to collect x amount at a time and build up the data? – Simon Price Sep 13 '19 at 7:54
  • It does not really take too long, 1.5 sec. However looking at the plan I am concerned at such a large estimate rows or row size. It might be working fast due underlying hardware, but can/ will have second order impact like locking, blocking, taking too much space in temp db. It basically returns 300 rows in worst case. – user2647784 Sep 13 '19 at 8:10
  • If the plan is only showing you estimates, then it's the estimated plan (or the compiled plan based on the estimates that you can find in the plan cache). You should get the actual query plan and if you can share it with us at pastetheplan. Stored procs generally will reuse the cached plan - unless there are recompile hints, dynamic SQL, or similar. – GabSql Sep 13 '19 at 12:55
1

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:

enter image description here

3) Replace the apostrophes

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

enter image description here

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 xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3223.3">
...

Incorrect use :

OPTION(USE PLAN 
N'
<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.

|improve this answer|||||
  • Thanks , this is what I was looking for. I still need to give it a try but looks like this will work for now. Marked as the answer. – user2647784 Sep 16 '19 at 7:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.