Index problem

I have a table, which contains address information,
have non-clustered index on phone no, search on phone
number was very quick, now users requested that search on
address be possible too, I created non-clustered index on
address which speeds up search by address but search by
phone number is extremely slow, query plan shows that
optimizer is using this new index instead of index on
phone number even during phone search, I updated stats
with full scan and still same result.
Dbcc show_statistics shows better selectivity for index on
address than phone number.
It is a very complicated dynamic query which I cannot
force optimizer to use specific index.
Any help will be appreciatedI would play with "set statistics IO on" to see your logical reads.
How are they asking for the address and phone number? (SQL syntax)
When they only ask for phone number how do they ask this? For example, if I
said
select phonenumber from table where street = 'main'
SQL would not use your nonclustered index on phone(unless you had a
clustered index on street)
You might consider covering non-clustered indexes.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sam" <sam.moayedi@.moneymanagement.org> wrote in message
news:044b01c39350$a27e2a70$a401280a@.phx.gbl...
> I have a table, which contains address information,
> have non-clustered index on phone no, search on phone
> number was very quick, now users requested that search on
> address be possible too, I created non-clustered index on
> address which speeds up search by address but search by
> phone number is extremely slow, query plan shows that
> optimizer is using this new index instead of index on
> phone number even during phone search, I updated stats
> with full scan and still same result.
> Dbcc show_statistics shows better selectivity for index on
> address than phone number.
> It is a very complicated dynamic query which I cannot
> force optimizer to use specific index.
> Any help will be appreciated
>|||The sp goes something like this
select * from table where phoneno='xxxxxxxxx'
It is more complicated than|||Then your NC index on phone number definitely won't help, it would make no
sense to go out and find phone numbers only to redirect back to the table,
did you say you had a clustered index? Again, I would play with my indexes
using "set statistics IO on"
If you were saying
select phone_no from table where phone_no = 'blah'
Then your NCI would act as a covering index and SQL would naturally go to
the index and not touch the table.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sam" <sam.moayedi@.moneymanagement.org> wrote in message
news:05f701c3935f$d57f1ec0$a401280a@.phx.gbl...
> The sp goes something like this
> select * from table where phoneno='xxxxxxxxx'
> It is more complicated than|||Sam
Have you checked for fragmentation on the phone number
index? Although it mostly tends to affect clustered
indexes, I have seen very similar problems with fragmented
non-clustered indexes.
Regards
Johnsql