Skip to main content
OverflowAI is here! AI power for your Stack Overflow for Teams knowledge community. Learn more
11 votes
Accepted

Extra NESTED LOOP / INNER JOIN causing NO JOIN PREDICATE warning

The optimizer decided your query was a selective star query on fact and dimension tables. The giveaway is the presence of StarJoinInfo attributes on the plan's join operators (except the cartesian ...
Paul White's user avatar
  • 85.4k
8 votes

Why is COMPATIBILITY_LEVEL: 160 using Clustered Index Scan?

The plans provided are taken with different database states at different points in time, or perhaps on different servers: In the hash join plan, Dokument has 16,478,000 rows and ExternalBookmark has ...
Paul White's user avatar
  • 85.4k
3 votes
Accepted

Index seeks and predicate push down

Solution You can use an expanded FORCESEEK hint with the seek keys needed: FROM dbo.MyTable AS M WITH (FORCESEEK(IDX(WTID, OETID, SRID))) For example: IF EXISTS ( SELECT T.* FROM ( ...
Paul White's user avatar
  • 85.4k
2 votes

How is Memory Grant Desired Memory calculated

The sort operation will have storage overheads. Pure in-place sort algorithms exist but they may costume more CPU especially if operating in parallel. Those row number and row size values are ...
David Spillett's user avatar
1 vote

How can I see the plan for ONE statement in a stored procedure?

There IS a way. In SSMS set Include Actual Exec Plan (Ctrl-M) SET STATISTICS XML OFF; in first statement of batch Wrap the statement in question like: SET STATISTICS XML ON; statement(s) SET ...
Masaka's user avatar
  • 11
1 vote

Index seeks and predicate push down

For some unclear reason, the compiler seems to be getting confused by the UNION and not pushing the NOT EXISTS anti-join predicates all the way, and therefore not matching the index fully. The ...
Charlieface's user avatar
  • 13.1k
1 vote

SQL Server management Studio does not show Index recommendation

Seeing a "Scan" does not automatically mean that SQL Server will suggest an index. Furthermore, just because SQL Server suggests a missing index - it doesn't always mean it is the necessary ...
Mike Walsh's user avatar
  • 18.2k

Only top scored, non community-wiki answers of a minimum length are eligible