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 (
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.
- I have a table called
OrganisationLocationswith primary key is
ID int IDENTITY(1,1)and a foreign key
OrganisationID int NOT NULL.
- The table is clustered as
Organisation, IDas they are always used together and it is better to group entries by Organisation than by ID
- I nearly always join to the
OrganisationLocationswithout 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.