Solaris 8 Migration Assistant
Rapidly move your Solaris 8 application environments to new systems running Solaris 10 with the Solaris 8 Migration Assistant. Reduce migration risk while taking advantage of increased performance, reliability and security of the latest SPARC hardware platforms and Solaris 10 OS.
»
Sun Eco Innovation: Good for Business, Good for the Environment
A complete solution to help you optimize and refresh your datacenter while properly recycling equipment and eliminating eWaste, including money-saving promotions to lower hardware acquisition costs.
»
Sun Eco Innovation: Power Calculators
Power consumption has increasingly become a priority in customer's minds when purchasing new systems or storage. Sun's Power Calculators provide data on power consumption of Sun products allowing IT managers to better plan the power requirements in the datacenter to achieve better energy and cost savings.
»
October 22, 2003 SQL Server Bulk Copy Optimization Tips By Alexander Chigrik
If you're having trouble with bulk copy, check out these 14 tips from Alexander Chigrik.
Use nonlogged bulk copy whenever possible.
The nonlogged bulk copy is much faster than the logged one, but to use it
you must provide all the following conditions:
The database option 'select into/bulkcopy' is set to true.
The target table is not being replicated.
The TABLOCK hint is specified.
The target table has no indexes, or if the table has indexes, they are empty when the bulk copy starts.
Use native mode bulk copy whenever possible.
This can improve performance in comparison with the character mode.
Try to use the BULK INSERT command instead of bcp or DTS to load data
into SQL Server.
The BULK INSERT command is much faster than bcp or the data pump to perform
text file import operations, however, the BULK INSERT statement cannot bulk
copy data from SQL Server to a data file.
Use the bcp utility instead of DTS when you need to export data from
the SQL Server table into a text file.
The bcp utility is much faster than DTS, so try to use it whenever possible.
Specify the number of the first and the last row to bulk copy, if
you do not need to bulk copy all the rows from the specified data file.
This can result in good performance benefits, because the total amount
of data copied will be less.
Specify the number of rows per batch of data copied, if the transaction
log was filled before the bulk copy is complete.
Because each batch is copied to the server as one transaction, SQL Server
commits or rolls back the transaction for every batch. When you bulk
copy large data files, the transaction log can be filled before the bulk
copy is complete. In this case, enlarge the transaction log, allow it to
grow automatically or specify the number of rows per batch of data copied.
Try to increase the packet_size option.
The packet_size option specifies the number of bytes, per network packet,
sent to and from the server. The packet_size can be from 4096 to 65535 bytes
with the default of 4096. Increased packet size can enhance performance of
bulk copy operations. Try to set the packet_size option to 8192 bytes and
continue monitoring.
Use the ORDER hint, if the clustered index exists on the table and the
data file is sorted according to the clustered index.
This can significantly improve performance of the bulk copy operation,
because SQL Server will load data in the clustered index order without
any reorder operations.
If you create a new table and bulk copy data into it, try to bulk
load data first and only after that create any indexes.
This can significantly improve performance of the bulk copy operation,
because data will be loaded into the SQL Server table without any index pages
creation during the bulk copy.
If you load data into an empty table with the existing nonclustered
indexes, try to drop the nonclustered indexes, bulk load data and only
after that re-create the nonclustered indexes.
This can significantly improve performance of the bulk copy operation,
because data will be loaded into the SQL Server table without any index pages
creation during the bulk copy.
If you load data into a nonempty table with the existing clustered
and/or nonclustered indexes, and the amount of data added is large,
it can be faster to drop all indexes on the table, perform the bulk copy
operation, and then re-create the indexes after the data is loaded.
Check the time needed to load data with dropping/re-creating indexes and
without dropping/re-creating indexes on your test server before running the
bulk copy operation on the production server.
If your SQL Server box has multiple CPUs, try to divide loaded data
into two or more sources and run multiple instances of bcp on separate
clients to load data in parallel.
Because SQL Server allows data to be bulk copied into a single table from
multiple clients in parallel using the bcp utility or BULK INSERT statement,
try to use parallel data loads whenever possible.
To bulk copy data into SQL Server in parallel, you must provide all the
following conditions:
The database option 'select into/bulkcopy' is set to true.
The TABLOCK hint is specified.
The target table does not have any indexes.
Specify the TABLOCK hint, if you bulk copy data into an empty table
from a single client.
This can improve performance of the bulk copy operation, because this causes
a table-level lock to be taken for the duration of the bulk copy operation.
Try to avoid using CHECK_CONSTRAINTS and FIRE_TRIGGERS hints.
Using these hints can significantly degrade performance of the bulk copy
operation, because for each row loaded the constraints and insert triggers
defined on the destination table will be executed.
Add databasejournal.com to your favorites Add databasejournal.com to your browser search box IE 7 | Firefox 2.0 | Firefox 1.5.xReceive news via our XML/RSS feed
IE 7
Firefox 2.0
