There's a long delay in this query that I need to resolve This is a multi-tenant/portal DNN (formally DotNetNuke) database, and a core query as part of the registration process seems to be terribly slow.

Here it is:

declare @PortalID int = 0;

SELECT R.*,
    (SELECT COUNT(*) 
     FROM   dbo.[UserRoles] U 
     WHERE  U.RoleID = R.RoleID 
            AND U.Status = 1 
            AND (U.EffectiveDate &lt; GETDATE() OR U.EffectiveDate IS NULL)
            AND (U.ExpiryDate &gt; GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
FROM  dbo.[Roles] R
WHERE (R.PortalId = @PortalID OR R.PortalId is null)
      AND (R.RoleId &gt;= 0) 
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalId UNKNOWN))


I'll be honest, I haven't used optimization options before. What's critical here is it's a multi-portal site so what I change I have to be absolutely sure it doesn't spring up a nasty gotcha down the road.

I'm assuming the execution plan has to accommodate potential nulls in the @PortalID that of which is a parameter within the stored procedure. My example code above is extracted and I'm setting the @PortalID to 0.

The core developers created this procedure, and response times to get assistance vary so I'm asking here. 

Is there a risk to changing this optimization to:

OPTION (OPTIMIZE FOR (@PortalId = 0))


This is the main portal used and is by far the most important. The core developers have to account for sites that have hundreds or even thousands of portals. 

Even so, changing this leaves no guarantee of future upgrades as they may overwrite it. Just trying to track down a problem and want to know of a fail-safe way to optimize this query.

So if I set:

OPTION (OPTIMIZE FOR (@PortalID = 0))


It runs fast... 10x faster.

But I'm uncertain of caching or other problems that may arise from changing this.
Testing the Impact that OPTION(OPTIMIZE FOR @PORTALID = 0) could have

Create the tables

CREATE TABLE dbo.[Roles]
(
RoleID INT PRIMARY KEY,
PortalId INT,
RoleName 
varchar(255)
)

CREATE TABLE DBO.[UserRoles]
(
UserRoleID INT IDENTITY(1,1),
RoleID INT FOREIGN KEY(RoleID) REFERENCES dbo.roles,
Status int default (1),
EffectiveDate datetime2,
ExpiryDate datetime2
)


Insert some data into roles

INSERT INTO DBO.Roles(RoleID,PortalId,RoleName) 
VALUES(1,0,'Role1') -- 1 value with portal id = 0


DECLARE @ID INT = 2
WHILE @ID &lt; 1000
BEGIN
INSERT INTO DBO.Roles(RoleID,PortalId,RoleName) 
VALUES(@ID,1,'Role2') -- 998 rows with @PortalID = 1

SET @ID += 1
END


Insert some data into UserRoles

DECLARE @ID INT = 1
WHILE @ID &lt; 1000 
BEGIN

INSERT INTO DBO.[UserRoles](RoleID,EffectiveDate,ExpiryDate)
VALUES(@ID,getdate(),NULL) -- one user role for each role id.

SET @ID += 1
END


Testing the query with @PortalID = 0 and @PortalID = 1

@PortalID = 0

SET STATISTICS IO, TIME ON;
declare @PortalID int = 0
SELECT R.*,
    (SELECT COUNT(*) FROM dbo.[UserRoles] U 
     WHERE U.RoleID = R.RoleID 
     AND U.Status = 1 
     AND (U.EffectiveDate &lt; GETDATE() OR U.EffectiveDate IS NULL)
     AND (U.ExpiryDate &gt; GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
 FROM dbo.[Roles] R
 WHERE (R.PortalId = @PortalID OR R.PortalId is null)
AND (R.RoleId &gt;= 0) 
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalId = 0))



The ususal scan's since we don't have indexes


The expectations match 100%

@PortalID = 1

declare @PortalID int = 1
SELECT R.*,
    (SELECT COUNT(*) FROM dbo.[UserRoles] U 
     WHERE U.RoleID = R.RoleID 
     AND U.Status = 1 
     AND (U.EffectiveDate &lt; GETDATE() OR U.EffectiveDate IS NULL)
     AND (U.ExpiryDate &gt; GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
 FROM dbo.[Roles] R
 WHERE (R.PortalId = @PortalID OR R.PortalId is null)
AND (R.RoleId &gt;= 0) 
ORDER BY R.RoleName
OPTION (OPTIMIZE FOR (@PortalId = 0))




That's gonna hurt on bigger tables


Low expectations

What if we run it without the optimize for? Spoiler: (This creates a new execution plan)

declare @PortalID int = 1
SELECT R.*,
    (SELECT COUNT(*) FROM dbo.[UserRoles] U 
     WHERE U.RoleID = R.RoleID 
     AND U.Status = 1 
     AND (U.EffectiveDate &lt; GETDATE() OR U.EffectiveDate IS NULL)
     AND (U.ExpiryDate &gt; GETDATE() OR U.ExpiryDate IS NULL)) AS UserCount
 FROM dbo.[Roles] R
 WHERE (R.PortalId = @PortalID OR R.PortalId is null)
AND (R.RoleId &gt;= 0) 
ORDER BY R.RoleName



That's better

TL;DR

As is usual, YMMV. 

What will happen when implementing something like this depends on the indexes, the data, and all the other things SQL Server takes into account when creating an execution plan. How much the query is executed with certain parameters, ....

This is just to show what might happen if you implement OPTION (OPTIMIZE FOR (@PortalId = 0)) on a production system. 
In the end it is up to you to test the possible outcomes, and what is best for your system. 
You could opt to use the query as it is and deal with parameter sniffing, to use optimize for unkown so the SQL Server engine will use stats and other heuristics to optimize its execution plan, to use OPTION(RECOMPILE) as to not cache the plan but create it on each execution, to use OPTION( OPTIMIZE FOR @PortalId = 0), but deal with queries that using a different value, as they might go slower, ... .