There's a long delay in this query that I need to resolve
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, ... .