SQL Unfixable parameter sniffing? Takes Seconds in SSMS, Minutes in SSRS

Question

I have a report. The queries take seconds to run on Microsoft SSMS (SQL Server Management Studio ) but whenever I run the report through SSRS on Visual Studio, now the report takes minutes. I've read a lot about this and everyone say's its parameter sniffing but I've tried all of the solutions for parameters sniffing and they don't seem to do anything.

At first, I used to put all my queries in store procedures. Then since I thought there was a chance that having them be a stored procedure was causing the slowdown, I created my datasets for the reports with the queries being in text. That didn't fix the issue however.

Here is my query, it's a bit long and complicated but that's not the issue here. And yes I know that is not the correct way to use nolock but that is how my employer requested I use nolock.

SELECT DISTINCT 

DENSE_RANK() OVER (PARTITION BY O.Customer_Number ORDER BY O.Customer_Purchase_Order_Number ASC) 
    + DENSE_RANK() OVER (PARTITION BY O.Customer_Number ORDER BY O.Customer_Purchase_Order_Number DESC) 
         - 1 AS Total_Orders_Count

,COUNT(*) OVER () AS Total_Units_Count

 ,Sum(S.Retail  * OD.Quantity_Ordered) OVER (PARTITION BY O.Customer_Number) as Total_Net_Value

,Sum(OD.Price * OD.Quantity_Ordered) OVER (PARTITION BY O.Customer_Number) as Price
,Sum(OD.Discount_Value) OVER (PARTITION BY O.Customer_Number) as Discount_Value
,Sum(OD.Freight_Charges) OVER (PARTITION BY O.Customer_Number) as Freight_Charges
,Sum(OD.Tax_Value) OVER (PARTITION BY O.Customer_Number) as Tax_Value


,SUM(CASE WHEN O.Order_Status = '30' then 1 ELSE 0 END) OVER (PARTITION BY O.Customer_Number) as "Cancelled_Orders"
,SUM(CASE WHEN OD.Line_Status = '80' then 1 ELSE 0 END) OVER (PARTITION BY O.Customer_Number) as "Cancelled_Lines"
,Sum (T.Cost) OVER () Total_Product_Cost
,Avg (T.Cost) OVER () Avg_Product_Cost

FROM 
    [JMNYC-AMTDB].[AMTPLUS].[dbo].Orders O (nolock)

LEFT JOIN 
    [JMNYC-AMTDB].[AMTPLUS].[dbo].Order_Detail OD (nolock) On O.Company_Code =
        OD.Company_Code And O.Division_Code =
        OD.Division_Code And O.Control_Number =
        OD.Control_Number

LEFT JOIN
    [JMNYC-AMTDB].[AMTPLUS].[dbo].[Z_N_RetailPrices] S (nolock) On 
        OD.Item_Number = S.SKU

LEFT JOIN
    (
    Select
    T.Sku,
    CASE WHEN T.AvgCostActual is NULL THEN T.AvgCostStandard ELSE T.AvgCostActual END Cost

    From(
        Select distinct 

        Z.Sku, 
        AVG (CASE WHEN st.Actual_Cost <> 0 THEN st.Actual_Cost ELSE NULL END) OVER (PARTITION BY Z.Sku) AvgCostActual,
        AVG (CASE WHEN st.Standard_Cost <> 0 THEN st.Standard_Cost ELSE NULL END) OVER (PARTITION BY Z.Sku) AvgCostStandard
        From 

            [JMNYC-AMTDB].[AMTPLUS].[dbo].[Z_N_RetailPrices] Z

        LEFT JOIN

            [JMNYC-AMTDB].[AMTPLUS].[dbo].Style St (nolock) On 
            Z.Sku = St.Item_Number
        ) T  
    ) T on OD.Item_Number = T.Sku

WHERE 
    (O.Company_Code = @CompanyCode OR @CompanyCode IS NULL) AND 
    (O.Division_Code = @DivisionCode OR @DivisionCode IS NULL) AND
    o.Customer_Number = 'ecom2x' AND 
    ISNUMERIC(O.Customer_Purchase_Order_Number) <> 0 AND
    o.DateRecordModified BETWEEN @FromDate AND DATEADD(dayofyear, 1, @ToDate)

I can add this before the query in MSSMS (since I need to declare the parameters if I am not selecting them through SSRS) and the query runs in seconds.

DECLARE @CompanyCode VARCHAR(5)
SET @CompanyCode = '03'

DECLARE @DivisionCode VARCHAR(5)
SET @DivisionCode = '001'

DECLARE @FromDate DATETIME
SET @FromDate = '2/1/2019'

DECLARE @ToDate DATETIME
SET @ToDate = '3/1/2019'

In SSRS it takes minutes with the same parameters.

I tried adding this the following code in my SSRS Query to speed it up (everyone says this is the solution for parameter sniffing )

Declare @LocalCompanyCode NVARCHAR(5) = @CompanyCode
Declare @LocalDivisionCode NVARCHAR(5)= @DivisionCode
Declare @LocalToDate DATETIME= @ToDate
Declare @LocalFromDate DATETIME= @FromDate

And then changing my Where clause to use these local variables

WHERE 
    (O.Company_Code = @LocalCompanyCode OR @LocalCompanyCode IS NULL) AND 
    (O.Division_Code = @LocalDivisionCode OR @LocalDivisionCode IS NULL) AND
    o.Customer_Number = 'ecom2x' AND 
    ISNUMERIC(O.Customer_Purchase_Order_Number) <> 0 AND
    o.DateRecordModified BETWEEN @LocalFromDate AND DATEADD(dayofyear, 1,@LocalToDate)

But that changes the nothing and the query still takes minutes to run in SSRS and seconds in SSMS.

I've tried using Recompile too, same thing.

Does anybody know what I can do?

Answer 1

Have you tried the OPTIMIZE FOR query hint? E.g. OPTION (OPTIMIZE FOR (@LocalCompanyCode = 5, @LocalDivisionCode = 1)) --Choose representative examples of what values are normally used for the variables, this comes after the where clause, and you can specify as many parameter-value pairs as needed by separating them with a comma. You can also use the keyword UNKNOWN when you don't know the best value, but this sometimes results in sub-optimal plans

Answer 2

Does anybody know what I can do?

• SET OPTIONS in SSRS are different from SET options in SSMS. Consider making them equal by comparing and enabling the same set in both apps since mismatch of them can be real reason for the performance difference

• If the first item didn't bring any improvement and there is really parameter sniffing, consider adding OPTION (RECOMPILE) in the end of the query.

SET options can be checked using:

DBCC USEROPTIONS

The default set of "SET options" in SSMS is:

SET quoted_identifier ON;   
SET arithabort ON;
SET SET ansi_null_dflt_on;  
SET ansi_warnings ON;   
SET ansi_padding ON;    
SET ansi_nulls ON;  
SET concat_null_yields_null ON;

To check the influence on the query they can be added before the SELECT