April 2, 2019 at 11:24 am
Hello
I am using SQL 2016
I have a package that performs a sizable number of ETL tasks
I'd like to profile the package with a view to (potentially) performance tuning some of the SQL tasks
Historically, I've looked at each one in turn
I know I could trace (profile) and run this through something like Database Tuning Advisor
Can I use Extended Events for this?
Ideally, I just want to run the package then look at all time consuming tasks and recommended indexes etc.
Thanks
- Damian
April 3, 2019 at 2:10 pm
Damian it sounds like you are looking at tuning your TSQL which in turn affects the performance of your package but if you have other tasks in your package you'd probably want to look at the package logs as well.
If you want to tune the TSQL code then I would suggest copying that code and running it in SSMS with the Actual Execution Plan turned on. This will give you a ton of information on what costs the most in that query as well as table/index scans etc.
March 10, 2020 at 10:45 am
Thanks AVB
- Damian
March 10, 2020 at 11:37 am
Why don't you start by looking at the Package Execution Time and just start with the longest running ones? 8 / 10 cases I've had were related to non TSQL things like Script Tasks or Script Components.
This way you can aswell easiliy relate the statement to which Package / DFT / whatever in detail is responsible for that, I've got SSIS Projects here with 150 SSIS Packages, considering some data might come / go to different tables yet have similar looking statements I see no direct use for Extended Events here. Check the execution duration -> check what happens with the statement in there (Estimated / Actual Execution Plans, Statistics) and you'll have the suggestions at your disposal.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply