When Hash Match on Merge takes FOREVER!

Let’s face it, we all have multiple humongous procs that take ages to execute, and are merging a bunch of tables with a bunch of temp tables.

When it works, it’s all wine and roses, but what if it doesn’t work? It always work until it does not 😦

So, here’s the scenario, you have a proc that always work but suddenly ( change cause of the histogram , wink wink – we will talk about that later) affects the execution plan and suddenly instead of a loop join we get a HASH GROUP operator, oh gosh, why? It’s Friday , and you want to go home – Well, we have a perfect fix.

So here’s something to recreate the problem.

USE AdventureWorks2014
GO
SELECT * FROM HumanResources.Department AS d
GO
/* check if it exists */
IF OBJECT_ID ('HumanResources.vDepartment', 'view') IS NOT NULL
DROP VIEW HumanResources.vDepartment ;
IF OBJECT_ID('tempdb..#TempDepartments') IS NOT NULL
DROP TABLE #TempDepartments;
GO
/* Create the view */

CREATE VIEW HumanResources.vDepartment
WITH SCHEMABINDING AS
SELECT DepartmentID, NAME, GroupName, ModifiedDate FROM HumanResources.Department

GO
/* Index the view */
CREATE UNIQUE CLUSTERED INDEX UCIX_HumanResources_vDepartment
ON HumanResources.vDepartment (DepartmentID);
GO

/* Check the first Plan */
UPDATE TOP (SELECT 100) HumanResources.Department SET NAME = CAST(NEWID() AS NVARCHAR(50))

GO
/* Create the type */
CREATE TYPE TVP_Department AS TABLE (DepartmentID SMALLINT, NAME NVARCHAR(50), GroupName NVARCHAR(50))
GO

/* Load some Data */

DECLARE @Departments TVP_Department
;WITH CTE AS
(
SELECT 100 AS A
UNION ALL
SELECT A-1 FROM CTE WHERE A>0

)
,ROWS AS
(
SELECT TOP 300 0 A FROM CTE,CTE X1,CTE X2
)
/* Reference the type */

INSERT INTO @Departments
(
-- DepartmentID -- this column value is auto-generated
DepartmentID, NAME, GroupName
)
select ABS(Checksum(NewID()) % 32767) AS DepartmentID, CAST(NEWID() AS NVARCHAR(50)) AS Name, CAST(NEWID() AS NVARCHAR(50)) AS GroupName from cte m,ROWS

SELECT * INTO #TempDepartments FROM @Departments
;WITH CTE2 AS(
SELECT DepartmentID,
RN = ROW_NUMBER()OVER(PARTITION BY DepartmentID ORDER BY DepartmentID DESC)
FROM #TempDepartments
)

DELETE FROM CTE2 WHERE RN > 1

CREATE UNIQUE CLUSTERED INDEX UCIX_#TempDepartments
ON #TempDepartments (DepartmentID);
SET IDENTITY_INSERT HumanResources.Department ON
MERGE HumanResources.Department TARGET
USING #TempDepartments Source
ON TARGET.DepartmentID = Source.DepartmentID
WHEN NOT MATCHED BY TARGET THEN
INSERT (DepartmentID, NAME, GroupName)
VALUES (Source.DepartmentID, Source.NAME,Source.GroupName);
SET IDENTITY_INSERT HumanResources.Department OFF

Well, once we set it up, we hit the most amazing button( F5), and we get the circle of despair.

Test3

Luckily for us, we’ve got the perfect antidote!

</pre>
SET IDENTITY_INSERT HumanResources.Department ON
MERGE HumanResources.Department TARGET
USING #TempDepartments Source
ON TARGET.DepartmentID = Source.DepartmentID
WHEN NOT MATCHED BY TARGET THEN
INSERT (DepartmentID, NAME, GroupName)
VALUES (Source.DepartmentID, Source.NAME,Source.GroupName)
option (loop join);
SET IDENTITY_INSERT HumanResources.Department OFF

This could be caused due to a histogram change and really crappy allocation ( going down from 168 to 42, or just messed up data key range), or a materialized view which is indeed sometime a pure evil 😦

Now, we can safely go back home and continue playing World of Warcraft, or whatever that is that you pretend is better.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s