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)
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
If I call a Store Procedure from another Store Procedure will this reduce the performance ?