May 27, 2004 at 8:23 am
Hi,
I have an urgent case at one of my customers. I'm piloting Informatica ETL tool and the inserts to SQL Server 2000 sp 3 empty table with no indexes is very slow - 100 rows/sec! Performance monitoring on the target DW and AIX Informatica server showed no bottle necks.
Nothing wrong with the SQL Server (DTS loads run 1000 to 4600 rows/sec). I think I have narrowed down the cause to the disk sub-system. I simulated the loads with my 700 MHz laptop and got 4 times better performance (IDE-disk)!. Customer have a separate SAN EMC(?) disk pack and 4 gigs memory and 4 cpu's and configurations (sp_configure) look ok. Network/Infa Aix Server are not problems in this case.
I think DTS causes no problem since it can use fast load. I cannot use Informatica 'bulk' option over AIX ODBC. This makes the inserts very slow with scsi or SAN EMC(?). I noticed no difference in performance with Informatica ‘Bulk’ / ‘Normal’ loads in my laptop ide disk environment.
Have I got this right – can the writing really be this slow depending on the disk system or disk configuration?
I could ask the customer for temporarily usage of a server where I could install Informatica Windows-environment to demonstrate the same loads when being able to use native SQL Server connection and thus the 'Bulk' option. Or could the disk system be reconfigured to somehow cache the writes without risking the database integrity or even getting corrupted.
Like you have noticed, I do not know the physics behind the disk systems nor how SQL Server takes advantage of the different systems..
I would highly appreciate it if someone could educate me on this matter and I’m sorry in advance if I just haven’t found an existing article in your databases,
Regards, Ville
May 28, 2004 at 7:40 am
I'm not familar with Informatica ETL tool, is there anything you need configure for this aspect.
For SQL part, you have 4 Gb of memory, is AWE, pae enabled ?
For H/W, is write cache on or off on the controller ?
On the EMC/SAN, it is complete different animal. Ask EMC sa, for the disk array allocation map. EMC allocation is something different than you think. They can carve up mix bag of disks to an array (ie, 5Gb from disk A, 3 GB from disk B, 2 GB from disk C and put them in a RAID). Is this happen to you ?
Since you are using EMC/SAN, I'm assuming you are using Fiber Channel. Is the controller on the server compatible with EMC / SAN ?
What's your DB option set to ? Try Bulk_logged may help.
I'm not a fan of DTS package, I noticed DTS package tend to slow down when deals with hug amount of data. You really should look into bulk insert or bcp option to deal with large amount of data
May 28, 2004 at 11:10 am
Hi John,
I trust the local admins have the disks configured OK. Reading from the db is very fast and DTS writes OK - 'Bulk' option can be used. My problem is that I cannot use 'Bulk' from AIX with ODBC.
Yes, SqlServer can allocate all memory it needs.
My hopes rely now on the possible reconfiguration of the disk write-caches unless someone suggests that there must be something else wrong in the system described here.
Thanks, Ville
November 8, 2005 at 2:52 am
I'm in the same situation, a single Sata disk at 7200rpm is nearly twice as fast as a SCSI RAID 0 with 4 Disks at 10k rpm. Inserts are very slow on SCSI.
We enabled Write Cache in OS and Battery powered Write Cache.
Write Strategy on Raid System is set to Write Back, so it should be really fast.
We are using SQL-Server8 on W2003
November 8, 2005 at 1:58 pm
I haven't done much bulk data loading, and I'm probably way off base here, but it sounds more like a connectivity issue than a hardware one. Leastways, given the setup you've described, that's what "it works when I do it this way but not when I do it that way" means to me. Is Informatica/AIX/whichever perofrming work in an optimal fashion?
You might get further insights on the problem through SQL Profiler. Fire it up and see if you can determine the nature of the commands actually hitting the database(s). There might be other events going on as well that may help identify problem situations.
Philip
November 9, 2005 at 2:33 am
Ok - situation is slightly different because we don't use AIX
We use MS-SQL Version 8 on Windows 20003, the import process is running direct on the server, all data for the task is local. So network shouldn't be the bottleneck.
The "test" Server is a Workstation with 2 SATA Disks, one for the OS W2003 Server, the other on is for the Data. For comparism we also connected our future productive external SCSI Raid Sytem to this Computer.
We are the same Import, several thousand records against a Database on the Sata Disk and later against the the same Database on the external Raid 0.
All Write Buffers are enabled, strategy on the external raid is set to write Back because it is battery buffered.
During normal file copy or random read/write actions the 2 different Disk systems behave as expected. The single Sata Disk is by a factor of 4 slower than the Raid 0 with 4 faster Disks.
We run 2 different Tests, the first one does Bulk inserts via a Delphi Application.
With that we got 3500 Inserts/s on the single Sata Disk and 2000 Inserts with the external Raid.
The second Test is pure DTS Massimport from a ASCII File. I got 8530 Inserts/ Second for the Raid, but 9750 for the single Sata Disk.
Is there anything special with SCSI and MS-SQL Server, Any tweaks to get the full performance on the raid with MS-SQL?
November 9, 2005 at 8:19 am
Still fumbling at straws here--I've no facts or evidence to back this up, it's just something that might be worth looking into.
When your doing the bulk inserts, are there indexes being updated? Is the target table clustered? If no, then you're merely adding rows to a heap; if indexes, then you are also updating indexes on the fly (lots of reads and writes); if there's a cluster, you could be dealing with page splits on your underlying data.
(For reading/writing entire files, you'd be dealing with reading/writing contiguous space on the disk; for random reads/writes, you've maybe got the lag time as the process figures out the next read/write to submit. For bulk uploads, it all hits the fan at once--and if operations are not against contiguous, that might be the issue.)
The more reads and writes you do, the more work the disk subsystem has to do... and, with RAID 0, that data is striped across four disks, in *possibly*non-contiguous space on the individual drives. (Index data is not likely to be stored in the same space as table data). Now the striping should make reads and writes faster (simultaneous parallel reads from four devices)--if the disk controller is up to it. What kind of capacity does the controller have? How well does it support massive amounts of reads and writes against four disks?
Just shots in the dark. If this don't help, it still might lead to the right track.
Philip
November 10, 2005 at 11:02 am
Hi,
I started this thread a year+ ago and funny as it sounds, almost at the same time that I started to get notifications to my email about replies to this topic, I am dealing with this problem again.
And again the target disks are SCSI disks with SqlServer2000.
Normal inserts with ETL tool are really slow but Bulk inserts fast.
Just need to design the big loads (FACT data) as isolated unique inserts ==> Bulk insert with ETL or Select * into table from .... with tsql
Ville
November 11, 2005 at 6:18 am
>>Just need to design the big loads (FACT data) as isolated unique inserts ==> >>Bulk insert with ETL or Select * into table from .... with tsql
Same here 🙁 I have to read linear 3 Tables, do some calculations, keep a lot of calculated data in Memory and, depending on some values, store this data in a 4th table.
Work around for now is to write the data in a ASCII File and import this data with DTS. That's faster than Bulk Inserts or Array Insert.
At least a factor of 10 to drop a number.
Something other I found out - Bulk Insert and Array insert works direkt on the Database. DTS does something strange - it uses the TempDB as File and not as DB to create a copy of the table of the Target Database.
After the import is finished it moves the data in one big stroke over to the target Database.
So the speed of a DTS import is limited by the Temp Device.
I still will have a look at this Cache SATA/SCSI Problem...
regard Dirk
November 13, 2005 at 12:56 pm
Hi Dirk,
please let me know should you find out something interesting
Regards, Ville
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply