Competition: What’s your favorite Redgate tool? Enter now.

NON CLUSTERED INDEX SEEK cost very high 70%

Earlier my query was taking less than a minute and now its taking 30 Mins
Please click here to see query plan https://www.brentozar.com/pastetheplan/?id=SJEOwszLH
Tagged:

Answers

  • Hi,

    Unfortunately, we have not come across long running queries for this tool.

    Can you please enable Verbose logging (Help > Logging > Minimum log levels > Verbose) and then restart SQL Index Manager and reproduce the issue, then click Help > Logging > Locate Log Files. 

    Hopefully the logs will give some insight into this issue.

    Kind regards

    Mac Frierson | Product Support Engineer | Redgate Software
    Have you visited our Help Center?

  • where should i enableverbose logging, i have two conditions one is division and other is date_loaded , if include these two columns in index columns , only division is being used for index seek and date_loaded is being used as predicate due to this query reading all the rows even which are not satiesfy where clause
  • Please suggest what index can improve query performance and what columns need to include in index columns and type of index  for my below query

    declare @Division varchar(40)='1520',@Location varchar(40)='ALL',@FROMDATE varchar(40)='20190701',@TODATE varchar(40)='20190731'

    ;WITH SIDAILY AS (
    SELECT 
    DIVISION,
    JOB_NUMBER,
    CUSTOMER_NUMBER,
    CUSTOMER_NAME,
    CONTRACT_NUMBER,
    QUANTITY,
    DATE_LOADED,
    ITEM,
    TABLE_ORIGIN,
    DB_NAME,
    B.Location
    FROM APS_Adhoc_Inventory.dbo.SITE_INVENTORY_DAILY_Profield A
    LEFT JOIN APS_Adhoc_Profield.dbo.Locations B
    ON A.DIVISION = B.DivisionCode
    AND A.TABLE_ORIGIN = B.DB_NAME
    WHERE (B.DivisionCode in (@Division)) --OR 'ALL' IN (@Division))
    and (convert(varchar,B.Location) IN (@Location) OR 'ALL' IN (@Location))
    AND CONVERT(VARCHAR(20),DATE_LOADED,112) = CONVERT(VARCHAR,@TODATE,112)
    ) SELECT * FROM SIDAILY

Sign In or Register to comment.