Avatar of Mike Eghtebas
Mike Eghtebas
Flag 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);

or:

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?
IndexAnalizer-1.png
Microsoft SQL ServerMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Mike Eghtebas

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Vitor Montalvão

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Mike Eghtebas

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

This doesn't work maybe because I am using Express version.
Vitor Montalvão

Can't test on Express edition because I don't have one.
Did you try right-click on it to see the available options?
Mike Eghtebas

ASKER
Yes I did. It must be the difference in Express version.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck