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 (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Stored Procedure Optimization Tips – Best Practices
Next Post
SQL SERVER – Plan Recompilation and Reduce Recompilation – Performance Tuning

Related Posts

2 Comments. Leave new

  • 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

    Reply
  • If I call a Store Procedure from another Store Procedure will this reduce the performance ?

    Reply

Leave a Reply

Menu