Missing Non Clustered Index Impact

Question

I have found several missing Non Clustered Index from the SQL tables. I just want to know the impact if I will implement the missing Non Clustered Index that I found.

Thanks

Answer 1

You can see that in the Execution Plan, find this button on SSMS:

Now paste the query into SSMS and press F5, the query will be executed. Now go into the Execution plan tab:

this query for example has an high impact:

(Sometimes SSMS suggest you how to improve the query sometimes no; don't expect to see this green phrase all the time.)

Now, from here to tell you that by adding that index the query will be 99.6192 faster is a long shot.

SQL Serve is suggesting you that adding that index might help a lot.

The best way would be to:

1. Backup the database
2. Create a test environment
3. add the index
4. test if the same query now run faster

Keep in mind that if you are using SQL Server 2016 or later you can use Query Store, which is the equivalent of the Execution plan but on steroids.

If you want more info use sp_BlitzIndex and get familiar with it.

Use Database Health Monitor, IDERA SQL Check, isitsql which are free tool and will help you take some screenshot about the graphical view of the query.

Basically you have to prove to your manager that the change you put in place has improved the performances.

EDIT:

I think your question is very pertinent and in I will also give you a query from Pinal Dave that will allow you to have impact of the missing index:

-- Missing Index Script
-- Original Author: Pinal Dave 
SELECT TOP 25
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','') 
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns 
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO

The Avg_Estimated_Impact is calculate by the formula:

(Number of times the table was queried) * (Seeks + Scans) 

So don't expect this value to be between 0 and 100. Is not on a scale.

Overall I think is a genius idea calculating the factor this way and ultimately your question was pertinent.

I know you are new, please next time post some code.