Mdtryhht

Would it be believable to defy demographics in a story?

Help with identifying unique aircraft over NE Pennsylvania

UK Tourist Visa- Enquiry

Error in master's thesis, I do not know what to do

Hackerrank All Women's Codesprint 2019: Name the Product

Do I need to convey a moral for each of my blog post?

Why doesn't the chatan sign the ketubah?

Should I be concerned about student access to a test bank?

How to read string as hex number in bash?

What are rules for concealing thieves tools (or items in general)?

How to find the largest number(s) in a list of elements?

Writing in a Christian voice

What is the reasoning behind standardization (dividing by standard deviation)?

Why are there no stars visible in cislunar space?

Pre-Employment Background Check With Consent For Future Checks

Print a physical multiplication table

Air travel with refrigerated insulin

Did Nintendo change its mind about 68000 SNES?

Would mining huge amounts of resources on the Moon change its orbit?

Is xar preinstalled on macOS?

Homology of the fiber

Why didn't Héctor fade away after this character died in the movie Coco?

What is it called when someone votes for an option that's not their first choice?

How to test the sharpness of a knife?

Stored Procedure: table-Value parameters

How (and why) does TOP impact an execution plan?How can row estimates be improved in order to reduce chances of spills to tempdbHow to improve row estimate of 1 row in join for newly inserted dataLow cardinality estimate in hash match operatorSQL server 2012 inner join estimated number of rows issueWhy is this join cardinality estimate so large?Why does SQL Server estimate less rows will be emitted from a join after inserting some rows?Bad execution plan after stats update due to temp tableHash join between master/detail tables produces too-low cardinality estimateCardinality estimation on tables with large cardinality variance between keys

2

We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and therefore should only estimate a single row?).

https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017

Recently, we've seen issues with plan generation where the estimated number of rows in the table is a high as 2000 (normally only 1 or 2 rows are in passed in). This is caching a plan which is highly inefficient (wrong join order with a largish table ~160M rows) and causing some issues.

Am I wrong here about what the estimates should be? I could force the join type/order but I'd rather avoid this if at all possible.

Would really appreciate any thoughts/feedback people might have.

Thanks
Dave

sql-server stored-procedures statistics

share|improve this question

asked 12 hours ago

Dave WallDave Wall

132

New contributor

Dave Wall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  You can't make SQL Server compute statistics for table variables. An option is to use a temporary table either created and loaded from outside the SP (not using the table variable as input), or created and loaded inside the SP from the table variable.
  
  – EzLo
  12 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Insert the valued from Table parameter to TempTable and use it to statistics of temp table.
  
  – MarmiK
  11 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You could try adding OPTION(RECOMPILE) to the query in the procedure if it is a viable option to do so.
  
  – Randi Vertongen
  10 hours ago
  
  

  
  
  
  

add a comment | 

2

We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and therefore should only estimate a single row?).

https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017

Recently, we've seen issues with plan generation where the estimated number of rows in the table is a high as 2000 (normally only 1 or 2 rows are in passed in). This is caching a plan which is highly inefficient (wrong join order with a largish table ~160M rows) and causing some issues.

Am I wrong here about what the estimates should be? I could force the join type/order but I'd rather avoid this if at all possible.

Would really appreciate any thoughts/feedback people might have.

Thanks
Dave

sql-server stored-procedures statistics

share|improve this question

asked 12 hours ago

Dave WallDave Wall

132

New contributor

Dave Wall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  2
  

  
  
  
  
  
  

  
  
  You can't make SQL Server compute statistics for table variables. An option is to use a temporary table either created and loaded from outside the SP (not using the table variable as input), or created and loaded inside the SP from the table variable.
  
  – EzLo
  12 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Insert the valued from Table parameter to TempTable and use it to statistics of temp table.
  
  – MarmiK
  11 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  You could try adding OPTION(RECOMPILE) to the query in the procedure if it is a viable option to do so.
  
  – Randi Vertongen
  10 hours ago
  
  

  
  
  
  

add a comment | 

We have a number of stored procedures where we pass in a user defined table type parameter. As per the MS doc below, my understanding is that SQL Server doesn't retain statistics on these (and therefore should only estimate a single row?).

https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine?view=sql-server-2017

Recently, we've seen issues with plan generation where the estimated number of rows in the table is a high as 2000 (normally only 1 or 2 rows are in passed in). This is caching a plan which is highly inefficient (wrong join order with a largish table ~160M rows) and causing some issues.

Am I wrong here about what the estimates should be? I could force the join type/order but I'd rather avoid this if at all possible.

Would really appreciate any thoughts/feedback people might have.

Thanks
Dave

sql-server stored-procedures statistics

share|improve this question

asked 12 hours ago

Dave WallDave Wall

132

New contributor

Dave Wall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 12 hours ago

Dave WallDave Wall

132

New contributor

Dave Wall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

asked 12 hours ago

Dave WallDave Wall

132

New contributor

Dave Wall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 12 hours ago

Dave WallDave Wall

132

New contributor

Dave Wall is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 12 hours ago

Dave WallDave Wall

132

1 Answer
1

active

oldest

votes

2

Check out this post from Erik Darling:

Table Valued Parameters: Unexpected Parameter Sniffing

