can't force index

Hi,
I have a table called "users"
basically, it has a column called "user_id",
and there is a clustered index called "user_id_index",
so everytimes I try to execute
select * from users
I look at the execution plan, it's always used user_id_index, which is
fine.
however, I added another index called "user_name_index"
then I execute:
select * from users (index=user_name_index)
now I look at the execution plan, it is still using "user_id_index" ,
obviously,
it didn't force the index. what happen? why sql server ignore my index
hint?
how do you solve this problem?Recall that the clustered index holds the data as well as the key
columns. Unless the index "user_name_index" contains all the columns of
the table the server still has to read the clustered index to retrieve
the data. You should see a bookmark lookup on the cluster key.
Why do you see this as a problem? Why are you attempting to force an
index hint? Why are you using SELECT *, which potentially hinders index
optimization and shouldn't be used at all in production code.
David Portas
SQL Server MVP
--|||Hi
An not supplying a WHERE clause results in SQL server doing a table scan so
indexes may not be used (why use an index when you are returning all the
data?).
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108762629.179991.114690@.o13g2000cwo.googlegroups.com...
> Recall that the clustered index holds the data as well as the key
> columns. Unless the index "user_name_index" contains all the columns of
> the table the server still has to read the clustered index to retrieve
> the data. You should see a bookmark lookup on the cluster key.
> Why do you see this as a problem? Why are you attempting to force an
> index hint? Why are you using SELECT *, which potentially hinders index
> optimization and shouldn't be used at all in production code.
> --
> David Portas
> SQL Server MVP
> --
>|||user_name_index is used on "user_name" column,
but even when I execute
select user_name from users (index=user_name_index)
where user_name='Joe'
I still see the execution plan is using clustered index "user_id_index".
don't you think it's weird?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108762629.179991.114690@.o13g2000cwo.googlegroups.com...
> Recall that the clustered index holds the data as well as the key
> columns. Unless the index "user_name_index" contains all the columns of
> the table the server still has to read the clustered index to retrieve
> the data. You should see a bookmark lookup on the cluster key.
> Why do you see this as a problem? Why are you attempting to force an
> index hint? Why are you using SELECT *, which potentially hinders index
> optimization and shouldn't be used at all in production code.
> --
> David Portas
> SQL Server MVP
> --
>|||> don't you think it's weird?
No. Did you read the replies from Mike and myself?
If you explain what you want to achieve maybe we can help you better.
If you just want to understand indexes and hints then I recommend Kalen
Delaney's book "Inside SQL Server". Hints are an advanced feature and
should be used only when essential and with an understanding of their
effects on the query plan.
David Portas
SQL Server MVP
--|||> select user_name from users (index=user_name_index)
> where user_name='Joe'
> I still see the execution plan is using clustered index
"user_id_index".
Maybe you could post some runnable code to reproduce that behaviour
(CREATE..., INSERT..., SELECT...). I don't see that myself. I get
user_name_index used with or without the hint. Also tell us your
edition, version and SP level.
David Portas
SQL Server MVP
--|||1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
table use? You can check this with "sp_spaceused".
2) What version of SQL-Server are you using
3) Please post (simplified) DDL and some sample rows, and preferably a
script to reproduce the behavior.
Gert-Jan
Britney wrote:
> user_name_index is used on "user_name" column,
> but even when I execute
> select user_name from users (index=user_name_index)
> where user_name='Joe'
> I still see the execution plan is using clustered index "user_id_index".
> don't you think it's weird?|||Oh my god, sorry guys.
I was wrong about it, "users" is not a table, but it's a view.
I just found out.
In case you ask me why i'm doing this stupid view:
the reason we create a view for this is because I want to do snapshot
isolation for read and write.
if there are data coming in to [2users] table, then I alter view to use
[1users] table. So users table have 2 tables:
Read and write. This way I don't worry about locking.
CREATE VIEW users
AS
select * from [2users]
--
Now We know what is happening...
IF I select from actual table ,
select user_name from [2users] (index=user_name_index)
where user_name='Joe'
I see that it 's using forced index.
I guess view doesn't work correctly for some reason.
---
sp_spaceused [2users]
result:
name rows reserved data index_size unused
[2users] 41892 13952 KB 6616 KB 7144 KB 192 KB
---
select @.@.version
result:
Microsoft SQL Server 2000 - 8.00.780 (Intel X86) Mar 3 2003 10:28:28
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
----

