SQLAuthority News – SQL Server Technical Article – The Data Loading Performance Guide

Note: SQL Server Technical Article – The Data Loading Performance Guide by Microsoft

The white paper describes load strategies for achieving high-speed data modifications of a Microsoft SQL Server database.

“Bulk Load Methods” and “Other Minimally Logged and Metadata Operations” provide an overview of two key and interrelated concepts for high-speed data loading: bulk loading and metadata operations.

After this background knowledge, white paper describe how these methods can be used to solve customer scenarios. Script examples illustrating common design pattern are found in “Solving Typical Scenarios with Bulk Loading” Special consideration must be taken when you need to load and read data concurrently in the same table. The section “Bulk Load, NOLOCK Queries, and Read Committed Snapshot Isolation” describes methods you can use to achieve concurrent loading and reading.

This white paper concludes with troubleshooting hints in “Optimizing Bulk Load”.

SQL Server Technical Article – The Data Loading Performance Guide

Abstract courtesy : Microsoft

Reference: Pinal Dave (http://blog.SQLAuthority.com)

2 thoughts on “SQLAuthority News – SQL Server Technical Article – The Data Loading Performance Guide

  1. Hi Pinal Dave

    I have some questions about this white paper was hoping you may be able to help.
    Setting t610 in on, the data base is in simply recover, and Server is 2008 sp1

    What I am doing
    I have a table w/clustered index and it empty I do first batch of insert into table minimum logging works and data look good. I run my second batch minimum logging does not seem to work. Just so we are clear the cluster index we are using very simple for this test four values A,B,C,D 10 million rec each and we insert that way 10m ‘A’ then 10m ‘B’ and so on.

    Here is a sample on the insert any thoughts would helpful
    Thanks
    Scott

    CREATE TABLE OutPutTable
    (
    IDRow int NULL
    ,ColInt int NULL
    ,ExpRow Char(1) NULL
    ,ColVarchar varchar(20) NULL
    ,Colchar char(2) NULL
    ,ColCSV varchar(80) NULL
    ,ColMoney money NULL
    ,ColNumeric numeric(16,4) NULL
    ,ColDate datetime NULL
    ,AutoId int IDENTITY(1,1) NOT NULL
    )

    CREATE CLUSTERED INDEX Clust_IDX ON OutPutTable (ExpRow)WITH (FillFactor = 100)
    GO

    DBCC TRACEON (610)
    Go
    –First Batch
    INSERT INTO OutPutTable WITH(Tablockx)
    (
    IDRow
    ,ColInt
    ,ExpRow
    ,ColVarchar
    ,Colchar
    ,ColCSV
    ,ColMoney
    ,ColNumeric
    ,ColDate
    )

    SELECT
    IDRow
    ,ColInt
    ,ExpRow
    ,ColVarchar
    ,Colchar
    ,ColCSV
    ,ColMoney
    ,ColNumeric
    ,ColDate

    FROM
    SAMPLEDATA

    WHERE
    ExpRow = ‘A’
    GO
    DBCC TRACEOFF (610)
    GO

    DBCC TRACEON (610)
    Go
    –Second Batch
    INSERT INTO OutPutTable WITH(Tablockx)
    (
    IDRow
    ,ColInt
    ,ExpRow
    ,ColVarchar
    ,Colchar
    ,ColCSV
    ,ColMoney
    ,ColNumeric
    ,ColDate
    )

    SELECT
    IDRow
    ,ColInt
    ,ExpRow
    ,ColVarchar
    ,Colchar
    ,ColCSV
    ,ColMoney
    ,ColNumeric
    ,ColDate

    FROM
    SAMPLEDATA

    WHERE
    ExpRow = ‘B’
    GO
    DBCC TRACEOFF (610)
    GO

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s