how to push sort operator

NEERAJ PRASAD SHARMA 2014-06-17 10:34:53

here in this query sort is spilling to tempdb due to bad cardinality estimations, can some how we can push sort predicate before merge join? any trick

SQLkiwi 2014-06-17 11:10:42
Are you able to also share the table and index definitions?
NEERAJ PRASAD SHARMA 2014-06-17 11:26:10
joining columns are unique clustered index,
i know Its a really wide key as a developer not my fault and i cant put any index on the tables
SQLkiwi 2014-06-17 11:47:49
The simplest answer seems to be to force a nested loops join with OPTION (LOOP JOIN). It's difficult to give a comprehensive answer without details.
NEERAJ PRASAD SHARMA 2014-06-18 05:59:45
HI paul,

Indeed a nested loop join is better solution for fewer rows,
and this will push sort operator befor joining the rows and will avoid sort in tempdb . but for larger rows this wont be good, is it possible optimzer choose sort operation before joining the rows and then join the rows (maybe with hash join), just to avoid sort in tempdb.

key defination on tables:
Table [B2B].[toheader] PRIMARY KEY (clustered) columns : FinYear, FinMonth, CompanyCode, OrgUnitCode, TONo
Table [B2B].[todetails] PRIMARY KEY (clustered) columns : FinYear, FinMonth, CompanyCode, OrgUnitCode, TONo, SKUID.

please ask any details you want to know

SQLkiwi 2014-06-18 06:08:04
Script out the full table, index, and statistics definitions and edit them into your question.
richard101 2014-06-17 10:43:06
ALTER DATABASE yourdatabasename SET COMPATIBILITY_LEVEL=120
SQLkiwi 2014-06-17 11:14:29
The query is running on a SQL Server 2012 instance.
richard101 2014-06-17 12:18:11
ok thanks, how'd you know that please?
SQLkiwi 2014-06-17 12:22:01
The showplan XML contains Build="11.0.2100.60" (which is SQL Server 2012 RTM)