How to avoid spill data to tempdb
2015-09-24 18:12:23
2015-09-26 11:30:02
Working from the anonymized query text:
SELECT DISTINCT ( Column1 ) AS Column2 , Function1(Column3) AS Column4 FROM Object1 Object2 INNER JOIN Object3 Object4 ON Object2.Column5 = Column6 WHERE Column7 = ? AND LEFT(Column8, ?) = ? GROUP BY Column1
…it's tricky to work out which column belongs to which table, but assuming the tables in question look something like this:
CREATE TABLE #Object1 ( Column1 integer NULL, Column5 integer NULL, Column7 integer NULL, Column8 varchar(100) NULL, ); CREATE TABLE #Object3 ( Column3 integer NULL, Column6 integer NULL );
…you can avoid the Sort completely by creating this index:
CREATE INDEX i ON #Object1 (Column7, Column1) INCLUDE (Column5, Column8);
This index allows a seek on the equality predicate on Column7, producing rows in Column1 order as required by the Stream Aggregate for the grouping operation. This is the main optimization. It seems like a suitable index for seeking already exists on the second table, something like:
CREATE INDEX i ON #Object3 (Column6) INCLUDE (Column3);
Given these indexes, the plan changes to:
From a style point of view:
- The DISTINCT is redundant here, given the GROUP BY clause
- You should use aliases on all column references in the query
- The predicate using LEFT should be rewritten as a LIKE