Investigation on performance of data load

reference concept

Soft-NUMA - software non-uniform memory access

Consider the following example. A computer with eight CPUs does not have hardware NUMA. Three soft-NUMA nodes are configured. Database Engine instance A is configured to use CPUs 0 through 3. A second instance of the Database Engine is installed and configured to use CPUs 4 through 7. The example can be visually represented as:
CPUs            0     1    2    3     4   5   6   7
Soft-NUMA   <-N0--><-N1-><----N2---->
SQL Server  <instance A   ><instance B>

dbgen –T o –fF –q –b dists.dss -s 1000 -C 56 -S 6

CREATE DATABASE TPCHdestination ON
PRIMARY
( NAME = N'TPCHdata0',
FILENAME = N'C:\Mount\Drive1\SQLdata\TPCHdata0.mdf' ,
SIZE = 1GB , MAXSIZE = UNLIMITED, FILEGROWTH = 10% ),
FILEGROUP FG1
( NAME = N'TPCHdataG1F1',
FILENAME = N'C:\Mount\Drive1\SQLdata\TPCHdataG1F1.mdf' ,
SIZE = 24GB , MAXSIZE = 24GB ),
FILEGROUP FG2
( NAME = N'TPCHdataG2F1',
FILENAME = N'C:\Mount\Drive2\SQLdata\TPCHdataG2F1.mdf' ,
SIZE = 24GB , MAXSIZE = 24GB ),
. . .
LOG ON
( NAME = N'TPCHdata_log',
FILENAME = N'C:\Mount\Log\SQLlog\TPCHdata_log.ldf' ,
SIZE = 25GB , MAXSIZE = 25GB )
GO
ALTER DATABASE TPCHdestination SET RECOVERY SIMPLE
GO
sp_dboption 'TPCHdestination','auto create statistics','OFF' ;
go
sp_dboption 'TPCHdestination','auto update statistics','OFF' ;
go
alter database TPCHdestination set PAGE_VERIFY NONE ;
go
With the file groups created, tables to be loaded with data were placed in the file groups, for example,
create table ORDERS_6
       (O_ORDERDATE  smalldatetimenot null,
        O_ORDERKEY          bigint        not null,
        O_CUSTKEY           int                  not null,
        O_ORDERPRIORITY     char(15)             not null,
        O_SHIPPRIORITY      int                  not null,
        O_CLERK             char(15)             not null,
        O_ORDERSTATUS       char(1)              not null,
        O_TOTALPRICEmoney                not null,
        O_COMMENT           varchar(79)          not null)
on FG6

CREATE PARTITION FUNCTION pfnORDER (bigint) AS RANGE LEFT FOR VALUES (
107142850,214285700,321428550,428571424,535714274,642857124,
749999974,857142848,964285698,1071428548,1178571398,1285714272,
1392857122,1499999972,1607142822,1714285696,1821428546,1928571396,
2035714246,2142857120,2249999970,2357142820,2464285670,2571428544,
2678571394,2785714244,2892857094,2999999968,3107142818,3214285668,
3321428518,3428571392,3535714242,3642857092,3749999942,3857142816,
3964285666,4071428516,4178571366,4285714240,4392857090,4499999940,
4607142790,4714285664,4821428514,4928571364,5035714214,5142857088,
5249999938,5357142788,5464285638,5571428512,5678571362,5785714212,
5892857062) -- maximum is 6000000000
CREATE PARTITION SCHEME pscORDER AS PARTITION pfnORDER TO (
FG1, FG2, FG3, FG4, FG5, FG6, FG7, FG8, FG9, FG10, FG11, FG12,
FG13, FG14, FG15, FG16, FG17, FG18, FG19, FG20, FG21, FG22, FG23,
FG24, FG25, FG26, FG27, FG28, FG29, FG30, FG31, FG32, FG33, FG34,
FG35, FG36, FG37, FG38, FG39, FG40, FG41, FG42, FG43, FG44, FG45,
FG46, FG47, FG48, FG49, FG50, FG51, FG52, FG53, FG54, FG55, FG56)
GO
create table ORDERS
       (O_ORDERDATE  smalldatetimenot null,
        O_ORDERKEY          bigint        not null,
        O_CUSTKEY           int                  not null,
        O_ORDERPRIORITY     char(15)             not null,
        O_SHIPPRIORITY      int                  not null,
        O_CLERK             char(15)             not null,
        O_ORDERSTATUS       char(1)              not null,
        O_TOTALPRICEmoney                not null,
        O_COMMENT           varchar(79)          not null)
