July 10, 2017 at 1:19 pm
Hello, I'm using a 32bit Attunity provider and 32 bit Teradata Driver to connect to Teradata and execute a query and load that result set into a SQL Server table. For over a year I have noticed that it took this pkg 1-2 min to run, then 30 min, and now 2.5 hr's to do the same exact thing. Nothing about the drivers, providers has changed. The Teradata system is still high speed and a performance bottle neck. Nor is the SQL serve itself, nothing else is competing for resources including network bandwdith. Where and how can I go about investigating why it has become so slow? Thanks
July 10, 2017 at 1:54 pm
quinn.jay - Monday, July 10, 2017 1:19 PMHello, I'm using a 32bit Attunity provider and 32 bit Teradata Driver to connect to Teradata and execute a query and load that result set into a SQL Server table. For over a year I have noticed that it took this pkg 1-2 min to run, then 30 min, and now 2.5 hr's to do the same exact thing. Nothing about the drivers, providers has changed. The Teradata system is still high speed and a performance bottle neck. Nor is the SQL serve itself, nothing else is competing for resources including network bandwdith. Where and how can I go about investigating why it has become so slow? Thanks
Assuming your packages are deployed to SSISDB, check the 'All Executions' report to identify which component(s) is/are sucking up all that time.
You could try building a duplicate package and sending the output to a Trash Destination rather than a physical table – this would help you isolate whether it's a problem on the SQL Server side.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 10, 2017 at 3:30 pm
Phil Parkin - Monday, July 10, 2017 1:54 PMquinn.jay - Monday, July 10, 2017 1:19 PMHello, I'm using a 32bit Attunity provider and 32 bit Teradata Driver to connect to Teradata and execute a query and load that result set into a SQL Server table. For over a year I have noticed that it took this pkg 1-2 min to run, then 30 min, and now 2.5 hr's to do the same exact thing. Nothing about the drivers, providers has changed. The Teradata system is still high speed and a performance bottle neck. Nor is the SQL serve itself, nothing else is competing for resources including network bandwdith. Where and how can I go about investigating why it has become so slow? ThanksAssuming your packages are deployed to SSISDB, check the 'All Executions' report to identify which component(s) is/are sucking up all that time.
You could try building a duplicate package and sending the output to a Trash Destination rather than a physical table – this would help you isolate whether it's a problem on the SQL Server side.
I monitored the package from all executions, and they run long on all 4 tables it loads, but gives no warnings. Teradata/Attunity are used to exec a query to pulling data result from Teradata, and then uses OLE DB to load to the SQL Server table. The bottle neck appears to be the loading of the tables. I did a index fragmentation check, and these tables being loaded are not on the frag list.
July 10, 2017 at 4:17 pm
"The bottle neck appears to be the loading of the tables."
Normally when this happens after awhile its most common reason is that originally the table was "small" and has grown overtime.
If table has indexes that is most likely the reason why it is now becoming slower.
If the table does have indexes one common solution is to drop the indexes before the load and recreate them after the load.
You would need to try it out and see how long those indexes take to recreate vs the time it takes to load - sometimes its better to leave as is.
One other thing that may affect the load is if the database files are nearly full and it requires the datafiles to grow - this will only be an issue if IFI is not enabled on that server for the SQL Server instance user.
And another thing that could affect this - the volume extracted has significantly increased and you aren't using the fastload option on the destination.
July 11, 2017 at 7:37 am
frederico_fonseca - Monday, July 10, 2017 4:17 PM"The bottle neck appears to be the loading of the tables."Normally when this happens after awhile its most common reason is that originally the table was "small" and has grown overtime.
If table has indexes that is most likely the reason why it is now becoming slower.
If the table does have indexes one common solution is to drop the indexes before the load and recreate them after the load.
You would need to try it out and see how long those indexes take to recreate vs the time it takes to load - sometimes its better to leave as is.
One other thing that may affect the load is if the database files are nearly full and it requires the datafiles to grow - this will only be an issue if IFI is not enabled on that server for the SQL Server instance user.
And another thing that could affect this - the volume extracted has significantly increased and you aren't using the fastload option on the destination.
The table is truncated each time before a load, it's laoding the same 1.75M rows of data, there are no indexes, the database has plenty of space.
July 11, 2017 at 7:47 am
quinn.jay - Tuesday, July 11, 2017 7:37 AMThe table is truncated each time before a load, it's laoding the same 1.75M rows of data, there are no indexes, the database has plenty of space.
Are any other jobs running on the target server at load time? Backups? Integrity checks? External backups (eg, of the server's VM)? Do other queries run slowly while the load is being performed?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 11, 2017 at 10:06 am
quinn.jay - Tuesday, July 11, 2017 7:37 AMfrederico_fonseca - Monday, July 10, 2017 4:17 PM"The bottle neck appears to be the loading of the tables."Normally when this happens after awhile its most common reason is that originally the table was "small" and has grown overtime.
If table has indexes that is most likely the reason why it is now becoming slower.
If the table does have indexes one common solution is to drop the indexes before the load and recreate them after the load.
You would need to try it out and see how long those indexes take to recreate vs the time it takes to load - sometimes its better to leave as is.
One other thing that may affect the load is if the database files are nearly full and it requires the datafiles to grow - this will only be an issue if IFI is not enabled on that server for the SQL Server instance user.
And another thing that could affect this - the volume extracted has significantly increased and you aren't using the fastload option on the destination.
The table is truncated each time before a load, it's laoding the same 1.75M rows of data, there are no indexes, the database has plenty of space.
Here is more feedback, a DBA and I are monitoring the job closer and we are seeing that as soon as it rolls to the step that the package that takes forever, shows its sleeping, and waiting and not executing anything. What would cause this? The job has nothing in it to do this, and the package doesn't either that I know of, where could this get introduced?
July 11, 2017 at 10:12 am
quinn.jay - Tuesday, July 11, 2017 10:06 AMHere is more feedback, a DBA and I are monitoring the job closer and we are seeing that as soon as it rolls to the step that the package that takes forever, shows its sleeping, and waiting and not executing anything. What would cause this? The job has nothing in it to do this, and the package doesn't either that I know of, where could this get introduced?
Presumably the DBA has checked locks, waits, sp_whoisactive etc?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 11, 2017 at 12:39 pm
Phil Parkin - Tuesday, July 11, 2017 10:12 AMquinn.jay - Tuesday, July 11, 2017 10:06 AMHere is more feedback, a DBA and I are monitoring the job closer and we are seeing that as soon as it rolls to the step that the package that takes forever, shows its sleeping, and waiting and not executing anything. What would cause this? The job has nothing in it to do this, and the package doesn't either that I know of, where could this get introduced?
Presumably the DBA has checked locks, waits, sp_whoisactive etc?
Yes, and now we think that the group logon being used, is getting held by the source systems as it's exceeded its max number of sessions, and its waiting for a slot to open up. The latest theory
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply