How To Create Missing Index From Execution Plan

Introduction

This article gives an explanation about how to find and create a missing index from an SQL server query execution plan and also shows you how you can improve your query execution performance and run your query faster. In performance tuning, indexing plays 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.

Many developers who are working with the data-driven application will at least have 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. Many may not know how to improve query performance and run a query faster in any of databases, whether it's the Oracle Database, SQL Server, MYSQL, PostgreSQL, SQLite, Microsoft Access, etc. So, In this article, I'll show you how to identify the suggested missing index by the actual execution plan and how to create the missing index.

While you run your query with the 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, for now I'll just show you the single missing index only.

Components

What is performance tuning technique?
What is the Index?
What Are Missing Indexes?
Types of Index and its syntax
How to create an actual execution plan and how to find and create a missing index from it.

What is a 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 and many other related articles, to read these articles you also can visit my blog codingvila.com.

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,

Clustered Index
Non-Clustered Index

Clustered Index

In SQL Server the clustered index is the index that will arrange and manage the rows of tables 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 tables 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 to run more quickly. These indexes are called 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.

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 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 the missing index.

This is the code that 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, which can degrade your SQL Server performance. So be sure to review your actual query execution plans and 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 missing index is.

Reference Link - Codingvila - Create Missing Index From Actual Execution Plan in SQL Server