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
Generating statistics after import process is complete
In SQL Server 2000 and SQL Server 2005
— to generate statistics quickly using a sample of data from the table
exec sp_createstats
— 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.