SQL Server Best Practice - AUTO STATISTICS will affect BCP or BULK INSERT performance?
For the first time I wasn't agreeing that AUTO STATISTICS would harm the bulk insert performance!
But after an assesment of PERFMON counters during the load and after disabling until the process is finished, there was a significant performance on the ETL process. Within any Database platform 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. I'm not talking about intermittent loads such as few thousand rows using BCP or BULK INSERT, this is to do with major ETL processes.
By default AUTO STATISTICS will be set on the database as per the SQL server configuration, so either way, SQL Server is going to want to update the statistics used by the SQL Server Query Optimiser for decision making. So for the performance basis it is 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.
It is better to perform intermittent UPDATE STATISTICS on the tables that has huge bulk insert activities, because SQL Server could decide to create/update stats in the background while you are populating tables, thus degrading BCP / BULK INSERT performance. If your database is spread across multiple files & filegroups then 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.
TO avoid such performance loss of BULK INSERT (which may be during offline hours on the database) just turn off Auto-create stats and Auto-update stats after creating the database, and use sp_createstats to create statistics afterwards.
With the statistics created, you may want to turn the auto stats options back on. Your database should now be ready for querying. For further gotchas on statistics visit: http://www.sql-server-performance.com/statistics.asp link.