June 4, 2020 at 5:32 pm
I have an SP which is truncating a table and then insert into and selecting again. Now, I am planning to tune this SP. if I decide to view the estimated execution plan then is it a safe action or will it change any data, I know actual execution plan will change. Please let me know your thoughts.
Ok, I was able to generate the estimated plan, I see the plan is broken into multiple parts, that could be due to the code. However can we change it to display like a continuous flow. Broken plan does not make much sense. attached picture for your reference.
Also, the query cost steps in execution plan do not display much, is it like I have to generate actual plan for more information>?
sorry about attaching multiple images, I am unable to delete the extra image.
Thanks!
June 4, 2020 at 6:34 pm
this looks like the execution plan for creating a bunch of stored procedures, not the execution of a stored procedure. Did you run the estimated plan against the stored procedure script, a call to the stored procedure, or the body of the stored procedure directly as a script?
June 4, 2020 at 6:37 pm
Yes, I selected the code generated using sp_helptext ...and then for that code, I generated the estimated plan.
June 4, 2020 at 7:01 pm
Do you understand why there would be a difference between the execution plans for
CREATE Proc x AS
Select * from tab1
and
Select * from tab1
? The first is pretty much useless, it's the second which is interesting.
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
June 5, 2020 at 12:57 pm
So, you're looking at the markers that SQL Server creates when you ask for an execution plan for Data Definition Language (DDL). There are no execution plans created for DDL. However, when you request a plan, SQL Server has to show you something. So it shows this. This is not the same as an execution plan.
If you want to see the plan for your procedure then do this:
EXEC dbo.MyProc @MyParameter = '42';
Highlight that code and ask for what's called an "Estimated Plan". You'll get an execution plan, or plans, based on the query, or queries, within the procedure.
"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
June 5, 2020 at 5:50 pm
Great, Thanks!! yes, I was able to do SP call in the test environment and generate the actual plan. Attached. Please let me know your thoughts, I do not want to rush on missing IDX hints, also the SP call is processing around 150000 rows...the good things is sp call took only 03 secs..which is nice.
June 5, 2020 at 8:07 pm
Great, Thanks!! yes, I was able to do SP call in the test environment and generate the actual plan. Attached. Please let me know your thoughts, I do not want to rush on missing IDX hints, also the SP call is processing around 150000 rows...the good things is sp call took only 03 secs..which is nice.
Glad to hear it's running fast.
However, I'm sorry, but I can't help. An execution plan isn't a picture. The details that determine what's going on are all in the properties behind the picture. You'd need to post the plans as an XML file for any of us to be able to comment on them. However, if you want to protect the information in the plan, you should get a copy of Plan Explorer from SentryOne. It's free. It has a way to anonymize the plan. Run through that before you post the plan.
Sorry to make this difficult. There's just no way to say anything about a plan based on a picture of it.
By the way, if you want to learn more about execution plans, look down at my signature. That book on execution plans is free to download.
"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
June 5, 2020 at 8:12 pm
Thanks! I do have the sql sentry ...so let me try to anonymize the plan ....this is a great tip!. Yes, I will be downloading the book and thanks for sharing the free version.
June 8, 2020 at 11:50 am
I was able to attached the plan, please look at the actual plan and let me know your thoughts. Thanks!
Not sure what you uploaded, but it's not a .sqlplan file that I can open in SSMS.
"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
June 8, 2020 at 4:47 pm
Sorry for the inconvenience, I uploaded the zipped format of sql sentry cost explorer plan. So, I think you might need to view it.
June 8, 2020 at 9:59 pm
Looking at your pesession file, I see the first query is doing an index scan on Object12 and Object13, then hash matching them together. The reads of Object12 seem to be a large part of the IO cost. Is there an appropriate index on Object12 that matches whatever filtering criteria there is?
In query 2, we see that Object12 is a problem again, it looks like it did a full scan to return 1 row. Query 3 looks similar to Query 1, and is constrained by reading Object12 as well.
How many rows are in Object12 and what indexes are on it? I notice that Object12 joins to Object13 on Column19, is Column19 indexed in both tables? Considering that is the largest join in 2 of the queries, it's a place to start for optimization.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply