August 14, 2009 at 2:18 pm
Elliott W (8/14/2009)
That is shockingly bad performance. The other thought I had is what ELSE is going on on the source and destination servers at the same time, could there be other processes manipulating the source data, could there be some reorg operation going on?And I know this is a shameless plug, guilty, but I wrote a tool for this and is available on my website:
http://www.novaconceptsltd.com
The tool is called SQL Job History Visualization and you can use it for 15 days before you need to consider licensing.
You should be able to see any job that is running on the server at the same time..
CEWII
Thanks for the "plug" 🙂 and the tips.
Indeed I should be looking at what else is happening at that time on the source and destination servers.
The source view is defined on a SELECT statement that - when I run it right now - takes 1 sec to return the data. I don't know if that's also the case at night when the package is scheduled to run.
Also, the SELECT query in the view involves a linked server to a 3rd instance, but as I said, the SELECT normally completes in about 1 sec.
I will take a look at what other activity is taking place at the time the package executes.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 14, 2009 at 2:21 pm
what response times do you get if you use OPENQUERY against the LinkedServer?
are you filtering on any records in the source table?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 14, 2009 at 2:29 pm
Marios, I think something is going wrong @ ur SSIS. I have a ssis package that loads over a 8 millions rows in less then 15 mins, to be exact 12mins 36 secs. Also there is option where you can substute your OLEDB destination with SQL server destination, You might gain performance gain. the user have to be a bulkadmin for sql server destination as it uses bulk load.
in my current project, they are using a linked server to load around 80 flat files. I changed everything to use ssis ( i have a master package that calls 4 child ssis packages in a loop). the timing was reduced by dramatically. I know the reson becoz of parallel load too. But I have tested a single load and ssis was better than linked server in my case.
August 14, 2009 at 3:10 pm
gah (8/14/2009)
what response times do you get if you use OPENQUERY against the LinkedServer?are you filtering on any records in the source table?
Thanks for the suggestion, actually this is in fact what we are doing inside the source view:
...
FROM
OPENQUERY
( LinkedServerName, 'SELECT...' )
...
The response time is quite good, but I do need to check whether it is also good during the night when the job is running.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 14, 2009 at 3:12 pm
Gkhadka (8/14/2009)
Marios, I think something is going wrong @ ur SSIS. I have a ssis package that loads over a 8 millions rows in less then 15 mins, to be exact 12mins 36 secs. Also there is option where you can substute your OLEDB destination with SQL server destination, You might gain performance gain. the user have to be a bulkadmin for sql server destination as it uses bulk load.in my current project, they are using a linked server to load around 80 flat files. I changed everything to use ssis ( i have a master package that calls 4 child ssis packages in a loop). the timing was reduced by dramatically. I know the reson becoz of parallel load too. But I have tested a single load and ssis was better than linked server in my case.
Hmm, thanks for the tip, I will check on replacing OLEDB with SQL destination .
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 14, 2009 at 3:48 pm
Marios Philippopoulos (8/14/2009)
gah (8/14/2009)
what response times do you get if you use OPENQUERY against the LinkedServer?are you filtering on any records in the source table?
Thanks for the suggestion, actually this is in fact what we are doing inside the source view:
...
FROM
OPENQUERY
( LinkedServerName, 'SELECT...' )
...
The response time is quite good, but I do need to check whether it is also good during the night when the job is running.
have you any "WHERE" clauses in your SELECT...
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 17, 2009 at 1:59 pm
gah (8/14/2009)
have you any "WHERE" clauses in your SELECT...
Yes, we have a WHERE clause in the OPENQUERY SELECT.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 26, 2009 at 4:45 pm
The performance issue is fixed.
The package has gone from running for over 3 hrs to 10 seconds!!
The change you ask?
In the OLEDB Source of my data-flow component I switched Data Access Mode from "Table or View" to "SQL Command" and, instead of specifying the view name, I specified the actual SELECT with the view columns explicitly defined:
SELECT col1,... FROM viewName;
That was it! Thank you to Elliott W who pointed me in this direction!
I don't understand why though, can someone explain why such a huge difference in performance between the 2 settings?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 27, 2009 at 7:54 am
I can't remember for sure, I know there is a reason, but I can't remember why. Sorry man..
CEWII
August 27, 2009 at 7:59 am
Elliott W (8/27/2009)
I can't remember for sure, I know there is a reason, but I can't remember why. Sorry man..CEWII
No worries, I'd like to learn more about the internals of SSIS;
this info is not easy to find though.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
August 27, 2009 at 11:21 am
I think I have your answer:
http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ssis-tuning-the-dataflow-task/
Use a SQL Select Statement to Retrieve Data From a View
Avoid using the Table or view access mode in the OLE DB Source Adapter. It is not as performant as using a SELECT statement because the adapter opens a rowset-based on the table or view. Then it calls OpenRowset in the validation phase to retrieve column metadata, and later in the execution phase to read out the data.
Testing has shown that using a SELECT statement can be at least an order of magnitude faster, because the adapter issues the specified command directly through the provider and fetches the data using sp_prepare without executing the command, avoiding the extra roundtrip and a possibly inappropriate cached query plan.
CEWII
August 27, 2009 at 11:26 am
Elliott W (8/27/2009)
I think I have your answer:http://www.simple-talk.com/sql/sql-server-2005/sql-server-2005-ssis-tuning-the-dataflow-task/
Use a SQL Select Statement to Retrieve Data From a View
Avoid using the Table or view access mode in the OLE DB Source Adapter. It is not as performant as using a SELECT statement because the adapter opens a rowset-based on the table or view. Then it calls OpenRowset in the validation phase to retrieve column metadata, and later in the execution phase to read out the data.
Testing has shown that using a SELECT statement can be at least an order of magnitude faster, because the adapter issues the specified command directly through the provider and fetches the data using sp_prepare without executing the command, avoiding the extra roundtrip and a possibly inappropriate cached query plan.
CEWII
Thanks!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply