Is my query having Parameter Sniffing performance issue

Question

Is my query having Parameter Sniffing performance issue ? I have kept my non-clustered index suggested by query execution plan, but still I have doubts whether this is parameter sniffing, or something else. Please check below query:

declare @orgid int=22,
@salesperson int=0
select
pd.col1,dd.col1
from t1 pd
inner join (select max(doId) doid,personId from t2 where productId=99 and personId>0 
 and effectDate IS NOT NULL  group by personId) d on pd.personId=d.personId
join t2 dd on d.doid=dd.doId
join (select max(requestId) requestid,doId from  t3 group by doId ) p on dd.doId=p.doId
join t3 pp on p.requestid=pp.requestIdjoin person prn on cp.personId=prn.personId
 where pd.organizationId=@orgId
 and (@salesperson=0 or cp.personId=@salesperson)
 order by pd.patientName

Will this line create an issue?

(@salesperson=0 or cp.personId=@salesperson)

Answer 1

nope

You don't have a parameter sniffing problem because you don't have any parameters. You are using local variables, which means you'll always get the same density vector estimate no matter which values you use.

You may want to look at this Q&A:

• Why does SQL Server use a better execution plan when I inline the variable?

sniffing vs sensitivity

It's helpful to understand that parameter sniffing is generally a good thing. People often talk negatively about it, when in reality the problem they're having is parameter sensitivity.

If the data that your parameters touch is highly skewed towards some values and less to others, then sharing plans for those query plans can lead to performance issues.

ackshually

The real problem you're having is that you've written a catch-all query that SQL Server can't do anything reasonable with. It can't seek into indexes, and it can't estimate cardinality well, all because you've written a runtime decision into your query: (@salesperson=0 or cp.personId=@salesperson)

For more detail, see my posts here:

• How To Fix Performance Problems With Optional Parameters In SQL Server Stored Procedures Part 1
• How To Fix Performance Problems With Optional Parameters In SQL Server Stored Procedures Part 2

You may find you get a better plan if you add OPTION(RECOMPILE); to the end of your query. That will allow for an optimization called parameter embedding. Even though you're using local variables, the same rule applies.

If you want a long term solution that doesn't require recompilation on every execution, you'll probably want to use dynamic SQL.

DECLARE  
    @orgId integer = 22,
    @salesperson integer = 0,
    @sql nvarchar(MAX) = N'';
SELECT
    @sql += N'
SELECT
    pd.col1,
    dd.col1
FROM t1 AS pd
JOIN
(
    SELECT
        doid = MAX(t2.doId),
        t2.personId
    FROM t2 AS t2
    WHERE t2.productId = 99
    AND   t2.personId > 0
    AND   t2.effectDate IS NOT NULL
    GROUP BY
        t2.personId
) AS d
  ON pd.personId = d.personId
JOIN t2 AS dd
  ON d.doid = dd.doid
JOIN
(
    SELECT
        requestid = MAX(t3.requestId),
        t3.doId
    FROM t3 AS t3
    GROUP BY
        t3.doId
) AS p
  ON dd.doid = p.doid
JOIN t3 AS pp
  ON p.requestid = pp.requestid
JOIN person AS prn
  ON cp.personId = prn.personId
WHERE pd.organizationId = @orgId';
IF @salesperson <> 0
BEGIN
   SET @sql += 
       N'
AND   cp.personId = @salesperson';
END;
SET @sql += N'
ORDER BY
    pd.patientName;'
PRINT @sql;
EXECUTE sys.sp_executesql
    @sql,
  N'@orgId integer,
    @salesperson integer',
    @orgId,
    @salesperson;

a potential rewrite

If you have good supporting indexes, you may want to try this version of the query instead. You'll know pretty quickly if your indexes are hot or not, but tuning those is a separate question.

DECLARE  
    @orgId integer = 22,
    @salesperson integer = 0,
    @sql nvarchar(MAX) = N'';
SELECT
    @sql += N'
SELECT
    pd.col1,
    dd.col1
FROM t1 AS pd
CROSS APPLY
(
    SELECT
        doid = MAX(t2.doId),
        t2.personId
    FROM t2 AS t2
    WHERE t2.productId = 99
    AND   t2.personId > 0
    AND   t2.effectDate IS NOT NULL
    AND   t2.personId = pd.personId
    GROUP BY
        t2.personId
) AS d
JOIN t2 AS dd
  ON d.doid = dd.doid
CROSS APPLY
(
    SELECT
        requestid = MAX(t3.requestId),
        t3.doId
    FROM t3 AS t3
    WHERE t3.doid = p.doid
    GROUP BY
        t3.doId
) AS p
JOIN t3 AS pp
  ON p.requestid = pp.requestid
JOIN person AS prn
  ON cp.personId = prn.personId
WHERE pd.organizationId = @orgId';
IF @salesperson <> 0
BEGIN
   SET @sql += 
       N'
AND   cp.personId = @salesperson';
END;
SET @sql += N'
ORDER BY
    pd.patientName;'
PRINT @sql;
EXECUTE sys.sp_executesql
    @sql,
  N'@orgId integer,
    @salesperson integer',
    @orgId,
    @salesperson;

important

Since the dynamic SQL I used above does use parameters, it's important to test performance with as much of a variety of @orgId and @salesperson values as will commonly be used. You may actually hit a parameter sensitivity issue.

Answer 2

ymmv

A further option/improvement to @ErikDarling's answer is to use window functions instead of those CROSS APPLYs.

DECLARE  
    @orgId integer = 22,
    @salesperson integer = 0,
    @sql nvarchar(MAX);
SET @sql = N'
SELECT
    pd.col1,
    dd.col1
FROM t1 AS pd
JOIN
(
    SELECT
        t2.,
        rn = ROW_NUMBER() OVER (PARTITION BY t2.personId ORDER BY t2.doId DESC)
    FROM t2 AS t2
    WHERE t2.productId = 99
    AND   t2.personId > 0
    AND   t2.effectDate IS NOT NULL
    GROUP BY
        t2.personId
) AS t2 ON t2.personId = pd.personId AND t2.rn = 1
JOIN
(
    SELECT
        t3.,
        rn = ROW_NUMBER() OVER (PARTITION BY t3.doId ORDER BY t3.requestId DESC)
    FROM t3 AS t3
) AS t3 ON t3.doId = t2.doId AND t3.rn = 1
JOIN person AS prn
  ON cp.personId = prn.personId
WHERE pd.organizationId = @orgId';
IF @salesperson <> 0
BEGIN
   SET @sql += 
       N'
  AND cp.personId = @salesperson';
END;
SET @sql += N'
ORDER BY
    pd.patientName;'
PRINT @sql;
EXECUTE sys.sp_executesql
    @sql,
  N'@orgId integer,
    @salesperson integer',
    @orgId,
    @salesperson;