asked on Client Statistics Explanations...
The attached image shows Execution Plan/ Client Statistics for this query:
Maybe: CREATE INDEX index_Analyte_LabAnalyteDa
But what if I add the following index; is there still a need to add additional indexes?
CREATE INDEX index_FK_IDinAccess_LabAna
Q2: On the Client Statistics, what do Trial 1, Trial 2, and Trial 3 mean?
Client-Statistics.png
select distinct AnalyteCode, Analyte
from LabAnalyteData
Order By AnalyteCode
Maybe: CREATE INDEX index_Analyte_LabAnalyteDa
But what if I add the following index; is there still a need to add additional indexes?
CREATE INDEX index_FK_IDinAccess_LabAna
Q2: On the Client Statistics, what do Trial 1, Trial 2, and Trial 3 mean?
Client-Statistics.png
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008
Last Comment
Vitor Montalvão
ASKER
Vitor,
But what if I add the following index; is there still a need to add additional indexes?
CREATE INDEX index_FK_IDinAccess_LabAna
Do we still need:
CREATE INDEX index_Analyte_LabAnalyteDa
But what if I add the following index; is there still a need to add additional indexes?
CREATE INDEX index_FK_IDinAccess_LabAna
Do we still need:
CREATE INDEX index_Analyte_LabAnalyteDa
You should add only the strictly necessary columns. Why add columns that aren't used by the query?
Also, I'm commenting only based on the query you posted. I don't know the schema of your table and also don't know the rest of the queries that table are part of.
Also, I'm commenting only based on the query you posted. I don't know the schema of your table and also don't know the rest of the queries that table are part of.
ASKER
To better state what I have. Assuming I already have:
CREATE INDEX index_FK_IDinAccess_LabAna
Is it still necessary to have:
CREATE INDEX index_Analyte_LabAnalyteDa
CREATE INDEX index_FK_IDinAccess_LabAna
Is it still necessary to have:
CREATE INDEX index_Analyte_LabAnalyteDa
ASKER CERTIFIED SOLUTION
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.
what i think is, if you are not providing any criteria in where clause.
Query execution plan will use clustered index by default because at the it need to get all the row.
So it's better to scan clustered index.
And in your case index_FK_IDinAccess_LabAna
Even if you create CREATE INDEX index_Analyte_LabAnalyteDa
Vitor Montalvão please have some comment on this.
Query execution plan will use clustered index by default because at the it need to get all the row.
So it's better to scan clustered index.
And in your case index_FK_IDinAccess_LabAna
Even if you create CREATE INDEX index_Analyte_LabAnalyteDa
Vitor Montalvão please have some comment on this.
It depends. The engine will always calculate and chose the operation with less cost. If you increase exponentially the number of records the engine may chose another index. Usually with very few records a scan is chosen instead of seek and this could be the case.
Means how many times you ran the query. Trial 2 used the cache since data still accessible from Trial 1.