SQL Server – BCP – BCP Hints

Background

Wanted to evaluate BCP Optimization Hints.

BCP HINTS

The ones we are going to talk about are:

Syntax

  1. TABLOCK
  2. ROWS_PER_BATCH
  3. Sorted

Sample

  1. TABLOCK
  2. ROWS_PER_BATCH
    • 1 million ( 1000000 )
  3. Sorted
    • ORDER (work_email ASC)

BCP Statement

Sample


bcp "dbo.hrdbPerson" in data\hrdbPerson.txt -T -n -h "TABLOCK, ROWS_PER_BATCH=1000000, ORDER (work_email ASC)" -S . -d hrdb

Explanation

  1. Table :-
    • dbo.hrdbPerson
  2. File :-
    • data\hrdbPerson.text
  3. Authentication
    • Active Directory ( -T)
  4. Format
    • -n ( Native Format )
  5. Hint
    • TABLOCK
    • ROWS_PER_BATCH=1000000
    • ORDER (work_email ASC)
      • Same as clustered index
  6. Sql Instance
    • -s
      • . ( local SQL Server Instance [Default ] )
  7. Database
    • -d
      • hrdb

SQL Server Profiler

Data Out

Trace Definition

Image

Explanation

  1. Events
    • Security Audit
      • Audit Login
      • Audit Logout
    • Stored Procedures
      • RPC:Completed
    • TSQL
      • SQL:BatchCompleted
      • SQL:BatchStarting
  2. Columns
    • Event Class
    • TextData
    • CPU
    • Reads
    • Writes
    • Duration
    • SPID
    • RowCounts

Trace Events

Image

Explanation

  1. Duration
    • Duration is in milliseconds
      • 6376 milliseconds
      • 6 seconds
  2. CPU
    • 1703
  3.  Reads
    • 26542
  4. Writes
    • 0
  5.  RowCounts
    • 317015 records

Data In

Trace Definition

Image

Explanation

  1. Events
    • SQL:BatchCompleted
  2. Columns
    • Event Class
    • TextData
    • CPU
    • Reads
    • Writes
    • Duration
    • SPID
    • RowCounts

Trace Events

Image

Tabulation

  1. EventClass
    • SQL:BatchCompleted
  2. TextData
    • insert bulk dbo.telecom([loc] char(2) collate SQL_Latin1_General_CP1_CI_AS,[employeeid] char(9) collate SQL_Latin1_General_CP1_CI_AS,[ssn] char(9) collate
  3. CPU
    • 20516
  4. Reads
    • 1185987
  5. Writes
    • 56870
  6. Duration
    • 21390
  7. SPID
    • 72
  8. RowCounts
    • 317015

Explanation

  1. Duration
    • Duration is in milliseconds
      • 21390 milliseconds
      • 21 seconds
  2. RowCounts
    • 317015 records

Summary

Unfortunately, there is not a lot of sample out there on how to use BCP Hints.

Reference

  1. Microsoft
    • Database Engine > Technical Reference > Command Prompt Utility Reference (Database Engine)
      • BCP Utility

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s