Sql-server – Query Plan showing missing index warning on trivial statements

execution-planprofilersql server

I have a Stored Procedure (ReportAdvancedUP) which runs fast (~1sec) in MSSMS but takes a long time (~1.8mins) via our Web Application. I am aware that this usually comes down to bad choice of Execution Plan so I've had a look at both examples.

Running in MSSQS the Actual Execution plan comprises of four Clustered Index Seeks joined by nested loops. No problems.

I then use the following query to pull out the cached execution plans used by my Stored Procedure (ReportAdvancedUP):

SELECT 
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM 
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE 
    cp.objtype = 'Proc'
    AND OBJECT_NAME(st.objectid,st.dbid) = 'ReportAdvancedUP';

I get a QueryPlan that is not only much larger but is also reporting "Missing Index (CREATE NONCLUSTERED INDEX)" entries on queries as trivial as SET NOCOUNT ON; and IF(@ToDate IS NOT NULL)!!!

Furthermore the index it is saying is missing doesn't make sense in the context of the procedure in general… e.g.

  1. I have a table called OrganisationLocations with primary key is ID int IDENTITY(1,1) and a foreign key OrganisationID int NOT NULL.
  2. The table is clustered as Organisation, ID as they are always used together and it is better to group entries by Organisation than by ID
  3. I nearly always join to the OrganisationLocations without providing BOTH the ID and OrganisationID, both from a security perspective and also for optimisation.

However, the index it keeps telling me is missing is:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[OrganisationLocations] ([OrganisationID])

Can anyone suggest what might be going on here, regarding [a] the difference in execution plans, [b] Missing Index warnings on trivial code and [c] why it might want to add an index by OrganisationID alone.

Also, please advise what additional information might be helpful.

Thanks,
Chris

Best Answer

Without seeing the plan and based on the information you provided, it sounds like you don't have that column, OrganisationID, in one of your existing indexes. Foreign keys aren't automatically indexed, so if it is part of your join or in the where clause then there could possibly be a benefit of adding the index. The missing index feature isn't always right, so you should always review whether you really need the columns suggested indexed or if you can modify another index to include the columns instead of adding a separate index.

You can use this query from Glenn Berry to look at the missing index dmvs and see what the index advantage, unique_compiles, and user_seeks to help make the decision:

select user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) as [index_advantage]
    ,migs.last_user_seek
    ,mid.[statement] as [Database.Schema.Table]
    ,mid.equality_columns
    ,mid.inequality_columns
    ,mid.included_columns
    ,migs.unique_compiles
    ,migs.user_seeks
    ,migs.avg_total_user_cost
    ,migs.avg_user_impact
from sys.dm_db_missing_index_group_stats as migs with (nolock)
inner join sys.dm_db_missing_index_groups as mig with (nolock) on migs.group_handle = mig.index_group_handle
inner join sys.dm_db_missing_index_details as mid with (nolock) on mig.index_handle = mid.index_handle
--where mid.[statement] = '[yourdb].[dbo].[OrganisationLocations]'

order by index_advantage desc
option (recompile);
Related Question