SQL Server - Create Missing Index From Actual Execution Plan

watch_later Saturday, April 13, 2019

Introduction


This article gives an explanation about how to find and create a missing index from SQL server query execution plan and also show you how you can improve your query execution performance and run your query faster. In performance tuning indexing play an important role and helps to run and execute your query faster so, this article also gives basic information about indexing in SQL server such as what is an index, what are the types of index, how to identify the current index from query execution plan and how to improve query performance and how to run query faster in SQL server database.

Missing Index

Many developers those who are working with the data-driven application will have at least heard talk about database performance tuning technique and indexing. Even if any developers/know on a basic level what database performance tuning technique and indexing do, they are not always certain when to use the index and how to create an index, what are the types of indexing and what is database performance tuning technique and what are the major factors of database performance tuning technique and also may didn't know how to improve query performance a run query faster in any of database either it is Oracle Database, SQL Server, MYSQL, PostgreSQL, SQLite, Microsoft Access, etc. So, In this article, I'll show you how to identify suggested missing index by actual execution plan and how to create the missing index.

while you run your query with actual execution plan the execution plan will suggest some indexes which are missing. In SQL Server the query optimizer can give multiple missing index suggestions for your individual queries and how to identify multiple missing indexes that I will discuss and show in my next article, now I'll just show you for single missing index only. 

Requirement 


1) Explain what is performance tuning technique?.
2) What is Index?.
3) What Are Missing Indexes?.
4) Types of Index and its syntax?.
5) Explain how to create an actual execution plan and how to find and create a missing index from it.

Implementation


What is performance tuning technique?


SQL server performance tuning is the method of making certain that the SQL statements issued by the associate application run within the quickest possible time. In other words, tuning SQL statements are finding and taking the quickest or fastest route to answer your query, similar to discovering the quickest or fastest route to your home after work. We can say it is a process of improvement of system performance.

In My previous article, I also explained what is a different kind of SQL server performance tuning techniques that helps to improve your query performance, where I explained what are the factors that you should remember while creating a query and how you can get query response faster.

What is Index?


Indexing is a process that returns your requested data as much faster or quickly as possible from the defined table. In SQL server Indexes are used to retrieve the data quickly. It is similar to an index available on the first or last page of the book whose purpose is to find a chapter or topic quickly.

Types of Index and its syntax?


In SQL Server indexes are two types:

1) Clustered Index
2) Non-Clustered Index

Clustered Index


In SQL Server the clustered index is the index that will arrange and manage the rows of table or view physically in the memory in sorted order on their key values. At the time of creating the table, the clustered index is automatically created on the primary key of the table and there can be only one clustered index per table.

Syntex

/*Create Clustered index index*/
CREATE CLUSTERED INDEX IX_your_table_name_column_name 
ON your_table_name (your_column_name ASC) 

Non-Clustered Index


In SQL Server the non-clustered index is an index that will not arrange and manage the rows of table physically in the memory in sorted order as compared to the clustered index.

Syntex

/*Create Non-Clustered index*/
CREATE NONCLUSTERED INDEX IX_your_table_name_column_name 
ON your_table_name (your_column_name ASC)

What Are Missing Indexes?


When SQL Server is processing a SQL query, it'll generally create a suggestion for an index that it believes will facilitate that query run quicker these Indexes are called as Missing Indexes.

Now, we will learn how to create an actual execution plan and how to find and create a missing index from it with a simple example. Here, I have two relational tables with name "tblCodingvila_1" and "tblCodingvila_2" in "tblCodingvila_1" "articleId" is primary key and in "tblCodingvila_2" "articleId" is foreign key.

To generate an actual execution plan you have to include the actual execution plan from the toolbar option as shown in the screen below.

Actual Execution Plan

Query

SELECT tblCodingvila_1.articleId, tblCodingvila_2.articleId
FROM dbo.tblCodingvila_1 tblCodingvila_1 WITH(NOLOCK)
    INNER JOIN dbo.tblCodingvila_2 tblCodingvila_2  WITH(NOLOCK) ON tblCodingvila_1.articleId = tblCodingvila_2.articleId
WHERE tblCodingvila_2.articleId = 126

Now, you have to run your query and you can see in your result window there is an extra tab is generated with the name "Execution plan" as shown below.


If you analyzed the generated execution plan then the green color text shows the details of the missing index, you can move your  mouse pointer on missing Index text and SQL Server 2008 Management Studio intelligence will show the T-SQL code that is required to create the missing index or you can press your mouse to right-click on missing index text then select the missing index details option from the list to see the details of missing index.


This is the code which is generated by SQL Server 2008 Management Studio intelligence while you select the missing index details option from the list as I have shown below.
/*
Missing Index Details from SQLQuery1.sql - DESKTOP-P1PHIU6\SQLEXPRESS.DB_Codingvila (DESKTOP-P1PHIU6\Nikunj-PC (53))
The Query Processor estimates that implementing the following index could improve the query cost by 49.3232%.
*/
 
/*
USE [DB_Codingvila]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[tblCodingvila_1] ([articleId])
 
GO
*/
Look,  SQL Server 2008 Management Studio intelligence automatically generated the missing index based on your actual execution plan, now you just have to give the name of your index and simply run the statement and your index is generated.
USE [DB_Codingvila]
GO
CREATE NONCLUSTERED INDEX [INDX_codingvila_articles]
ON [dbo].[tblCodingvila_1] ([articleId])

Important Points


Missing indexes might affect your SQL Server performance, that can down your SQL Server performance, So be sure to review your actual query execution plans and the identify the right index.

Note that the above script does not include a name of the index, so you need to give a name of your index as per your coding standard

Summary


In this article, we learned about how to identify missing index from the actual execution plan in SQL server management studio and what is performance tuning technique, how to generate an actual execution plan in SQL server management studio and what is an index, types of the index and what is missing index.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.



sentiment_satisfied Emoticon