The gist of it is that table-valued parameters are susceptible to parameter sniffing just like other parameters. If the execution plan happened to get cached when 2,000 rows were passed in via the TVP, then that's the plan you're stuck with (until a recompile).

All of the usual solutions to parameter sniffing apply. It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

Probably the "simplest" solution is to add a RECOMPILE hint to the query that's performing the join to this big table. This will incur higher CPU (due to more plan recompiles) and you lose the "predictability" of the query plan, but it could solve the immediate plan quality issue.

This should also help with the problem of fixed join estimates, mentioned in Erik's post:

Non-join cardinality estimates behave like local variables (fixed estimates)

...we get 10% for equality, 30% for inequality, and 9% for two inequalities.

share|improve this answer

edited 8 hours ago

answered 8 hours ago

Josh DarnellJosh Darnell

6,75522140

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Josh - I'd not seen Erik's article.. it makes sense now (and to know I was wrong about the estimates :) ).. if it's just a sniffing problem, I can deal with that easily enough. Thanks again!
  
  – Dave Wall
  6 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @DaveWall Glad I could help! I was in the same boat as you until I read Erik's article.
  
  – Josh Darnell
  4 hours ago
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

Dave Wall is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f232414%2fstored-procedure-table-value-parameters%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

Check out this post from Erik Darling:

Table Valued Parameters: Unexpected Parameter Sniffing

The gist of it is that table-valued parameters are susceptible to parameter sniffing just like other parameters. If the execution plan happened to get cached when 2,000 rows were passed in via the TVP, then that's the plan you're stuck with (until a recompile).

All of the usual solutions to parameter sniffing apply. It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

Probably the "simplest" solution is to add a RECOMPILE hint to the query that's performing the join to this big table. This will incur higher CPU (due to more plan recompiles) and you lose the "predictability" of the query plan, but it could solve the immediate plan quality issue.

This should also help with the problem of fixed join estimates, mentioned in Erik's post:

Non-join cardinality estimates behave like local variables (fixed estimates)

...we get 10% for equality, 30% for inequality, and 9% for two inequalities.

share|improve this answer

edited 8 hours ago

answered 8 hours ago

Josh DarnellJosh Darnell

6,75522140

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Josh - I'd not seen Erik's article.. it makes sense now (and to know I was wrong about the estimates :) ).. if it's just a sniffing problem, I can deal with that easily enough. Thanks again!
  
  – Dave Wall
  6 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @DaveWall Glad I could help! I was in the same boat as you until I read Erik's article.
  
  – Josh Darnell
  4 hours ago
  

  
  
  
  

add a comment | 

2

Check out this post from Erik Darling:

Table Valued Parameters: Unexpected Parameter Sniffing

The gist of it is that table-valued parameters are susceptible to parameter sniffing just like other parameters. If the execution plan happened to get cached when 2,000 rows were passed in via the TVP, then that's the plan you're stuck with (until a recompile).

All of the usual solutions to parameter sniffing apply. It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

Probably the "simplest" solution is to add a RECOMPILE hint to the query that's performing the join to this big table. This will incur higher CPU (due to more plan recompiles) and you lose the "predictability" of the query plan, but it could solve the immediate plan quality issue.

This should also help with the problem of fixed join estimates, mentioned in Erik's post:

Non-join cardinality estimates behave like local variables (fixed estimates)

...we get 10% for equality, 30% for inequality, and 9% for two inequalities.

share|improve this answer

edited 8 hours ago

answered 8 hours ago

Josh DarnellJosh Darnell

6,75522140

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thanks Josh - I'd not seen Erik's article.. it makes sense now (and to know I was wrong about the estimates :) ).. if it's just a sniffing problem, I can deal with that easily enough. Thanks again!
  
  – Dave Wall
  6 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @DaveWall Glad I could help! I was in the same boat as you until I read Erik's article.
  
  – Josh Darnell
  4 hours ago
  

  
  
  
  

add a comment | 

Check out this post from Erik Darling:

Table Valued Parameters: Unexpected Parameter Sniffing

The gist of it is that table-valued parameters are susceptible to parameter sniffing just like other parameters. If the execution plan happened to get cached when 2,000 rows were passed in via the TVP, then that's the plan you're stuck with (until a recompile).

All of the usual solutions to parameter sniffing apply. It would be helpful if you could provide an actual execution plan and query text to get a better idea of what's going on.

Probably the "simplest" solution is to add a RECOMPILE hint to the query that's performing the join to this big table. This will incur higher CPU (due to more plan recompiles) and you lose the "predictability" of the query plan, but it could solve the immediate plan quality issue.

This should also help with the problem of fixed join estimates, mentioned in Erik's post:

Non-join cardinality estimates behave like local variables (fixed estimates)

...we get 10% for equality, 30% for inequality, and 9% for two inequalities.

share|improve this answer

edited 8 hours ago

answered 8 hours ago

Josh DarnellJosh Darnell

6,75522140

share|improve this answer

edited 8 hours ago

answered 8 hours ago

Josh DarnellJosh Darnell

6,75522140

answered 8 hours ago

Josh DarnellJosh Darnell

6,75522140

answered 8 hours ago

Josh DarnellJosh Darnell

6,75522140