James Wiseman

Learned something really useful today….

The Problem

Recently I encountered a problem whereby an early-morning database merge background process kept overrunning

A series of stored procedures where taking a series of records from some SQL Server tables, and then merging them across to related table, i.e. applying all records marked as ‘inserts’, ‘updates’ and ‘deletes’. In other words:

MERGEDATA_TABLE1 —Merge–> TABLE1
MERGEDATA_TABLE2 —Merge–> TABLE2

On some days, the stored procedures that were merging larger tables were starting to take significantly longer. Merging 200,000 records into a table 180,000,000 in size took 40 seconds (on a pretty high spec server). However, merging 400,000 (double) into the same table took 40 minutes – sixty times as long for twice the number of records.

The Cause

When the stored procedures were applied to the database, a query plan was generated for them. The optimisations for these plans were based on the content of the tables at that time.

In particular, each of the MERGEDATA_ tables would have been empty, so the optimisations and query plans would have been completely unsuitable.

Static query plans are more suited to stored procedures that are run frequently, and that query tables that do not have vast fluctuations in size.

The Solution

Placing WITH RECOMPILE at the top of each stored procedure to force a recompile whenever it was executed seemed fixed the problem.

This was fine as we were only going to call the procedures once a day. If we were going to call them once a second then we may have had to rethink.

Code

CREATE PROCEDURE Table1_Merge

WITH RECOMPILE

AS