NON CLUSTERED INDEX SEEK cost very high 70%

YS_00632762

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

Mac_F

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

YS_00632762

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

YS_00632762

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