SQL Server Missing Index

Hi,

The use of Index in the SQL Server database occurs in environments that require the most performance, speed, and memory savings.

 

In a table with millions or billions of records, we can use a Index  to read fewer records and search less to find related record.

Accurately created Index, millions of records within the database we have searched within a very short time to bring the record of the caller’s convenience, while at the same time less read the record by reaching the target record, we use the operating system resources effectively.

You should create index for mostly read only queries on a table.  If Delete,update operations are more than read only queries, you should not create index that table.

You can look at the missing index recommendation of SQL Server with following script. You can create missing index but you should monitor these index, If they are not useful, you should drop them.

 

SELECT MID.[statement] AS ObjectName
,MID.equality_columns AS EqualityColumns
,MID.inequality_columns AS InequalityColms
,MID.included_columns AS IncludedColumns
,MIGS.last_user_seek AS LastUserSeek
,MIGS.avg_total_user_cost 
* MIGS.avg_user_impact 
* (MIGS.user_seeks + MIGS.user_scans) AS Impact
,N'CREATE NONCLUSTERED INDEX <TYPE_Index_Name> ' + 
N'ON ' + MID.[statement] + 
N' (' + MID.equality_columns 
+ ISNULL(', ' + MID.inequality_columns, N'') +
N') ' + ISNULL(N'INCLUDE (' + MID.included_columns + N');', ';')
AS CreateStatement
FROM sys.dm_db_missing_index_group_stats AS MIGS
INNER JOIN sys.dm_db_missing_index_groups AS MIG
ON MIGS.group_handle = MIG.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS MID
ON MIG.index_handle = MID.index_handle
WHERE database_id = DB_ID()
AND MIGS.last_user_seek >= DATEDIFF(month, GetDate(), -1)
ORDER BY Impact DESC;

 

Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.

https://ittutorial.org/sql-server-tutorials-microsoft-database-for-beginners/