High Performing Big Table Loads in SSIS

If you are trying to load a very large SQL Server table with fast load, you might be thwarted by not having the rows per batch/commit that you think you have.

Here is a rather vanilla fast table load destination using SSIS data flow task. Notice how I have Rows per batch=200,000 and Maximum insert commit size=200,000. You might expect that the fast load will insert about 200,000 rows per batch and transaction. You would be wrong, as it is also gated by the buffer size of the data flow task. To determine how many rows are being inserted per batch/transaction, simply use SQL Profiler and trace the INSERT BULK commands. They are simply SQL:BatchCompleted events. To see the number of rows, look at the RowCounts column in the trace. It will probably be a little less than 10,000.

Firstly why is 10,000 rows per commit slow? If you have no indexes on your large table, the chances are that the loading is very fast. However, if there are non clustered indexes, the loading is slowed down by sorting the batch records into each index sequence and applying them to the indexes. Applying the keys to the index could touch up to 10,000 pages. Then the updates are committed, requiring writing out to disk all updates. Increasing the commit size can make updating indexes more efficient. Note, the performance does not get forever faster as the commit size increases. There is a memory cost with the large commit sizes and sorting larger groups of records increases at a greater then linear rate. To determine the best size for your task, run and monitor several sizes and plot their speeds. Choose the buffer size (batch/commit size) that maximizes load speed. Note, the optimal speed for a small table, will not be the same once the table becomes very large with indexes larger than db cache memory.

Also note, if you are loading a table from scratch, it can be more efficient to load the table with just a clustered index, then create the indexes in parallel after loading. Of course, partitioned tables will give you more flexibility in tuning your index build and loading.

Anyway, to increase the commit size, you will need to increase the Data Flow buffer size. See below, the DefaultBufferMaxRows is 10,000 and DefaultBufferSize is 10,485,760 bytes. Increasing these configuration settings to get larger commits.

(re) Clustering a Large Online Table

One of the most important performance controls you have on a relational database is the clustering sequence of your large tables. Unfortunately, it is often not considered until there is a performance issue. Clustered indexes are important on their own, but they are especially significant when you are joining multiple large tables. There are often orders of magnitude performance gains by clustering the large tables in the same sequence. However if you have a large table that needs to be online 24 hours a day, seven days a week, it can be difficult to make such a big change.

I have achieved this a few times now, on tables that are only inserted into (ie transaction tables). The only requirements are that the table is inserted into only (no updates) and that the table has a column that is inserted into in ascending sequence only (eg transactionid, datetime etc). Essentially, this is done by creating a new table, which has all the live table's records inserted into it using bulk load (SSIS package task). This makes it very efficient, but it will take a long time on a large table, so the new table won't be up to date. The trick is to keep low and high water marks, then you can open a transaction to copy in the last few records and rename the tables within the same transaction. In this way, at the point of committing, both tables will have the same content, and any active transactions will, at worst, wait a couple of seconds for the last insert and renaming to occur.

Here are the steps involved in the SSIS package

1. Create the new table with the clustered (and non clustered) indexes you want.
2. Create a control table to maintain high-low watermarks. Set the high water mark to the maximum ascending key value in the live table.
3. Perform a bulk load (SSIS Data Flow task) for all the live table rows up to and including the high water mark. This task might take several hours.
4. Set the low water mark to the high water mark, then re set the high water mark to the new maximum ascending key value in the live table.
5. Perform the bulk load again. This time it will just be the rows that have been inserted during our earlier bulk load. After this task, the new table will be very close, but not quite, up to date to the live table.
6. Within the scope of a transaction lock the live table, insert records above the high water mark, rename the tables (and PKs) and commit the transaction. The new table is now live.

A few notes on the process

• You will need to have sufficient space in your database to accommodate two copies of the table.
• You will need to have sufficient space in your database logs to hold log records from the start of the Main Bulk Load to the end of the Main Bulk Load. So, if the bulk load takes 4 hours, their will be a transaction open for 4 hours and a log truncation won't truncate anything within the 4 hours.
• You will need to have sufficient space in tempdb to perform the sorts for the clustered index and nonclustered indexes.
• If you have sufficient space in the database files and logs, you should set the maximum commit size on the destination to 0 (zero). Having other values will tend to slow down the load process (although less disk space is required) since the indexes will be maintained at each commit.
• It is quite likely that your large tables are partitioned and compressed. This doesn't need to make any difference to the reclustering process above. However, if the table is already partitioned, you could improve performance by looping through each partition. If your table isn't already partitioned, this process could be the opportunity to partition your table.
• Don't be tempted to use NOLOCK when reading the live table.
• Test your package before you run it in production.

There is no special code in the package I have created. The most significant task is the very last one, which is performed within the scope of a transaction. To ensure there is no opportunity of a deadlock, the task first asks for an X lock on the live table. Here is some sample SQL from each of the tasks.

Create Control table

create table ReclusteringControl( ReclusteringControlID int default 1, LowWater bigint not null default 0, HighWater bigint not null default 0, CONSTRAINT [PK_ReclusteringControl] PRIMARY KEY NONCLUSTERED (ReclusteringControlID ASC))

Insert into ReclusteringControl values(1,0,0)

update ReclusteringControl set HighWater=(select max(rid) from MyLargeTable)

Input for Main Bulk Load

select * from MyLargeTablewhere rid<=(select HighWater from ReclusteringControl)

Set New HighWatermark

update ReclusteringControl set LowWater=HighWater
update ReclusteringControl set HighWater=(select max(rid) from MyLargeTable)

Second Bulk Load (input statement)

select * from MyLargeTablewhere rid<=(select HighWater from ReclusteringControl) and rid>(select LowWater from ReclusteringControl)

Final INSERT and Rename

--set identity_insert MyLargeTable_new on

--This is required if there is an identity column on the table

GO

begin transaction

update ReclusteringControl

set LowWater=HighWater

select top 0 * from MyLargeTable with (tablockx)

update ReclusteringControl set HighWater=(select max(rid) from MyLargeTable)

insert into MyLargeTable_new --columns need to be explicitly declared if identity cols are inserted

select * from MyLargeTable where rid<=(select HighWater from ReclusteringControl) and rid>(select LowWater from ReclusteringControl);

exec sp_rename MyLargeTable,XXXX_MyLargeTable;--should delete this table

exec sp_rename MyLargeTable_new,MyLargeTable;

exec sp_rename PK_MyLargeTable, PK_MyLargeTable_XXXX

exec sp_rename PK_MyLargeTable_new, PK_MyLargeTable

commit transaction

--set identity_insert MyLargeTable off

SSIS Slowly Changing Dimension SCD Wizard is Too Slow

Pre SQL Server 2005, we had to write our own scripts for managing slowly changing dimensions (aka Type 2 dimensions). Since SQL Server 2005 we now have an easy to use SSIS wizard to maintain SCD. Unfortunately, the SSIS wizard generates an OLEDB update statement that is executed once for every record. Naturally the iterative statement is very slow with large volumes.


Hence if you have large volumes of data and need to have high performing SCD updates, you will need to use a batch update statement. This is not all that difficult. I would even suggest that if you can't write the SQL, you might not the be the best person to design a DW.

Essentially, you need to "close off" the records that are getting replaced, and "insert" all new records. The "closing" of records can be done in a single UPDATE statement as shown in the query below. Note, the exact script you need will depend on your SCD and non-SCD attributes and the format of your source data. But essentially, you are updating all records in the DW where there exists a newer record. If you have some Type 1 attributes, will need another update statement for them.

The second task is simply to insert the new (and updated) records. You should do this in an SSIS data flow task. The select statement in the image below, is an example of what the query will look like to feed that load. Note, your query will be a little different depending on how you identify changes, but the query below will give you a starting template.

By the way, if you really don't want to write the above queries (or prefer the elegance of a component) there are 3rd party SSIS components that manage SCD with set based operations.







Of course, I am sure a future version of SQL Server will have an SCD wizard that works with batch update commands. Just not sure which one.

The Transaction Manager is not available

A few things you need to know about SSIS TransactionOption "Required".

