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 play an important role and helps to run and execute your query faster.
This article also gives basic information about indexing in SQLserversr such as what is an index, what are the types of index, how to identify the current index from the 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 have at least heard talk about database performance tuning techniques and indexing.
Even if any developers/know on a basic level what database performance tuning techniques 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 techniques 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 the suggested missing index by the actual execution plan and how to create the missing index.
Requirement
What is the 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 technique 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 of two types:
Clustered Index
In SQL Server the clustered index is the index that will arrange and manage the rows of a 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 to 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 names "tblCodingvila_1" and "tblCodingvila_2" in "tblCodingvila_1" "articleId" is the primary key and in "tblCodingvila_2" "articleId" is a 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 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 to the 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 down 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
