June 14, 2010 at 11:46 am
Hi All,
I am having speed issues when pulling data into my SQL Server where even a simple 100K table (using bulk insert and without transformations) takes 4 hours to run. I started out with openquery in my stored procedure, then moved it to an SSIS package assuming I would get a substantial performance improvement, but only gained 15 minutes.
The install is still pretty close to default. Where should I begin looking?
Thanks,
David
June 14, 2010 at 12:05 pm
What is the data source?
Same server/different server?
How complex is the query used to generate the data?
One of the things I've seen is the source isn't returning rows fast enough to keep the pipe full. I've seen three different reasons (I'm sure there are more):
1. Slow and/or busy network connection.
2. Very complex query over a large dataset.
3. Slow I/O subsystem on either source or destination system.
Now, I don't have enough information to really give you much else. Let us know..
CEWII
June 14, 2010 at 2:23 pm
Elliott Whitlow (6/14/2010)
What is the data source?Same server/different server?
How complex is the query used to generate the data?
One of the things I've seen is the source isn't returning rows fast enough to keep the pipe full. I've seen three different reasons (I'm sure there are more):
1. Slow and/or busy network connection.
2. Very complex query over a large dataset.
3. Slow I/O subsystem on either source or destination system.
Now, I don't have enough information to really give you much else. Let us know..
CEWII
It only takes a few minutes using PC SAS, so I think the source is ok. It's a direct mapping using OLE DB Source and SQL Server Destination (no query or data conversion required). I think that leaves the destination IO as the culprit -- I just don't know anything about optimizing IO.
Thanks,
David
June 14, 2010 at 3:06 pm
Why don't you tell us about the destination server.
June 14, 2010 at 3:18 pm
Elliott Whitlow (6/14/2010)
Why don't you tell us about the destination server.
It's a Windows 2003 box using 4 Xeon 3.2GHz chips and 3.75 GB of RAM.
SQL Server 2005:
ProductVersionProductLevel
9.00.3282.00SP2
June 14, 2010 at 3:50 pm
Well the processors seem good, RAM seems a little low but probably ok..
32 or 64-bit?
Local drives or SAN?
RAID array? What raid level?
Are the OS drives on seperate disks from the SQL data files?
Are the database log files on seperate disks from the data files?
Is tempdb on its own set of disks?
CEWII
June 14, 2010 at 4:13 pm
Elliott Whitlow (6/14/2010)
32 or 64-bit?Local drives or SAN?
RAID array? What raid level?
Are the OS drives on seperate disks from the SQL data files?
Are the database log files on seperate disks from the data files?
Is tempdb on its own set of disks?
CEWII
It's 32 bit.
The drives are local -- at least their properties all refer to HITACHI Disk Devices.
The Device Manager has four items under SCSI and RAID controllers. I don't know what they mean, so here's the full list:
1. Multi-Path Support
2. QLogic Fibre Channel Adapter
3. QLogic Fibre Channel Adapter
4. Smart Array 5i
Smart Array 5i Configuration: 2 hard drives, 1 logical drive.
OS is on C:, system databases are on D:, Data files on F:, Log files on G:, Backups on H:.
June 15, 2010 at 8:49 am
David Korb (6/14/2010)
Elliott Whitlow (6/14/2010)
32 or 64-bit?Local drives or SAN?
RAID array? What raid level?
Are the OS drives on seperate disks from the SQL data files?
Are the database log files on seperate disks from the data files?
Is tempdb on its own set of disks?
CEWII
It's 32 bit.
The drives are local -- at least their properties all refer to HITACHI Disk Devices.
The Device Manager has four items under SCSI and RAID controllers. I don't know what they mean, so here's the full list:
1. Multi-Path Support
2. QLogic Fibre Channel Adapter
3. QLogic Fibre Channel Adapter
4. Smart Array 5i
Smart Array 5i Configuration: 2 hard drives, 1 logical drive.
OS is on C:, system databases are on D:, Data files on F:, Log files on G:, Backups on H:.
I have to say that that drive layout at first glance looks good.
But I was a little fuzzy, how many hard drives are there total? Just because you have a C, D, E, G, and H drives does not mean they are on seperate DISKS. You may need to look at the underlying disks from your Fibre Attached Storage (FAS) to see you they are laid out.
I am *guessing* that your OS drive is probably a pair of mirrored drives inside the machine itself. I am also *guessing* that the FAS is one big array that has been carved out to seperate logical drives. If this is true then short of completely restructurig the array you will have serious performance limitations imposed by the disk subsystem. Now I am guessing on a lot, this is a mistake I have seen multiple time and without additional disk space is VERY hard to fix.
Unfortunately this requires some research on your part into areas of the server that I would not normally associate with database management, but are always good to know about anyway.
CEWII
June 15, 2010 at 9:40 am
...You may need to look at the underlying disks from your Fibre Attached Storage (FAS) to see you they are laid out....
There appear to be 5: C,D,F,G, & H. However, I don't have credentials to get into the Veritas administrator. I guess this will have to wait until I can get my hands on the admin people.
Thanks,
David
June 15, 2010 at 10:04 am
David Korb (6/15/2010)
...You may need to look at the underlying disks from your Fibre Attached Storage (FAS) to see you they are laid out....
There appear to be 5: C,D,F,G, & H. However, I don't have credentials to get into the Veritas administrator. I guess this will have to wait until I can get my hands on the admin people.
Where I'm going is you have C, D, F, G, & H VOLUMES, this has nothing to do with the underlying structure on the physical disks. It is not uncommon to take all the disks and assign them to a RAID 5 volume, or a RAID 10, or a RAID 0+1, or some other flavor, and then carve out volumes from the space. I would not be in the least surprised if you had this on your server. Let us know when you find out the actual layout. I'm sure we would be glad to hear about it.
CEWII
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply