OPTIMIZE FOR UNKNOWN when doing a UNION

Question

So I have a few really slow queries in SP's I need to speed up, I have been using OPTIMIZE FOR UNKNOWN and seeing some dramatic increases in performance. I still have one query that really is slow and I want to apply this but it contains a UNION of 2 SQL Queries, so my question is do I apply the OPTIMIZE FOR UNKNOWN to both?

THIS IS A GREATLY SIMPLIFIED VERSION OF MY SP FOR CLARITY:

SELECT * FROM MyTable ManufacturerID=@ManufacturerID and tStamp > @tStamp
OPTION (OPTIMIZE FOR (@ManufacturerID UNKNOWN, @tStamp UNKNOWN)) 
UNION
SELECT * FROM MyTable ManufacturerID=@ManufacturerID
OPTION (OPTIMIZE FOR (@ManufacturerID UNKNOWN) 

Here is the actual SP as it stand right now:

SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY Products.ItemID) AS RowNum, *
    FROM
    (
    SELECT  Products.ProductID, Products.ItemID, Products.ManufacturerID,
          CategoryID = NULL, CategoryName = NULL, 
          CategoryProductID = NULL,  Products.ItemName, Products.Description, Products.Notes, 
                      Products.Dimensions, Products.BasePrice, Products.OrderMinimumQuantity, 
                      ContainerMinimumQuantity = 
                        CASE  COALESCE(Products.ContainerMinQty, 0)
                            WHEN 0 THEN Products.OrderMinimumQuantity
                            ELSE Products.ContainerMinQty
                        END 
                      , Products.OrderMultipleQuantity, Products.OnHandQuantity, 
                      Products.Category, Products.IntroDate, Products.BackOrderDate, Products.UPC, Products.PriceLevel1, Products.PriceLevel2, Products.PriceLevel3, 
                      Products.PriceLevel4, Products.PriceLevel5, Products.PriceLevel6, Products.PriceLevel7, Products.PriceLevel8, Products.PriceLevel9, Products.PieceBox, 
                      Products.Cubes, Products.UnitOfMeasure, Products.UDF1, Products.UDF2, Products.UDF3, Products.UDF4, Products.UDF5, Products.AdditionalImageCount, 
                     PhotoName= LOWER(Products.PhotoName),  Products.Discontinued, Products.ModifiedOn, 
                        Products.IsDeleted, Products.PriceLevel10, Products.PriceLevel11, Products.PriceLevel12, Products.PriceLevel13, 
                      Products.PriceLevel14, Products.PriceLevel15, Products.PriceLevel16, Products.PriceLevel17, Products.PriceLevel18, Products.PriceLevel19, Products.PriceLevel20, 
                      Products.Weight, Products.DimensionsMetric, Products.Source, Products.InventoryStatus, Products.CatalogCode, Products.CatalogName, 
                       SortOrder = NULL,
                       Products.reportCategory,Products.additionalPhotos,Products.udf6,Products.udf7,Products.udf8,
                       Products.udf9,Products.udf10,Products.udf11,Products.udf12,Products.udf13,Products.udf14,Products.udf15,Products.udf16,
                       Products.udf17,Products.udf18,Products.udf19,Products.udf20, Products.showRelatedFor,Products.showChildFor
FROM    
CategoryProducts (nolock) RIGHT OUTER JOIN
                      Products (nolock) ON CategoryProducts.ManufacturerID = Products.ManufacturerID AND CategoryProducts.ItemID = Products.ItemID
WHERE     (Products.ManufacturerID = @ManufacturerID)
            AND 
            (Products.ModifiedOn > @tStamp ) AND  ((CategoryProducts.IsDeleted = 1) OR (CategoryProducts.IsDeleted IS NULL)
            )

            UNION

            SELECT Products.ProductID, Products.ItemID, Products.ManufacturerID, Categories.CategoryID, Categories.CategoryName, CategoryProducts.CategoryProductID,  Products.ItemName, Products.Description, Products.Notes, 
                      Products.Dimensions, Products.BasePrice, Products.OrderMinimumQuantity, 
                      ContainerMinimumQuantity = 
                        CASE  COALESCE(Products.ContainerMinQty, 0)
                            WHEN 0 THEN Products.OrderMinimumQuantity
                            ELSE Products.ContainerMinQty
                        END 
                      , Products.OrderMultipleQuantity, Products.OnHandQuantity, 
                      Products.Category, Products.IntroDate, Products.BackOrderDate, Products.UPC, Products.PriceLevel1, Products.PriceLevel2, Products.PriceLevel3, 
                      Products.PriceLevel4, Products.PriceLevel5, Products.PriceLevel6, Products.PriceLevel7, Products.PriceLevel8, Products.PriceLevel9, Products.PieceBox, 
                      Products.Cubes, Products.UnitOfMeasure, Products.UDF1, Products.UDF2, Products.UDF3, Products.UDF4, Products.UDF5, Products.AdditionalImageCount, 
                     PhotoName= LOWER(Products.PhotoName),  Products.Discontinued, Products.ModifiedOn, 
                        CategoryProducts.IsDeleted, Products.PriceLevel10, Products.PriceLevel11, Products.PriceLevel12, Products.PriceLevel13, 
                      Products.PriceLevel14, Products.PriceLevel15, Products.PriceLevel16, Products.PriceLevel17, Products.PriceLevel18, Products.PriceLevel19, Products.PriceLevel20, 
                      Products.Weight, Products.DimensionsMetric, Products.Source, Products.InventoryStatus, Products.CatalogCode, Products.CatalogName, 
                       CategoryProducts.SortOrder,
                       Products.reportCategory,Products.additionalPhotos,Products.udf6,Products.udf7,Products.udf8,
                       Products.udf9,Products.udf10,Products.udf11,Products.udf12,Products.udf13,Products.udf14,Products.udf15,Products.udf16,
                       Products.udf17,Products.udf18,Products.udf19,Products.udf20, Products.showRelatedFor,Products.showChildFor
FROM      Categories (nolock) INNER JOIN
                      CategoryProducts (nolock) ON Categories.CategoryID = CategoryProducts.CategoryID INNER JOIN
                      Products (nolock) ON CategoryProducts.ManufacturerID = Products.ManufacturerID AND CategoryProducts.ItemID = Products.ItemID
WHERE     (Products.ManufacturerID = @ManufacturerID)
            AND 
            (Products.ModifiedOn > @tStamp OR  CategoryProducts.ModifiedOn > @tStamp)
            )  AS Products
        ) AS C  WHERE RowNum >= @StartRow AND RowNum <= @EndRow ORDER BY ItemID, ManufacturerID
    OPTION (OPTIMIZE FOR (@ManufacturerID UNKNOWN, @StartRow UNKNOWN, @EndRow UNKNOWN, @tStamp UNKNOWN)) 

