SQL Query Plans Each Developer Should Know

Overview

The heading of this topic itself says it all: SQL Query Plans that each and every developer should know. So far I have seen many developers in my total experience and they are really good developers; developing really nice applications and having a good knowledge about developing a webapp, window app and so on - whether in Java or .NET. I remember in my previous firm there was a developing unit who was really good at developing applications for the banks where they have to fetch CustID from various databases like Finance and they were good in doing so. The developers are generally doing a great job as they are constantly working day in and day out on their projects.


(Image Source:dmitriydef.ru)

When a particular module or report data is getting delayed, they straight away run to the DBA team to analyze the problems. They have the credentials and other details but they just don’t know how to begin and from where to start . They feel it’s not part of their job and that’s right depending which DBA team is there to help you. When you have the credentials of the database, it’s just a matter of trying things out. 


(Image Source:www.alexnenov.com)

How long does the developer keep on developing applications? I feel that each developer should have some knowledge and that's how my queries are executing and why in a particular module, the reports are taking a long time.

Tip 1: These are just SQL execution plans as to how a particular session is executing which query.

Tip 2: Just keep on practicing on Local PC if you are having any UAT Setup.. An added advantage of these kind of topics is that it always helps in cracking an interview. One of my friend didn’t get selected in top MNC’s because he had knowledge only on Java and the  other guy got selected as he had Skills in JAVA and Some Skills in SQL . These things help developers to grow.

Let’s Start

• Just run the respective SQL Queries and resultant output is shown in grid. Right click Display estimated execution plan followed by clicking on the execution plan tab and you will see the execution. Whatever it is, it may be a simple SELECT statement or complex joins and you will able to see the plans of your queries.
  
  
  
  You will see a query plan which is executed below:
  
  
  
  As you can see Query Cost, the query and some icons appeared.
  
  Note: Reading a query is done from left to right.
  
  
• Let’s look at some of the Icons so that we will get a clear idea.
  
  As there are many graphical execution plans, I am emphasizing on the most used plans here
  
  

  	Shows the result that are returned.
  	It is a language element T-SQL
  	The Non-clustered Index Delete operator deletes rows from a non-clustered index
  	The Non-clustered Index Insert operator inserts rows into the non-clustered index
  	The Non-clustered Index Scan operator reads all rows from the non-clustered index.
  	The Non-clustered Index Seek operator, doesn’t scan the whole table/view to read the necessary records, but uses indexes to seek for specific
  	While Loop

  
  
• I am illustrating this example on the local machine on a server level where there are more users connected. Just run the query to get output.
  
  
  
  The above output shows-

Two tables are created in test as in above diagram and the values are inserted in the table

• 
  
  
  Open a new Connection on that same database and just run the following Query
  
  
  Note: Do not write end transaction. Now open another connection
  
  
  
  You will see that query is executing .
  
  Now open another session and run the query on the master.
  
  SP_WHO2.
  
  
  
  When you run SP_WHO2 query, you will see that DBName appears which I highlighted and also note the SPID.
  
  Now run the above query.
  
  
  
  You will see the SQL Handle and lets see what query is being shown in SQL Handle.
  
  
  
  Now you will see that which query is taking time for that particular session and where SQL text is clearly specified? It becomes easier to find out SQL Text and tune the queries accordingly. Consider complex joins or SQL sub queries which requires tuning.
  
  Now run the query as:
  
  
  
  It will give you XML output format when you run the below query CUSTOMER INFO and hyperlink information type will be displayed.
  
  

Now just click on that hyperlink and you will see the execution plan

Other useful commands are:

• SET STATISTICS XML ON

It will show the statistics of a query:

• SET SHOWPLAN_TEXT OFF

It will show you the plan.



When you hover a mouse, you will see that a Yellow color tooltip appears.



Use of Query Cache



Here you will see the details of each query that is running.

Conclusion

All the queries which I had mentioned and attached screenshot are just the basics for any developer as these basics come in handy wherever you are.

More advanced queries for tuning a query are forcing to kill a session on update and so on which will be coming soon.