April 15, 2010 at 12:32 am
We have a table with an index on column "A". We encountered a perfomance issue and the Database Tuning Advisor recommended we create a new index
on Column "A" (already indexed) and column "B" (not indexed). We followed its advise and the query now performs much better but, as column A was already indexed,
did we really need to include it in the new index (as suggested by the DTA)?
TIA,
Barkingdog
April 15, 2010 at 12:42 am
Most chances are that when you added the other column to the index, the index was a covering index. A covering index is an index that is composed of all the columns that are participating in the query. When you have a covering index, once the value that you are looking for was found, there is no need to go to the table itself, because the index contains all the needed information.
Another option is that the index with once column was not selective but when you added the new column, the index was selective. For example – suppose that your table has 100000 rows. If column A has only 10 unique values, then it won’t be used in the query. If you add another column that has about 98000 unique values, then the index becomes selective and it will be used.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
April 15, 2010 at 12:58 am
Adi,
You wrote
>>>
Most chances are that when you added the other column to the index, the index was a covering index. A covering index is an index that is composed of all the columns that are participating in the query. When you have a covering index, once the value that you are looking for was found, there is no need to go to the table itself, because the index contains all the needed information.
>>>
You are right. It was a covering index so you are saying that once the value is "found" in the index there is no need for the query to even access (scan) the table to look for matches. The query can go directly to the row in the table. Right?
Barkingdog
April 15, 2010 at 1:30 am
Here's another twist from http://www.sql-server-performance.com/tips/covering_indexes_p1.aspx
>>>
If you want to create a covering index, if possible, try to piggyback on already existing indexes that exist for the table. For example, say you need a covering index for columns c1 and c3. If you already have an index on column c1, instead of creating a new covering index, change the index on c1 to be a composite index on c1 and c3. Anytime you can prevent indexing the same column more than once, the less I/O overhead SQL Server will experience, and the faster performance will be
>>>
So they are saying I should modify the orignal index, on column A to include both A and B not create a new index. Any comments on this idea?
Barkingdog
April 15, 2010 at 1:47 am
Barkingdog (4/15/2010)
We have a table with an index on column "A". We encountered a perfomance issue and the Database Tuning Advisor recommended we create a new indexon Column "A" (already indexed) and column "B" (not indexed). We followed its advise and the query now performs much better but, as column A was already indexed,
did we really need to include it in the new index (as suggested by the DTA)?
TIA,
Barkingdog
Can you post the query, Execution Plan & Index structure details.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
April 15, 2010 at 4:21 am
Barkingdog (4/15/2010)
Adi,You wrote
>>>
Most chances are that when you added the other column to the index, the index was a covering index. A covering index is an index that is composed of all the columns that are participating in the query. When you have a covering index, once the value that you are looking for was found, there is no need to go to the table itself, because the index contains all the needed information.
>>>
You are right. It was a covering index so you are saying that once the value is "found" in the index there is no need for the query to even access (scan) the table to look for matches. The query can go directly to the row in the table. Right?
Barkingdog
Yes you are correct. This is called covering index. You can find many articles about it if you’ll look for it in Google or another search engine.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply