when will index be used ? - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

when will index be used ?

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-02 : 20:39:22

Hi friends
am just wondering when will an index be used when running sql stmts ?
i found this article that says
"If there's an index on the state column but most of the authors came from California, there's no point to using an index. "

http://www.samspublishing.com/library/content.asp?b=STY_Sql_Server_7&seqNum=145&rl=1

my question is how does sql server know most of the values are same?
the reason this question came up bcoz i created a non clustered index on a column and when a run a sql stmt with WHERE condition on this column, it doing index scan but i was expecting to see "index seek"

Cheers

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-03 : 01:00:20

quote:
"If there's an index on the state column but most of the authors came from California, there's no point to using an index. "

It simply means that if most of the author records contains CA as state, there is no point in using Index to traverse and locate records as it will cause more overhead than simply reading entire table.

quote:
my question is how does sql server know most of the values are same?

There is something called as distribution statistics which SQL server keeps for every index it creates. This distribution statistics describe the distribution of key values in the index and this statistics is used by Query optimizer in deciding which index to use.

quote:
the reason this question came up bcoz i created a non clustered index on a column and when a run a sql stmt with WHERE condition on this column, it doing index scan but i was expecting to see "index seek"

Simply creating Index and including that column in where condition is not a sufficient information to decide why you are getting index seek. It is also important what is your WHERE condition. For example, if you have index on column (suppose dt_of_birth), but your where condition is like this:

where year(dt_of_birth) = 1967

Index seek will not happen because you are wrapping your column inside a function. Likewise, there are many other criterias. So, please post your query here, so that we can guide you more precisely.

Harsh Athalye
India.
"Nothing is Impossible"

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-03 : 15:43:28

Hi Harsha
thanks for ur post.
btw am not using any functions in where clause. it is as simple as following
select * from mytable where indexed_column = 21

am just wondering whether index usage also depends the no of records in a table ?
at the moment the table i tested has only 10 records but it'll grow much bigger in no time.
Thanks for ur help.

Cheers

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-03 : 15:53:47

In DB2, anything with less the 8 4k pages will cause a scan regardless of the indexes....mostly because at a minimum it will read in 32k chunck at 1 time. I don't if SQL Server does this, but the SHOWPLAN will show the index being used, whether it uses it or not, I don no know. Nor do I think it matters because it will appear to be using the index anyway.

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-03 : 15:57:27

And again I should read the posts.

If the cardinality of the data is very low, you will incur the scan, because it will be the least expensive option. Do you have anything else to go by?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-03 : 17:42:07

As harsh pointed out SQL Server keeps distribution stats for an index.

For example (an extreme example to illustrate the point

) if you have an index on a state column, it is possible that you have one row with IL, one with NY, one with MI and 1000 rows with CA in that column.

Now it would be pointless for SQL Server to do an index seek for a row with CA, but it would make lots of sense to seek for IL - and SQL Server's distribution stats on the index will allow the optimize to make that choice!

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-03 : 17:47:23

thanks for that . that makes sense.

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-03 : 17:48:46

btw just wondering is it possible to read these "distribution stats" some how ?

Cheers

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-03 : 18:47:28

DBCC SHOW_STATISTICS('myTable', 'myIndex')

Substitute your table and index names where appropriate. You may also want to run UPDATE STATISTICS on your table first. Books Online has more details on both these commands.

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-03 : 18:54:15

wonderful,thats useful info "robvolk"
Thank you very much

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-03 : 19:21:03

btw do we need to update these statistics manually once in awhile as part of maintenance or they are updated by sql server automatically ?

Cheers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-10-03 : 19:26:14

You need to run update statistics regularly plus use the auto update option.

Tara Kizer

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-03 : 20:38:34

thanks for that tara.
am still trying get my head around these concepts. i understand that sql server uses stastics whether to use an index or not . am just wondering whether you guys can point me some resource that explains this technical info.

i want to know whether am making good choice when creating index on some column ?
Thanks for ur help.

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-03 : 20:56:29

the reason i ask , recently we added some non clustered indexes on some foreign key columns as we are using them frequently in our code. so we thought adding indexes will speed up query loading.
if these indexes never going to be used then they dont need to be created in the first place.
so i want to make informed choice when creating indexes .
Thanks

Cheers

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-04 : 01:17:03

quote:
Originally posted by rajani

i want to know whether am making good choice when creating index on some column ?

The best place to start with is Index Tuning Wizard which will show you Index Usage and recommend which column(s) should be indexed. But to gain most from this, you need to generate workload file which should ideally cover all the real-time queries which are executed against database on day-to-day basis.

For more info, check this link:

[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/tunesql.mspx[/url]

Harsh Athalye
India.
"Nothing is Impossible"

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2006-10-04 : 15:45:27

thanks Harsh
will give that a go

Cheers

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-10-04 : 17:57:21