1

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

  • 3
    I bet you are looking at the plan in SSMS. There is a missing index involved with one of the statements in the batch, but it is repeated for many. This is a bug I reported five years ago, that hasn't been fixed yet. My suggestion: use SQL Sentry Plan Explorer instead. Disclaimer: I work for SQL Sentry. – Aaron Bertrand Nov 19 '14 at 14:08
  • 2
    Performance itself is almost certainly due to different SET settings. This is a common issue that comes up often and is best explained by Erland Sommarskog. – Aaron Bertrand Nov 19 '14 at 14:11
  • The first case was taken from SSMS, the second (WebApplication cached plan) from the query I've included in the opening post. Thanks for explaining the "repeated for many" issue - I wasn't aware of that. So that explains why it is repeated. I guess I need to provide more details to know why that suggestion is there in the first place. – Chris Walsh Nov 19 '14 at 14:11
  • I don't mean taken from SSMS, I mean viewing in SSMS (regardless of source). And yes, to understand the recommendation, we'd need to see the query, plan, etc. – Aaron Bertrand Nov 19 '14 at 14:11
  • @Aaron, yes, you are correct. I will look at your software - it sounds good. And thanks for the Erland Sommarskog article. I did have a good read through that about six months ago but it is heavy going. I will focus on the SET statements. Cheers. :-) – Chris Walsh Nov 19 '14 at 14:13
1

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);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.