Date:

Share:

Create Missing Index From Actual Execution Plan | SQL

Related Articles

This article explains how to find and create a missing index from a SQL Server query execution program and shows you how you can improve your query execution Performence and run your query faster. B performance tuning An index plays an important role and helps to run and execute your query faster.

This article also gives basic information about index 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 the query PerformenceAnd how to run query faster in SQL server database.

Many developers who work with the data-driven application have at least heard talk of a database Performance tuning techniques and indexed.

Even if developers/know at a basic level what database and index performance tuning techniques do, they are not always sure when to use an index and how to create an index, what are index types and what are database performance tuning techniques. What are the main factors of database performance tuning technique, and may not know how to improve query performance Run a query faster In any database it is Oracle Database, SQL Server, MYSQL, PostgreSQL, SQLite, Microsoft Access, etc.

Therefore, in this article, I will show you how to identify the missing index proposed by the actual execution plan and how to create the missing index.

demand

1) Explain what is performance tuning technique?.

2) What is an index?

3) What are missing indexes?.

4) Index types and their syntax?.

5) Explain how to create an actual execution plan and how to find and create a missing index from it.

What is the performance tuning technique?

SQL Server Performance Tuning is the method of making sure that SQL statements issued by the associated application will execute at the fastest time. In other words, tuning SQL statements is finding and taking the fastest or fastest route to answer your query, similar to discovering the fastest or fastest route to your home after work. We can say that it is a process of improving system performance.

In my previous article, I also explained what another type of SQL Server Performance Tuning Technique that helps improve your query performance, where I explained what factors you should keep in mind when creating a query and how you can get a faster query response.

What is an index?

An index is a process that returns your requested data as quickly or as quickly as possible from the defined table. In SQL Server, indexes are used to retrieve the data quickly. It is similar to the index available on the first or last page of the book that aims to find a chapter or topic quickly.

Index types and its syntax?

In SQL Server, indexes are of 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 the table or view physically in memory in sorted order by their key values. While 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.

syntax

/*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 table rows physically in memory in a sorted order compared to the clustered index.

syntax

/*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 processes an SQL query, it typically creates a suggestion for an index that it thinks will make that query run faster. 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 relationship tables with names “tblCodingvila_1“and”tblCodingvila_2“B”tblCodingvila_1“”Article ID“is the master key and”tblCodingvila_2“”Article ID“He is a foreign developer.

To create an actual execution plan, you need 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 should run your query and you can see in your results window another tab is created called “Execution Plan” as shown below.

If you analyzed the generated execution plan then the green colored text shows the details of the missing index, you can move the mouse pointer to the missing text and index SQL Server 2008 Management Studio will display the T-SQL code required to create the missing index or you can click the mouse to Right-click the missing index text, and then select Missing Index Details from the list to see the details of the missing index.

This is the code generated by SQL Server 2008 Management Studio intelligence while you select the missing index information option from the list as I have shown below.

/*
Missing Index Details from SQLQuery1.sql - DESKTOP-P1PHIU6SQLEXPRESS.DB_Codingvila (DESKTOP-P1PHIU6Nikunj-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 */

See, SQL Server 2008 Management Studio intelligence automatically created the missing index based on your actual execution plan, now you just need to give your index name and just run the statement, and your index is created.

USE [DB_Codingvila]
GO
CREATE NONCLUSTERED INDEX [INDX_codingvila_articles]
ON [dbo].[tblCodingvila_1] ([articleId])

important points

Missing indexes can affect your SQL Server performance, which can reduce your SQL Server performance, so be sure to check your actual query execution plans and identify the correct index.

Note that the script above does not include an index name, so you must name your index according to your coding standard

Summary

In this article, we learned how to identify missing indexes from the actual execution plan in SQL Server Management Studio and what is a performance tuning technique, how to create an actual execution plan in SQL Server Management Studio and what is an index, types of measure and what is a missing measure.

Source

Popular Articles