missing indexes dmv and selectivity
When the missing indexes dmv suggests an index, it gives the equality and inequality columns. Has it already ordered the columns based on the selectivity?
thanks
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Yes, if a particular index involves more that one column like
Index1 - col1, col2
Then this index can be used by the following two queries which has WHERE conditions
* Where col1 = ?
* Where col2 = ? and col1 = ?
If you have the order of your index as col2, col1, then this index would not be used by first query.
Hope this clarifies.
Index1 - col1, col2
Then this index can be used by the following two queries which has WHERE conditions
* Where col1 = ?
* Where col2 = ? and col1 = ?
If you have the order of your index as col2, col1, then this index would not be used by first query.
Hope this clarifies.
ASKER
*Do the DMV does not check the selectivity for us; we have to, manually?
*let's say the index is col2, col1 order. But if selectivity is 0.6, 0.8 respectively, we should ask the programmer to rearrange his query?
*let's say the index is col2, col1 order. But if selectivity is 0.6, 0.8 respectively, we should ask the programmer to rearrange his query?
>> *Do the DMV does not check the selectivity for us; we have to, manually?
In other words Yes, based upon the selectivity it suggests you the order of the columns based upon the queries till now executed on that table
>> *let's say the index is col2, col1 order. But if selectivity is 0.6, 0.8 respectively, we should ask the programmer to rearrange his query?
Can you explain more on this..
In other words Yes, based upon the selectivity it suggests you the order of the columns based upon the queries till now executed on that table
>> *let's say the index is col2, col1 order. But if selectivity is 0.6, 0.8 respectively, we should ask the programmer to rearrange his query?
Can you explain more on this..
ASKER
Thanks.
I meant the index is based on 2 keys: col2, col1. The selectivity in the columns are 60% and 80% respectively for the 2 columns. Let's assume the query uses "Where col2 = ? and col1 = ?"- but see that the selectivity for col1 is better than col2. Is this OK, or should the programmer try to change the query to "Where col1 = ? and col2 = ?" and also change the index to col1,col2 order, because of the greater selectivity in col1.
I meant the index is based on 2 keys: col2, col1. The selectivity in the columns are 60% and 80% respectively for the 2 columns. Let's assume the query uses "Where col2 = ? and col1 = ?"- but see that the selectivity for col1 is better than col2. Is this OK, or should the programmer try to change the query to "Where col1 = ? and col2 = ?" and also change the index to col1,col2 order, because of the greater selectivity in col1.
Then the order should be col1, col2 since col1 has 80% selectivity compared to col2 with 60% selectivity.
>> and also change the index to col1,col2 order, because of the greater selectivity in col1.
Yes..Order in WHERE clause doesn't makes sense as SQL Server would try to use the index in an optimal way..
That's where optimizer plays a major role in the selection of indexes.
>> and also change the index to col1,col2 order, because of the greater selectivity in col1.
Yes..Order in WHERE clause doesn't makes sense as SQL Server would try to use the index in an optimal way..
That's where optimizer plays a major role in the selection of indexes.
ASKER
OK. So 'Where col2 = ? and col1 = ?' can stay the same, but the index should be dropped and redone with the correct order, right?
but if the DMV suggested this index, it would have already checked the selectivity, and put the col1 as the main key, right?
but if the DMV suggested this index, it would have already checked the selectivity, and put the col1 as the main key, right?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much for your guidance.
Welcome..
ASKER
Is it recommending us to figure out the selectivity?