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.
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.