Skip to main content
Database Administrators

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

10
  • are the data equally distributed or do you have e.g. 1 mio rows for partition 1 and only a few rows for the other partition numbers? If yes (and you have parameterization on your DB enabled) it could be a parameter sniffing problem, if the very same query (with other parameter values) was executed before. You can verify this by adding spaces, aliases etc. to your query (so it gets a new execution plan) Commented May 14, 2019 at 9:30
  • I updated my question. This is a axapta query. @P1 is variable. I run query with parameter value in execution plan. declare @P1 bigint=5639064671 Commented May 14, 2019 at 9:37
  • Seems like a estimation issue, probably due to the ~240 columns selected. Why are so many columns returned as a result? Could you add the query plan to pastetheplan. This will help in getting a more complete answer. Commented May 14, 2019 at 9:37
  • I tried OPTION(RECOMPILE). But same plan again. And @RandiVertongen I added plan in my question. Commented May 14, 2019 at 9:41
  • Awesome, thanks. Is there a possibility to add the plan with the forced index aswell? That might show the estimation differences Commented May 14, 2019 at 9:44