Answer 1

From Books Online (Query Hints):

Query hints specify that the indicated hints should be used throughout the query. They affect all operators in the statement. If UNION is involved in the main query, only the last query involving a UNION operation can have the OPTION clause.

So that's pretty explicit that you can only use OPTION once, not twice like in your example.

That makes sense, since even though there are two SELECT statements put together, SQL Server will still treat it as one overall statement for the purposes of compilation and optimization.

This does mean that if you want to use different hints in the different statements that make up the UNION you'll have to go about it a different way, e.g. using TEMP tables to store the interim results.

So, in your specific example, even though @tStamp is only in the first SELECT statement, you can still reference it in the one OPTION clause as required.

Answer 2

Try this one -

SELECT
      p.ProductID
    , RowNum = ROW_NUMBER() OVER(ORDER BY p.ItemID) 
    , p.ItemID
    , p.ManufacturerID
    , CategoryID = NULL
    , CategoryName = NULL
    , CategoryProductID = NULL
    , p.ItemName
    , p.[Description]
    , p.Notes
    , p.Dimensions
    , p.BasePrice
    , p.OrderMinimumQuantity
    , ContainerMinimumQuantity =
          CASE WHEN ISNULL(p.ContainerMinQty, 0) = 0
               THEN p.OrderMinimumQuantity ELSE p.ContainerMinQty
          END
    , p.OrderMultipleQuantity
    , p.OnHandQuantity
    , p.category
    , p.IntroDate
    , p.BackOrderDate
    , p.UPC
    ...
    , p.PieceBox
    , p.Cubes
    , p.UnitOfMeasure
    , p.UDF1, p.UDF2, p.UDF3
    , p.UDF4, p.UDF5
    , p.AdditionalImageCount
    , PhotoName = LOWER(p.PhotoName)
    , p.Discontinued
    , p.ModifiedOn
    , p.IsDeleted
    ...
    , p.[Weight]
    , p.DimensionsMetric
    , p.[Source]
    , p.InventoryStatus
    , p.CatalogCode
    , p.CatalogName
    , SortOrder = NULL
    , p.reportCategory
    , p.additionalPhotos
    ....
    , p.showRelatedFor
    , p.showChildFor
FROM dbo.Products p WITH(NOLOCK)
--LEFT JOIN (
--     SELECT *
--     FROM dbo.CategoryProducts cp WITH(NOLOCK)
--     WHERE ISNULL(cp.IsDeleted, 1) = 1
--) cp ON cp.ManufacturerID = p.ManufacturerID AND cp.ItemID = p.ItemID
WHERE p.ManufacturerID = @ManufacturerID
     AND p.ModifiedOn > @tStamp