Query runs quickly on MS SQL Server 2016, but takes forever on identical tables in 2008 R2

Hello Experts,
I wrote a query that was bogging down my 2008 R2 production R2 server, so I copied the DB to my test 2016 server where it runs in about 45 seconds. I beefed up my 2008 server hardware to match the 2016 and ran the query on it overnight on off-peak. When I checked it over 13 hours later, it still hadn't finished. Is there something drastically better in the 2016 engine or is there something I can do about this? Both copies of the DB tables have the same indices.

There may very well be a more efficient way that provides the same results which I am open to, but I am also curious on the 2008 vs 2016 thing.

The unusual variables are used here to make sure datatypes in WHERE clauses match the table field types, avoiding implicit conversions / table scans.

DECLARE
	@DateToday SMALLDATETIME,
	@DateL4 SMALLDATETIME,
	@DateL5 SMALLDATETIME,
	@DateL9 SMALLDATETIME,
	@DateL10 SMALLDATETIME,
	@DateL14 SMALLDATETIME,
	@DateL15 SMALLDATETIME,
	@DateL365 SMALLDATETIME,
	@Zero money

SET	@DateToday = CAST(GETDATE() AS DATE)
SET	@DateL4 = DATEADD(dd,-4,@DateToday)
SET	@DateL5 = DATEADD(dd,-5,@DateToday)
SET	@DateL9 = DATEADD(dd,-9,@DateToday)
SET	@DateL10 = DATEADD(dd,-10,@DateToday)
SET	@DateL14 = DATEADD(dd,-14,@DateToday)
SET	@DateL15 = DATEADD(dd,-15,@DateToday)
SET	@DateL365 = DATEADD(dd,-365,@DateToday)
SET @Zero = CAST(0 as money)
	
SELECT
	TableProducts.ProductCategoryCode,
	TableProducts.ProductCode,
	TableProducts.WarehouseCode,
	TableProducts.Description1,
	SUM(TableProductLots.OnHand_Stk) AS Total_Qty_Stk,
	SUM(TableProductLots.OnHand_Alt) AS Total_Qty_Alt,
	--SUM([q0-4].OnHand_Stk) AS [0-4_Qty_Stk],
	SUM([q0-4].OnHand_Alt) AS [0-4_Qty_Alt],
	--SUM([q5-9].OnHand_Stk) AS [5-9_Qty_Stk],
	SUM([q5-9].OnHand_Alt) AS [5-9_Qty_Alt],
	--SUM([q10-14].OnHand_Stk) AS [10-14_Qty_Stk],
	SUM([q10-14].OnHand_Alt) AS [10-14_Qty_Alt],
	--SUM([q15+].OnHand_Stk) AS [15+_Qty_Stk],
	SUM([q15+].OnHand_Alt) AS [15+_Qty_Alt]
FROM
	Database..TableProducts
	INNER JOIN Database..TableProductLots
		ON TableProductLots.ProductKey = TableProducts.ProductKey
	LEFT JOIN (
	
		SELECT
			TableProductLots.ProductKey,
			--TableProductLots.OnHand_Stk,
			TableProductLots.OnHand_Alt
		FROM
			Database..TableProductLots
		WHERE
			TableProductLots.OnHand_Alt <> @Zero
			AND TableProductLots.ProductionDate < @DateToday
			AND TableProductLots.ProductionDate >= @DateL4
	) AS [q0-4]
		ON [q0-4].ProductKey = TableProducts.ProductKey
	
	LEFT JOIN (
	
		SELECT
			TableProductLots.ProductKey,
			--TableProductLots.OnHand_Stk,
			TableProductLots.OnHand_Alt
		FROM
			Database..TableProductLots
		WHERE
			TableProductLots.OnHand_Alt <> @Zero
			AND TableProductLots.ProductionDate < @DateL5
			AND TableProductLots.ProductionDate >= @DateL9
	) AS [q5-9]
		ON [q5-9].ProductKey = TableProducts.ProductKey
	
	LEFT JOIN (
	
		SELECT
			TableProductLots.ProductKey,
			--TableProductLots.OnHand_Stk,
			TableProductLots.OnHand_Alt
		FROM
			Database..TableProductLots
		WHERE
			TableProductLots.OnHand_Alt <> @Zero
			AND TableProductLots.ProductionDate < @DateL10
			AND TableProductLots.ProductionDate >= @DateL14
	) AS [q10-14]
		ON [q10-14].ProductKey = TableProducts.ProductKey
	
	LEFT JOIN (
	
		SELECT
			TableProductLots.ProductKey,
			--TableProductLots.OnHand_Stk,
			TableProductLots.OnHand_Alt
		FROM
			Database..TableProductLots
		WHERE
			TableProductLots.OnHand_Alt <> @Zero
			AND TableProductLots.ProductionDate < @DateL15
			AND TableProductLots.ProductionDate >= @DateL365
	) AS [q15+]
		ON [q15+].ProductKey = TableProducts.ProductKey
	
