Dear Experts,
Our Server Information is:
HP Proliant ML570 with 4 CPU (2.00GHz 512KB L2, 2 MB L3)
It has 4GB RAM.
We have 2 HP Smart Array 6400 Controllers with 192MB RAM.
The Disks of the system are all HP ULTRA320 SCSI. We have 8 x 146.8 GB 15K, 2 x 72.8 GB 10K and 2 x 36.4 GB 15K
We configured the Disks as follows:
First Controller
RAID 1 = 2 x 36.4 GB 15K - Operating System(Microsoft Windows Server 2003 Standard Edition Service Pack 1) and SQL Server(Microsoft SQL Server 2005 Enterprise Edition - 9.00.1399.06 )
RAID 1 = 2 x 146.8 GB 15K - Not Used
RAID 1 = 2 x 72.8 GB 10K - Not Used
Second Controller
RAID 0 = 1 x 146.8 GB 15K - Temp DB
RAID 5 = 5 x 146.8 GB 15K - Database Files And Indexes one filegroup
The only change made to the SQL server configuration was to enable the Boost SQL Server Priority option. Then I created a testing database with a simple recovery model.
Then I Created the following table and I inserted 4,148,577
CREATE TABLE [dbo].[RAW](
[periodid] [smallint] NOT NULL,
[outletid] [int] NOT NULL,
[productid] [int] NOT NULL,
[categoryid] [int] NOT NULL,
[purchases1] [int] NULL,
[purchases2] [int] NULL,
[purchases3] [int] NULL,
[totalpurchases] [int] NULL,
[stock1] [int] NULL,
[stock2] [int] NULL,
[stock3] [int] NULL,
[totalstock] [int] NULL,
[sales] [int] NULL,
[price] [money] NULL,
[cprice] [money] NULL,
[slot1] [int] NULL,
[slot2] [int] NULL,
[slot3] [int] NULL,
[slot4] [int] NULL,
[slot5] [int] NULL,
[slot6] [int] NULL,
[slot7] [int] NULL,
[slot8] [int] NULL,
[slot9] [int] NULL,
[slot10] [int] NULL,
[status] [tinyint] NULL,
[statustmp] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[userwhoinsert] [smallint] NULL,
[inseriondate] [datetime] NULL,
[userwhoupdate] [smallint] NULL,
[updatedate] [datetime] NULL
) ON [PRIMARY]
Then I created the following Indexes:
create clustered index Periodid on raw(periodid)
create index op on raw (outletid,productid)
create index p on raw (productid)
create index c on raw (categoryid)
Then I Executed 2 commands:
delete
-- Period100.txt holds data in the same format as the table for periodid=100
bulk
from 'c:\Period100.txt'
with (order(periodid,outletid,productid))
The delete command takes 9 minutes and the bulk insert 6 minutes.
From a test we made in the past with a beta version of sql 2005 and a RAW table with around 800 million records the time to execute the above commands was around 3 minutes.
Does anyone why is so slow. Any indications if the times are normal
Regards,
Spyros Christodoulou
Bulk Insert Speed Problems
Gerardo Rossi
After an intensive testing of various hardware compnents for error(RAID controller), Server Memory we haven't manage to get any results.
The option of enablind the write cache and the advance performance options cannot be enabled in windows 2003 because it is controlled by the RAID controller.
It seems that the problem is that from the CTP versions of SQL up until the Final versions some options in the Index creation have changed. We are more concern about the covering indexes or with indexes that contain a lot of data pages.
Based on my first post if I remove the index op (outletid, productid) and create for this index an index only on outletid the times for delete and bulk insert become 8 and 9 minutes respectively.
Do you know if somehing has changed from the August CTP into the Sep. CTP for Indexes or for anything else that will affect the performance so much
Regards,
Spyros Christodoulou
lagu2653
JF G.P.
I executed the same test by separating transaction log and with a 800 million records the time it takes to delete 4 million records and bulk insert them back is 51 minutes!
On the beta verion of sql 2005 (Sep. CTP) with the same database of 800 million records and with TEMPDB on a RAID 0, Transaction LOG on a RAID 1 and Data on a RAID 5 the delete and bulk import were taking only 3 minutes with all the index I mentioned in my previous post.
The only things we changed was to add 2 more processors to the system , to install win 2003 with SP1 included (instead of manually upgrading to SP1) and to install the Enterprise edition of SQL server 2005.
From a quick look at performance counters it seems that the writing on the disk after adding the indexes is very slow. (The table is created by duplicating data from a temporary table and changing one of the fields - periodid. The speed for the addition of the 800 million records without any indexes is about 1 minute per periodid - 4,148,577 records)
Even with a different combination of indexes(adding periodid to all non cluster indexes) with the beta it was taking around 10 minutes for both the delete and bulk insert at the table with 800 million records.
From the second link you sent me I estimated that the bulk insert should be much faster (the text file that I bulk Insert is 300MB):
"Loading time / size of data with the clustered index (T with the index): 112 sec/GB"
Do you have any ideas what is the cause of the speed degration
Regards,
Spyros Christodoulou
Ken JCI
Dear Tony,
We formatted everything and we will try the tests again. From a quick check after the installation of the Operating system 'advance performance and write caching' is not enable by default.
We will perform the tests again and I will let you know the results.
Thanks a lot,
Best regards,
Spyros Christodoulou
Stan Lin
Amit Banerjee - MSFT
http://msdn.microsoft.com/library/default.asp url=/library/en-us/optimsql/odp_tun_1a_5gyt.asp
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/incbulkload.mspx