title: OPTIMIZE FOR UNKNOWN when doing a UNION
body: <p>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?  </p>
  
  <p><strong>THIS IS A GREATLY SIMPLIFIED VERSION OF MY SP FOR CLARITY:</strong></p>
  
  <pre><code>SELECT * FROM MyTable ManufacturerID=@ManufacturerID and tStamp &gt; @tStamp
  OPTION (OPTIMIZE FOR (@ManufacturerID UNKNOWN, @tStamp UNKNOWN)) 
  UNION
  SELECT * FROM MyTable ManufacturerID=@ManufacturerID
  OPTION (OPTIMIZE FOR (@ManufacturerID UNKNOWN) 
  </code></pre>
  
  <p>Here is the actual SP as it stand right now:</p>
  
  <pre><code>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 &gt; @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 &gt; @tStamp OR  CategoryProducts.ModifiedOn &gt; @tStamp)
              )  AS Products
          ) AS C  WHERE RowNum &gt;= @StartRow AND RowNum &lt;= @EndRow ORDER BY ItemID, ManufacturerID
      OPTION (OPTIMIZE FOR (@ManufacturerID UNKNOWN, @StartRow UNKNOWN, @EndRow UNKNOWN, @tStamp UNKNOWN)) 
  </code></pre>