Over the years I have come across a lot of merge statements and I find most of the time there is one fundamental flaw in how it is written by most people.
In the update section in most cases I find that there is no section there to check if anything has changed against the target from the source, therefore everything gets updated each and every single time. Whereas if there was this section then only the records that changed would be updated. This saves a lot of time on performance of the query.
Here is an example of a merge that checks if something is changed between source and target before updating the record:
MERGE dbo.DimEmployee TRG USING Staging.Employee SRC ON TRG.EmployeeID = SRC.EmployeeID WHEN MATCHED AND ( ISNULL(SRC.FirstName,'Unknown') != ISNULL(TRG.FirstName,'Unknown') OR ISNULL(SRC.LastName,'Unknown') != ISNULL(TRG.LastName,'Unknown') OR ISNULL(SRC.MiddleName,'Unknown') != ISNULL(TRG.MiddleName,'Unknown') OR ISNULL(SRC.NameStyle,'Unknown') != ISNULL(TRG.NameStyle,'Unknown') OR ISNULL(SRC.Title,'Unknown') != ISNULL(TRG.Title,'Unknown') ) THEN UPDATE SET TRG.FirstName = SRC.FirstName ,TRG.LastName = SRC.LastName ,TRG.MiddleName = SRC.MiddleName ,TRG.NameStyle = SRC.NameStyle ,TRG.Title = SRC.Title WHEN NOT MATCHED THEN INSERT ( FirstName ,LastName ,MiddleName ,NameStyle ,Title ) VALUES ( SRC.FirstName ,SRC.LastName ,SRC.MiddleName ,SRC.NameStyle ,SRC.Title )
Hope this helps you improve the performance of you MERGE statements.