Sql Tunining - Understanding execution Plan in Simple Way

  Last post we have seen how to clear data pages ,index pages and execution plan from buffer.Now next step in performance tuning is to understand execution plan.you may have question like how the execution plan is  generated? who generate it?how it is important? or you may have lot of question which I have not mentioned here.  Execution plan is visual representation of the operation performed by database engine in order to return data required by the query.If you cam read and understand execution plans then you can better understand how you query is executing internally.

How it will helpful to increase performance as I don't know how to read it ?
              I will answer this question like suppose you have one query and you have executed  and execution plan is generated .You have execution plan too.Reading execution is bit difficult first time but after some time you will find it easy. You may have question from which side I will start reading it? You always need too start from right side.Logic is simple we will have  few input  query ,which will merge at last and provide result  .Yo can see few different terms like merge join ,inner join,nested loop,RID look up,Stream Aggregated etc all this term are called operator in execution plan .So you will see what are my input queries which are generating result,which query is taking more time  and how better I can  tune query to get performance.
         Lets try to understand simple execution Plan,
Script :

CREATE TABLE  TEST3
  (
     [Credit_Card_id] [INT] NOT NULL,
     [Name]           [VARCHAR](100) NULL,
     [Limit]          [INT] NULL,
     [Valid_from]     [INT] NULL,
     [Valid_To]       [INT] NULL,
    
  )

select * from Test3

We have created script and lets run it ans see execution plan,

You can see above execution plan  and try to understand how it looks like.It shows two operator ie. Select and Table Scan.From where you will start reading it ? Off course you will start from right .you can understand there is table scan on Test3 table and finally select is running and giving output.Lets try to understand bit complex execution plan.

Script :

SELECT *
FROM   test3 Test1
       INNER JOIN test3 test2
               ON Test1.Credit_Card_id = test2.Credit_Card_id

  This query has two input as we are joining  two table .Lets  understand it with help of execution plan

 In above plan you can see three operator Select,Table Scan,Hash Join .We can clearly see there are two input Test1 and Test2  each one is contributing 13% so total time for forming input is 26%.
Now you can see 73% of time is for hash join which is because our input tables are not having any index if we create index on this table we can avoid hash join and gain performance.
Lets create index  and will see what will happen,


CREATE TABLE  TEST3
  (
     [Credit_Card_id] [INT] NOT NULL,
     [Name]           [VARCHAR](100) NULL,
     [Limit]          [INT] NULL,
     [Valid_from]     [INT] NULL,
     [Valid_To]       [INT] NULL,
   PRIMARY KEY CLUSTERED ( [Credit_Card_id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  )

Run below query,
SELECT *
FROM   test3 Test1
       INNER JOIN test3 test2
               ON Test1.Credit_Card_id = test2.Credit_Card_id

Execution Plan,

.



In above plan you can see comparison cost is 0% So we have increase performance of the query.Please let me know  if you have doubt.Please comment if you like it.