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_Unitfrom LabAnalyteData as a inner join LabSampleData as s on s.IDinAccess= a.IDinAccessGroup By s.[year], a.AnalyteCode, a.Analyte, a.ConcentrationLevelUnitOrder By a.Analyte
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
This doesn't work maybe because I am using Express version.