Improving BCP / BULK INSERT Performance by Disabling Auto Statistics
One of the tasks regularly performed in ETL processes is using BCP or BULK INSERT to import large amounts of data into a empty database. You might be using these tools to import a single file into a table, or many files into a table. Either way, SQL Server is going to want to update the statistics used by the SQL Server Query Optimiser for decision making.
Its important to keep up-to-date statistics for the optimiser to use, but if your ETL process calls for populating an empty database before using the data, you may get a performance increase from turning off automatic creation / updating of statistics. Why? Because SQL Server could decide to create/update stats in the background while you are populating tables, thus degrading BCP / BULK INSERT performance. This problem would be most obvious if you import multiple files into a table. After each import, SQL Server would determine the statistics to be out-of-date and potentially update them in between your BCP / BULK INSERT calls.
So how can you optimise your database for BCP / BULK INSERTs and still have stats for your queries post-population ?
Easy: Turn off Auto-create stats and Auto-update stats after creating the database, and use sp_createstats to create statistics afterwards.Turning off Auto Create Stats and Auto Update Stats In SQL Server 2000
exec sp_dboption N'databasename', 'auto create statistics', 'false'
exec sp_dboption N'databasename', 'auto update statistics', 'false'
Note: SQL Server 2005 documentation stats the sp_dboption feature will be removed from future versions of SQL Server, and suggests using ALTER DATABASE instead.In SQL Server 2005
ALTER DATABASE databasename SET AUTO_CREATE_STATISTICS OFF WITH NO_WAIT
ALTER DATABASE databasename SET AUTO_UPDATE_STATISTICS OFF WITH NO_WAIT
-- to generate statistics quickly using a sample of data from the table
-- to generate statistics using a full scan of the table
exec sp_createstats @fullscan = 'fullscan'
With the statistics created, you may want to turn the auto stats options back on. Your database should now be ready for querying.More performance tips and details about statistics can be found here, over at Sql-Server-Performance.com