query consuming more io and CPU
I have tried a lot to fix the sort issue and unfortunately its not getting resolve.I have updated statistics,Adding new indexes/Removing indexes, forces query to use a index.
Can someone plz help me to sort out tempdb spill issue.
The sort to tempdb is trigged by the distinct clause. Since you're outputting only columns from the Standard table, you could try adding a subquery with the other 3 tables in the where clause. Assuming that StandardID is unique in the Standard table, this will allow you to dish the distinct.
INSERT INTO @table ( StandardID, StdNbr, StdActiveFlag, RegionID ) SELECT StandardID = S.StandardID, StdNbr = ISNULL ( CASE WHEN DATALENGTH(S.StandardNumber) > 0 THEN S.StandardNumber + '' ELSE RTRIM(S.StdPrefixCode) + '-' + RIGHT('000' + CONVERT(VARCHAR(3), S.StandardNbr), 3) + '-' + S.StandardVersion END, 'N/A' ), StandardActiveFlag = S.StandardActiveFlag, RegionID = S.RegionID FROM dbo.Standards AS S WHERE S.CompanyID = @OATI_CoID AND (@ActiveFlag IS NULL OR S.StandardActiveFlag = @ActiveFlag) AND EXISTS ( SELECT * FROM dbo.Requirement AS req JOIN dbo.RequirementRespEntity AS ReqRE ON ReqRE.StandardID = req.StandardID AND ReqRE.RequirementID = req.RequirementID JOIN dbo.CompanyRespEntity AS CRE ON CRE.ResponsibleEntityID = ReqRE.ResponsibleEntityID WHERE req.StandardID = S.StandardID AND CRE.CompanyID = @CompanyID ) OPTION (RECOMPILE); -- Optional
If that is not suitable for some reason, the cardinality estimate (CE) to look at first is the one on the Standards table:
The seek predicate is probably not a problem, given good statistics, but the highlighted residual predicate most likely is causing the underestimate (11 vs 489 rows). This sort of conditional predicate is tough for CE to get right.
The usual solutions are to remove the dependency on the variable using dynamic SQL, an IF block with separate specific queries for the two cases (@ActiveFlag IS NULL, or not), or an OPTION (RECOMPILE) hint. That last option will add a little extra overhead to each call for a fresh compilation, but the current value of @ActiveFlag will be inlined.
I would probably lean toward using an IF block with two queries optimized for the two cases. The one for not null would have a S.StandardActiveFlag = @ActiveFlag predicate, the other would have no predicate on S.StandardActiveFlag.
Once that issue is corrected, you will most likely find that the Sort no longer spills. Not that it appears to be a huge problem right now – 80ms total duration is not that huge, unless the query is executed very very frequently perhaps.