WHERE
	TableProductLots.OnHand_Alt <> @Zero
	AND TableProductLots.ProductionDate >= @DateL365
GROUP BY
	TableProducts.ProductCategoryCode,
	TableProducts.ProductCode,
	TableProducts.WarehouseCode,
	TableProducts.Description1

;

Open in new window


I added an index to TableProducts with ProductKey as the key column and ProductCode, WarehouseCode, Description1, and ProductCategoryCode as Included Columns.

I added an index to TableProductLots with ProductKey as the key column and ProductionDate, OnHand_Stk, and OnHand_Alt as Included Columns.

2016 query est ex plan:
2016 query est ex plan
2008 query est ex plan:
2008 query est ex plan
FilonowstAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Verify the memory configuration for both SQL Server instances, specially the max server memory property.

NOTE: Providing Query Execution plans as images is almost useless. If possible provide them as .sqlplan files so we can load it in our SSMS for analysis.
0
Scott PletcherSenior DBACommented:
You can do all the totals in a single pass of the Lots table, which should be much faster:

SELECT
      TableProducts.ProductCategoryCode,
      TableProducts.ProductCode,
      TableProducts.WarehouseCode,
      TableProducts.Description1,
      Lots.[0-4_Qty_Alt],
      Lots.[5-9_Qty_Alt],
      Lots.[10-14_Qty_Alt],
      Lots.[15+_Qty_Alt]
FROM [Database]..TableProducts
INNER JOIN (      
            SELECT
                  TableProductLots.ProductKey,
                  --TableProductLots.OnHand_Stk,
                 SUM(TableProductLots.OnHand_Stk) AS Total_Qty_Stk,
              SUM(TableProductLots.OnHand_Alt) AS Total_Qty_Alt,
                  SUM(CASE WHEN TableProductLots.ProductionDate < @DateToday
                      AND TableProductLots.ProductionDate >= @DateL4  THEN TableProductLots.OnHand_Alt ELSE 0 END) AS [0-4_Qty_Alt],
                  SUM(CASE WHEN TableProductLots.ProductionDate < @DateL5
                      AND TableProductLots.ProductionDate >= @DateL9  THEN TableProductLots.OnHand_Alt ELSE 0 END) AS [5-9_Qty_Alt],
                  SUM(CASE WHEN TableProductLots.ProductionDate < @DateL10
                      AND TableProductLots.ProductionDate >= @DateL14 THEN TableProductLots.OnHand_Alt ELSE 0 END) AS [10-14_Qty_Alt],
            SUM(CASE WHEN TableProductLots.ProductionDate < @DateL15  THEN TableProductLots.OnHand_Alt ELSE 0 END) AS [15+_Qty_Alt]
            FROM
                  [Database]..TableProductLots
            WHERE
                  TableProductLots.OnHand_Alt <> @Zero AND
            TableProductLots.ProductionDate >= @DateL365
        GROUP BY
                  TableProductLots.ProductKey
) AS Lots
    ON Lots.ProductKey = TableProducts.ProductKey


As to the existing query speed, merge joins are extraordinarily fast when applicable.  As Vitor noted, we would have to see the actual query plan, and the DDL , including index defs, to know why the SQL 2008 version didn't use merge joins also.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
FilonowstAuthor Commented:
Thank you very much. The once 13+ hour query now runs in 1:39 on my 2008 R2 box.

I didn't know that doing:

SUM(
     CASE WHEN TableProductLots.ProductionDate < @DateToday AND TableProductLots.ProductionDate >= @DateL4
          THEN TableProductLots.OnHand_Alt ELSE 0
          END
) AS [0-4_Qty_Alt]

eliminated the need for the logical operands (i.e. ProductionDate) to be included the Group By, which was why I structured the query the way I did initially. I had always done this:

CASE WHEN TableProductLots.ProductionDate < @DateToday AND TableProductLots.ProductionDate >= @DateL4
     THEN SUM( TableProductLots.OnHand_Alt )
     END AS [0-4_Qty_Alt]

which requires the logical operands to be included the Group By.


Is the ELSE 0 required?
0
Scott PletcherSenior DBACommented:
No, it's not. But it does help prevent annoying warning msgs about "null values are ignored in aggregate functions".  I just have a habit of using 0 there.
0
FilonowstAuthor Commented:
Thanks again
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.