Link to home
Create AccountLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

asked on

Execution Plan/ Client Statistics

The Execution Plan/ Client Statistics for this query prior to indexing is shown on the attached image:
select s.[year], a.AnalyteCode, a.Analyte
, Min(a.ConcentrationLevel) as min_Concentration_Level
, Max(a.ConcentrationLevel) as max_Concentration_Level
, a.ConcentrationLevelUnit as Concentration_Unit
from LabAnalyteData as a inner join 
LabSampleData as s on s.IDinAccess= a.IDinAccess
Group By s.[year], a.AnalyteCode, a.Analyte, a.ConcentrationLevelUnit
Order By a.Analyte

Open in new window

On this image, under Execution Plan tab, there is indexing recommendation in green.
Question 1: Is there a way to copy and paste the string of this recommendation?

The list of missing non-clustered indexes are:
- a.IDinAccess  (FK)
- Include a.AnalyteCode, a.Analyte, a.ConcentrationLevelUnit

Question 2: What exactly does the above recommendation mean? Which of the following it is recommending:

CREATE INDEX index_FK_IDinAccess_LabAnalyteData ONLabAnalyteData (IDinAccess);    
CREATE INDEX index_FK_Fields_LabAnalyteData ONLabAnalyteData (AnalyteCode, Analyte, ConcentrationLevelUnit);


CREATE INDEX index_FK_IDinAccess_LabAnalyteData ONLabAnalyteData (IDinAccess, AnalyteCode, Analyte, ConcentrationLevelUnit);

Question 3: What red flags do you see on the Execution Plan/ Client Statistics tabs?
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Mike Eghtebas


re:> Double click on the recommendation index (green text).

This doesn't work maybe because I am using Express version.
Can't test on Express edition because I don't have one.
Did you try right-click on it to see the available options?
Yes I did. It must be the difference in Express version.