Skip to content
This repository has been archived by the owner on Dec 23, 2017. It is now read-only.

Value length of string parameters defaults to explicit causing cache bloat. #50

Open
aidapsibr opened this issue Aug 15, 2016 · 2 comments
Labels

Comments

@aidapsibr
Copy link
Collaborator

The parameter sizes on string parameters are variable based on the length of the string that is being sent to SQL. For instance, if I perform a dynamic query with a string parameter, the field definition that is added to the SQL string will be the actual length of the string.

pasted1

Because of this behavior, a procedure plan will be created and cached for every possible length of the string, and every combination when more than one parameter is sent. This could be argued as intended behavior so that the plan cache is accurate to the length of string being queried. It could equally be argued that it bloats the cache, requires a compilation per combination of string length, and creates a good potential for a corrupt plan that hits on 11 character length but not 10 characters.

@aidapsibr
Copy link
Collaborator Author

This seems like a pretty easy target, choosing when creating a string parameter to default to MAX or 4K would likely solve this issue, and is what I assumed was happening originally.

@porterchris
Copy link

MAX is likely the safest but that needs verification. 4000 is the default (non)MAX value for NVARCHAR and 8000 is the default (non)MAX value for VARCHAR. This logic likely needs to tie to the string to char conversion in issue #52 .

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
Projects
None yet
Development

No branches or pull requests

2 participants