SQL Server Performance Forum – Threads Archive

How much to index?

I am relatively new to tuning SQL Server databasesa and I am trying to get a handle on indeces. I have a basic select on a table: select col1, col2, col3 from tab1 where col3 > 100 I setup an index on col3 and saw no improvement (in the query analyzer execution plan). When I setup 2 more indeces on col1 and col2 I saw massive improvement. The execution plan looked like it was using each of the indeces to search the whole table and then merging the results with a "Hash match/Inner join". Why is it searching on col1 or col2? I would think it just needs to search on col3 and return the associated entries in that row. The query text (in the execution plan) reads something like: select [col1]=[col1], [col2]=[col2], [col3]=[col3] from tab1 where [col3]>@1

---

It is searching on col3, but when the other columns are indexed it gets the data from the index rather than from the table. Bambola.

---

Try creating a composite index on col1, col2 and col3 keeping col3 as first column. All the data will be found in one index only and so no other indexes will be required. This kind of index is called covering index. But note creation of this index dependsa lot on the queries executed against this table. Gaurav

---

What if I want a "select *"? Do I have to include every column in the index. This doesn’t make a lot of sense, I thought you only needed an index on items in the where or group clauses. -A Freeman

---

"Select *" is generally not recommended condition for queries as first of all all the columns are genearlly not required and also this make break your application when some changes in the table structure are made due to some requirement. So it is always advisable to include column names in query. I agree that index is required for WHERE / GROUP BY clauses. However I would like to explain this- For a clustered index –
All the data will be present in the index page (which is the actual data page in a clustered index) and so u’ll not have to include any more columns in the index once you have located the row. In this case you can create a clustered index just on col1. For a non-clustered index –
After the record has been identified, if all the columns required in the query are not a part of the index, an additional bookmark lookup is required for finding the data page so as to get the rest if the records. In this case it is advisable to include other columns required in the query in the index defination. But again, this does not men we create a index with many columns. As this will incerase the index size and the query optimizer may well decide not to use it. So of the index size is not going to be too large, create a non-clustered index on col1, col2 and col3 making col3 as first column in index as this is reqired to searcch the record. Gaurav

---

]]>