• I believe that the index you created is far away from perfect.

    First you need to find the most selective condition, please post the results of the following queries:

    DECLARE @CustAccount varchar(20),

    @PriceDate datetime--,

    SET @CustAccount = '105295'

    SET @PriceDate = '01/01/13'

    SELECT COUNT(*)

    FROM PriceData.dbo.AxProductPriceExtract pr

    WHERE pr.FromDate <= @PriceDate and

    pr.ToDate >= @PriceDate

    SELECT COUNT(*)

    FROM PriceData.dbo.AxProductPriceExtract pr

    WHERE pr.price <> 9999.99

    SELECT COUNT(*)

    FROM PriceData.dbo.AxProductPriceExtract pr

    join dynamicsax.dbo.CUSTTABLE cu WITH (NOLOCK)

    on pr.PriceList = cu.MARITEMPRICEYARD

    WHERE cu.ACCOUNTNUM = @CustAccount and

    cu.dataareaid = 'abb'

    Also you can try to use OPTION(RECOMPILE)

    Is it a stored procedure you are trying to optimize? Do you know that when you are running it as a query you may get completely different plan and performance due to parameter sniffing?


    Alex Suprun