query consuming more io and CPU

VINOD 2016-09-15 07:11:07

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.

Xanthos 2016-09-15 08:13:30

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.

SQLkiwi 2016-09-15 10:34:41
To give an example of Xanthos' suggestion, the INSERT could be written as follows:

    StandardID = S.StandardID,
    StdNbr = 
                WHEN DATALENGTH(S.StandardNumber) > 0 
                THEN S.StandardNumber + ''
                    RTRIM(S.StdPrefixCode) + '-' + 
                    RIGHT('000' + CONVERT(VARCHAR(3), S.StandardNbr), 3) + '-' + 
        StandardActiveFlag = S.StandardActiveFlag,
        RegionID = S.RegionID
FROM dbo.Standards AS S
    S.CompanyID = @OATI_CoID
    AND (@ActiveFlag IS NULL OR S.StandardActiveFlag = @ActiveFlag)
        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
            req.StandardID = S.StandardID
            AND CRE.CompanyID = @CompanyID

If that is not suitable for some reason, the cardinality estimate (CE) to look at first is the one on the Standards table:

alt text

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.

VINOD 2016-09-15 11:54:14
Hi SQLkiwi,

thanks for an update to rectify the query. I executed the query as mentioned above and as a result IO is reduced but the sort operator still persist in the execution plan.

I tried to add option(Fast 20) query hint to remove sort operalink texttor and i got success. Unfortunately Logical reads,Read-ahead reads are increased after implementation of this approach.

please suggest for any improvement. see attached plan of two different queries.


SQLkiwi 2016-09-15 14:40:15
Please edit your question to include CREATE TABLE and CREATE INDEX statements for all tables in the query. The queries are all low cost now, so I think you might be optimizing beyond what is necessary. But, if you provide that detail, I'll have another look.