Easy performance gain for data extensive process

This is one of our tuning successes, particularly for the nightly process of loading data into a report server from the main database. Quite a basic change had a significant impact saving a lot of resources making the process much faster more stable and at the end of the day making our customers happier making them worry less about it.

We knew the exact code that needs to be tuned and we also had access to the test environment where we have done our analysis and test runs.

The solution consisted of 2 covering indexes for the disk tables, which were missing by the data load.
The process uses a temp table to load the main data and then run a few calculations.
The originally used clause

SELECT column1,  
       column2
  INTO #temptable
  FROM diskBasedTable

was replaced by the best practice solution (although lazy developers don’t like it) to create the temp table using proper column declaration.

What else we have done:

• we added a clustered index to this table to help with all the calculations, which run on this table and not having any index meant the table including millions of rows was scanned entirely multiple times. No index on a large table was a trouble for a Query optimizer while joining this temporary table to large a disk table, so adding this index allowed a much better plan even for the joined tables.
• One additional step was also made as a simple refactoring to remove queries from the SELECT clause into two separate queries.

The changes resulted in the following changes in the execution plan (please consider it only as a highlight of the changes, can’t make it any better to view).

Figure 1: Original plan 

Figure 2 – 4: Plans of the refactored query. Significantly simpler, much easier to manage.

To summarise the outcome of our tuning here are a few figures describing the impact.

The total running time

Dropped from 4-6hrs to 1hr 50mins. After the changes the duration of the whole process is stable, and it rarely runs longer than 2 hours. Having a shorter duration means the process does not interfere with other processes running later at night or even in the early morning.

Plan costs

Initially, the costs of the plan were 31690. After the index changes and the refactoring, we ended with 3 plans of costs of 1518, 142 and 74. This is about 20 times less.

IO stats for one of the main table used in the major query that has been changed and at the end divided into one main and two simple queries;

• Before: Table ‘ABC’. Scan count 47915, logical reads 11372625, physical reads 46, read-ahead reads 3207071, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
• After: Table ‘ABC’. Scan count 18, logical reads 460741, physical reads 39, read-ahead reads 229294, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Beyond the simple verification through the SQL agent history to check the running times we were able to see the drop in SMT as well. One of those options how to verify this was to compare the wait statistics from the period before and after the change.

Figure 5: before waits statistics covering the time when the tuned process starts

Figure 6: after waits statistics covering the time when the tuned process starts

We can see that after we tuned the process CXPACKET wait type dropped more than by half and also the following intervals of the waits on the server are smaller. The CXPACKET was here the most prevailing wait type because the process was so unnecessarily expensive

Final note; as part of our process all the changes were first suggested to our customer, who managed with their supplier to verify the logical changes and update the code in production where it has been proved to be working and useful.