November 9, 2016 at 7:38 am
Hello friends,
Although very new, I have been assigned the task of going through a large (~10,000 line) stored procedure which apparently does all of the calculations for COGS and all of those related reports are run off of this data. The first part of my job is to pull together a list of tables that need to be pushed to the data warehouse via ETL.
Do you have any suggestions or advice for how to go about doing this? I apologize in advance if this seems like a simple question but I am new and feel very overwhelmed on this.
Thank you
November 9, 2016 at 7:54 am
If it's not using dynamic sql, you can start by using sys.sql_expression_dependencies and sys.sql_dependencies
November 9, 2016 at 8:26 am
I'm sorry you have to learn T-SQL by having a 10,000 line stored procedure dumped on your plate, but this is how most of us got our start.
You can go into SSMS Object Explorer, right click on stored procedure, choose 'View Dependencies', choose 'Objects on which procedure depends', and then drill down on a treeview of tables, views, and functions for which there is a dependency. Some objects depend on additional objects, so you'll need to expand the entire tree to see everything.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
November 9, 2016 at 9:06 am
jporter 4363 (11/9/2016)
Hello friends,Although very new, I have been assigned the task of going through a large (~10,000 line) stored procedure which apparently does all of the calculations for COGS and all of those related reports are run off of this data. The first part of my job is to pull together a list of tables that need to be pushed to the data warehouse via ETL.
Do you have any suggestions or advice for how to go about doing this? I apologize in advance if this seems like a simple question but I am new and feel very overwhelmed on this.
Thank you
I'm tempted to make the assumption that there may be different execution paths within such a stored procedure, not certain that all dependencies would by detected/reported. Suggest to open the procedure in either SSMS or a good text editor such as Notepad++ and start chewing, find all FROM clauses and grab what you find there.
😎
In my experience, working through 10K lines of code for dependencies etc. is an easy days worth of work but optimising one of those can easily take weeks.
November 9, 2016 at 10:34 am
There's a chance with that kind of procedure that you're also dealing with ad hoc execution. If you have ad hoc T-SQL, you may find that you won't see all the tables through the dependency tracker.
Probably your best bet to getting this right is the Mk I Eyeball. Approach it like eating an elephant, one bite at a time.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply