December 26, 2013 at 1:33 pm
If I create a covered index, analyzing a specific query
like
Select F3, F4, F5 from Table1 Where F1=@F1 and F2=@F2
Index created as follows:
Create index IXc_Table1__F1F2
on Table1 (F1, F2)
INCLUDE (F3, F4, F5)
My question is: will this index be used only by this specific query, or can other queries start using it as well?
By other queries I mean queries like
Select F3, F4 from Table1 Where F1=@F1 and F2=@F2
or
Select F3, F4, F5 from Table1 Where F1=@F1 ? (queries with different number of fields in SELECT part and different WHERE clause predicates
but which are still partially contain fields used in both Index and Include statements?
Likes to play Chess
December 26, 2013 at 1:38 pm
The queries you posted should be able to use it even for a seek operation.
If you had Select F3, F4, F5 from Table1 Where F2=@F2, then SQL Server could probably use it with an index scan.
It depends on other things, but you should test, test and test again.
December 27, 2013 at 8:18 am
vladisaev@hotmail.com (12/26/2013)
If I create a covered index, analyzing a specific querylike
Select F3, F4, F5 from Table1 Where F1=@F1 and F2=@F2
Index created as follows:
Create index IXc_Table1__F1F2
on Table1 (F1, F2)
INCLUDE (F3, F4, F5)
My question is: will this index be used only by this specific query, or can other queries start using it as well?
By other queries I mean queries like
Select F3, F4 from Table1 Where F1=@F1 and F2=@F2
or
Select F3, F4, F5 from Table1 Where F1=@F1 ? (queries with different number of fields in SELECT part and different WHERE clause predicates
but which are still partially contain fields used in both Index and Include statements?
The general answer is yes, the query optimizer will make use of any index that it finds cost effective for any particular query. As Luis suggested above, your example index with a key of (F1, F2) is likely to be used for just about any query that refers to F1 in a WHERE clause condition. Even if the index is not covering for a particular query but the key provides a cost-effective means of identifying rows that satisfy the WHERE clause conditions, the optimizer may use the index to identify those rows and a key lookup to get the non-covered data from the underlying table.
Jason Wolfkill
December 27, 2013 at 8:25 am
thank you for your feedback/insight.
So when we say that Covered Index is created for one particular SQL statement it is a relative term, right? We really mean that it is best suited for that query but may be used by other queries should Optimizer decide so ?
Likes to play Chess
December 27, 2013 at 8:32 am
vladisaev@hotmail.com (12/27/2013)
thank you for your feedback/insight.So when we say that Covered Index is created for one particular SQL statement it is a relative term, right? We really mean that it is best suited for that query but may be used by other queries should Optimizer decide so ?
That's correct. Covering indexes are usually created to benefit a particular query, but once they're released into the wild, they're fair game for any query that can benefit from them. AFAIK, there's no way to restrict an index to use by one query only. You can restrict a query to use only a particular index with hints, although this should be done only in very specific cases where it's proven to help, but not the other way around.
Jason Wolfkill
December 31, 2013 at 10:02 am
I'd go further and try to make the covering index the best it can be.
1. Run the missing index DM queries and discover as many index suggestions as fit the columns, and INCLUDE to cover the most highly trafficked queries. However, it is useless to INCLUDE most or all of the table's columns into the index.
2. Analyze the uniqueness of each of the columns and order them into most unique first down to least unique. This may seem counterintuitive, but you are inviting the query optimizer to make things faster. For example, if your query is based on company ID, Branch ID, order ID, the proper index column order is probably ORDER ID, BRANCH ID, COMPANY ID. This will reduce many index scans to seeks.
Thanks
John.
December 31, 2013 at 10:52 am
If you have two fields in your where clause, in this case F1 and F2, then it would take advantage of an index that has the key fields (just F1, just F2, F1 then F2, or F2 then F1), with the indexes having both key fields being better at finding the values you're looking for faster. As for what order you do them in there is conventional logic that says to pick the field that will narrow the results down the most as the first key field, but I don't like doing it that way. Instead I pick the field that more queries will filter the data by and put that as the first key field, which is maximizing the reusability of the index instead of focusing on getting the query in question to run 1 ms quicker.
For your question, anything that has the first key field of an index in the WHERE or JOIN clauses may take advantage of doing a seek against that index. Even if they don't, they may be able to run more efficiently scanning the 5 columns in this index instead of scanning the wider clustered index, especially if this index is able to cover the other queries or at least filter down the results drastically.
Just remember to keep in mind how the data in that table is used and make the indexes that more queries can take advantage of over what could make one query run perfect (unless, of course, that one query runs 10 times a second).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply