Stop bullying the optimizer
Sometimes the estimator can get things wrong. Often it's because statistics are out of date or missing and it has no way to gather a correct estimate. Usually memory grant problems go the other way and the issue is that not enough memory is granted.
The person who wrote (or "fixed") this particular query has taken matters into his or her own hands by including many hints which force the optimiser to take a specific approach when generating a plan.
INNER LOOP JOIN
WITH (INDEX=IX_tbl_Identity_AccountName_TypeId, FORCESEEK, UPDLOCK, HOLDLOCK)
OPTION (OPTIMIZE FOR (@partitionId UNKNOWN))
Using query hints to boss around the optimizer is widely considered to be fraught with danger. From the use of these hints in the first place it's clear that there have been performance issues with this query before this problem cropped up. The solution might be as simple as adding an appropriate covering index. It's also difficult to guess what's going on with the temp table #identitites so we're missing the full picture. The use of MAX_GRANT_PERCENT = 1 seems like just another nasty hack and not the final solution.
The presence of these hints should raise a red flag during code review and should definitely be reviewed before upgrading to a new optimizer.
IMHO this should be considered a quality process fail for the VSTS team rather than an SQL Server problem.
Biting the hand that feeds IT