By default, SSIS packages do not require transactions, and will not coordinate transactions. That is because the default TransactionOption is "Supported". Supported means that if this package (or subordinate object) is called from another object that has TransactionOption "Required" then it will be part of the transaction. However, if you select "Required" as the package TransactionOption (or task that consumes multiple connections, such as a sequence container) then SSIS will use DTC to manage the transactions across multiple connections. This may not be immediately intuitive to you since you might only connect to a single SQL Server. However, with multiple connections, the only way SSIS can ensure they are all committed, or none are committed, is to use DTC (Distributed Transaction Coordinator).

If the SSIS package is running on the same server as the only SQL Server database, then it is quite simple, you only need to have DTC service running. However, if the SSIS package is running on a different machine to SQL Server (this includes running SSIS in dev mode locally and attaching to another SQL machine) then you will need to ensure that DTC can communicate across machines. Otherwise, you might get a message like these

Error: The SSIS Runtime has failed to start the distributed transaction due to error 0x8004D01B "The Transaction Manager is not available.". The DTC transaction failed to start. This could occur because the MSDTC Service is not running.

Error: The SSIS Runtime has failed to enlist the OLE DB connection in a distributed transaction with error 0x8004D00A "Unable to enlist in the transaction.".

• These error messages are, in essence, telling you that DTC cannot communicate with SQL Server. Here's what you need to check
  Ensure both computers can ping each other using netbios names. Netbios names must be used for DTC transactions.
• Both computers must have MSDTC running
• If there are firewalls on either machine, they must have exceptions for Port 135 or for the program msdtc.exe.

The dtcPing tool can be helpful in diagnosing DTC issues. For more information see http://support.microsoft.com/kb/918331

Now, whether or not you want to use large scope transactions is another question, and not a simple one to answer. A couple of points to think about when you are considering using transactions are

• Any update, insert etc to a rdmbs is either completed successfully or rolled back. Using TransactionOption "Required" as I discuss above is about combining many update/insert/delete statements so that they all succeed or none of them do.
• Be mindful of how long the transaction runs for. This is the time that another transaction might have to wait if there is a locking issue.
• There might be other ways to ensure your data integrity. For example, if you are thinking about transactions to enable simple re-running of the package in the event of an error, you might be able to structure the package so that it can re-run even after successful completion of some, or all, of the tasks.

Another point worth noting with SSIS transactions is that you can set TransactionOption of "Required" on the package, and have a task (such as Execute SQL) which has a TransactionOption of NotSupported. In this way, so long as the tasks executes, it will not be rolled back with the rest of the package in the event of a failure.

For a real-time demonstration of data that has SSIS packages loading/processing about 300 new records/second (all day, every day) see http://RichardLees.com.au/sites/Demonstrations

Restartable or Rerunnable SSIS Packages

BI solutions need to be continually importing/updating data from external source systems. I believe that a good BI solution will have automatically correcting load and process jobs. What I mean by that is if a package fails (such as a power or system failure) the application will automatically correct itself next time it runs. In the old days, we used to create "restartable" jobs, where, after a failure, the operator would manually intervene to restart the job with specific parameters. I think there is little excuse nowadays to have applications that require manual intervention after a failure. They should be designed in such a way that they “know” where they are up to and continue, or reprocess.

For example, my Permon application (Perfmon Dashboard) has a job that runs every 180 seconds to transform and load the new records, and incrementally process the OLAP cube. Every now and again the machine is restarted for some reason and the Perfmon update process could be at any point when it is stopped. However, the job will automatically run from the right place, next time it runs. It will know whether there is 3 minutes or 10 minutes of data to process, by using high water marks in the database. In this way, I have no operational overhead in running this application. It just looks after itself. It is the same for the weblogs BI solution on Weblogs Dashboard. If your BI solution requires manual intervention after a failure or an error, get someone else in, like myself, to fix it up to run automatically without manual intervention.

Now having said that, it is often useful to be able to pass parameters to an SSIS package. (I just needed to mention automatically correcting jobs above, as some developers make packages restartable,to help with manual correction, when it should be automatic.) For example, you might want to have the ability to ask the SSIS package to conditionally run one task. This can be achieved quite easily by assigning the Disable property on the task (container etc) using Expressions on the task to set it to a parameter. To pass the parameter it is simply a matter of adding /SET "\Package.Variables[DisableBackup]";False to the cmd for the job in SQL Agent.