Tsql Query speed slow due to Or within where clause, causing index scan instead of seek
This is what I call the "kitchen sink" stored procedure - you want one procedure that handles all possible combinations of search criteria that a user might enter. It is virtually impossible to make SQL Server derive a single execution plan that will be optimal and efficient for all of these combinations - I don't care what kind of tricks you might think ISNULL can pull that COALESCE or OR can't.

The solutions I usually attempt, in this order, are:


Add OPTION (RECOMPILE) to the query. Yes, you pay a compilation cost every time, but you will get the right plan given the parameters that are supplied and their values.
Use dynamic SQL. Now you will be able to cache multiple different plans based on the different parameters passed. Combine this with the server-level setting optimize for ad hoc workloads (more info from Kimberly Tripp here and here) so that only versions of the plan that are used more than once are fully cached. Example:

DECLARE
  @PERSON_ID         INT = 0,
  @ITEM_ID           INT = 0,
  @ITEM_VERSION      INT = 1,
  @ITEM_SUB_NAME     VARCHAR(250),
  @ITEM_SUB_SUB_NAME VARCHAR(250);

DECLARE @sql NVARCHAR(MAX) = N'',

SET @sql = N'SELECT ID, PERSON_ID, 
   ISNULL(ITEM_VERSION, 1) AS ITEM_VERSION,
   ISNULL(ITEM_SUB_NAME, '''') AS ITEM_SUB_NAME,
   ISNULL(ITEM_SUB_SUB_NAME, '''') AS ITEM_SUB_SUB_NAME,
   ISNULL(ITEM_DATE, ''19000101'') AS ITEM_DATE
FROM dbo.PERSON_TBL AS s WITH (NOLOCK)     
WHERE ITEM_VERSION ' 
+ CASE WHEN @ITEM_VERSION &lt;&gt; 1 THEN 
   N' = @ITEM_VERSION' ELSE N' IS NULL' END
+ CASE WHEN @PERSON_ID &lt;&gt; 0 THEN 
   N' AND PERSON_ID = @PERSON_ID' ELSE N'' END
+ CASE WHEN @EMPLOYEE_ID &lt;&gt; 0 THEN
   N' AND EMPLOYEE_ID = @EMPLOYEE_ID' ELSE N'' END
+ CASE WHEN @ITEM_SUB_NAME IS NOT NULL THEN
   N' AND ITEM_SUB_NAME = @ITEM_SUB_NAME' ELSE N'' END
+ CASE WHEN @ITEM_SUB_SUB_NAME IS NOT NULL THEN
   N' AND ITEM_SUB_SUB_NAME = @ITEM_SUB_SUB_NAME' ELSE N'' END
ORDER BY PERSON_ID, ITEM_SUB_NAME;';

EXEC sys.sp_executesql @sql,
  N'@PERSON_ID INT, @ITEM_ID INT, @ITEM_VERSION INT,
    @ITEM_SUB_NAME VARCHAR(250), @ITEM_SUB_SUB_NAME VARCHAR(250)',
  @PERSON_ID, @ITEM_ID, @ITEM_VERSION, 
  @ITEM_SUB_NAME, @ITEM_SUB_SUB_NAME;



If you find that parameter sniffing is still an issue even when you have individual plans for each combination of parameters provided (due to vastly different cardinality based on the actual values), you can add OPTION (RECOMPILE) inside the dynamic SQL, too - you once again pay the compile cost but it should be better when you have a much simpler where clause.

Some resources about this pattern:


The "Kitchen Sink" procedure (video)
#BackToBasics: An Updated Kitchen Sink Example


Paul White has a great post worth reading too:


Parameter Sniffing, Embedding, and the RECOMPILE Options


As an aside:


Stop using single quotes for alias delimiters. It makes them look like strings and is deprecated in some scenarios, never mind that delimiters are not even necessary here. (I talk about this issue here.)
Stop using ambiguous, regional date formats like m/d/yyyy (or is that d/m/yyyy?).
Always use the schema prefix.
Be very careful about using NOLOCK as a magic turbo button.
My thoughts on COALESCE vs. ISNULL.