How to find missing indexes- SQL Server

As a developer its required to have proper index on tables for better performance.
But while number of person querying a table its not possible to track and analyze all queries.
In this case we need some metadata for all queries which shows us which queries does not have index and creating index can improve x% of performance.



Now its possible after SQL Server 2005

SQL Server has system tables which provides this metadata

sys.dm_db_missing_index_columns

sys.dm_db_missing_index_details

sys.dm_db_missing_index_groups

sys.dm_db_missing_index_group_stats

First we want to know stats of columns on which index can benenfit us.

sys.dm_db_missing_index_group_stats
It provides number of user_seeks and user_scans which can be benefitted from creating index. The high number of user_seeks and user_scan its more prone to new index.It also provides how much perfomance can be improved by avg_user_impact.Similary also provide info for system_seeks and system_scans and avg_system_impact for scans and seeks by system.We are generally more interested in user_seeks and user_scans.

sys.dm_db_missing_index_groups
It provides relationship between dm_db_missing_index_group_stats and dm_db_missing_index_details.

sys.dm_db_missing_index_details
It provides index column details for indexes suggested by dm_db_missing_index_group_stats.


sys.dm_db_missing_index_columns
Returns information for columns which are missing index.
Provides information for column name and column usage

Column usage
1. Equality -- Columns which are used for Equality like a.col1 = b.col1
2. InEquality -- Columns which are comared other than Equaltiy like a.col1 > b.col1
3. Included -- Columns which are not part of comparison but part of query like covering index

While creating index we should first pue equality columns , then in equality column and then Included if we want to create covering index



USE adventureworks


DECLARE @i INT


SET @i = 1


WHILE @i <= 10

BEGIN

SELECT *

FROM sales.customer

WHERE customertype = 's'

AND territoryid = @i

SET @i = @i + 1

END




Now we will look into system tables for this query to find suggested indexes

First we need to find suggested index and number of user_seeks/user_scans

SELECT md.object_id,

md.database_id,

mgs.unique_compiles,

mgs.user_seeks,

mgs.user_scans,

mgs.last_user_seek,

mgs.avg_total_user_cost,

mgs.avg_user_impact,

md.equality_columns,

md.inequality_columns,

md.included_columns,

md.statement

FROM sys.dm_db_missing_index_group_stats mgs

INNER JOIN sys.dm_db_missing_index_groups mg

ON mg.index_group_handle = mgs.group_handle

INNER JOIN sys.dm_db_missing_index_details md

ON mg.index_handle = md.index_handle

WHERE md.database_id = Db_id()

AND md.object_id = Object_id('sales.customer')






Here we can see that number of user_seeks are 10, avg_user_impact is 88.2. It means if we create index the performance can be 88% improved. Its great!Now we will look at equality coulmns which are [TerritoryID], [CustomerType] and inequality columns are null.

So we need to create index on [TerritoryID], [CustomerType].

Create index idx_customer_TerritoryID_CustomerType on sales.customer([TerritoryID], [CustomerType])

Cheers.