> 1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
> table use? You can check this with "sp_spaceused".
>
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:42166EA6.EA6900C3@.toomuchspamalready.nl...
> 1) How big is the table? How many pages (blocks of 8 Kilobyte) does the
> table use? You can check this with "sp_spaceused".
> 2) What version of SQL-Server are you using
> 3) Please post (simplified) DDL and some sample rows, and preferably a
> script to reproduce the behavior.
> Gert-Jan
>
> Britney wrote:

Can't delete full text catalog on clustered sql serer 2000

Basically SQL Server has allowed me to create a full text catalog on a drive
which is not part of the cluster's shared resources, and I believe doesn't
exist either (I not familiar with clustering at all).
Now the problem is that it now won't let me remove this full text catalog,
giving me the error that it can't *create* a full text catalog on a disk
which isn't part of the cluster's shared resources. How can I delete it?
Ian.Ian,
Most un-usual! As by design, you should be only able to create FT Catalogs
on the shard drive in a clustered environment. How did you do this? If it
can be reproduced, it should be filed as a bug. Have you tried the
following:
use <your_database_name>
go
exec sp_fulltext_catalog '<Your_FT_Catalog_Name>','drop'
go
If not, try it. If it fails, please post the error that it returns. If it
does fail, you may have to try:
EXEC sp_fulltext_service 'clean_up'
if this too fails, I have some code I can email you directly code (the code
is not for the faint-of-heart) that can get you out of this "catch-22"
situation.
Thanks,
John
--
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Ian" <Ixpah@.newsgroup.nospam> wrote in message
news:#rqy8sFBFHA.3588@.TK2MSFTNGP11.phx.gbl...
> Basically SQL Server has allowed me to create a full text catalog on a
drive
> which is not part of the cluster's shared resources, and I believe doesn't
> exist either (I not familiar with clustering at all).
> Now the problem is that it now won't let me remove this full text catalog,
> giving me the error that it can't *create* a full text catalog on a disk
> which isn't part of the cluster's shared resources. How can I delete it?
> Ian.
>|||Hello Ian,
To understsand the issue better, I'd like to know the exact steps you
create and delete the full text catalog. What is the exact error message
you encountered?
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
| From: "Ian" <Ixpah@.newsgroup.nospam>
| Subject: Can't delete full text catalog on clustered sql serer 2000
| Date: Thu, 27 Jan 2005 10:30:33 -0000
| Lines: 11
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <#rqy8sFBFHA.3588@.TK2MSFTNGP11.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.programming
| NNTP-Posting-Host: host81-137-218-140.in-addr.btopenworld.com
81.137.218.140
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTNGXS01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP08
.phx.gbl!TK2MSFTNGP11.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.programming:499844
| X-Tomcat-NG: microsoft.public.sqlserver.programming
|
| Basically SQL Server has allowed me to create a full text catalog on a
drive
| which is not part of the cluster's shared resources, and I believe
doesn't
| exist either (I not familiar with clustering at all).
|
| Now the problem is that it now won't let me remove this full text
catalog,
| giving me the error that it can't *create* a full text catalog on a disk
| which isn't part of the cluster's shared resources. How can I delete it?
|
| Ian.
|
|
||||I used a script which was very similiar (if not the same) as this:
exec sp_fulltext_database 'enable'
exec sp_fulltext_catalog 'popall_ft','create'
exec sp_fulltext_table 'dbo. articles','create','popall_ft','pk__arti
cles'
exec sp_fulltext_column 'dbo.articles','comment','add'
exec sp_fulltext_table 'dbo.articles','activate'
exec sp_fulltext_table 'dbo.articles','start_change_tracking'
exec sp_fulltext_table 'dbo. articles','start_background_updateindex'
Unfortunately I tried disabling/enabling full text search in order to try
and fix this problem and now can't re-enable it due to the following error:
Server: Msg 7627, Level 16, State 1, Procedure sp_fulltext_database, Line 61
Full-text catalog in directory 'e:\mssql\ftdata' for clustered server cannot
be created. Only directories on a disk in the cluster group of the server
can be used.
Ian.
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:z8KjaoOBFHA.2732@.cpmsftngxa10.phx.gbl...
> Hello Ian,
> To understsand the issue better, I'd like to know the exact steps you
> create and delete the full text catalog. What is the exact error message
> you encountered?
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
>
> --
> | From: "Ian" <Ixpah@.newsgroup.nospam>
> | Subject: Can't delete full text catalog on clustered sql serer 2000
> | Date: Thu, 27 Jan 2005 10:30:33 -0000
> | Lines: 11
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
> | X-RFC2646: Format=Flowed; Original
> | Message-ID: <#rqy8sFBFHA.3588@.TK2MSFTNGP11.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.programming
> | NNTP-Posting-Host: host81-137-218-140.in-addr.btopenworld.com
> 81.137.218.140
> | Path:
> cpmsftngxa10.phx.gbl!TK2MSFTNGXS01.phx.gbl!cpmsftngxa06.phx.gbl!TK2MSFTNGP
08
> phx.gbl!TK2MSFTNGP11.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.programming:499844
> | X-Tomcat-NG: microsoft.public.sqlserver.programming
> |
> | Basically SQL Server has allowed me to create a full text catalog on a
> drive
> | which is not part of the cluster's shared resources, and I believe
> doesn't
> | exist either (I not familiar with clustering at all).
> |
> | Now the problem is that it now won't let me remove this full text
> catalog,
> | giving me the error that it can't *create* a full text catalog on a disk
> | which isn't part of the cluster's shared resources. How can I delete it?
> |
> | Ian.
> |
> |
> |
>|||Hi lan,
Thanks for your posting!
Peter Yang is OOF and I am his backup!
From your descriptions, I understood that you would like to create a
full-text index catalog, which is on a disk on which the SQL Server
resource is not dependant. In SQL Server 2000 virtual server instance, you
will have to add the disk as a dependency to the SQL Server resource in the
Cluster Administrator.
Refer to the Knowledge Base article for more information and how to add the
disk as a dependency
INF: Creating Databases or Changing Disk File Locations on a Shared Cluster
Drive on Which SQL Server 2000 was not Originally Installed
http://support.microsoft.com/?id=295732
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a w to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others: https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||I have *already* created a full text catalog on a drive which isn't part of
the cluster and probably doesn't exist either. This is the problem I'm
experiencing: not able to delete it or enable full text search because of
it.
I think I've worked out a way round the problem of re-enabling full text
search for the database by changing the path column in the
sysfulltextcatalogs table to a clustered resource. Hopefully once ft is
re-enabled I can then either delete the catalog or use it as is.
Ian.
"Michael Cheng [MSFT]" <v-mingqc@.online.microsoft.com> wrote in message
news:BrTuYlRDFHA.3744@.cpmsftngxa10.phx.gbl...
> Hi lan,
> Thanks for your posting!
> Peter Yang is OOF and I am his backup!
> From your descriptions, I understood that you would like to create a
> full-text index catalog, which is on a disk on which the SQL Server
> resource is not dependant. In SQL Server 2000 virtual server instance, you
> will have to add the disk as a dependency to the SQL Server resource in
> the
> Cluster Administrator.
> Refer to the Knowledge Base article for more information and how to add
> the
> disk as a dependency
> INF: Creating Databases or Changing Disk File Locations on a Shared
> Cluster
> Drive on Which SQL Server 2000 was not Originally Installed
> http://support.microsoft.com/?id=295732
> Thank you for your patience and corporation. If you have any questions or
> concerns, don't hesitate to let me know. We are always here to be of
> assistance!
>
> Sincerely yours,
> Michael Cheng
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ========================================
=============
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a w to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/te...erview/40010469
> Others: https://partner.microsoft.com/US/te...upportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/defaul...rnational.aspx.
> ========================================
=============
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>