Improving BCP / BULK INSERT Performance by Disabling Auto Statistics

Database Design

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.

More performance tips and details about statistics can be found here, over at Sql-Server-Performance.com

Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Improving BCP / BULK INSERT Performance by Disabling Auto Statistics | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.