on pscORDER(O_ORDERKEY)

ALTER TABLE ORDERS_6 WITH CHECK ADD CONSTRAINT check_ORDERS_6
CHECK (O_ORDERKEY >= 535714275 AND O_ORDERKEY <= 642857124)

ALTER TABLE ORDERS_6 SWITCH TO ORDERS PARTITION 6

The creation of soft-NUMA nodes and port mappings is done in the system registry. The registry settings used were [ref04]:
Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration]
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node0]
"CpuMask"=hex:01
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node1]
"CPUMask"=hex:02
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node2]
"CPUMask"=hex:04
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node4]
"CPUMask"=hex:10
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node3]
"CPUMask"=hex:08
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node5]
"CPUMask"=hex:20
. . .
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node62]
"CpuMask"=hex:00,00,00,00,00,00,00,40
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\100\NodeConfiguration\Node63]
"CpuMask"=hex:00,00,00,00,00,00,00,80
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.STAB1300_04\
MSSQLSERVER\SuperSocketNetLib\Tcp\IPAll]
"TcpPort"="2000[0x00000001],2001[0x00000002],2002[0x00000004],2003[0x00000008],
2004[0x00000010],2005[0x00000020],2006[0x00000040],2007[0x00000080],2008[0x00000100],
2009[0x00000200],2010[0x00000400],2011[0x00000800],2012[0x00001000],2013[0x00002000],
2014[0x00004000],2015[0x00008000],2016[0x00010000],2017[0x00020000],2018[0x00040000],
2019[0x00080000],2020[0x00100000],2021[0x00200000],2022[0x00400000],2023[0x00800000],
2024[0x01000000],2025[0x02000000],2026[0x04000000],2027[0x08000000],2028[0x10000000],
2029[0x20000000],2030[0x40000000],2031[0x80000000],2032[0x100000000],2033[0x200000000],
2034[0x400000000],2035[0x800000000],2036[0x1000000000],2037[0x2000000000],2038[0x4000000000],
2039[0x8000000000],2040[0x10000000000],2041[0x20000000000],2042[0x40000000000],
2043[0x80000000000],2044[0x100000000000],2045[0x200000000000],2046[0x400000000000],
2047[0x800000000000],2048[0x1000000000000],2049[0x2000000000000],2050[0x4000000000000],
2051[0x8000000000000],2052[0x10000000000000],2053[0x20000000000000],2054[0x40000000000000],2055[0x80000000000000],2056[0x100000000000000],2057[0x0200000000000000],
2058[0x400000000000000],2059[0x800000000000000],2060[0x1000000000000000],
2061[0x2000000000000000],2062[0x4000000000000000],2063[0x8000000000000000]"
"TcpDynamicPorts"=""
"DisplayName"="Any IP Address"

We set the network packet size to 32k instead of the default 4k. This was implemented on both the database side using the option  network packet size  (B)

DTExec.exe
       /Conn DestinationDB;
"Data Source=10.1.1.2,2006; Initial Catalog=TPCHdestination;
Provider=SQLNCLI10.1; Integrated Security=SSPI;
PacketSize=32767;AutoTranslate=False;"
       /Conn Customer;"C:\Mount\Source6\TPCH1000GBby56\customer.tbl.6"
       /Conn LineItem;"C:\Mount\Source6\TPCH1000GBby56\lineitem.tbl.6"
       /Conn Orders;"C:\Mount\Source6\TPCH1000GBby56\orders.tbl.6"
       /Conn Part;"C:\Mount\Source6\TPCH1000GBby56\part.tbl.6"
       /Conn PartSupp;"C:\Mount\Source6\TPCH1000GBby56\partsupp.tbl.6"
       /Conn Supplier;"C:\Mount\Source6\TPCH1000GBby56\supplier.tbl.6"
       /set \Package\Customer.Variables[dynCustomerTabName].Value;CUSTOMER_6
       /set \Package\Supplier.Variables[dynSupplierTabName].Value;SUPPLIER_6
       /set \Package\Part.Variables[dynPartTabName].Value;PART_6
       /set \Package\PartSupp.Variables[dynPartSuppTabName].Value;PARTSUPP_6
       /set \Package\Orders.Variables[dynOrdersTabName].Value;ORDERS_6
       /set \Package\LineItem.Variables[dynLineItemTabName].Value;LINEITEM_6
       /F “c:\etlwr\ops\src\WRpackages\WRproject\WRproject\SOLEDBstream.dtsx"