October 21, 2019 at 8:53 pm
Please let me know your thoughts. FYI.. I removed the tb and Dbname.
First question..
Attached is the screen shot of the SP for which I generated the estimated plan the steps I did to create the plan was
USE DBNAME
EXEC SPNAME – then right click on the code estimated plan. My only concern is this the right way to create the execution plan or did I change any data by generating the plan?
Second question…
Upon looking at the execution plan I know it has INX hint however how to make sure it is not going to impact negatively like too many writes instead of reads?
Also, is there any way to improve the look and feel. Like instead of query 1….5 being split can we make it into one.
I really want to know why it splits.
Thank you!
October 22, 2019 at 10:42 am
The plan splits for each block of code - the bottom 2 sections are function calls within your proc - i'm afraid you can't keep them as 1 plan (that I know of)
an estimated execution plan wont change your data - but there is a way of getting an actual execution plan without changing data
begin transaction
exec myproc
rollback transaction
actual execution plans are far more usefull
MVDBA
October 22, 2019 at 11:08 am
@sizal0234 - It is also important how you read the execution plan. Usually, you read a graphical execution plan from right to left and top to bottom.
Read the below article for self-help to get started with the Execution Plan in SQL Server including how to interpret it.
https://www.red-gate.com/simple-talk/sql/performance/execution-plan-basics/
October 22, 2019 at 3:04 pm
Understanding the impact of creating a potential index requires you take a more holistic approach to how that table is used. If you look at sys.dm_db_index_operational_stats and sys.dm_db_index_usasge_stats, you can get an impression of how it has been recently used with counts of seeks, scans, lookups, updates etc:
https://www.mssqltips.com/sqlservertip/1239/how-to-get-index-usage-information-in-sql-server/
Also be careful when dealing with missing index hints in execution plans or using the missing index DMVs, they can often give you misleading or even poor results:
https://www.brentozar.com/archive/2017/08/missing-index-recommendations-arent-perfect/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply