Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dineshrajan_it
Posting Yak Master
217 Posts |
 Posted - 2009-01-16 : 00:59:08
|
| Hi, I checked for index efficiency in one of my tables. when i used the query sys.dm_db_index_usage_stats for this table, i found out that this table has more scans than seeks and seeks seem to be too little. what is best way to overcome those scans. |
|
|
thiyagu_rind
Starting Member
46 Posts |
Posted - 2009-01-16 : 01:08:36
|
| Hi Dinesh(Third Eye) What is the condition you used in the where condition. Are you using any Non index columns in the where condition? Index Seek will happen when the density of the rows will always low.RegardsThiyagarajanwww.sqlhunt.blogspot.com |
 |
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-01-16 : 01:13:19
|
| Hello Sir, Yes Ex-Third eye employee.Fine? coming back to my post Yes there are situations where non-index columns are used instead of indexed columns. In this scenario, i have a table(4 cols). primary key is only clustered index used here. and i cant avoid non-index columns in where conditions. any soln? |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-01-16 : 01:14:47
|
| Hello Sir, Yes Ex-Third eye employee.Fine? coming back to my post Yes there are situations where non-index columns are used instead of indexed columns. In this scenario, i have a table(4 cols). primary key is only clustered index used here. and i cant avoid non-index columns in where conditions. any soln? |
|
|
|
thiyagu_rind
Starting Member
46 Posts |
Posted - 2009-01-16 : 01:38:24
|
| Dear Dinesh, Index Seek will happen only when the Where condition clause is pointing the values. Say for example where empid = 4, where empid in(4,6) i hope. So you better create non clustered index for the columns which are not having index and include them as covering index.Use INCLUDE Keyword which is available for non clustered index in 2005.RegardsThiyagarajanwww.sqlhunt.blogspot.com |
|
|
|
dineshrajan_it
Posting Yak Master
217 Posts |
Posted - 2009-01-16 : 02:18:15
|
| with ur guidance, i have included covering indexes for 2 more columns. now the index scan has drastically reduced. but i have not used index for one column. so when this column comes under where clause it hits index scan. Is it advisable to use covering index for all columns to reduce index scans or dont mind for columns which are not used often in where clause? |
|
|
|
thiyagu_rind
Starting Member
46 Posts |
Posted - 2009-01-16 : 02:38:30
|
| No, it is advised to used the covering index for the columns which are selected in the select list. Again keep in mind, the Query optimizer will guide your query for index scan or index seek is based on the density of the rows returns by your query.RegardsThiyagarajanwww.sqlhunt.blogspot.com |
|
|
|
|
|
|
|
|
sys.dm_db_index_usage_stats